Extended challenge for everyone - Answer
-
I just wanted to say I've been using this approach to generate ranks for over a year now. I found the undocumented update syntax somewhere on the web. It works well, although I still use cursors to generate 96 different caches for queries of different parameters, every hour, and I might someday look into eliminating those cursors. I question whether I can replace this technique with anything better when I migrate to OLAP, since OLAP stores aggregates but not rank values. Of course, SQL 2005 AS has ranking functions...
-
Be careful when using this UPDATE syntax. Make sure you always specify OPTION(MAXDOP 1). I've been burned in the past. Another problem is that you cannot specify an ORDER BY clause in an update statement, so there is no guarantee that the statement will produce the same results on the same rows every time.
Brian
MCDBA, MCSE+I, Master CNE -
This is great. I have been having trouble converting data from an old DOS based system but I think this code will do the trick.
-
Make sure you understand the part about the order by in the exemple.. or this is gonna come byte you in the ass
. -
This is a known trick used in OLAP STAGEING situations and rely on the CLustered index being setup.
I do have a bit improvement (?) for the update (which is really not very well known
):update dbo.Test set @Int = Colid2 = case When @LastId <> id THEN 0 ELSE @Int END + 1, LastId = @LastId, @LastId = id
you can instead use:
update dbo.Test set @Int = Colid2 = case When @LastId <> id THEN 0 ELSE @Int END + 1, @LastId = LastId = id
Cheers!
* Noel -
Nice catch... if it works once it works twice
. Is it any faster speed wise? -
Is it any faster speed wise?
Not really
It should be about the samebut is something to keep in mind: Update allows for DOUBLE assingments.
* Noel -
Yup it's the same... which ever statement I run second has the best speed (only dropping the buffers between runs, not the proccache).
-
Strange... I tried dropping but the buffers and the proccache between each runs and the 2nd query always got the best speed (alternating the queries). what am I missing??
-
it is call data cache warm up
* Noel -
How do you go around that?
-
Drop the Tables TOO!
* Noel -
So that can't be made to work??
set statistics io on
set statistics time on
dbcc dropcleanbuffers
dbcc freeproccache
go
update1
go
dbcc dropcleanbuffers
dbcc freeproccachego
go
update2
go
set statistics io off
set statistics time off
-
That works well for "STANDARD" kind of queries. When variables are used in the update and double assingments are in the game I am not really sure if something is also cached else where. Removing any dependencies (like the temp Tables) might be a way around it.
I haven't tried this before but then again I don't see a reason for this queries to be different in speed!
* Noel -
Well this I'll have to find out another time.. thanx for the info.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply