November 19, 2009 at 11:15 pm
Hi, my query (as shown below) is very slow to return its result from a a few tables. It takes about 38s to return 36,000+ rows at the moment and I expect it will become even slower as the tables size are increasing.
Can anyone advise me how to recode it to improve the performance?
Thank you.
SELECT DISTINCT
TOP (100) PERCENT w.workorderno, w.date, w.job_no, w.mrs_no, w.prod_no, w.part_code, w.lot_no, w.from_wh, w.from_loc, w.from_shelf, w.IssueQty,
w.to_wh, w.to_loc, w.to_shelf, w.ReturnQty, w.Return_date, w.usedqty, w.rowid, w.mrstype, w.rejectqty, w.add_reqno, w.remark,
w.mrsprep_no, j.process, j.wippartcode, j.mc_code1, d.qpa, d.totalreqqty, d.totalissueQty, d.totalreqqty - d.totalissueQty AS balqty,
LTRIM(RTRIM(s1.part_desc)) + ' || ' + s1.specification AS wip_desc0, LTRIM(RTRIM(s2.part_desc)) + ' || ' + s2.specification AS part_desc0,
LTRIM(RTRIM(s2.part_desc)) AS part_desc, LTRIM(RTRIM(s1.part_desc)) AS wip_desc, s1.specification AS wip_spec, s2.specification AS part_spec,
w.to_wh AS Expr1, w.to_loc AS Expr2, w.to_shelf AS Expr3
FROM dbo.s_workorder AS w INNER JOIN
dbo.s_joborder AS j ON w.job_no = j.Job_no INNER JOIN
dbo.s_mrsdtl AS d ON j.mrs_no = d.mrs_no AND w.part_code = d.part_code INNER JOIN
dbo.s_stkmst AS s1 ON j.wippartcode = s1.part_code INNER JOIN
dbo.s_stkmst AS s2 ON w.part_code = s2.part_code
WHERE (d.exchanged = 0) AND (d.remove = 0)
ORDER BY w.workorderno
November 19, 2009 at 11:26 pm
Yes.
To get more specific, we would need table structures, execution plans, and indexes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 19, 2009 at 11:50 pm
I am with Jason, to help better the performance of the query, somemore info is needed about the underlying tables and so on....
Before you get back with the underlying table details, I have some points you can start tuning the query and test.
Looking at the code, it seems you are using some LTRIM, RTRIM functions and order by clause which may be some of the reasons for the slowness of the query execution.
Next, I suggest you to look for the underlying tables and see that you have Indexes for the columns used in the Join condition, such as w.job_no, j.job_no and etc...
Next up, columns used in the where clause, d.exchanged and d.remove and also the column used in the order by clause they need indexes on them.
Lastly, use the covered indexes for the columns used in the Select clause.
Look at the following links to see the performance difference when this is followed.
http://www.sqlservercentral.com/Forums/Topic819542-1291-2.aspx#bm820818
http://www.sqlservercentral.com/Forums/Topic817390-392-1.aspx#bm817424
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 12:13 am
Hi Bru and Jason, thank you for your advice. Follow your advice, I will look for my missing indexes now. At the meantime, i attached my EP herein for your reference. O, one more thing, how to export my table indexes to a file?
Thank you.
November 20, 2009 at 12:24 am
Could not open the sqlplan attached...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 12:34 am
Can u try to open this one?
(I can open it by sql2005.)
TQ
November 20, 2009 at 12:43 am
AhTu_SQL2k+ (11/20/2009)
Can u try to open this one?(I can open it by sql2005.)
TQ
I still can't open the attachment, as soon as I click on that IE browser opens with some error message displayed. I guess you too can try by clicking on the attachment in your post.
Any way, It would be early morning in couple of hours, I need to get few hours of sleep.
Do try with the suggestions given already......
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 1:17 am
You are right, can't open it in IE. But, if I click it in Firefox then it will direct me to use Sql Management tool to open it. I also notice the file extension was changed from .sqlpln to .xml after I uploaded to this forum.
Anyway, I followed your advice to make sure those 'join' columns and 'where' columns have their index; Rtrim(Ltrim(()) and Order by clauses were also removed but it still takes 38s+/-.
Good night to you. (I am still at work and it is 4PM now) 😉
November 20, 2009 at 1:53 am
DISTINCT TOP 100 PERCENT .... ORDER BY????
Is the distinct really necessary?
Why the top 100 percent?
Please post table and index definitions.
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
November 20, 2009 at 2:21 am
Hi Gail,
'Distinct' result is needed. Please refer to my code (revised) below and one attached file for imvolved tables and indexes.
Thank you,
SELECT DISTINCT
w.workorderno, w.date, w.job_no, w.mrs_no, w.prod_no, w.part_code, w.lot_no, w.from_wh, w.from_loc, w.from_shelf, w.IssueQty,
w.to_wh, w.to_loc, w.to_shelf, w.ReturnQty, w.Return_date, w.usedqty, w.rowid, w.id_upd, w.dt_upd, w.mrstype, w.rejectqty, w.add_reqno, w.remark,
w.mrsprep_no, j.process, j.wippartcode, j.mc_code1, d.qpa, d.totalreqqty, d.totalissueQty, d.totalreqqty - d.totalissueQty AS balqty,
s1.part_desc + ' || ' + s1.specification AS wip_desc0, s2.part_desc+ ' || ' + s2.specification AS part_desc0,
s2.part_desc AS part_desc, s1.part_desc AS wip_desc, s1.specification AS wip_spec, s2.specification AS part_spec,
w.to_wh AS Expr1, w.to_loc AS Expr2, w.to_shelf AS Expr3
FROM dbo.s_workorder AS w INNER JOIN
dbo.s_joborder AS j ON w.job_no = j.Job_no INNER JOIN
dbo.s_mrsdtl AS d ON j.mrs_no = d.mrs_no AND w.part_code = d.part_code INNER JOIN
dbo.s_stkmst AS s1 ON j.wippartcode = s1.part_code INNER JOIN
dbo.s_stkmst AS s2 ON w.part_code = s2.part_code
WHERE (d.exchanged = 0) AND (d.remove = 0)
November 20, 2009 at 6:19 am
AhTu_SQL2k+ (11/20/2009)
Anyway, I followed your advice to make sure those 'join' columns and 'where' columns have their index; Rtrim(Ltrim(()) and Order by clauses were also removed but it still takes 38s+/-.
Can you post the revised 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
November 20, 2009 at 9:50 pm
AhTu_SQL2k+ (11/19/2009)
It takes about 38s to return 36,000+ rows at the moment and I expect it will become even slower as the tables size are increasing.
Heh...hold on a minute... return from where to where? In other words, where is the server and where are you?
Also, who is going to read 36,000 rows of anything? What do you really want to do with the output of this sproc?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply