How do I add records fast with ODBC

[clive]

I'm adding about 1 million records to a table using ODBC insert statements. There are also 19 indexes on this table. As you can imagine this takes a while.<br><br>Is there a way to speed things up?<br>Can I stop the indexing till after all the inserts?

 

[jnicho02]

It would be far faster to use bcp which is non-logged. However, if you have duplicate records or incorrect keys it can be a pain to re-index. <br><br>The ODBC mechanism itself is slow, there is a passthrough method that means that the SQL is not checked and speeds up the messaging. <br><br>How many records are already in the table? What is your clustered index? and will it make the new data you are adding go right at the end of your table. e.g. clustered index on time, and your new data is a new year's worth of data. Otherwise, the new data will be physically inserted all over the table. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>

</a><br>

 

[foxdev]

It might also hurt things if your fillfactors are set high, depending on whether or not the new records are going in the middle of existing entries (with 19 indexes, I would imagine this must happen somewhat).<br><br>I have inserted millions of records at a time through ODBC (using Visual FoxPro as the data-pusher), and received good results (over 1 million records per hour).&nbsp;&nbsp;But I didn't have 19 indexes, so I'm sure this is a big factor.<br><br>One thing I found that helps in large inserts is increasing the Recovery Interval setting to about 60, if that is acceptable in your situation.&nbsp;&nbsp;This allowed more data to be kept in cache before physically writing to disk. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[clive]

Well, I get the feeling that dropping the indexes isn't going to do much. SQL server seems to be adding indexes automatically when it feels it needs to. For example this one, _WA_Sys_clientid_3D5E1FD2 which I did not create.<br><br>I seems to still be adding records at a rate of 200 per second which I guess I can't complain about.<br><br>As for clustering, the administrator feels that clustering will just kill the server. It would have to re-order at least 1 gig of data. Thats 1 gig of data that will be changing about one or twice a week (ie, whacking half a million records and adding another half million, sometimes more).<br><br>I think in the near future we'll be getting a raid array and probably more RAM. I'd like to see the bosses go for a sparc with Oracle. I don't think I'm extremely impressed with SQL server.<br><br>-Clive

 

[Guest_imported]

You may want to make a stored procedure to add the rows.&nbsp;&nbsp;You pass values with parameters, or use defaults.

 

[jnicho02]

The stuff about clustering was only asking whether it had a clustered index, <u>not</u> advising you to add one, that would of course be a really bad idea for the reasons you mentioned! <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br>

 

[calahans]

Hi Clive<br><br>A few ideas:<br><br>1) Can this job be scheduled at night? Drop ALL indexes when that volume of data is inserted in one go, your current method is using 20 writes to add a 1 record. <br><br>When you have added the records rebuild the indexes [20 mins].<br><br>2) 19 indexes!!!!!!!!! thats about 12 to many, your machine must run like a dog. Drop at least 10+<br><br>3) <b>ADD A CLUSTERED INDEX</b> - this will speed up your queries and should compensate for the lost 12/13 indexes you are dropping. Create the clustered index on your <br>GROUP BY queried fields. If you are using SQl Server 6.5 you NEED a clustered index to avoid hotspots<br><br>4) As&nbsp;&nbsp;jnicho02 said use BCP. BCP can be a pain to get working but when you do you will reap the results. Times of 2000 rows per second (on a good machine) are reachable.<br><br>5) Tell you DBA to go on database course!!!!<br><br>HTH<br><br> <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br>

 

[clive]

Some very useful suggestions calahans.<br><br>1) Yes it will be a scheduled task running over night. That is why it uses ODBC. I had to write a win32 application so that it could be scheduled under the windows task scheduler thingo.<br><br>2) These indexes are a big problem. They've been created automatically. They don't show up under enterprise manager but when I type sp_help &lt;table&gt; they do show up. We'll have to look into this. The speed diference between 19 indexes and no indexes is 150 records per sec vs 190 records per sec. This is when the database is empty. I'll have to re-test this after I get my 100 megabit per second network connection. The network could be a bottle-neck.<br><br>3) I'll have to talk to the DBA about clustering and see what we can come up with. It may be a case of just clustering after the adds have been completed.<br><br>4) This BCP sounds very interesting to me. I assume it's a bulk loader. The bulk loaders I've worked with before have been very simple. I have to process 1.6Gigs of plain ASCII data from a DAT tape. I have to extract useful information and perform running totals on the data. Can BCP do much text processing? Initially my options were either C or perl. I went with win32 C because of the task scedule thing but if I can possibly get over 1000 records per sec I'll consider rewriting.<br><br>5) Point noted.<br><br>-Clive<br>Thanks for the info.

 

[Packermann]

Also, if you do not have to worry about existing data in the target table, try using a SELECT INTO instead of an INSERT SELECT. A SELECT INTO bypasses writing to the transaction log. But this requires that you to first drop the table and let the SELECT INTO re-create the table. It also requires any procedure, trigger, or view that references that target table to be re-compiled. These dropbacks may prevent you to use this, but if you can this is extremely fast.