June 27, 2014 at 6:16 am
Hi All,
I have a query like below, when i execute this it will takes more time to display the records. how can i reduce the execution time. please help me from this.
SELECT DISTINCT(GRN.VENDORCODE),GRN_ITEM.ITEMCODE,MASTERITEM.ITEMNAME,MASTERVENDOR.VENDORNAME,
GRN_ITEM.UNITPRICE FROM GRN INNER JOIN GRN_ITEM ON GRN.MRECIEPTNO=GRN_ITEM.MRECIEPTNO
INNER JOIN MASTERVENDOR ON GRN.VENDORCODE=MASTERVENDOR.VENDORCODE INNER JOIN
MASTERITEM ON GRN_ITEM.ITEMCODE=MASTERITEM.ITEMCODE
WHERE GRN_ITEM.UNITPRICE= (SELECT MIN(UNITPRICE) FROM GRN_ITEM AS A
WHERE A.ITEMCODE=GRN_ITEM.ITEMCODE)
AND GRN.STORECD='PHAST'
ORDER BY MASTERITEM.ITEMNAME, GRN_ITEM.UNITPRICE
Thanks & Regards,
Avinash
June 27, 2014 at 6:47 am
Please post table definitions, index definitions and execution plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2014 at 8:11 am
Almost impossible to give you substantial suggestions without at least seeing the execution plan, but a couple of points. Do you really need the DISTINCT clause. That's going to cause quite a lot of performance issues. You have a MIN without an ORDER BY. That can lead to bad data, and might cause some performance problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2014 at 8:26 am
Grant Fritchey (6/27/2014)
You have a MIN without an ORDER BY. That can lead to bad data
How? Top without an order by I can see, MIN (the minimum value) should be explicit without an order by though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2014 at 8:34 am
GilaMonster (6/27/2014)
Grant Fritchey (6/27/2014)
You have a MIN without an ORDER BY. That can lead to bad dataHow? Top without an order by I can see, MIN (the minimum value) should be explicit without an order by though.
I'm guessing he must have put that mess through a formatter and had the same idea I did. Replacing the distinct and the subquery with a top 1 and a group by.
In case anybody wants to see a somewhat more legible version here it is:
SELECT DISTINCT( GRN.VENDORCODE ),
GRN_ITEM.ITEMCODE,
MASTERITEM.ITEMNAME,
MASTERVENDOR.VENDORNAME,
GRN_ITEM.UNITPRICE
FROM GRN
INNER JOIN GRN_ITEM ON GRN.MRECIEPTNO = GRN_ITEM.MRECIEPTNO
INNER JOIN MASTERVENDOR ON GRN.VENDORCODE = MASTERVENDOR.VENDORCODE
INNER JOIN MASTERITEM ON GRN_ITEM.ITEMCODE = MASTERITEM.ITEMCODE
WHERE GRN_ITEM.UNITPRICE = (SELECT Min(UNITPRICE)
FROM GRN_ITEM AS A
WHERE A.ITEMCODE = GRN_ITEM.ITEMCODE)
AND GRN.STORECD = 'PHAST'
ORDER BY MASTERITEM.ITEMNAME,
GRN_ITEM.UNITPRICE
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 2:52 pm
GilaMonster (6/27/2014)
Grant Fritchey (6/27/2014)
You have a MIN without an ORDER BY. That can lead to bad dataHow? Top without an order by I can see, MIN (the minimum value) should be explicit without an order by though.
Cause I was typing faster than I was thinking?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply