Efficient INSERTs - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Efficient INSERTs

Author

Topic

francism
Starting Member

22 Posts

Posted - 2008-08-05 : 10:59:16

I have a B.I. program that generates a file containing about 1000 MS SQL Server INSERT statements. They look something like this (sorry about the long line of code):

INSERT INTO dbo.ActivitySummary ( OWNERID, AS_AT_DTTM, AS_AT_DATE, AS_AT_TIME,
REGION, DAY_ACTIVITY, DAY_PHONECALL, DAY_APPOINTMENT, DAY_EMAIL, DAY_TASK,
DAY_FAX, DAY_LETTER, WTD_ACTIVITY, WTD_PHONECALL, WTD_APPOINTMENT, WTD_EMAIL,
WTD_TASK, WTD_FAX, WTD_LETTER, MTD_ACTIVITY, MTD_PHONECALL, MTD_APPOINTMENT,
MTD_EMAIL, MTD_TASK, MTD_FAX, MTD_LETTER, QTD_ACTIVITY, QTD_PHONECALL,
QTD_APPOINTMENT, QTD_EMAIL, QTD_TASK, QTD_FAX, QTD_LETTER, YTD_ACTIVITY,
YTD_PHONECALL, YTD_APPOINTMENT, YTD_EMAIL, YTD_TASK, YTD_FAX, YTD_LETTER,
FQTD_ACTIVITY, FQTD_PHONECALL, FQTD_APPOINTMENT, FQTD_EMAIL, FQTD_TASK, FQTD_FAX,
FQTD_LETTER, FYTD_ACTIVITY, FYTD_PHONECALL, FYTD_APPOINTMENT, FYTD_EMAIL,
FYTD_TASK, FYTD_FAX, FYTD_LETTER) VALUES ( '{05E8056D-D78E-DC11-97B6-001438BFF493}',
'GETDATE(20080805 10:41:14)', '20080805', '104114', '',  0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 12, 0, 8, 2, 2, 0, 0, 0, 0,
0, 0, 0, 0, 0, 54, 0, 50, 2, 2, 0, 0);

1000 INSERT statements appear to be taking a very long time to run, is there anything I can do to make this more efficient? I have admin access the table...

Thanks very much,

Francis.

Microsoft CRM 3 - SQL Server 2000

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-05 : 12:08:16

Change the "B.I." process to create a file and load it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 13:45:33

try with BULK INSERT option once you create a file out of data.

francism
Starting Member

22 Posts

Posted - 2008-08-05 : 15:06:59

"B.I." : Business Intelligence : WebFOCUS by Information Builders.

I modified my B.I. program to create a temporary MS SQL Server database and then changed my insert code to this:


INSERT EIDW.dbo.ActivitySummary
  (OwnerId, Region, OwnerUserId, OwnerFullName, ...)

  SELECT
   OwnerId, Region, OwnerUserId, OwnerFullName, ...
  FROM #ACT1H011

This works quite well. I'm not sure if it's more efficient, but it seems faster.

Thanks,

Francis.

Microsoft CRM 3 - SQL Server 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources