May 24, 2010 at 1:42 pm
Great article Paul. This is useful stuff.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2010 at 1:44 pm
CirquedeSQLeil (5/24/2010)
Great article Paul. This is useful stuff.
Appreciated Jason, thanks.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 25, 2010 at 12:48 am
Thanks, you've won me over.
November 9, 2010 at 8:26 am
Great article. I have been trying to implement your ideas but have come unstuck with what I am doing. The issue revolves around implemenmting the key seek method when trying to get distinct results from the first query. ie
with Keys as (
SELECT distinct
....
)
when using distinct you can't use the row_number over mothod as you don't get sequential results, though this can be resolved by adding another section below the keys part and moving the row_number to that.
distinctKeys AS
(
SELECT TOP (@page * @recsPerPage)
rn = ROW_NUMBER() OVER (ORDER by '+@SQLOrderByTemp+')
FROM keys sk
)
With that working the problem is taht I can't figure out how to get the count of keys quickly. Because I am using the distinct in a way that means that I can't just get the pk back but have to use a number of columns you can't guarantee that the count will match the select.
The only way to do this seems to ve to move the count sub query to the distinctKeys section and remove the TOP part of the Keys section. This works but obviously I am now having to get all the records back from keys which is not very efficient on large sets.
I hope that makes sense! Any help on this would be much appreciated.
cheers
Tim
November 9, 2010 at 9:37 am
Hi Tim,
I almost see what you're getting at, but it would make it a lot easier to help if you could provide a CREATE TABLE statement, and a few rows of sample data to illustrate.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 9, 2010 at 9:58 am
ha I knew that was coming. This is a very rudimentry example, but it gives you an idea of the issue
--create table #test (clientID int,contactID int,firstname varchar(255),lastname varchar(255),linkID int)
--insert into #test VALUES (1,1,'tim','s',1)
--insert into #test VALUES (1,1,'tim','s',2)
--insert into #test VALUES (1,1,'tim','s',2)
--select * FROM #test
;WITH
keys AS
(
SELECT DISTINCT contactID,firstname,lastname,linkID
,(SELECt count(*) FROM #test) as allrecords
FROM #test
)
SELECT * FROM keys
is that enough to make more sense of what I was saying?
Cheers
Tim
November 18, 2010 at 2:52 am
Anyone got any thoughts on this?
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply