|
| |
User Groups : Forums : SiteExperts :
Microsoft .NET
:  |  Limitations on DataTable?
Ok, I have an issue with using a .Fill() on a DataTable.
The problem, I think, is that the DataTable is being populated with over 100,000 rows.
Here is the function I am using:
Public Function GetDataTable() As DataTable
Dim DT As New DataTable()
odbAdapter = New OleDbDataAdapter
odbAdapter.SelectCommand = New OleDbCommand(QueryToExecute, odbConn)
odbConn.Open()
odbAdapter.Fill(DT)
odbConn.Close()
Return DT
End Function
#####
Well, I have a couple of queries that pull over 100,000 records into a DataTable (see the function above).
When I execute the queries in my Query Editor (TOAD), they take milliseconds to execute. However, when I try to run it in the testing environment, one of the queries takes between 22 and 34 seconds to execute.
So, my question is this: Are there practical limitations to the number of rows/columns that a DataTable can hold? Am I going about this the wrong way?
This is driving me crazy.
Any advice?Started By Monte on Sep 14, 2009 at 8:19:59 AM | | | 2 Response(s) | Reply |
| Earlier Replies | Replies 1 to 2 of 2 | Later Replies | | | Monte on Sep 14, 2009 at 1:04:41 PM (# 1) I think I found a "work-around" for this.
I created an other function with the same basic functionality as the original post, except I replaced this line:
odbAdapter.Fill(DT)
with this one:
odbAdapter.Fill(1, 100, DT)
Essentially, on the .Fill(), I start with record 1, and fill the DataTable with the first 100 rows.
It seems to work so far, I'm testing it right now.
ChrisRickard on Sep 14, 2009 at 5:12:53 PM (# 2)Yeah 100,000 rows of data is a lot no matter how you look at it. I'm curious as to why you're querying for that many when all you need is 100 of them.
Even though you're saving a lot of time because storage for 99,900 rows is no longer being created on the client your database server is still executing a very expensive query. Also depending upon your database provider all that extra data is also still going over the network.
All the higher end DB tools like Toad run queries asynchronously and display a portion of the data as soon as it's available. Many will also impose a hard capped limit (usually this is configurable). Contrast this with your code that runs synchronously the Fill() method won't return until the whole thing is done which is why it's taking 30+ seconds.
| | Earlier Replies | Replies 1 to 2 of 2 | Later Replies |
To respond to a discussion, you must first logon.
If you are not registered, please register yourself to become a member of the SiteExperts.community.
|
