Rohi avatar image
Rohi asked

Performance Issue Of Stored Procedure- sql server 2008

The below stored procedure took 3 hrs , but still continue executing........ any help or suggestion please. and at the end error : as "**system io memorystream out of memory**". **How Can I first Reduce the rows , then do the JOINS.** SQL SERVER 2008 30 million recordset. USP CREATE PROCEDURE [CompareData] @count DECIMAL(10,2) = NULL, @REFID INT = 0, @DATE VARCHAR(50) AS WITH CTE AS ( SELECT ZBTSPOUT.MYDATE ,CMSTR.COMPANYNAME ,XREF.CNUMBER ,XREF.REFID ,C.NEWZBTEB ,D.NEWPRICE ,E.REMARKS , row_number() OVER (partition by XREF.CNUMBER order by D.NEWPRICE asc) as rn FROM Table1 CMSTR INNER JOIN table2 XREF ON CMSTR.REFID = XREF.REFID INNER JOIN table3 ZBTSPOUT ON ZBTSPOUT.REFID = CMSTR.REFID INNER JOIN table4 PRCACTV ON PRCACTV.SKU = XREF.ZCPSKU INNER JOIN table5 MSTACTV ON MSTACTV.SKU = XREF.ZCPSKU INNER JOIN table6 DGPCXREF ON DGPCXREF.PCO = MSTACTV.PCO CROSS APPLY (SELECT NEWZBTEB=(PRCACTV.ZBTEB - ((PRCACTV.ZBTEB * @count)/100))) AS C CROSS APPLY (SELECT NEWPRICEE = ((PRCACTV.ZBTEB - ((PRCACTV.ZBTEB * @count)/100))/1.2)) AS D CROSS APPLY (SELECT REMARKS = CASE WHEN NEWPRICE > (ZBTSPOUT.OLDPRICE/159.2) THEN 'GOOD' ELSE 'BAD' END) AS E ) SELECT * FROM CTE WHERE REFID = @REFID AND rn = 1 Thank You
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
What does the execution plan look like? Just curious.
0 Likes 0 ·
seanlange avatar image seanlange commented ·
There is way too much information missing here. What is the point of @Date? It isn't in your query at all. Why is @Date a varchar and not a date/datetime? What is the point of using CROSS APPLY here? Those are simply a computed column. To really help we would need an execution plan (estimated is probably all you can get), table definitions including indexes. I am guessing you need some indexing here but without a lot more details it is guess work.
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.