none
Slow while loop with SQL Server 2008

    Question

  • Hi all,
    I have created a simple 'WHILE' loop to populate a dime dimension with the hour and minute detail.
    This is the code:

    CREATE

     

    PROC

    [dbo]

    .[usp_OLAP_PopulateDimTime]

    @StartDate

    DATETIME,

    @EndTime

    DATETIME

    AS

    BEGIN

     

    DECLARE @intYear AS INT

     

    DECLARE @intMonth AS INT

     

    DECLARE @intDay AS INT

     

    DECLARE @intHour AS INT

     

    DECLARE @intMinute AS INT

     

    DECLARE @strMonth AS VARCHAR (50)

     

    --DECLARE @strYearMonthDay AS VARCHAR (50)

     

    DECLARE @intAddMinute AS INT

     

    DECLARE @intYearMonth AS INT

     

    DECLARE @intYearMonthDay AS INT

     

    DECLARE @intYearMonthDayHour AS INT

     

    DECLARE @intYearMonthDayHourMinute AS BIGINT

     

    DECLARE @strYearMonth AS VARCHAR (50)

     

    DECLARE @strYearMonthDay AS VARCHAR (50)

     

     

    WHILE @StartDate < @EndTime

     

    BEGIN

     

     

     

     

     

    SELECT @intYear = YEAR (@StartDate)

     

    SELECT @intMonth = MONTH (@StartDate)

     

    SELECT @intDay = DAY (@StartDate)

     

    SELECT @strMonth = DATENAME (m, @StartDate)

     

    SELECT @intHour = DATEPART (HH, @StartDate)

     

    SELECT @intMinute = DATEPART (MI, @StartDate)

     

    SELECT @intAddMinute = 1

     

    SELECT @intYearMonth = @intYear *100 + @intMonth

     

    SELECT @intYearMonthDay = @intYear*10000 + @intMonth*100 + @intDay

     

    SELECT @intYearMonthDayHour = @intYear*1000000 + @intMonth*10000 + @intDay * 100 + @intHour

     

    SELECT @intYearMonthDayHourMinute = CAST (@intYear AS BIGINT)*100000000 + CAST (@intMonth AS BIGINT) * 1000000 + CAST (@intDay AS BIGINT) * 10000 + CAST (@intHour AS BIGINT) * 100 + CAST (@intMinute AS BIGINT)

     

    SELECT @strYearMonth = @strMonth + ' ' + CAST (@intYear AS VARCHAR)

     

    SELECT @strYearMonthDay = CAST (@intDay AS VARCHAR) + ' ' + @strMonth + ' ' + CAST (@intYear AS VARCHAR)

     

     

    INSERT INTO OLAP_DIM_Time

     

    (

    dtDate

    ,

    idYear

    ,

    idMonth

    ,

    idDay

    ,

    idHour

    ,

    idMinute

    ,

    idYearMonth

    ,

    idYearMonthDay

    ,

    idYearMonthDayHour

    ,

    idYearMonthDayHourMinute

    ,

    strYearMonth

    ,

    strYearMonthDay

     

    )

     

    SELECT

    @StartDate

    ,

    @intYear

    ,

    @intMonth

    ,

    @intDay

    ,

    @intHour

    ,

    @intMinute

    ,

    @intYearMonth

    ,

    @intYearMonthDay

    ,

    @intYearMonthDayHour

    ,

    @intYearMonthDayHourMinute

    ,

    @strYearMonth

    ,

    @strYearMonthDay

     

    SELECT @StartDate = DATEADD (mi,@intAddMinute,@StartDate)

     

     

    END

     

    END

     



    Now, if I run this query on my pc only for a month (EXEC dbo.usp_OLAP_PopulateDimTime '2010-11-01', '2010-11-30') it takes about 21 second. I have on my pc SQL Server 2005 32 bit and Windows Vista Ultimate (3Gb RAM).

    But if I run this query on my production server with Windows Server 2008 (2Gb RAM) and SQL Server 2008 64 bit, it takes about 6 minutes!!!

    Any other insert or selest in my production environment is faster than my pc, only this procedure with the loop is very slow.
    Can you help me?
    Why the proc is so slow with SQL 2008 (21 sec verus 6 minutes!!!!!) ? How can I rewrite the procedure?
    Thank you all,
    SImonaP

    • Moved by Tom PhillipsModerator Wednesday, May 20, 2009 1:39 PM TSQL question (From:SQL Server Database Engine)
    Wednesday, May 20, 2009 8:36 AM

Answers

  • I tried the code on my x64 SQL Server 2008 SP1 on Windows Server 2008 and it finished in 18 secs. My box has 8 GB RAM, but SQL Server (sqlservr.exe) never consumed more than 250 MB RAM for the whole time, so I don't think this query is memory bounded - after all, all the inserted data (41K rows) is only around 4MB in size.

    I am guessing this is more likely to be CPU bounded as there is a lot of computation. What is the clock speed of your desktop and the production server? Also the types of RAM can make a difference as they could vary in write speed.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, May 21, 2009 12:08 AM
  • Also, there are a lot of factors that could affect the perfomance of so many INSERTs, like database recovery mode (http://msdn.microsoft.com/en-us/library/aa173531.aspx), whether automatic checkpoint will be triggered (http://msdn.microsoft.com/en-us/library/ms188748.aspx), just to name a few. Make sure the recovery mode is the same on test server and production server for direct comparisons.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, May 21, 2009 12:31 AM

All replies

  • Add SET NOCOUNT ON at the beginning of the procedure, otherwise SQL Server returns a (n) rows(s) affected type of message after every SELECT.  The network round trips involved make the query crawl.

    Also consider:

    SELECT @a = formula, @b = formula2, @c = formula3...and so on, rather than separate SELECT statements.

    Paul
    Wednesday, May 20, 2009 8:46 AM
  • Thank you Paul for your answer.
    I applied your suggestions, but the execution time is still of about 6 minutes, while on my local machine is about 20 second ....

    Any other idea?

    SImonaP

    Wednesday, May 20, 2009 9:11 AM
  • You added the SET NOCOUNT ON just after the BEGIN statement?

    And you replaced all those SELECTs with one?

    I wish I could type as fast as you!!!

    Please confirm the placement of the SET statement, and I'll have another think.

    Do you still get lots of x row(s) affected messages?

    Paul

    P.S. Ensure you don't have show actual execution plan turned on in Management Studio either!
    Wednesday, May 20, 2009 9:15 AM
  • Yes Paul,
    I have added SET NOCOUNT ON just after the BEGIN (I don't see the messase 'n rows' anymore now) and I have replaced all the select statement with one SELECT.

    But it is still slow!
    I remember you that on my local machine I have a SQL 2005 32 bit, while on the server I have SQL 2008 64 bit.

    Thank you again!
    SImona

    P.S.
    I haven't execution plan turner on, neither Profiler turned on! 
    Wednesday, May 20, 2009 9:22 AM
  • Hi Simona

    I've run your code, with Paul's enhancement sugestions, and I get an execution time of 2s on SQL2005, 64-bit...

    Is there anything else happening on that server while your query is running?

    Do you have any indexes on that table on the server? If you create a clean (ie new, empty) Time table, does that run faster?

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link under the star. If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, May 20, 2009 9:57 AM
  • Hi Ewan,
    thank you for your suggestion.

    You run the query on a sql 2005, but I have a 2008.

    I have already recreated the table without any index or key, but I got again 6 minutes execution time.
    The server hasn't any load, but it has only 2Gb RAM.

    Any other query (select, insert) is fast, only this with the loop is very slow!

    Any idea?
    Thank you,
    SImonaP
    Wednesday, May 20, 2009 10:22 AM
  • Can I just check that the 2GB RAM you said was in the server was a typo?

    Not that I'm saying that is the cause - I just noticed it when re-reading your first post.

    Paul
    Wednesday, May 20, 2009 10:22 AM
  • Yes Paul,
    unfortunately the windows 2008 server has only 2Gb RAM.
    Ciao,
    SImonaP
    Wednesday, May 20, 2009 10:35 AM
  • You are aware that is only 250MB right? (it's GB not Gb).

    2GB for a windows server 2008 + sql server 2008 is very much on the low side. If it is something that has to go in production i would advice you to write an investment plan and plug in more memory directly. Right now where i work we don't deliver any SQL servers with less then 6GB, and most of them have 12GB+.
    Wednesday, May 20, 2009 10:56 AM
  • Yes,
    2 GB, not Gb!

    Wednesday, May 20, 2009 12:01 PM
  • Hi Simona

    2GB is on the low side, but shouldn't cause a query to degrade to this level.

    I've retried the code on a 32-bit SQL2008 install with 2GB RAM (don't have a 64-bit SQL2008 build, sorry) and get the same performance as before - ie 2s. Without a repro I'm afraid I can't assist any further.

    Regards

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link under the star. If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, May 20, 2009 1:13 PM
  • Do you still get the slow performance if you are the only user on the system?

    Paul
    Wednesday, May 20, 2009 11:17 PM
  • How about if you add all the records at once instead of one-at-a-time in a loop?:

    CREATE PROC [dbo].[usp_OLAP_PopulateDimTime] 
       @StartTime DATETIME
      ,@EndTime DATETIME 
    AS
    BEGIN
    
    ;with MinutesInRange as
    (
      SELECT Dt=DATEADD(mi,n-1,@StartTime)
      FROM dbo.fn_Nums(DATEDIFF(mi,@StartTime,@EndTime))
    )
    INSERT INTO OLAP_DIM_Time 
      (
        dtDate
       ,idYear
       ,idMonth
       ,idDay
       ,idHour
       ,idMinute
       ,idYearMonth
       ,idYearMonthDay
       ,idYearMonthDayHour
       ,idYearMonthDayHourMinute
       ,strYearMonth
       ,strYearMonthDay
      )
    SELECT 
        Dt
       ,YEAR(Dt)
       ,MONTH(Dt)
       ,DAY(Dt)
       ,DATENAME(m,Dt)
       ,DATEPART(hh,Dt)
       ,DATEPART(mi,Dt)
       ,YEAR(Dt)*100 + MONTH(Dt) 
       ,YEAR(Dt)*10000 + MONTH(Dt)*100 + DAY(Dt) 
       ,YEAR(Dt)*1000000 + MONTH(Dt)*10000 + DAY(Dt) * 100 + DATEPART(hh,Dt) 
       ,CAST(YEAR(Dt) AS BIGINT)*100000000 + CAST(MONTH(Dt) AS BIGINT)*1000000 + CAST(DAY(Dt) AS BIGINT)*10000 + CAST(DATEPART(hh,Dt) AS BIGINT)*100 + CAST(DATEPART(mi,Dt) AS BIGINT)
       ,DATENAME(m,Dt) + ' ' + CAST(YEAR(Dt) AS VARCHAR)
       ,CAST(DAY(Dt) AS VARCHAR) + ' ' + DATENAME(m,Dt) + ' ' + CAST(YEAR(Dt) AS VARCHAR)
    FROM MinutesInRange
    
    END
    


    The fn_Nums function that the above refers to can be created as follows:

    CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) 
    RETURNS TABLE
    AS
    RETURN 
    WITH 
       L0 AS (SELECT 1 AS c UNION ALL SELECT 1)
      ,L1 AS (SELECT 1 AS c FROM L0 AS A, L0 AS B)
      ,L2 AS (SELECT 1 AS c FROM L1 AS A, L1 AS B)
      ,L3 AS (SELECT 1 AS c FROM L2 AS A, L2 AS B)
      ,L4 AS (SELECT 1 AS c FROM L3 AS A, L3 AS B)
      ,L5 AS (SELECT 1 AS c FROM L4 AS A, L4 AS B)
      ,Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)  
    SELECT n FROM Nums
    WHERE n<=@n 
    



    --Brad
    Thursday, May 21, 2009 12:04 AM
  • I tried the code on my x64 SQL Server 2008 SP1 on Windows Server 2008 and it finished in 18 secs. My box has 8 GB RAM, but SQL Server (sqlservr.exe) never consumed more than 250 MB RAM for the whole time, so I don't think this query is memory bounded - after all, all the inserted data (41K rows) is only around 4MB in size.

    I am guessing this is more likely to be CPU bounded as there is a lot of computation. What is the clock speed of your desktop and the production server? Also the types of RAM can make a difference as they could vary in write speed.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, May 21, 2009 12:08 AM
  • Also, there are a lot of factors that could affect the perfomance of so many INSERTs, like database recovery mode (http://msdn.microsoft.com/en-us/library/aa173531.aspx), whether automatic checkpoint will be triggered (http://msdn.microsoft.com/en-us/library/ms188748.aspx), just to name a few. Make sure the recovery mode is the same on test server and production server for direct comparisons.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, May 21, 2009 12:31 AM