Trying to speed up a loop; there's just too much data?

Question

Trying to speed up a loop; there's just too much data?

u5andy

Valued Member

Points: 56
More actions
December 23, 2008 at 10:06 am

#195372

Hi all!
I'm not all that well versed in SQL and I'm brand new to SQL Server. I'm trying to speed the following code up:
DECLARE @TMP_INCREMENT_DATES table(q varchar(255))
INSERT INTO @TMP_INCREMENT_DATES
SELECT
'UPDATE [dbo].' + QUOTENAME(table_name) + ' SET ' + QUOTENAME(column_name) + ' = DATEADD(d,1,' + QUOTENAME(column_name) + ')'
FROM
information_schema.columns
WHERE
data_type IN ('datetime', 'smalldatetime')
DECLARE @statement varchar(255)
SET @statement = ''
WHILE EXISTS (SELECT q FROM @TMP_INCREMENT_DATES WHERE q>@statement)
BEGIN
SELECT @statement = min(q)
FROM @TMP_INCREMENT_DATES
WHERE q>@statement
BEGIN TRY
EXEC (@statement)
END TRY
BEGIN CATCH
SELECT 'Could not execute: ' + @statement
END CATCH
END
After the INSERT, TMP_INCREMENT_DATES has 748 rows (which are SQL queries for specific columns in specific tables). These columns could have any number of rows themselves (some in the 1000s). It takes about 9 minutes to execute the entire thing.
I've heard recursive CTEs can help (with speed), but I don't know if what's been doing with it can be done with a CTE. Is there any way (CTE or other) this code can be sped up?
Thanks for your time

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

What is this supposed to do?

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

This looks like it generates update statements that increment every datetime column in the database by 1 day.

Is that really what you want to do? I'd be curious as to the business requirement that mandates that.

And then again, I might be wrong ...
David Webb

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Count me in with David in wondering what the business purpose for this might be. Updating 748 columns in multiple tables with n number of rows in 9 minutes doesn't sound that bad to me. One thing I would consider is taking the time to fix the code generation portion to do one update per table if there are any tables with multiple datetime/smalldatetime columns.

I also believe using explicit begin transaction and end transaction around each update will also speed up the process. Right now you have one long transaction going on.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

I agree with Jack... we could make the query lookup a bit faster but it would only be by milliseconds for each query. Unless you understand the business rules behind this one, I wouldn't mess with it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

u5andy Valued Member Points: 56 More actions · Answer 5

David Webb (12/23/2008)
This looks like it generates update statements that increment every datetime column in the database by 1 day.
Is that really what you want to do? I'd be curious as to the business requirement that mandates that.

This is correct.

The point of doing this is for demoing purposes. The age of the data in the database serves a multitude of purposes in the application. The script would be ran once a day to keep the age of all data the same; so when I give a demo I can give the same demo over and over. It will ultimately be dropped on some server and automated, but for now I'd like to speed it up a bit (if possible) instead of waiting 10 minutes.

and thankyou for all your replies

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

Well, since you're updating multiple tables, you might just trap the update statements into scripts and run several in parallel. If you're not adding new columns on a regular basis, that shouldn't require too much maintenance.

If this resides on a server somewhere, you could put all the scripts into an SSIS job and run it every night so the database will be ready with new data every morning.

And then again, I might be wrong ...
David Webb

noeld SSC Guru Points: 96590 More actions · Answer 7

u5andy (12/23/2008)
Hi all!
I'm not all that well versed in SQL and I'm brand new to SQL Server. I'm trying to speed the following code up:
DECLARE @TMP_INCREMENT_DATES table(q varchar(255))
INSERT INTO @TMP_INCREMENT_DATES
SELECT
'UPDATE [dbo].' + QUOTENAME(table_name) + ' SET ' + QUOTENAME(column_name) + ' = DATEADD(d,1,' + QUOTENAME(column_name) + ')'
FROM
information_schema.columns
WHERE
data_type IN ('datetime', 'smalldatetime')
DECLARE @statement varchar(255)
SET @statement = ''
WHILE EXISTS (SELECT q FROM @TMP_INCREMENT_DATES WHERE q>@statement)
BEGIN
SELECT @statement = min(q)
FROM @TMP_INCREMENT_DATES
WHERE q>@statement
BEGIN TRY
EXEC (@statement)
END TRY
BEGIN CATCH
SELECT 'Could not execute: ' + @statement
END CATCH
END
After the INSERT, TMP_INCREMENT_DATES has 748 rows (which are SQL queries for specific columns in specific tables). These columns could have any number of rows themselves (some in the 1000s). It takes about 9 minutes to execute the entire thing.
I've heard recursive CTEs can help (with speed), but I don't know if what's been doing with it can be done with a CTE. Is there any way (CTE or other) this code can be sped up?
Thanks for your time

Although I believe there are probably better ways to do this "demo" ... Here is how you can change the query to do just one update per table instead of one update per datetime column per table:

SELECT

'UPDATE [dbo].' + QUOTENAME(table_name) + ' SET ' +

STUFF((SELECT ', ' + QUOTENAME(c.column_name) + ' = DATEADD(d,1,' + QUOTENAME(c.column_name) + ')' AS [text()]

FROM information_schema.columns c

WHERE c.table_name = t.table_name

AND c.table_schema = t.table_schema

AND c.data_type IN ('datetime', 'smalldatetime')

ORDER BY c.column_name

FOR XML PATH('')), 1, 2, '')

FROM

information_schema.tables t

WHERE

EXISTS (SELECT 1 FROM information_schema.columns c1

WHERE c1.table_name = t.table_name

AND c1.data_type IN ('datetime', 'smalldatetime')

)

AND t.table_type = 'BASE TABLE'

ORDER BY t.table_name

* Noel