Large VB ADO.NET Recordset Import into Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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.
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;





CerfurMark said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;





CerfurMark said:
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 said:
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/

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?
Click to expand...
Click to expand...
Click to expand...
 
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.


CerfurMark said:
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 said:
Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;





CerfurMark said:
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.

:

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/

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?
Click to expand...
Click to expand...
Click to expand...
 
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
 
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

--
Tom Garth


CerfurMark said:
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 said:
Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;





CerfurMark said:
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.

:

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/

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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top