none
executing query takes a lot of time

    Question

  • Hi, im new to BI and stuff but to the point
    i have database with million of records, so i i created a database with just 500k rows

    i wrote a function that updates my column billed. when the date is pretty recent i put the column billed on 'N'
    when it's in the past for like 2 months or more a put it on 'Y'  

    -------------------------Piece of code--------------------------------------

    DECLARE @Date DATETIME;
    DECLARE @Month INT;
    DECLARE @Year INT;
    DECLARE @Counter INT;
    DECLARE @CurrentID INT;
    DECLARE @Totalrows INT;
    DECLARE @NextID INT;
    DECLARE @Day INT;


    SET @Counter = 1
    SELECT @Totalrows = count(*) from test.dbo.t
    SELECT @CurrentID = test.dbo.t.[ EXTERNAL_ID ] from test.dbo.t where ID = @Counter


    while(@Counter < @Totalrows)
    BEGIN
    SELECT @NextID = test.dbo.t.[ EXTERNAL_ID ] from test.dbo.t where ID = @Counter + 1
    SELECT @Month = month(test.dbo.t.[ TIMESTAMP ]) from test.dbo.t where ID = @Counter
    SELECT @Year = year(test.dbo.t.[ TIMESTAMP ]) from test.dbo.t where ID = @Counter
    IF(@CurrentID = @NextID)
    BEGIN
    IF(dbo.f_month(@CurrentID) = @Month AND dbo.f_year(@CurrentID) = @Year)
    BEGIN
    UPDATE test.dbo.t
    SET BILLED = 'N'
    WHERE @Month = dbo.f_month(@CurrentID) AND ID = @Counter
    END 
    ELSE
    BEGIN
    UPDATE test.dbo.t
    SET BILLED = 'Y'
    WHERE ID = @Counter
    END
    END
    ELSE
    BEGIN
    SET @CurrentID = @NextID
    END
    SET @Counter = @Counter + 1
    END
    ---seems that the tabs vanish here, so it's less readable
    --------------------end of the code ---------------------------

    i'm testing it now, and it's quering for 40+ and still going , is there a way to lower this query time? i now check row per row but i don't have another way to do so, if someone has experience with this, i would be really happy if you could help me

    kind regards
    matthias



    Monday, March 12, 2012 3:46 PM

All replies

  • Why you're using loop in SQL Server? SQL Server is best working with sets and slow when doing row-by-row processing. In your case you may get best performance if you will perform the update in 10K batches.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 12, 2012 3:56 PM
  • You need to eliminate the WHILE loop and use set-based operations.

    Query optimization guidelines:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL SERVER & BI TRAINING

    Monday, March 12, 2012 3:58 PM
  • As others before me have said you are using a non-set based way of doing things which isn't as fast as doing everything in one batch operation.

    The question I'm going to ask is does this data need to be persisted? In SQL Server you can have something called computed columns, that take up no space and are worked out on the fly. If you rarely use this data then it might be better to do it this way as then you do not have the daily overhead of changing the data.

    The other thing that I would say is that you should use a boolean value of 0 or 1 in a bit data type as that could save space. Using a character of "Y" or "N" would take up 1 byte in char(1) or two bytes in NCHAR(2). Extrapolate out 1 million rows and that's quite a bit of space.
    A bit data type will use 1 bit per row. If you don't have any other bit attributes it won't save you anyspace straight off. But if you do or add some in the future you could have 8 bit fields in 1 byte.

    Hope this helps.


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich

    • Proposed as answer by Naomi NModerator Monday, March 12, 2012 4:40 PM
    • Unproposed as answer by matthias1989 Tuesday, March 13, 2012 12:43 PM
    Monday, March 12, 2012 4:39 PM
  • Thanks for your reply, how do you work with set-based operations? 

    well this data is from a .csv file that i got as an assignment. so all the data in it will stay the same.
    My task it to add columns for example this column billed.

    i have an external_id this like an id that appears 1000's, so if i'm right, I have update in a set of external id's?
    like update all these rows where the id = externalID?

    cause if i look at my code the logical part where i check dates, and than update, with a set based i just need to update all the records where the id = external id but i have over 9000 unique external id's so i did row by row to check when my id changes and then use that id

    regards
    matthias



    Tuesday, March 13, 2012 7:52 AM