Stored procedure round-a-bout



  • One of our products is a build-a-site-for-yourself type of product and we do track each and every visit for our clients. Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    The problem lies in this piece of stored procedure:

    DECLARE PageCursor CURSOR FOR
    SELECT
        CONVERT(varchar(8), [FirstAction], 112)
        FROM
            [Visits]
        WHERE
            [SiteId] = @SiteId
            AND
            (
                [Visits].[IsReturning] = 0
                OR
                @GetAll = 1
            )
        GROUP BY
            CONVERT(varchar(8), [FirstAction], 112)

    DECLARE @FirstAction varchar(8), @Hits int
    DECLARE @ReturnTable table(FirstAction varchar(8), Hits int)
    OPEN PageCursor
    FETCH NEXT FROM PageCursor
    INTO @FirstAction
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Hits = (SELECT count([FirstAction]) FROM [Visits]
                        WHERE [SiteId] = @SiteId AND ([Visits].[IsReturning] = 0 OR @GetAll = 1)
                                AND CONVERT(varchar(8), [FirstAction], 112) = @FirstAction)
        INSERT INTO @ReturnTable VALUES (@FirstAction, @Hits)
        FETCH NEXT FROM PageCursor
        INTO @FirstAction
    END

    CLOSE PageCursor
    DEALLOCATE PageCursor
    SELECT * FROM @ReturnTable


    I cut execution time from about a minute to less than a second by counting hits with "COUNT(1) AS Hits" instead of looping results.



  • @Airhead said:

    Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    People keep telling me I need to groov up, but I like being immature.



  • So this is a story of how SQL got its groov back?



  • @toth said:

    @Airhead said:
    Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    People keep telling me I need to groov up, but I like being immature.

    Not amused on two non-consecutive occasions:



  • @bstorer said:

    @toth said:

    @Airhead said:
    Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    People keep telling me I need to groov up, but I like being immature.

    Not amused on two non-consecutive occasions:

     

     

    who is this old gaffer looks like a dong end?



  • @bstorer said:

    Not amused on two non-consecutive occasions:

    Did I give you dah-bee-dis?



  • @Helix said:

    @bstorer said:

    @toth said:

    @Airhead said:
    Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    People keep telling me I need to groov up, but I like being immature.

    Not amused on two non-consecutive occasions:

     

     

    who is this old gaffer looks like a dong end?

     

    The most famous mayor of Buffalo, New York.



  • @Helix said:

    @bstorer said:

    Not amused on two non-consecutive occasions:

    [Image http://upload.wikimedia.org/wikipedia/commons/thumb/5/55/StephenGroverCleveland.png/461px-StephenGroverCleveland.png]

     

    who is this old gaffer looks like a dong end?

    I can't even begin to guess who that could be.



  • @Someone You Know said:

    @Helix said:

    @bstorer said:

    @toth said:

    @Airhead said:
    Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    People keep telling me I need to groov up, but I like being immature.

    Not amused on two non-consecutive occasions:

     

     

    who is this old gaffer looks like a dong end?

     

    The most famous mayor of Buffalo, New York.

     

     

    what did the miserable old buffalo cunt say when her child died ?

    "Bison."

     



  • @Helix said:

    who is this old gaffer looks like a dong end?
    http://upload.wikimedia.org/wikipedia/commons/thumb/5/55/StephenGroverCleveland.png/461px-StephenGroverCleveland.png
    I HAVE NO IDEA!



  • @Helix said:

    @Someone You Know said:

    @Helix said:

    @bstorer said:

    @toth said:

    @Airhead said:
    Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    People keep telling me I need to groov up, but I like being immature.

    Not amused on two non-consecutive occasions:

     

     

    who is this old gaffer looks like a dong end?

     

    The most famous mayor of Buffalo, New York.

     

    what did the miserable old buffalo cunt say when her child died ?

    "Bison."

     

    Unfortunately, this joke might be lost on actual Buffalo residents, most of whom insist on pronouncing "bison" as "bye-zuhn".



  • @Airhead said:

    CONVERT(varchar(8), [FirstAction], 112)
     

    This bit bugged me a bit, until I just remembered what it was: this converts the date to probably yyyymmdd, right?

    I just need to warn you, down that road madness lies. Having been in a huge y2k project ages ago, I've seen so many crap that stemmed from people treating dates as strings (or numbers, or almost any data type besides proper good old dates) that I'm still a bit over sensitive to this. I imagine this sproc feeds some object in the application that converts 20100420 back to a date, which works fine until you get to a system with different date settings, or...

     </rant>

    Having said that, I also seem to remember it's a bitch getting SQL server to treat datetime as just a date.



  • @b_redeker said:

    @Airhead said:

    CONVERT(varchar(8), [FirstAction], 112)
     

    This bit bugged me a bit, until I just remembered what it was: this converts the date to probably yyyymmdd, right?

    I just need to warn you, down that road madness lies. Having been in a huge y2k project ages ago, I've seen so many crap that stemmed from people treating dates as strings (or numbers, or almost any data type besides proper good old dates) that I'm still a bit over sensitive to this. I imagine this sproc feeds some object in the application that converts 20100420 back to a date, which works fine until you get to a system with different date settings, or...

    ...or until 01/01/10000.



  • @b_redeker said:

    @Airhead said:

    CONVERT(varchar(8), [FirstAction], 112)
     

    This bit bugged me a bit, until I just remembered what it was: this converts the date to probably yyyymmdd, right?

    I just need to warn you, down that road madness lies. Having been in a huge y2k project ages ago, I've seen so many crap that stemmed from people treating dates as strings (or numbers, or almost any data type besides proper good old dates) that I'm still a bit over sensitive to this. I imagine this sproc feeds some object in the application that converts 20100420 back to a date, which works fine until you get to a system with different date settings, or...

     </rant>

    Having said that, I also seem to remember it's a bitch getting SQL server to treat datetime as just a date.

     

    That said, I actually don't even know why it's converted to yyyymmdd.  That was a minor wtf for me, since everywhere else we're using yyyy-mm-dd or as dates. Anywho, it's sproc is executed by PHP (let me quess, another wtf) which draws some charts. I really have to check why it's not retrieved as a date.



  • @Airhead said:

    One of our products is a build-a-site-for-yourself type of product and we do track each and every visit for our clients. Time used to gather data about visits was just groving and groving so I had to find out what was the problem.

    The problem lies in this piece of stored procedure:

    DECLARE PageCursor CURSOR FOR

    ...snip...

    CLOSE PageCursor
    DEALLOCATE PageCursor
    SELECT * FROM @ReturnTable


    I cut execution time from about a minute to less than a second by counting hits with "COUNT(1) AS Hits" instead of looping results.

     

    If you treat cursors as evil, your life will generally be better.  By that, I mean only use them when you really need them and you should always be wary of the consequenses of doing so.  Microsoft's official training curriculum actually stopped including cursors to discourage their use about ten years ago.

    One thing I found really interesting when I started getting into Oracle is that the Oracle training material and Oracle University trainers (which I was one for a while) don't seem to discourage the use of cursors as much as Microsoft does.  When I started getting into performance tuning, I decided to see if the difference in cultures was due to actual differences in the products, or if Oracle people really simply preferred cursors.  It turns out that cursors incur just as much of a performance penalty in Oracle as they do in MS SQL Server.

    So, maybe the guy who wrote this stored procedure was an Oracle guy.



  • @Airhead said:

    @b_redeker said:

    @Airhead said:

    CONVERT(varchar(8), [FirstAction], 112)
     

    This bit bugged me a bit, until I just remembered what it was: this converts the date to probably yyyymmdd, right?

    I just need to warn you, down that road madness lies. Having been in a huge y2k project ages ago, I've seen so many crap that stemmed from people treating dates as strings (or numbers, or almost any data type besides proper good old dates) that I'm still a bit over sensitive to this. I imagine this sproc feeds some object in the application that converts 20100420 back to a date, which works fine until you get to a system with different date settings, or...

     </rant>

    Having said that, I also seem to remember it's a bitch getting SQL server to treat datetime as just a date.

     

    That said, I actually don't even know why it's converted to yyyymmdd.  That was a minor wtf for me, since everywhere else we're using yyyy-mm-dd or as dates. Anywho, it's sproc is executed by PHP (let me quess, another wtf) which draws some charts. I really have to check why it's not retrieved as a date.

     

    99.999% chance the conversion is being done to remove the time portion from the datetime.  Format 112 is yyyymmdd.  Not retreiving it as a date is probably a very minor WTF.  I'm guessing that it was already in the format wanted for the charts and since PHP apps are generally built using a "big ball of mud" architecture, no thought was given to returning data in an appropriate type from the data layer.


Log in to reply