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

Answer 1

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

Answer 2

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

Answer 3

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!

Answer 4

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! 

Answer 5

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.

Answer 6

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

Answer 7

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

Answer 8

Yes Paul,
unfortunately the windows 2008 server has only 2Gb RAM.
Ciao,
SImonaP

Answer 9

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

Answer 10

Yes,
2 GB, not Gb!

Answer 11

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.

Answer 12

Do you still get the slow performance if you are the only user on the system?

Paul

Answer 13

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

Answer 14

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.

Answer 15

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.