IDataAdapter taking too long

[Robb Smith]

I am using a SqlDataAdapter to retrieve a DataSet from the database. When I
Trace the creation of the SqlDataAdapter object, it is taking more time than
it does to retrieve the DataSet! Here's my code:

Dim da As New SqlDataAdapter(sSQL, cn)
Dim ds As DataSet = da.Fill

where sSQL is the SQL statement and cn is the connection. On a client
machine that is running EXTREMELY poorly, I have a large query that returns
33,000 records in 1.7s (second line). However, it takes 1.9s just to create
the data adapter (first line)! (Times on my development and test machines
are fractions of a second.)

What is the data adapter doing to take so long? Even on my machine it
shouldn't take more time to simply create an object as it does to read the
data. Are there options I can turn "off" for the data adapter? Is there
another (simple) way to pull from the database into a DataSet? I'm already
using my own code to do updates to the database because I don't like the
commands generated by the data adapter -- I should be able to do the same
for reading data.

Robb

 

[William \(Bill\) Vaughn]

How are you measuring the time? My real question is why are you using a
query tool to do bulk operations?

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Robb Smith]

Bill,

Let me clarify -- this code is part of custom DataGrid paging. I want to
read just a page worth of data for display in my custom control. In my
particular case, I only want 35 records to be returned for the page so I'm
using the startRecord and maxRecord parameters of the Fill method to do
this. Unfortunately, the Fill method is ALSO ineffient because it reads all
33,000 records then only returns a DataSet with the 35 records I want. I
currently have my SQL statement "smart" enough to return only the TOP 35 x
page records, which makes the Fill faster, but not the SqlDataAdapter
creation.

My connection is a custom object that creates the SqlDataAdapter then
assigns it to an IDbDataAdapter variable. If I were connected to Oracle, it
would create an OracleDataAdapter object then assign it to IDbDataAdapter. I
then use IDbDataAdapter.Fill to retrieve the data.

If there is another way to extract from the database to a DataSet that will
do what I want more efficiently, it may resolve my data a dapter issue.

As it stands, it still takes a relatively "long" time just to create the
IDbDataAdapter object when the underlying SQL statement returns a large
number of records -- and I don't know why.

Robb

 

[William \(Bill\) Vaughn]

I would get the server to do the work. Use a TOP query with a bounded WHERE
clause to restrict the number of qualifying rows to a range using the PK.

SELECT TOP 25 col, col2 FROM X WHERE pk BETWEEN lastPkRead AND
lastPkRead+35

or somesuch

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Robb Smith]

Bill,

That is a good suggestion for my problem with the Fill method, except
that it doesn't work when I allow the user to sort on any column.

The bigger problem is why it still takes as much time to create the
SqlDataAdapter as it does to read in the full 33,000 records!

Robb

 

[William Ryan]

Robb:

That sounds very questionable. Like Bill asked, how are you measuring that
on the client machine? That really sounds bizarre and I'd like to see how
measurement is working.