VSTO Excel Rows to SQL Server 2005

[Jim]

I have a VSTO app (VS 2008) that allows users to combine data from 2
or more Excel 2007 worksheets in the active workbook. Currently I am
sending the data back to SQL Server to do the heavy lifting. I'm
looping over the rows and inserting each via a stored proc.
Unfortunately with anything over a hundred or so rows it runs really
slow and I am running into MDA messages (ContextSwitchDeadlock was
detected).

Is there a better/faster way to load this data into SQL Server?

I like VSTO as opposed to VBA which I understand may be faster at this
because it provides a nice add-in that users can add or remove.

SSIS is not really an option for me because the data will in the
sheets will be generated on the fly by the user based on an OLAP
drillthrough - so I would prefer not to have to save the file.

Any thoughts are appreciated.

 

[Tim Zych]

I have had great success looping through thousands of rows and saving them
via a stored procedure to a SQL Server database using VBA -- when directly
connected to the LAN the speed is quick -- a couple of seconds but then
again the tables were smallish.

The only slowdown I have seen is when performing this through a VPN but
hundreds of rows should be no problem. If these are humongous tables with
constraints or triggers maybe you are running into some performance
issues...

Another way to do this is to write everything to a temporary table with no
constraints and at the end of the loop perform a batch insert/update from
the temp table the the real table.

Just a thought.

 

[Jim]

Thanks Tim, perhaps I can look at adding the VBA dynamically at
runtime. My SQL tables are real lean with no constraints so I think
the issue is related to VSTO and interop.