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

ROW_NUMBER(): An efficient alternative to subqueries – Scenario 2 Expand / Collapse
Author
Message
Posted Tuesday, May 26, 2009 12:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:30 AM
Points: 229, Visits: 167
Comments posted to this topic are about the item ROW_NUMBER(): An efficient alternative to subqueries – Scenario 2
Post #723051
Posted Friday, May 29, 2009 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 8:05 AM
Points: 3, Visits: 39
How does the ROW_NUMBER() approach compare with using self-joins as follows?

SELECT Previous.Category, Previous.Date, Next.Date
FROM Dates.CategoryDate Previous LEFT JOIN Dates.CategoryDate Next ON Previous.Category = Next.Category AND Previous.Date < Next.Date
LEFT JOIN Dates.CategoryDate Middle ON Previous.Category = Middle.Category AND Previous.Date < Middle.Date AND Middle.Date < Next.Date
WHERE Middle.Category IS NULL;

Post #725761
Posted Friday, May 29, 2009 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 6, 2014 4:47 AM
Points: 6, Visits: 70
Good article. There is however no need to include the same field in the order by predicate when included in the partition by predicate, as you are ordering the field's value within the field's own value partition. (e.g. order by 123, 123, 123 makes no difference)
Post #725825
Posted Friday, May 29, 2009 9:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:30 AM
Points: 229, Visits: 167
mbergstrom (5/29/2009)
How does the ROW_NUMBER() approach compare with using self-joins as follows?

SELECT Previous.Category,
Previous.Date,
Next.Date
FROM Dates.CategoryDate Previous
LEFT JOIN Dates.CategoryDate Next
ON Previous.Category = Next.Category
AND Previous.Date < Next.Date
LEFT JOIN Dates.CategoryDate Middle
ON Previous.Category = Middle.Category
AND Previous.Date < Middle.Date
AND Middle.Date < Next.Date
WHERE Middle.Category IS NULL;



Take a look at the execution plans. The self-joins result in one hefty Hash Match join. This join is helpful if one of the tables is much smaller than the other. In this particular case, the tables are not so small. I don't understand the purpose of "Middle." Overall, the self-joins take a lot longer than ROW_NUMBER() for both indexed and non-indexed versions of the table.
Post #725917
Posted Monday, June 1, 2009 11:09 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:27 AM
Points: 542, Visits: 805
Very educational. Thank you! It's interesting that sub-query version sometimes outperforms ROW_NUMBER version in your example when proper indexes are in place. I guess the moral is to test various solutions.


Post #727148
Posted Tuesday, June 2, 2009 3:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Very educational article. Lately I have to deal with performance issues created by similar queries as given in this article. I like to read more about subqueries and their alternatives. For instance, could row_number be used to search in a string-column of a table? I now have something like:

DECLARE @var1 AS String, @var2 AS String
SELECT c1, c2, c3 FROM Table
WHERE StringColumn LIKE '%' + @var1 + '%' AND StringColumn LIKE '%' + @var2 + '%'

I'm trying several aproaches and I'm sure I'll either figure it out or conclude it can't be made pretty.

Tnx again.



Greetz,
Hans Brouwer
Post #727227
Posted Tuesday, June 2, 2009 11:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:30 AM
Points: 229, Visits: 167
FreeHansje (6/2/2009)
Very educational article. Lately I have to deal with performance issues created by similar queries as given in this article. I like to read more about subqueries and their alternatives. For instance, could row_number be used to search in a string-column of a table? I now have something like:

DECLARE @var1 AS String, @var2 AS String
SELECT c1, c2, c3 FROM Table
WHERE StringColumn LIKE '%' + @var1 + '%' AND StringColumn LIKE '%' + @var2 + '%'

I'm trying several aproaches and I'm sure I'll either figure it out or conclude it can't be made pretty.

Tnx again.


Very good question. I am working on a code set that might be able to help. The question gives me an idea for another article. Will try and post the code set soon.
Post #727607
Posted Wednesday, June 3, 2009 9:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:30 AM
Points: 229, Visits: 167
FreeHansje (6/2/2009)
Very educational article. Lately I have to deal with performance issues created by similar queries as given in this article. I like to read more about subqueries and their alternatives. For instance, could row_number be used to search in a string-column of a table? I now have something like:

DECLARE @var1 AS String, @var2 AS String
SELECT c1, c2, c3 FROM Table
WHERE StringColumn LIKE '%' + @var1 + '%' AND StringColumn LIKE '%' + @var2 + '%'

I'm trying several aproaches and I'm sure I'll either figure it out or conclude it can't be made pretty.

Tnx again.


You might also want to look into this article by Michael Coles. http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

Post #728334
Posted Sunday, June 28, 2009 6:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
FreeHansje (6/2/2009)
Very educational article. Lately I have to deal with performance issues created by similar queries as given in this article. I like to read more about subqueries and their alternatives. For instance, could row_number be used to search in a string-column of a table? I now have something like:

DECLARE @var1 AS String, @var2 AS String
SELECT c1, c2, c3 FROM Table
WHERE StringColumn LIKE '%' + @var1 + '%' AND StringColumn LIKE '%' + @var2 + '%'

I'm trying several aproaches and I'm sure I'll either figure it out or conclude it can't be made pretty.

Tnx again.


For this scenario I'd say your best bet would be to use a method like n-gram search. Using LIKE '%' + @var1 + '%' will result in inefficient scans. Basically you lose the efficiencies associated with indexing. If you use a method like n-grams, you can index the resultant fixed-length character strings (3 characters = trigram, etc.) and retrieve results similar to the ones you would get with LIKE '%' + @var1 + '%'.

When I do n-gram style searches in T-SQL I usually use ROW_NUMBER() to number each of the n-grams when I split words up.
Post #743317
Posted Thursday, July 30, 2009 4:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx




Madhivanan

Failing to plan is Planning to fail
Post #762264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse