asked
Complicated Joins
Hi I have a complicated query which takes 2 - 3 minutes run from my web application which ends up as Timeout. I am running it through a stored procedure but here I have provided the values of the parameters directly I want to make this query as quick as possible. SELECT DISTINCT '1HLS' 'Project Code', 2013 'Plan Year', 6 'Plan Week', mi.[Contractor] 'Contractor On Spread Sheet', jp.[Contractor No_] 'Contractor On Plan', mi.[Surname], mi.Name, mi.[Job No ] 'Job No on Spread Sheet', jp.[Job No_] 'Job No on Plan', mi.[Line Number] 'Line Number on Spreadsheet',jp.[Line No_] 'Line No on Plan' FROM [DEVENV].[dbo].[WebApp_MatrixImport] mi WITH (NOLOCK) LEFT OUTER JOIN [staffline].dbo.[job planning] jp ON (mi.[Line Number] = jp.[Line No_] AND mi.[Job No ] = jp.[Job No_] AND mi.[Contractor] <> jp.[contractor no_]) OR (mi.[Contractor] = jp.[contractor no_] AND mi.[Line Number] = jp.[Line No_] AND mi.[Job No ] <> jp.[Job No_]) OR (mi.[Contractor] = jp.[contractor no_] AND mi.[Job No ] = jp.[Job No_] AND mi.[Line Number] <> jp.[Line No_]) OR (mi.[Contractor] = jp.[contractor no_] AND mi.[Line Number] = 0 ) WHERE mi.[Project Code] = '1HLS' AND mi.[year] = 2013 AND mi.[Week No ] = 6 AND mi.[GUID] = 'ABC' AND jp.[Year] = 2013 AND jp.[Week No_] = 6 I have added the indexes suggested by SQL Server. Job Planning table is huge and contains more than 5 million lines, This table is always being used by number of users so I have also used NO Lock to get read of the blocking issue but still it isn't helping much. Can you please suggest me something to improve the performance of this query. Thanks, Maulin
Hi Friends, Thank you very much for your replies. After reading dev green's comment I had an idea that I should copy required lines into a temp table from Job Planning and run inner joins on that temp table so it only has to go through couple of thousand lines (Lot better than millions :) ) and then I can drop the temp table at the end of my stored procedure while doing that, I noticed that I was missing a condition in my where clause which is AND jp.[Project Code] = '1HLS' and boom! Query execution time dropped to 1 Second from 3 minutes and 12 seconds. Still I appreciate every one's reply and special thanks to dev for the links to the great article. I didn't wanted the solution to be this simple but I am glad that its resolved. Once again thank you very much, Maulin
The first thing I would do, that would make the most dramatic performance improvement would be to get the data all on the same server and eliminate your current querying across linked servers.
The questioner's query seems to reference cross-database tables (in databases DEVENV and staffline), but I can't see it using linked servers; surely these would use 4 part naming, not 3 part? Can you help me understand what I'm missing?
Good catch. My brain's not working, so I've deleted my comment...
Hi mouliin1573, I would suggest you are very careful to understand what you are doing when using the nolock hint - it will cause SQL Server to use a "dirty read", which means your data may be in an inconsistent state (and possibly duplicated). I would suggest that you take a read of [this post][1] - particularly the links in the foreword. Whilst there is a place for using this hint, you should carefully evaluate whether it meets your needs. That said, I note that your nolock hint is in fact on the WebApp_MatrixImport table - not the one that you state you expect blocking on. To quote from [Microsoft:][2] "Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause" - so this is probably why you are still seeing blocking issues on the [job planning] table. I would also question whether you have the correct object in your sights. My suspicion is that this query is the one that is blocked by the other users (hence the timeout), but the culprit (the blocker) may actually be elsewhere. I suggest you have a read of [this article][3] to help you track down what's causing the issue, although you can probably confirm my theory using sp_who2. If you still need help with tuning this query (i.e. you are confident that this is the one that is causing your issue), are you able to share the execution plan you are seeing, or perhaps some more information on your statistics, or the structure of your tables to enable us to see what the query optimiser is doing? [1]:
http://www.sqlservercentral.com/articles/Performance+Tuning/2764/ [2]:
http://msdn.microsoft.com/en-gb/library/ms187373.aspx [3]:
http://support.microsoft.com/kb/224453
You mention the Job Planning table is more than 5 million lines. How many lines does your query typically return? It may be an issue of getting millions of rows into the web application within your allotted time-out period - you may have to extend it. A quick thought on the performance side is that `SELECT DISTINCT` can be a performance killer because, instead of just returning your results, it has to store the entire result set in memory (or tempdb disk, if there isn't enough RAM) until it is de-duplicated. Is there a way you can guarantee uniqueness without using DISTINCT? As a side-noteL your WHERE clause, by looking for specific values in the right side of your join, has actually turned the operation into an INNER JOIN rather than a LEFT OUTER JOIN because it doesn't allow any NULL rows from the right - the hallmark of a left join
15 People are following this question.
