INSERT INTO T1 SELECT FROM T2,T3,T1 IS SLOW

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INSERT INTO T1 SELECT FROM T2,T3,T1 IS SLOW

Author

Topic

solai
Starting Member

6 Posts

Posted - 2006-05-09 : 11:13:29

I am asked to fine tune a batch process that runs almost for a day. I have zoomed in to see where the problem occurs. A procedure that is called multiple time uses an insert into from a select statement. select by itself runs fairly ok, but I suspect in conjunction with insert slows it down. The insert into table also participates in the SELECT statement, the author wanted to deter double entry. When the process was running I also noticed that any select statemnt issued was running very slow (I then used WITH NOLOCK). Can anyone shed some light. Thank you.

-solai

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-09 : 12:43:18

Not without more info.
Maybe the select just runs fine when the data is in memory but not when it has to fetch from disk.
Have a look to see if it is scanning rather than using indexes.
How much data is it inserting - can you limit the columns?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 13:10:09

You need to post some code....read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-05-09 : 13:10:28

"I then used WITH NOLOCK"

Sorry, but it always scares the beJesus out of me when I see that as a route to a solution ...

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 13:52:28

NO LOCK? Nooooooooooooooooooooooooooooooo

If you want to prevent "duplicates" you should put a unique constraint on the columns

Post your code and read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

solai
Starting Member

6 Posts

Posted - 2006-05-09 : 14:10:23

Thanks for your replies, here is the code:

INSERT INTO dbo.InvoiceDetailTransaction (
fkCustomerID,
fkRatePlanID,
ActivityDate,
BatchDate,
fkClientCode,
fkMerchantNumber,
TranCode,
TranCodeIndicator,
SuccessfulCount,
FailedCount,
CreatedDate )
(SELECT
BERP.fkCustomerID,
BERP.RatePlanID,
TDT.ActivityDate,
TDT.BatchDate,
TDT.fkClientCode,
TDT.fkMerchantNumber,
TDT.TranCode,
TDT.TranCodeIndicator,
SUM(CASE TDT.ActionTaken
WHEN 'I'
THEN TDT.SuccessfulCount
ELSE TDT.SuccessfulCount * -1
END) ,
SUM(CASE TDT.ActionTaken
WHEN 'I'
THEN TDT.FailedCount
ELSE TDT.FailedCount * -1
END) ,
GETDATE()
FROM
dbo.Invoice I
INNER JOIN
dbo.CustomerRatePlan BERP
ON
(BERP.fkCustomerID = I.fkCustomerID and
BERP.fkDataTypeID = 1 and
BERP.StartDate <= I.InvoiceStartDate and
IsNull(BERP.EndDate, '12-31-2099') >= I.InvoiceEndDate)
INNER JOIN
dbo.CustomerClientCode BECC
ON
(BECC.fkCustomerID = I.fkCustomerID)
INNER JOIN
dbo.StagingTransactions TDT
ON
(TDT.ActivityDate between BERP.StartDate and I.InvoiceEndDate and
TDT.fkClientCode = BECC.ClientCode and
TDT.fkMerchantNumber = BECC.MerchantNumber)
LEFT JOIN
dbo.RatePlanTierTransaction RPTT
ON
(RPTT.fkRatePlanID = BERP.RatePlanID and
@inNumTrans between RPTT.LowerLimit and RPTT.UpperLimit)
LEFT JOIN
dbo.RatePlanTierTransactionCode RPTTC
ON
(RPTT.fkRatePlanID = RPTTC.fkRatePlanID
and (RPTTC.TransactionCode = TDT.TranCode
OR RPTTC.TransactionCode = 999 ) )
LEFT JOIN
dbo.InvoiceDetailTransaction IDT
ON
(IDT.fkCustomerID = BERP.fkCustomerID and
IDT.fkRatePlanID = BERP.RatePlanID and
IDT.fkClientCode = TDT.fkClientCode and
IDT.fkMerchantNumber = BECC.MerchantNumber and
IDT.ActivityDate = TDT.ActivityDate and
IDT.TranCode = TDT.TranCode and
IDT.TranCodeIndicator = TDT.TranCodeIndicator)
WHERE
I.InvoiceStartDate = @inStartDate and
I.fkCustomerID between @chkStartCustomerID and @chkEndCustomerID and
(@inRatePlanId = 0 or BERP.RatePlanId = @inRatePlanId) and
IDT.ActivityDate IS NULL and
( ( RPTT.BillExceptTC = 0 and RPTTC.TransactionCode is not NULL
)

OR
( RPTTC.TransactionCode is null AND RPTT.BillExceptTC = 1
)
)
GROUP BY
BERP.fkCustomerID,
BERP.RatePlanID,
TDT.ActivityDate,
TDT.BatchDate,
TDT.fkClientCode,
TDT.fkMerchantNumber,
TDT.TranCode,
TDT.TranCodeIndicator
)

solai
Starting Member

6 Posts

Posted - 2006-05-09 : 14:15:06

Just to add, the InvoiceDetailTransaction has just about 3M, the select typically retrieves about 10,000 Records. We see that InvoiceDetailTransaction doen't have proper indexes, we can index them. Even without indexes the SELECT runs for only about a minute! -solai

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources