Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need help with query rewrite Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 1:39 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:04 PM
Points: 636, Visits: 5,012
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




Post #1566843
Posted Thursday, May 1, 2014 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
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)
Post #1566846
Posted Thursday, May 1, 2014 1:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
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)
Post #1566847
Posted Thursday, May 1, 2014 2:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 1,066, Visits: 3,138
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

Post #1566850
Posted Thursday, May 1, 2014 2:21 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:04 PM
Points: 636, Visits: 5,012
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.



Post #1566852
Posted Thursday, May 1, 2014 2:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
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)
Post #1566855
Posted Thursday, May 1, 2014 2:51 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:46 PM
Points: 4,410, Visits: 6,283
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
Post #1566857
Posted Friday, May 2, 2014 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:44 AM
Points: 6,828, Visits: 14,066
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
Post #1566963
Posted Saturday, May 3, 2014 4:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:05 PM
Points: 13,925, Visits: 28,318
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1567250
Posted Wednesday, May 7, 2014 5:02 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:04 PM
Points: 636, Visits: 5,012
Thanks Chris, your solution cut down the execution time by 3/4 and the execution plan cost is down 2/3. That's fantastic!


Post #1568734
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse