Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wallsr
Starting Member
1 Post |
 Posted - 2006-01-13 : 15:44:28
|
| I was hoping to get some advice on a project I am working on that deals with huge data tables.I am using LogParser.exe to read selected events from archived security logs on our domain’s domain controllers.(The archives are only 15 min old, since each of six domain controller’s logs are rolled over every 15 minutes and archived).This results in 576 .evt files per day. I’m only looking at a handful of events, but we have over 20,000 users, so we end up inserting about 12M events into the data table every day.To maintain “queryability”, every field is indexed, plus there is an identity field. A query can normally return results in less than 1 minute even though there are 500M+ records.Anyway, I’m trying to speed up the loading of the data.Currently, LogParser writes to a .csv file for every .evt file and then bcp is used to insert into the db. This is repeated forever and seems to take too long.It is taking bcp 20 to 50 seconds to insert 22K or so selected events from each of these logs, depending on what else the server is dealing with at the time.Everything I’ve read about speeding up bulk inserts and using minimum or no logging says that the table must either start empty or there can be no indexes. Since the table must be available for query, I can’t turn off indexes. Indexing millions of records from scratch takes a long time too, so maybe I’m just stuck.Does the number of records in the data file inserted with bcp have much to do with performance? That is, is it better to run bcp fewer times with larger data files or the other way around?Or, should I maybe dump a day’s records into an unindexed table and then bulk insert from there into the indexed table?I guess, since the inserts run faster than the real-time event creation, once I finally catch up, I’ll be OK, but I was hoping to free up processing cycles to speed up the rest of the machine’s duties.Any opinions? |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2006-01-13 : 17:57:53
|
| Moved to the Import/Export forum.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
 |
|
|
|
|
|
|
|
Speeding up BCP