Post reply

Extended challenge for everyone - Answer

  • June 11, 2005 at 7:13 pm

    #564886

    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...

  • June 11, 2005 at 9:19 pm

    #564888

    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

  • July 14, 2005 at 10:23 am

    #574474

    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.

  • July 14, 2005 at 10:29 am

    #574480

    Make sure you understand the part about the order by in the exemple.. or this is gonna come byte you in the ass .

  • July 14, 2005 at 12:37 pm

    #574528

    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

  • July 14, 2005 at 12:40 pm

    #574531

    Nice catch... if it works once it works twice . Is it any faster speed wise?

  • July 14, 2005 at 12:46 pm

    #574533

    Is it any faster speed wise?

    Not  really   It should be about the same

     but is something to keep in mind:  Update allows for DOUBLE assingments.  


    * Noel

  • July 14, 2005 at 12:53 pm

    #574535

    Yup it's the same... which ever statement I run second has the best speed (only dropping the buffers between runs, not the proccache).

  • July 14, 2005 at 12:55 pm

    #574536

    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??

  • July 14, 2005 at 12:57 pm

    #574538

    it is call data cache warm up


    * Noel

  • July 14, 2005 at 12:58 pm

    #574539

    How do you go around that?

  • July 14, 2005 at 1:01 pm

    #574540

    Drop the Tables TOO!


    * Noel

  • July 14, 2005 at 1:33 pm

    #574551

    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

  • July 14, 2005 at 1:50 pm

    #574565

    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

  • July 14, 2005 at 1:54 pm

    #574569

    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