SQL Server 2008 - Performance Issue

[glencooley]

Hi Guys

Having an interesting problem with SQL Server 2008 at work.

We are inserting 1.7million records in to a table (not using BCP but there are reasons for this) and its taking 30-40 minutes to run.

When we run the same SQL in PostGres or MySQL it takes a minute or two.

Any thoughts on why there is such a difference in performance.

These are all on the same hardware and the exact same SQL.

Thoughts appreciated.

Thanks

Glen

 

[Crowley16]

1.7 million? shouldn't take more than a couple of mins.

Have you got a lot of indexes on the tables?

--------------------
Procrastinate Now!

 

[glencooley]

No thats the odd thing, no indexes or any constraints.

Basically its a 3 column table.

We are replicating some of the realtime use the system gets and inserting 1.7mil in batches of one thousand.

 

[Crowley16]

is there any triggers?

how long does it take for each batch to run?

what do you see in the activity monitor when you run this? Particularly in the status/wait types/blocking columns...

have you tried to run a profiler trace on this to see exactly what the server is actually doing?

are you using set statements, loops or batch insert statements to insert the records?

--------------------
Procrastinate Now!

 

[glencooley]

Nope, no triggers, no indexes. Its a table freshly created with a varchar(10) and 2 float columns.

No blocking issues or waits in activity monitor. Profiler shows each insert statement running in 1.3 seconds which is horrendously slow.

We have tried several methods, from individual statements to batches of 1000 wrapped in transactions.

The other oddity is that its running 100% cpu when these really should be straight I/O opperations.

 

[markros]

Did you try

select Field1, Field2, Field3 into myNewTable from ...

This should be minimally logged operation.

PluralSight Learning Library

 

[Crowley16]

assuming the processor is multi core, have you tried to use maxdop = 1?

--------------------
Procrastinate Now!

 

[glencooley]

Thats a good point, we have tried setting the parallelism and it runs faster but not enough.

Im just confused that compared to the other DB engines is so poor performing on INSERTS.

SQL is our production platform so need to find a way round this, i guess the next step is more hardware.....

 

[Crowley16]

inserting 1.7 million records shouldn't take more than a few mins, I often populate test data of 1 million or more, and the longest they tend to take is like 3mins.

there's definitely something not right...

have you checked the table fill factors/padding?
db files and growth factors?
maybe tempdb?

it's hard to diagnose these sorts of problems remotely without error messages...

--------------------
Procrastinate Now!