tsql performance optimisation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

tsql performance optimisation

Hi, Can anyone tell me whether the following procedure can be improved in any way? It currently takes over 14 seconds to run each time. It is run many times on our system. Many thanks… INSERT INTO [soda]
SELECT
Ca.intMemberID,
@CostCentreID as intCCId,
Ca.intUserID,
COUNT(Ca.intInvoiceID) AS intVolume,
SUM(Ca.intDuration) AS intDuration,
SUM(Ca.monCallCharge) AS monCost,
Ca.vchrDCode,
Ca.vchrConvDigits,
Ca.vchrDialLoc,
Ca.chrCategoryID
FROM
Ca
WHERE
[Ca].intInvoiceID in (select intInvoiceID from InvoiceIDs_CostCentre(@CostCentreID))
GROUP BY
Ca.intMemberID,
Ca.intUserID,
Ca.vchrDCode,
Ca.vchrConvDigits,
Ca.vchrDialLoc,
Ca.chrCategoryID
Did you see execution plan?
Did you try ITW? What indexes CA table has?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
You would be better off doing an INNER JOIN I think. INSERT INTO [soda]
SELECT
Ca.intMemberID,
@CostCentreID as intCCId,
Ca.intUserID,
COUNT(Ca.intInvoiceID) AS intVolume,
SUM(Ca.intDuration) AS intDuration,
SUM(Ca.monCallCharge) AS monCost,
Ca.vchrDCode,
Ca.vchrConvDigits,
Ca.vchrDialLoc,
Ca.chrCategoryID
FROM
Ca
INNER JOIN InvoiceIDs_CostCentre(@CostCentreID) icc ON Ca.intInvoiceID = icc.intInvoiceID
GROUP BY
Ca.intMemberID,
Ca.intUserID,
Ca.vchrDCode,
Ca.vchrConvDigits,
Ca.vchrDialLoc,
Ca.chrCategoryID
I’m assuming InvoiceIDs_CostCentre is a table function???? Also, you need to use a column list for your INSERT statement. (INSERT table(col1, col2, etc)) Besides that, check the indexes as Luis suggested. Also, what’s inside that table function? That could be a big performance hit, depending on how it’s written. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
How many rows does this
WHERE
[Ca].intInvoiceID in (select intInvoiceID from InvoiceIDs_CostCentre(@CostCentreID)) usually have? —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
If the number of rows return by your function are more, then In(sql statement) will not be good choice. Try some other options like derrickleggett’s Inner Join….
If you can create covering index (Non cluster) on the columns which are used in Group By clause will be big boost else try to create more suitable index for your query.
Also look whether you are using views on table ca.
Hi, Many thanks for pointing me in the right direction. I added an index which the execution plan now uses. The query now runs in under 2 seconds! Great result. Thanks, Joe
]]>