asked on

SQL Mass Insert

It is quicker to insert into a heap table (table with no indexes) or into a table with a clustered index if I know 99% of the time my inserts can be sequential to the column the clustered index is on.

Thanks,

SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dqmq

Not a simple question, really...the answer may very well depend on things like table size, # rows being inserted at one time, and number of processors. But, I expect a heap with no indexes (assuming that's what you really mean) would be faster since there is no index structure to maintain.

Academic question, I hope. You do believe in primary keys, right?

dqmq

In some environments, you can get better performance by loading into un-indexed heaps and building the indexes (clustered or otherwise) in a subsequent step.

dthansen

ASKER

The table has only 4 columns, all of them integer. We will be inserting 500,000 rows at a time. The server has 2 quad-core process and 16GB RAM.

This table is functioning as a temporary queue for another system. Basically, we dump records into it and this other process comes along as copies/deletes the records at its leisure. This table may contain as many as 30,000,000 rows before this other process picks up the records.

I do believe in primary keys. In this particular situation though, there is nothing unique in the data itself and duplicate records are permitted.

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial