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

Improve Insert script Expand / Collapse
Author
Message
Posted Friday, June 20, 2014 6:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:32 AM
Points: 139, Visits: 369
I use this script to insert 99,999 records into a table with specific values. It takes about a minute or 2 to run. I know it can be done more effeciently. How can I change this to perform better?


DECLARE @CLIENT VARCHAR(50) = 11
DECLARE @CONCEPT VARCHAR(50) = 3
DECLARE @SEQ VARCHAR(50) = 1
DECLARE @GROUPID VARCHAR(50) = RIGHT('00' + @CLIENT,2) + RIGHT('00' + @CONCEPT,2) + RIGHT('00000' + @SEQ,5)


WHILE @SEQ <= 99999
BEGIN
INSERT INTO dbo.TBLGROUPID (GROUPID,id) VALUES (@GROUPID,NEWID())
SET @SEQ = @SEQ + 1
set @GROUPID = RIGHT('00' + @CLIENT,2) + RIGHT('00' + @CONCEPT,2) + RIGHT('00000' + @SEQ,5)
END

go



Post #1584329
Posted Friday, June 20, 2014 6:55 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
Are you familiar with a Tally table? If not, Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/62867/ is well worth time time to read it. It will change the way you look at data.

As for your insert, I think this covers everything:

with level0 AS (SELECT 0 AS g UNION ALL SELECT 0),                    --2
level1 AS (SELECT 0 AS g FROM level0 AS a CROSS JOIN level0 AS b), --2^2 = 4
level2 AS (SELECT 0 AS g FROM level1 AS a CROSS JOIN level1 AS b), --4^2 = 16
level3 AS (SELECT 0 AS g FROM level2 AS a CROSS JOIN level2 AS b), --16^2 = 256
level4 AS (SELECT 0 AS g FROM level3 AS a CROSS JOIN level3 AS b), --256^2 = 65536
level5 AS (SELECT 0 AS g FROM level4 AS a CROSS JOIN level4 AS b), --65536^2 = 4294967296
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM level5)
INSERT INTO dbo.tblGroupID(GROUPID, id)
SELECT TOP 99999 '1103' + RIGHT('00000' + CONVERT(Varchar(8), t.N), 5), NEWID()
FROM Tally t;

To verify that I have your GroupID done correctly, you can rem out the INSERT line and run the query to see the values selected.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1584334
Posted Friday, June 20, 2014 7:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:32 AM
Points: 139, Visits: 369

Excellent!
This worked and the article is very interesting.

Thanks



Post #1584374
Posted Friday, June 20, 2014 7:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
Glad I could help. Thanks for the feedback.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1584377
Posted Friday, June 20, 2014 8:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:32 AM
Points: 139, Visits: 369
Only 1 thing i just noticed.
in my loop I declare the @SEQ. I use this to give me the starting number. So if I want to start from 500, I would use this to start populating from 500. how can i do this with your query?



Post #1584384
Posted Friday, June 20, 2014 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
GrassHopper (6/20/2014)
Only 1 thing i just noticed.
in my loop I declare the @SEQ. I use this to give me the starting number. So if I want to start from 500, I would use this to start populating from 500. how can i do this with your query?


Add a WHERE clause.

WHERE t.N >= 500

Or you could add 500 to t.N

CONVERT(Varchar(8), t.N + 500)

Either of those approaches should work just fine.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1584388
Posted Friday, June 20, 2014 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:32 AM
Points: 139, Visits: 369
I tried it the first time before replying and it gave me an error msg...i tried again now and it worked...must have fat fingered something. Thanks!


Post #1584399
Posted Friday, June 20, 2014 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
You're welcome. Glad I jump in late and help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1584401
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse