Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Improving performance with INSERT statement Expand / Collapse
Author
Message
Posted Saturday, August 31, 2013 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 6:21 AM
Points: 7, Visits: 24
The following code uses a WHILE loop to Insert a row in table2 with every ClientID in table1 and every consecutive Year between 2000 and 2010.
It works, but it is slow when table 1 is large, say 100,000 records.

DECLARE @MyYear smallint
SET @MyYear = 2000

WHILE @MyYear <= 2010 -- i.e., for each year from 2000 to 2010
BEGIN

INSERT INTO table2 (Year, ClientID)
SELECT @MyYear, table1.ClientID FROM table1

SET @MyYear = @MyYear + 1
END

Is there any way to speed this up by using just an INSERT statement such as:

INSERT INTO table2 (Year, ClientID)
SELECT <years from 2000 to 2010>, table1.ClientID FROM table1

Where the expression <years from 2000 to 2010> increments the Year value?

Thanks
Post #1490375
Posted Saturday, August 31, 2013 5:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 41,517, Visits: 34,433
Google: tally table (or dates table maybe)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1490378
Posted Saturday, August 31, 2013 7:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:43 PM
Points: 4,128, Visits: 5,836
GilaMonster (8/31/2013)
Google: tally table (or dates table maybe)


+100

What kills you the way you are doing it is log buffer flushes. Google them too if you are interested. Tally table is also known as a numbers table, and there are a number of articles about what you can do with them here. Jeff Moden is a primary author to seek out. I think he also has an article here on SSC.com using them to generate test data, which would definitely be helpful for you to review as well.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1490384
Posted Saturday, August 31, 2013 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 31, 2013 11:50 AM
Points: 2, Visits: 3
I would avoid the loop, create a temp table named #temp and insert the values of the Years from 2000 to 2010.

I would then do your insert like this:

Insert Into TargetTable (ClientID, Year) Select Distinct ClientID, #temp.Year from SourceTable, #temp

Note there is no join in the second query, it works by FM (Freaking Magic)

That ought to do it for you. You can follow me at www.LiveSQLHelp.com/myapp
Post #1490399
Posted Saturday, August 31, 2013 10:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 41,517, Visits: 34,433
LiveSqlHelp (8/31/2013)
I would avoid the loop, create a temp table named #temp and insert the values of the Years from 2000 to 2010.


And you would do that without a loop ....

Note there is no join in the second query, it works by FM (Freaking Magic)


No magic, just a cartesian product.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1490404
Posted Sunday, September 01, 2013 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 6:21 AM
Points: 7, Visits: 24
Thanks ail. That's exactly what I needed.
Post #1490459
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse