Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with query rewrite


Need help with query rewrite

Author
Message
shahgols
shahgols
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 5727
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





Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16641 Visits: 17027
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/

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16641 Visits: 17027
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
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


shahgols
shahgols
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 5727
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.



Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16641 Visits: 17027
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6004 Visits: 8314
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 at GMail
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9025 Visits: 19036
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17635 Visits: 32268
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
shahgols
shahgols
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 5727
Thanks Chris, your solution cut down the execution time by 3/4 and the execution plan cost is down 2/3. That's fantastic!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search