|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Large VB ADO.NET Recordset Import into ExcelIf I use Excel to open an Excel file with 61K records, they appear instantly.
If I fill an ADO.NET recordset in Visual Basic with 61K records, it fills almost immediately. But if I bind the dataset to a ListObject in Excel in VSTO, then populating the workshseet takes 20 minutes. I can use a For...Next loop to read the dataset and fill each row of the worksheet, but that takes a couple of minutes. What is the way to instantly fill an Excel spreadsheet with a dataset from SQL Server? Hi,
I don't think this is ado.net related problem. You should do some performance measuring, perhaps using a performance profiler to find the origin of the bottle neck. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message news:B8BD6529-5C23-46F7-AA63-62AF9A224AC1@microsoft.com... > If I use Excel to open an Excel file with 61K records, they appear > instantly. > If I fill an ADO.NET recordset in Visual Basic with 61K records, it fills > almost immediately. But if I bind the dataset to a ListObject in Excel in > VSTO, then populating the workshseet takes 20 minutes. I can use a > For...Next loop to read the dataset and fill each row of the worksheet, > but > that takes a couple of minutes. What is the way to instantly fill an > Excel > spreadsheet with a dataset from SQL Server? Yes, you are correct, it is not an ADO.NET problem. ADO.NET gets the 61K
records almost instantly. The bottleneck occurs in the binding of the dataset to the ListObject control in Excel. I will try posting this in another discussion group. Thank you. Show quote "Miha Markic" wrote: > Hi, > > I don't think this is ado.net related problem. You should do some > performance measuring, perhaps using a performance profiler to find the > origin of the bottle neck. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message > news:B8BD6529-5C23-46F7-AA63-62AF9A224AC1@microsoft.com... > > If I use Excel to open an Excel file with 61K records, they appear > > instantly. > > If I fill an ADO.NET recordset in Visual Basic with 61K records, it fills > > almost immediately. But if I bind the dataset to a ListObject in Excel in > > VSTO, then populating the workshseet takes 20 minutes. I can use a > > For...Next loop to read the dataset and fill each row of the worksheet, > > but > > that takes a couple of minutes. What is the way to instantly fill an > > Excel > > spreadsheet with a dataset from SQL Server? > > Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false; Show quote "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message news:120588A0-A7D3-4FF7-BF28-FE3C6598B830@microsoft.com... > Yes, you are correct, it is not an ADO.NET problem. ADO.NET gets the 61K > records almost instantly. The bottleneck occurs in the binding of the > dataset to the ListObject control in Excel. I will try posting this in > another discussion group. > Thank you. > > "Miha Markic" wrote: > >> Hi, >> >> I don't think this is ado.net related problem. You should do some >> performance measuring, perhaps using a performance profiler to find the >> origin of the bottle neck. >> >> -- >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> RightHand .NET consulting & development www.rthand.com >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> >> "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message >> news:B8BD6529-5C23-46F7-AA63-62AF9A224AC1@microsoft.com... >> > If I use Excel to open an Excel file with 61K records, they appear >> > instantly. >> > If I fill an ADO.NET recordset in Visual Basic with 61K records, it >> > fills >> > almost immediately. But if I bind the dataset to a ListObject in Excel >> > in >> > VSTO, then populating the workshseet takes 20 minutes. I can use a >> > For...Next loop to read the dataset and fill each row of the worksheet, >> > but >> > that takes a couple of minutes. What is the way to instantly fill an >> > Excel >> > spreadsheet with a dataset from SQL Server? >> >> When I call the myTableAdapter.Fill() operation, a blue rectangle appears
around all of the cells that were bound to the ListObject, but nothing else is happening to the screen during the loading of the data. So when I tried your suggestion, all that happened is that the blue rectangle didn't appear. No performance improvement. The way I know that ADO.NET is fast is because I tried the myTableAdapter.Fill() operation without being bound to the cells and it executed in a second or two. I tried loading 2500 records and it takes about 20 seconds with the bound ListObject for the data to appear. Why can ADO.NET query 61K records instantly, but it takes 10 minutes to get them into the Excel cells? I'm using VB in VSTO, not VBA, so the syntax for me is Globals.ThisWorkbook.Application.ScreenUpdating = false. Show quote "Jim Rand" wrote: > Did putting this code in prior to the data binding help? > this.Application.ScreenUpdating = false; > > > > > > "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message > news:120588A0-A7D3-4FF7-BF28-FE3C6598B830@microsoft.com... > > Yes, you are correct, it is not an ADO.NET problem. ADO.NET gets the 61K > > records almost instantly. The bottleneck occurs in the binding of the > > dataset to the ListObject control in Excel. I will try posting this in > > another discussion group. > > Thank you. > > > > "Miha Markic" wrote: > > > >> Hi, > >> > >> I don't think this is ado.net related problem. You should do some > >> performance measuring, perhaps using a performance profiler to find the > >> origin of the bottle neck. > >> > >> -- > >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] > >> RightHand .NET consulting & development www.rthand.com > >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > >> > >> "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message > >> news:B8BD6529-5C23-46F7-AA63-62AF9A224AC1@microsoft.com... > >> > If I use Excel to open an Excel file with 61K records, they appear > >> > instantly. > >> > If I fill an ADO.NET recordset in Visual Basic with 61K records, it > >> > fills > >> > almost immediately. But if I bind the dataset to a ListObject in Excel > >> > in > >> > VSTO, then populating the workshseet takes 20 minutes. I can use a > >> > For...Next loop to read the dataset and fill each row of the worksheet, > >> > but > >> > that takes a couple of minutes. What is the way to instantly fill an > >> > Excel > >> > spreadsheet with a dataset from SQL Server? > >> > >> > > > I'm just starting with VSTO. One concern that I have, other than deployment
issues, is the cost of interop calls between managed and unmanaged code. Because of this concern, right from the get-go, I'll be limiting VSTO programming to just those things that are unique to .NET such as obtaining data via web services. The rest of the programming is going to be done using straight VBA in the Excel unmanaged space. An interesting experiment for you would be to write the data in csv format to a file like "data.csv". Then, from within Excel, open "data.csv" as just another worksheet. Show quote "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message news:83AE0EB8-6C84-4A88-8D22-CD096E93CF1F@microsoft.com... > When I call the myTableAdapter.Fill() operation, a blue rectangle appears > around all of the cells that were bound to the ListObject, but nothing > else > is happening to the screen during the loading of the data. So when I > tried > your suggestion, all that happened is that the blue rectangle didn't > appear. > No performance improvement. The way I know that ADO.NET is fast is > because I > tried the myTableAdapter.Fill() operation without being bound to the cells > and it executed in a second or two. I tried loading 2500 records and it > takes about 20 seconds with the bound ListObject for the data to appear. > Why > can ADO.NET query 61K records instantly, but it takes 10 minutes to get > them > into the Excel cells? > > I'm using VB in VSTO, not VBA, so the syntax for me is > Globals.ThisWorkbook.Application.ScreenUpdating = false. > > > "Jim Rand" wrote: > >> Did putting this code in prior to the data binding help? >> this.Application.ScreenUpdating = false; >> >> >> >> >> >> "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message >> news:120588A0-A7D3-4FF7-BF28-FE3C6598B830@microsoft.com... >> > Yes, you are correct, it is not an ADO.NET problem. ADO.NET gets the >> > 61K >> > records almost instantly. The bottleneck occurs in the binding of the >> > dataset to the ListObject control in Excel. I will try posting this in >> > another discussion group. >> > Thank you. >> > >> > "Miha Markic" wrote: >> > >> >> Hi, >> >> >> >> I don't think this is ado.net related problem. You should do some >> >> performance measuring, perhaps using a performance profiler to find >> >> the >> >> origin of the bottle neck. >> >> >> >> -- >> >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> >> RightHand .NET consulting & development www.rthand.com >> >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> >> >> >> "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message >> >> news:B8BD6529-5C23-46F7-AA63-62AF9A224AC1@microsoft.com... >> >> > If I use Excel to open an Excel file with 61K records, they appear >> >> > instantly. >> >> > If I fill an ADO.NET recordset in Visual Basic with 61K records, it >> >> > fills >> >> > almost immediately. But if I bind the dataset to a ListObject in >> >> > Excel >> >> > in >> >> > VSTO, then populating the workshseet takes 20 minutes. I can use a >> >> > For...Next loop to read the dataset and fill each row of the >> >> > worksheet, >> >> > but >> >> > that takes a couple of minutes. What is the way to instantly fill >> >> > an >> >> > Excel >> >> > spreadsheet with a dataset from SQL Server? >> >> >> >> >> >> >> Whether it's ADO.NET or ADO in the VBA, copying an array to the worksheet has
been the quickest I have worked with. There are lots of other options though. Great link here, with additional links at the bottom. http://support.microsoft.com/default.aspx/kb/321686 -- Show quoteTom Garth "CerfurMark" wrote: > When I call the myTableAdapter.Fill() operation, a blue rectangle appears > around all of the cells that were bound to the ListObject, but nothing else > is happening to the screen during the loading of the data. So when I tried > your suggestion, all that happened is that the blue rectangle didn't appear. > No performance improvement. The way I know that ADO.NET is fast is because I > tried the myTableAdapter.Fill() operation without being bound to the cells > and it executed in a second or two. I tried loading 2500 records and it > takes about 20 seconds with the bound ListObject for the data to appear. Why > can ADO.NET query 61K records instantly, but it takes 10 minutes to get them > into the Excel cells? > > I'm using VB in VSTO, not VBA, so the syntax for me is > Globals.ThisWorkbook.Application.ScreenUpdating = false. > > > "Jim Rand" wrote: > > > Did putting this code in prior to the data binding help? > > this.Application.ScreenUpdating = false; > > > > > > > > > > > > "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message > > news:120588A0-A7D3-4FF7-BF28-FE3C6598B830@microsoft.com... > > > Yes, you are correct, it is not an ADO.NET problem. ADO.NET gets the 61K > > > records almost instantly. The bottleneck occurs in the binding of the > > > dataset to the ListObject control in Excel. I will try posting this in > > > another discussion group. > > > Thank you. > > > > > > "Miha Markic" wrote: > > > > > >> Hi, > > >> > > >> I don't think this is ado.net related problem. You should do some > > >> performance measuring, perhaps using a performance profiler to find the > > >> origin of the bottle neck. > > >> > > >> -- > > >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] > > >> RightHand .NET consulting & development www.rthand.com > > >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > >> > > >> "CerfurMark" <CerfurM***@discussions.microsoft.com> wrote in message > > >> news:B8BD6529-5C23-46F7-AA63-62AF9A224AC1@microsoft.com... > > >> > If I use Excel to open an Excel file with 61K records, they appear > > >> > instantly. > > >> > If I fill an ADO.NET recordset in Visual Basic with 61K records, it > > >> > fills > > >> > almost immediately. But if I bind the dataset to a ListObject in Excel > > >> > in > > >> > VSTO, then populating the workshseet takes 20 minutes. I can use a > > >> > For...Next loop to read the dataset and fill each row of the worksheet, > > >> > but > > >> > that takes a couple of minutes. What is the way to instantly fill an > > >> > Excel > > >> > spreadsheet with a dataset from SQL Server? > > >> > > >> > > > > > > Hi,
Ado.Net does not know the fenomen Recordset. The Recordset is from classic Ado therefore you can ask this in my idea better in a newsgroup for classic Ado. (You can use the classic ADO methods in VB.Net, that is used by the conversion from VB6 to VB.Net however it is not Ado.Net) Cor  
Other interesting topics
|
|||||||||||||||||||||||
