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 |
|
virtuesplea
Starting Member
1 Post |
 Posted - 2007-11-23 : 20:38:57
|
| I have designed a 22 table database in sql server that is to act as a backup/alternate access to the data we have stored in an ADABAS database. I've also written a vb.net console program that will take data from ADABAS through a broker connection (one row at a time), checks the sql server database to see if that information is already stored, and then either performs an insert or an update.I can write the rows from ADABAS to a text file (not using the broker), at the rate of about 1.3 million rows in 1.3 hours. Data can be imported (I'm not sure how this import is done, possibly via a CSV file. SELECTS/UPDATES are not done, just INSERTS) at about 1 million or so an hour. But when I do the update, receiving information via the broker from ADABAS to the VB program (with it's SELECT, then UPDATE/INSERT), I'm only doing about 20-25 thousand rows an hour. I ran a trace using the SQL Server Analyzer on the database while running the update program, and then ran Profiler using the generated workload. It created a few indices, but I just restarted the update program (I'm still developing, so I delete all rows from all tables each time I rerun the update), but I haven't seen that it's really any faster.I have a rather large set of data to transfer over, and this 20-25 thousand row time is not nearly fast enough.Any help will be appreciated.Thanks, |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-23 : 21:09:54
|
You should set this up as a bulk insert on the SQL server side instead of from the .net side. SQL's native fast load or bulk insert would be much faster than row by row evaluation and inserts. A job like this could be scheduled to run at any interval you choose to improve performance.It sounds like the bottleneck actually exists in the row level checking to see if the record exists. It might even be faster to set this up such that all records which have been added in a specific pre-defined time period are imported to a staging table on the SQL server where the server could handle the import and update of records.You will see improved performance from indexes on the server once you are running active updates and checking against the records SQL side of the equation...
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
|
|
slow database update