Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VSTO Excel Rows to SQL Server 2005
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VSTO Excel Rows to SQL Server 2005
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. -- Tim Zych http://www.higherdata.com Workbook Compare - free and pro versions "Jim" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VSTO Excel Rows to SQL Server 2005
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. On Mar 9, 2:52*am, "Tim Zych" <tzych@nospam at earthlink dot net wrote: 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. -- Tim Zychhttp://www.higherdata.com Workbook Compare - free and pro versions "Jim" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VSTO 2005 Excel Addin | Excel Programming | |||
VSTO 2005 - How to use ThisWorkbook as an Excel.Workbook | Excel Programming | |||
VSTO 2005 Excel picturebox control question | Excel Programming | |||
VSTO 2005 Excel picturebox question | Excel Programming | |||
reference vsto Excel ListObject in Vb.Net 2005 project/app | Excel Programming |