Geek Answers Handbook

SQL Server / MySQL / Access - speeds up insertion of many rows inefficiently

SETTINGS

I need to insert a couple of million rows in SQL Server 2000/2005, MySQL or Access. Unfortunately, I have no easy way to use volumetric insertion or BCP or any other way that a normal person will do this. The inserts will be performed in one specific database, but this code must be db agnostic, so I can not perform a bulk copy, or SELECT INTO, or BCP. However, I can run certain queries before and after the insertions, depending on which database I'm importing to.

eg.

If IsSqlServer() Then
    DisableTransactionLogging();
ElseIf IsMySQL() Then
    DisableMySQLIndices();
End If

... do inserts ...

If IsSqlServer() Then
    EnableTransactionLogging();
ElseIf IsMySQL() Then
    EnableMySQLIndices();
End If

Question

Are there any interesting things I can do for SQL Server that can speed up these inserts?

, , , SQL Server: ", ".

, , : ", , , , ".

ALTER INDEX [IX_TableIndex] ON Table DISABLE
     ... inserts
ALTER INDEX [IX_TableIndex] ON Table REBUILD

(. 2005 , 2000. , 2000 ).

MySQL Access?

+5
11

, , , (,) INSERT . INSERT . , .

MySQL SQL Server "" , :

INSERT INTO MyTable(ID, name)
SELECT 1, 'Fred'
UNION ALL SELECT 2, 'Wilma'
UNION ALL SELECT 3, 'Barney'
UNION ALL SELECT 4, 'Betty'

, Access , . , Access SELECT, , ANSI SQL-92 ( 3- , "" DB, ),

FROM OneRowTable

SELECT, "OneRowTable" - .

INSERT, , , , . .

+4

- ?

, , , .

SQL Management Studio script ...

+2

SQL Server:

  • "", . .

  • . SQL 2005, SQL Server 2000.

    [INDEX_NAME] [TABLE_NAME]

alter index [INDEX_NAME] on [TABLE_NAME] rebuild

. , , , .

+1

( , ), / , / ,

+1

, , , , , .

Samre , , .

.

0

, , "Insert Delayed" MySQL. , , . , - , . , , , . .

, Access MS SQL.

0

Factory? , , , Factory, Factory, "IDataInserter", .

.

0

SQL Server 2000/2005, MySQL Access tab/cr, . case, , , .

0

DTS (2000) SSIS (2005) ? DTS SSIS . SSIS, . , , IsSQLServer, IsMySQL ..

0

; .

0

, SQL- .

http://msdn.microsoft.com/en-us/library/ms190422(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms190203(SQL.90).aspx

You can also disable indexes on the target table during insertions.

-1
source

More articles:


All Articles