SqlDataReader reads are ridiculously slow

  • Thread starter Thread starter enantiomer
  • Start date Start date
E

enantiomer

Hey there. I have ported my business services project over to the CF.
All of my services use a dataReader to fill the business objects. In
porting over, however, I have noticed that reading data from the
SqlDataReader is very slow. I am not doing anything fancy. I create
my connection, command and execute a SqlDataReader.

As I loop through the result set and am reading column values, the
actualy reading of the column value seems to be the bottleneck. It was
taking over a second just to read a column value! Could this be an
issue with round trip time? I thought that when you call Read() from
your datareader, it puts the entire row into memory.

below is the read part that is taking forever. Anyone use a
SqlDataReader running on a compact framework device? My device is an
hp iPAQ... it's supposed to be pretty fast.

if (dataReader["DoDCageCode"] != DBNull.Value)
supplier.DoDCageCode = (string)dataReader["DoDCageCode"];

if (dataReader["EanUccCompanyPrefix"] != DBNull.Value)
supplier.EanUccCompanyPrefix =
(string)dataReader["EanUccCompanyPrefix"];

I experimented with just assigning the dataReader values to strings and
it is still just as slow. Do mobile devices have less networking
bandwidth on them? Any clues?

Thanks,

Jonathan
 
I forgot to mention that I tried using a sqlDataAdapter to fill a
dataset and that way worked pretty fast. I can't see myself modifying
all of my services just to fit this problem though. We have over 50
service classes that I would have to change. Plus there is the
corporate beaurocracy to deal with. Just thought I would mention that
datasets seem to be working much faster for some reason.
 
you should look at the indexes on your SQL CE/SQL Mobile tables
and whether the SQL that you are using to initialize your SqlCeDataReader
is using a selective index.

you are also paying a BIG penalty by referring to the columns in the
datareader
by their names. the fastest thing to do is call GetValues() on a given row
that
the reader is currently on and then use numeric ordinals on the resulting
object
array to grab the column value you want. if you don't want to use
GetValues(),
at least refer to the datareader column by ordinal and not by name.

you can also look at using the SqlCeResultSet if you are talking about
a SQL Mobile/CF2 application.

--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
 
That's the first report I see about DataReader been slower than DataAdapter.
J

Anyway, DataAdapter actually uses the very same DataReader to fill the
DataSet spending 85-90% of the time in the reader.



I've few suggestions:



1.. Use IsDBNull() to check for nulls, do not compare with DbNull.
2.. Use Get<Type> to get particular types.
3.. Use column ordinals, not names.
4.. Use GetValues() to get all columns at ones.
--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
 
Back
Top