How to handle millions of INSERT statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to handle millions of INSERT statements

Author

Topic

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-17 : 02:57:13

Hey,

how can i solve this with the best performance?

LOOP
LOOP
LOOP
LOOP
INSERT INTO table SELECT a, b FROM table2 WHERE table2.value = value;
END
END
END
END

There will be millions of insert statements so the question is if there is anything like bulk insert that i can use here to increase performance?

thx for any help :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 03:01:20

BCP
BULK INSERT

are two ways to insert records into a table.

But without knowing more of your business rules or present code, we can't really help you.

E 12°55'05.63"
N 56°04'39.26"

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-17 : 03:14:44

Thx for your fast reply.

quote:
Originally posted by Peso

BCP
BULK INSERT

are two ways to insert records into a table.

They only work with files afaik. So i have to do a 2-step-process. saving to file and then executing the bulk insert. Is this realy the best way to handle this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 03:33:04

Show us the actual code!

Why are you inserting with a WHERE clause to INSERT only (perhaps) one record at a time?
You should think set based and do one insert.

E 12°55'05.63"
N 56°04'39.26"

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-17 : 03:52:14

quote:
Originally posted by Peso

Show us the actual code!

Why are you inserting with a WHERE clause to INSERT only (perhaps) one record at a time?
You should think set based and do one insert.

E 12°55'05.63"
N 56°04'39.26"

here we go:

For all companies
For all shop
For all employees_in_this_shop
{
/* Let me do this in this way now: */
Select companyId from company where companyId = companies;
Select shopId from shop where shopId = shop;
Select employeesId from employees where employeesId = emplyees_in_this_shop;

Insert into table values companyId, shopId, employeesId, CalcValue;
}
End
End
End

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 04:15:36

[code]INSERT Table1
(
companyID,
shopID,
employeeID,
calcValue
)
SELECT c.companyID,
s.shopID,
e.employeeID,
{calc value here}
FROM Company AS c
INNER JOIN Shop AS s ON s.companyID = c.companyID
INNER JOIN Employee AS e ON e.shopID = s.shopID[/code]

E 12°55'05.63"
N 56°04'39.26"

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-20 : 11:04:40

Thank you Peso :)

There is just one thing that doesn't work atm. The "{calc value here}" is the number of rows of the select statement. i tried it with count, but that doesn't work as i get the total number of rows in the Company table. But i just want the number of rows that i get back by the select statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-20 : 11:31:55

[code]INSERT Table1
(
companyID,
shopID,
employeeID,
calcValue
)
SELECT c.companyID,
s.shopID,
e.employeeID,
COUNT(*) OVER (PARTITION BY s.shopID)
FROM Company AS c
INNER JOIN Shop AS s ON s.companyID = c.companyID
INNER JOIN Employee AS e ON e.shopID = s.shopID[/code]

E 12°55'05.63"
N 56°04'39.26"

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-22 : 09:04:12

thank you very much for you help Peso.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 10:27:33

Thank you.

I hope this exercise learnt you that set-based methods almost always is the method to use.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources