Large VB ADO.NET Recordset Import into Excel

[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?

 

[Miha Markic]

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.

 

[Guest]

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?

 

[Jim Rand]

Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;

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?

 

[Guest]

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.

Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;

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?

 

[Jim Rand]

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.

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.

Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;

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?

 

[Cor Ligthert[MVP]]

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

 

[Guest]

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

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.

Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;

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?