May 1, 2014 at 1:39 pm
Hi all,
We have a very expensive query that I am tring to rewrite, but the rewritten query (even though is much faster) doesn't return the same number of records. I'm desperate for some help, thank you very much.
Before:
SELECT a.col1
,a.Id AS col2
,(
SELECT col3
FROM (
SELECT e.col3
,ROW_NUMBER() OVER (
PARTITION BY d.col2 ORDER BY d.[col4]
) AS ROWNUMBER
FROM db1.dbo.table1 d(NOLOCK)
INNER JOIN db1.dbo.[table2] e(NOLOCK) ON d.[col4] = e.col5
WHERE d.col2 = a.Id
AND e.col6 = 505
) x
WHERE ROWNUMBER = 1
) AS callcol41
FROM db1.dbo.table2 a(NOLOCK)
After:
SELECT a.col1
,a.Id AS col2
,col3 AS callcol41
FROM (
SELECT e.col3
,ROW_NUMBER() OVER (
PARTITION BY d.col2 ORDER BY d.[col4]
) AS ROWNUMBER
,a.col1
,a.Id
FROM db1.dbo.table1 d(NOLOCK)
INNER JOIN db1.dbo.[table2] e(NOLOCK) ON d.[col4] = e.col5
LEFT OUTER JOIN db1.dbo.table2 a ON d.col2 = a.Id
AND e.col6 = 505
) x
WHERE ROWNUMBER = 1
May 1, 2014 at 1:50 pm
shahgols (5/1/2014)
Hi all,We have a very expensive query that I am tring to rewrite, but the rewritten query (even though is much faster) doesn't return the same number of records. I'm desperate for some help, thank you very much.
Before:
SELECT a.col1,a.Id AS col2
,(
SELECT col3
FROM (
SELECT e.col3
,ROW_NUMBER() OVER (
PARTITION BY d.col2 ORDER BY d.[col4]
) AS ROWNUMBER
FROM db1.dbo.table1 d(NOLOCK)
INNER JOIN db1.dbo.[table2] e(NOLOCK) ON d.[col4] = e.col5
WHERE d.col2 = a.Id
AND e.col6 = 505
) x
WHERE ROWNUMBER = 1
) AS callcol41
FROM db1.dbo.table2 a(NOLOCK)
After:
SELECT a.col1,a.Id AS col2
,col3 AS callcol41
FROM (
SELECT e.col3
,ROW_NUMBER() OVER (
PARTITION BY d.col2 ORDER BY d.[col4]
) AS ROWNUMBER
,a.col1
,a.Id
FROM db1.dbo.table1 d(NOLOCK)
INNER JOIN db1.dbo.[table2] e(NOLOCK) ON d.[col4] = e.col5
LEFT OUTER JOIN db1.dbo.table2 a ON d.col2 = a.Id
AND e.col6 = 505
) x
WHERE ROWNUMBER = 1
Try getting rid of that NOLOCK hint first. Are you ok with randomly returning extra and/or missing rows?
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
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/
May 1, 2014 at 1:52 pm
Then...you have been around here long enough to know better than just slapping up a query and asking how to fix it. You have obfuscated the tables and such to a point where it is really hard to figure out what is going on there. Maybe somebody with a lot more patience than I have will attempt to decipher this.
_______________________________________________________________
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/
May 1, 2014 at 2:13 pm
I'll take a quick stab in the dark at this. Have no idea if it'll return the right results or perform any better, but as Sean said you haven't provided enough information for people to work on.
SELECT a.col1
,a.id as col2
,b.col3
FROM db1.dbo.table2 a
CROSS APPLY (
SELECT TOP 1 e.col3
FROM db1.dbo.table1 d
INNER JOIN db1.dbo.[table2] e ON d.[col4] = e.col5
WHERE d.col2 = a.Id
AND e.col6 = 505
ORDER BY d.[col4]
) b
-- OR
WITH cte as (
SELECT d.col2, e.col3
,ROW_NUMBER() OVER (
PARTITION BY d.col2 ORDER BY d.[col4]
) AS ROWNUMBER
FROM db1.dbo.table1 d
INNER JOIN db1.dbo.[table2] e ON d.[col4] = e.col5
WHERE e.col6 = 505
)
SELECT a.col1
,a.id as col2
,b.col3
FROM db1.dbo.table2 a
INNER JOIN cte b ON b.col2 = a.Id
WHERE b.ROWNUMBER = 1
May 1, 2014 at 2:21 pm
Yes Sean, you do need more patience, nowhere did I say that I didn't spend any time on this myself. In fact, I have spent 2 days on and off trying to figure this out. So please take your attitude to the next thread, I don't need you help.
Thanks Micky, I'll give those a shot.
May 1, 2014 at 2:31 pm
shahgols (5/1/2014)
Yes Sean, you do need more patience, nowhere did I say that I didn't spend any time on this myself. In fact, I have spent 2 days on and off trying to figure this out. So please take your attitude to the next thread, I don't need you help.Thanks Micky, I'll give those a shot.
Ease up there. I never suggested you didn't spend any time on it yourself. I was saying that you didn't provide details so we have anything to work with. It is awfully difficult to work on a query when you have nothing to test it against. You have well over 4,000 visits, this is not new to you. Good luck, I honestly hope you can find a solution. At your request I will not offer any more assistance to you.
_______________________________________________________________
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/
May 1, 2014 at 2:51 pm
Since you have given us no table schemas, indexing, query plans, sample data, examples how the 2 queries differ in their output I can only say that when faced with the issue you have I break each query down into it's component parts to determine where my logic flaw resides. I also examine (and set up test data cases for, especially boundary and NULL/NOT NULL scenarios if appropriate) output differences very closely to look for reasons why the diffs exist.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 2, 2014 at 3:04 am
shahgols (5/1/2014)
Hi all,We have a very expensive query that I am tring to rewrite, but the rewritten query (even though is much faster) doesn't return the same number of records. I'm desperate for some help, thank you very much.
...
Why is the query expensive, have you looked at the plan?
Ignoring the second query because it doesn't work (SELECT n = 1 is really fast but it doesn't work either), the query references the same table twice. If you were to include e.col6 = 505 in the ROW_NUMBER expression, you could almost certainly eliminate one of those references to table2.
Here's a slight rewrite which some folks might find easier to scan:
SELECT
a.col1,
col2 = a.Id
callcol41 = y.col3
FROM db1.dbo.table2 a
OUTER APPLY (
SELECT
col3
FROM (
SELECT
e.col3,
rn = ROW_NUMBER() OVER (PARTITION BY d.col2 ORDER BY d.[col4])
FROM db1.dbo.table1 d
INNER JOIN db1.dbo.[table2] e
ON e.col5 = d.[col4] -- join
AND e.col6 = 505 -- filter
WHERE d.col2 = a.Id -- outer reference
) x
WHERE rn = 1
) y
To get anywhere with this, I think we'll need either the actual execution plan or a couple of sample data scripts. Preferably both.
Whilst your data may be secret, it's highly unlikely that your table structures are too. If you're unwilling to be seen to be requesting help on a forum, obfuscating structures to the point where it's impossible for folks to figure out what you are trying to do probably isn't going to help anyone and will only cause frustration - and the loss, to you, of one of ssc's most highly regarded analysts. Use meaningful names.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 3, 2014 at 4:33 am
I think Chris has a real improvement there, but we're just guessing. Without seeing the execution plan, I'm not sure why your original query is running slow.
"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
May 7, 2014 at 5:02 pm
Thanks Chris, your solution cut down the execution time by 3/4 and the execution plan cost is down 2/3. That's fantastic!
May 8, 2014 at 6:10 am
shahgols (5/7/2014)
... the execution plan cost is down 2/3...
Which is a meaningless measure. The costs within execution plans are only applicable within themselves. I would never compare execution plan cost to execution plan cost (well, not any more, the original version of my book suggested that was a good idea in a couple of places, but it was wrong).
"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
May 16, 2014 at 12:50 am
shahgols (5/1/2014)
Yes Sean, you do need more patience, nowhere did I say that I didn't spend any time on this myself. In fact, I have spent 2 days on and off trying to figure this out. So please take your attitude to the next thread, I don't need you help.Thanks Micky, I'll give those a shot.
Please avoid direct attacks. I was just a polite suggestion from Sean. This is a forum where we get help for FREE and I respect these guys immensely.
Regards
Chandan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply