July 22, 2004 at 10:23 am
My SQL box is a P4 2.8HT with 2GB RAM.
In the past I was able to import ~14Mil rows worth of data in about 40 minutes to a database from a flat file.
Now it is taking me nearly 3 hours to import 3Mil rows worth of data from the same flat file format. The table that this is going into is nothing spectacular (IE no indexes, no constraints, etc.)
I've tried looking through the settings of the server and am not seeing what the problem could be. Everything looks "normal." Nothing has been done to the machine other than mounting it on its side rather than laying flat.
The file,etc is all stored locally on the machine to maximize throughput for the import process, and it is a stand-alone SQL box, nobody hits it remotely,etc.
Anyone got some idea of what to look for that might be causing this?
TIA
July 22, 2004 at 11:05 am
is there a primary key on the table? if so is it clustered???
have you looked at the data load options in your dts package ??? are you using the fast load options ??
MVDBA
July 22, 2004 at 11:54 am
I would look to see if your database is trying to grow while the inserts are happening. That will slow the insert time considerably.
You can issue: sp_spaceused to see how much free space is in the database.
Also look to see if your log is growing and allocating disk space.
dbcc sqlperf(logspace)
Let us know.
Tom
July 22, 2004 at 1:00 pm
No indexes of any sort. No constraints,etc. Flat file import into a basic temporary holding table. All data manipulation is going to be done once the import is done.
Log file size and usage is 24% so the likelyhood that it is growing the log file is minimal. The size of the file being imported currently is 1.7 GB and the free space in the database is roughly 5GB (although I haven't re-verified this since looking earlier in the day)
July 22, 2004 at 1:09 pm
How big is the log file, and how big is the datafile. The database freespace includes the unused portion of the log file. So if that 5gb is mostly free log space and not free dataspace, when your inserting your physical datafile is constantly growing.
Is there any sort of order or date that you can make into a clustered key. If so you can put your fill factor to 100% and guarantee that the inserts are happening at the end of the file. Thus not splitting pages, or in your case making heaps.
Tom
July 22, 2004 at 3:20 pm
That 5 GB was free DB space, I didn't report on the log file.
But I did "find" the root of my problem I believe, and it's fairly humorous.
This SQL box was laying on its side due to me having moved things around in my office (4x250GB SATA drives and whatnot). Import of a file today took 2 hours to import 1.4Mil rows, so I thought what the heck and laid the box flat again ... 20 minutes later another 2.4 Mil rows imported. Either one of the connections in the box isn't totally seated correct for the drives OR something about laying flat makes the things speed up phenomenally.
July 23, 2004 at 1:59 am
a - time for a joke about servers running FLAT out...
sorry
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply