February 9, 2009 at 2:41 pm
The code posted below is intended to build master-detail records from an EDI flat file.
The final (thrid) step below requires 102 seconds to execute, on what seems to be reasonably-sized tables (update target has 34968 records, source table has 2593 records).
I have tried creating indexes on the two tables DDL'd below, this only makes things even worse.
Can soemone suggest a better strategy ?
Regards
[font="Courier New"]
--EDI FILE STRUCTURE
-- 'CG' (CONTROL GROUP) SENDER PARTNERID, EXCHANGE TIME AND DATE
-- 'PO' PURCHASE ORDER NUMBER, DATE, ETC.
-- 'IT' ITEM PART NUMBER, PRICE
-- 'OD' ORDER
20 STORE ID'S 6 DIGITS AND 20 QTIES FOR EACH STORE, 4 DIGITS
-- 'IT' ITEM PART NUMBER, PRICE
-- 'OD' ORDER
20 STORE ID'S 6 DIGITS AND 20 QTIES FOR EACH STORE, 4 DIGITS
-- 'IT' ITEM PART NUMBER, PRICE
-- 'OD' ORDER
20 STORE ID'S 6 DIGITS AND 20 QTIES FOR EACH STORE, 4 DIGITS
--HERE COMES A SECOND P.O. ...
-- 'CG' (CONTROL GROUP) SENDER PARTNERID, EXCHANGE TIME AND DATE
-- 'PO' PURCHASE ORDER NUMBER, DATE, ETC.
-- 'IT' ITEM PART NUMBER, PRICE
-- 'OD' ORDER
20 STORE ID'S 6 DIGITS AND 20 QTIES FOR EACH STORE, 4 DIGITS
-- 'IT' ITEM PART NUMBER, PRICE
-- 'OD' ORDER
20 STORE ID'S 6 DIGITS AND 20 QTIES FOR EACH STORE, 4 DIGITS
-- AND SO ON...
--DDL
CREATE TABLE #T_Store_Item_Quantity -- BREAK DOWN OF "OD" LISTS OF UP TO 20 STORES AND QTIES
(
pk_TmpSIQ_ID int PRIMARY KEY NOT NULL IDENTITY,
fk_OD_ID int NOT NULL,
fk_IT_ID int NOT NULL,
fk_PO_ID int NOT NULL,
fk_CG_ID int NOT NULL,
fk_TmpSalesID int NULL,
ProductID int NULL,
ChainID int NULL,
ShipTo varchar(6),
ShipToID int,
Quantity int,
POHeaderID int NOT NULL DEFAULT -1,
ModuleTypeID varchar(5),
ModuleID int NOT NULL DEFAULT -1,
ModuleMinQty int NOT NULL DEFAULT 0,
ModuleMaxQty int NOT NULL DEFAULT 0,
isQtyInModuleLimits bit NULL,
StoreItemModuleQty int NULL,
fk_StoreItemModuleQty int NULL
)
-- ** NO INDEX CREATED
CREATE TABLE #T_Sales -- CONSOLIDATED LIST OF DISTINCT SALES ORDERS IN EACH CUSTOMER PO
(
pk_TmpSalesID int NOT NULL PRIMARY KEY IDENTITY,
fk_PO_ID int NOT NULL,
fk_CG_ID int NOT NULL,
StoreNo varchar(6) NULL,
CustomerID int NOT NULL DEFAULT -1,
ChainID int NOT NULL DEFAULT -1,
fk_SalCusPOID int NOT NULL DEFAULT -1,
TotalItems int NULL,
AmountOrder money NULL,
ReferenceDate datetime NULL,
POHeaderID int NOT NULL DEFAULT -1,
CustomerPONumber varchar(12) NULL,
ShipToID int NULL,
ShippingTypeID varchar(5),
BillingTypeID varchar(5),
)
-- ** NO INDEX CREATED
-- SPLIT EDI FILE INTO SEPARATE RECRODS
--CODE IS EXECUTED WITHIN A LOOP ON A CURSOR : 5,4 SECONDS
INSERT INTO #T_Store_Item_Quantity
(
fk_OD_ID,
fk_IT_ID,
fk_PO_ID,
fk_CG_ID,
ProductID,
ShipTo,
Quantity
)
VALUES
(
@li_Current_pk_OD_ID,
@li_Current_fk_IT_ID,
@li_Current_fk_PO_ID,
@li_Current_fk_CG_ID,
@li_ProductID,
@ls_ShipTo,
@ls_Quantity
)
SELECT COUNT(*) FROM #T_Store_Item_Quantity -- 34968 RECORDS
-- OBTAIN THE DISTINCT SALES
INSERT INTO #T_Sales
(
fk_PO_ID,
fk_CG_ID,
ChainID,
StoreNo,
TotalItems,
msg,
OwnerID
)
SELECT DISTINCT
fk_PO_ID,
fk_CG_ID,
ChainID,
ShipTo,
SUM(ISNULL(Quantity, 0)),
'',
@ps_OwnerID
FROM #T_Store_Item_Quantity
WHERE fk_PO_ID IN (SELECT pk_PO_ID FROM #T_PO_Lines WHERE fk_SalCusPOID IS NULL)
SELECT COUNT(*) FROM #T_Sales -- 2593 RECORDS
-- ----------------------------------------------------------------
-- MATCH DETAIL TO MASTER *** THIS STEP TAKES 102 SECONDS ... ***
-- ----------------------------------------------------------------
SET NOCOUNT OFF
UPDATE T1 --#T_Store_Item_Quantity
SET fk_TmpSalesID = T2.pk_TmpSalesID,
ShipToID = T2.CustomerID
FROM #T_Sales T2
INNER JOIN #T_Store_Item_Quantity T1
ON T1.fk_PO_ID = T2.fk_PO_ID
AND T1.ShipTo = T2.StoreNo
"(34968 row(s) affected)"
SET NOCOUNT ON[/font]
February 10, 2009 at 8:02 am
Maybe indexes on fk_PO_ID, ShipTo and fk_PO_ID, StoreNo?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 10, 2009 at 8:09 am
Jack,
I already tried creating indexes on columns fk_PO_ID of each table. Instead of the 5000-line SP completing in 3.1 minutes, it was still running after 4.5 minutes, at which point I aborted the run. I did not see any point in trying to add more indexes...
Regards
February 10, 2009 at 8:18 am
I think Jack is talking about creating two composite indexes, each containing two columns.
Looks like his suggestion would help you JOIN critea!
Please could you attach the query plain as a zip file.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 10, 2009 at 8:23 am
Christopher,
This is a 5000 line long SP. I was afraid to even attempt to get the SQL Profiler involved...
I'll give it a try but I am now stuck on something urgent and I can't do this before the end of the day.
As for composite indexes, for some arbitrary (?) reason, I always tried to avoid them like the plague. I'll give it a try tonight.
Regards
February 10, 2009 at 8:26 am
thats understandable.
Ok firstly why would you avoid composite indexes?
It's one of the great tools available for performance on tables with regards to creating useful indexes for the optimizer to create useful query plans.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 10, 2009 at 8:32 am
Christopher,
The composite index had caused problems for me earlier when I was inserting data in a table when for some records the data on the two columns indexed was not always non null. Since I was using the index as a primary key, this did not work out too well.
Also, I must admit to ignorance and also prejudice (for the above mentioned reason) concerning the advantages of a composite index.
In fact, if you would please elaborate a bit more on this subject or recommend a link, I would be glad to learn something.
I'll give a try...
Regards
February 10, 2009 at 8:44 am
indeed if your index clustered then it's prob not a good idea I agree...
To be honest I'm not 100% where I learn't this but I could point to Gail's blog.
She is a forum member here and an MVP as well 🙂
Her explanations are very good.
I recommend that you check out the indexes section of her blog and then perhaps the performance tuning afterwards 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 10, 2009 at 8:48 am
Thanks.
I'll Gail's bl;og a try.
Regards
February 12, 2009 at 9:14 am
OK, this is fixed.
Had nothing to do, just a stupid programming mistake in the loop that parses the OD lines. I am too embarrassed to describe it.
Thanks to all who helped.:blush:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply