CODEWITHSUNDEEP
sql-serverperformancesql-updatesql-insertinsert-update
Ichneutic
Ichneutic

Reputation: 13

SQL inserts slow - "pre populate with blank rows??"

I have an .exe that compares a vbTab delimited .txt file with an SQL table.

Updates to the table's existing records goes very fast. Inserts into the table for new records is quite slow.

As I'm new to SQL, I'm wondering if my idea is crazy talk:

I thought that maybe a solution would be to "pre populate" the database with 10,000 empty rows (minus the primary key) and somehow have this speed up the process?

Any suggestions would be greatly appreciated.

Upvotes: 1

Views: 161

Answers (2)

Nachiket Kate
Nachiket Kate

Reputation: 8571

There is no straightforward answer to your question as many things are unknown to us (DB configuration, HW, existing data etc.)

But you can try below things,

  1. Try using DB export-import functionality

    Instead of fetching records from DB with an iterator and comparing them with a record from a file and then do insert of modification you can directly import those records into DB using upsert (update if present or insert if not) strategy. Believe me this works lot faster than previous.

  2. If you have indexes on that table, while import or insert drop the current indexes on that table and do the operation. After operation re-apply those indexes again. Indexes slows down the performance of inserts.

  3. If import strategy is not good for you (If you are doing with those records first before insertion) then probably go for stored procedure for modification and insert new rows after dropping indexes.

During this activity check for DB configuration as well. Use proper tuning for buffers, paging, locking.

Hope this helps :)

Upvotes: 2

Daniel Calbimonte
Daniel Calbimonte

Reputation: 35

To answer your question we may need more information. How many rows does your table have? I guess it may be a lack of indexes.

Upvotes: 0

Related Questions