Slow Response Updating SQL Server Database

[mamartin]

I have a VB6.0 application that creates an Excel spreadsheet and updates 1 table in a SQL Server (2000) database. When I run the application from the Visual Basic environment, no problem. Takes about 12-15 minutes to complete. However, if I install this application on a workstation (XP) and run it, it takes over 2 hours. I created a logfile to monitor the execute process and the "slowness" appears to come from the section that performs the table update. When I run from the VB environment, the update takes less than a second. When I run from the installed app, the update takes 3-4 seconds. All other functions/procedures in the application run quick as a bunny, only the update slows the whole thing down. Any ideas why there is a difference between running from VB environment and installed app? The update is a fairly straight-forward SQL UPDATE statment. Thanks in advance, Michael A. Martin

 

[gmmastros]

How big is this file that it takes 12-15 minutes (best case)? Seriously, you should be able to process several megabytes within a second. Are you looping through the rows in the spreadsheet and 'hitting' the database for each row? There's a better way.

You could use the OpenDataSource or the OpenRowSet function to import the spreadsheet in to a temp table and then process it from a stored procedure.

Alternatively, you could grab the data from VB, make XML from the data, and then send the XML to a stored procedure for processing.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mamartin]

I should define the purpose/function of the application a bit better. I have a master table that resides on a server in my office. There's about 2800 rows in this master table. I read this table sequentially and then compare the values for a given row to a corresponding table in a remote database (actually, there are about 20 different remote databases). Any changes between the master table and the remote table are then recorded on the Excel spreadsheet, and then the master table is updated with the most recent info from the remote table. This update is where my problem resides. Running the same exact problem from within the VB design appliation results in about 12-15 minute execute time. Create a deployment/install package and install this package on a workstation, 2.5 hour execute time. Why?