SQL server again

  • Thread starter Thread starter Zanna
  • Start date Start date
Z

Zanna

Hi all!

I got a little problem with the connection of a pocketPC to a SQL server DB.

I noticed that the DataAdapter.Fill(DataSet) is reeeeealy slow... :(

I don't know if this is due to the wireless lan or to my newbye-way of doing
it or whatever (the same app works fast on as a normal win32 app).

Someone can give me some tips to make the fill in the faster way?

Thank you!
 
Zanna,

If you've got it working at all, that's about what you can expect. It's not
realistic to expect the same performance on a compact device as you get on
the desktop, partly because of the hardware and partly because of the
implementation of the DataSet on the device. I don't know of any way to
really speed things up, but you might be able to improve the apparent speed
to your users if you are able to construct your app so you are only getting
the minimum amount of data that you need from the server each time.
 
Ginny Caughey said:
I don't know of any way to
really speed things up, but you might be able to improve the apparent speed
to your users if you are able to construct your app so you are only getting
the minimum amount of data that you need from the server each time.
Click to expand...

Ok, but I think I'm doing something wrong (so if you have some code...)
because the .Fill() take up to a couple of minutes for reading some hundreds
of records and some seconds for a single record.

This is not acceptable. :(
 
C#

cn = new SqlCeConnection("Data Source=\\My Documents\\MyDB.sdf;");
cn.Open();
SqlCeCommant cmd = cn.CreateCommand();
cmd.CommandText = "Select * FROM YourTable";
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

at this point you can set the data source of a grid or something else to the
ds.Tables[0] table...

A couple of things that can slow the query down...

#1 - list the items to select instead of a * - as this may result in a table
scan
#2 - index your records if they haven't been (can increase performance
dramatically)
#3 - return the results in 'pages' if you can
#4 - if you are adding / deleteing regularly in your db - compact it

Rick Winscot
www.zyche.com
 
Zanna,

I got performance similar to this when reading just XML from a file already
on the device into a DataSet, so I don't necessarily think you're doing
anything wrong. I don't have a wireless CE device to test this with at the
moment, but perhaps some other people can give you an idea how long it takes
them to fill a dataset with n records using SqlClient.

Assuming that even if you could double the performance you still might find
it unacceptable, let's try to think of alternative ways to accomplish the
same thing. Do you really need all those hundreds of records on the device
at once, or could you read them in in pages as a user scrolls though a
listbox?
 
Rick,

I think Zanna is getting the data directly from a SQL Server database using
SqlClient instead of using a local SqlCe database, but I agree with your
suggestions.
--
Ginny Caughey
..Net Compact Framework MVP

Rick Winscot said:
C#

cn = new SqlCeConnection("Data Source=\\My Documents\\MyDB.sdf;");
cn.Open();
SqlCeCommant cmd = cn.CreateCommand();
cmd.CommandText = "Select * FROM YourTable";
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

at this point you can set the data source of a grid or something else to the
ds.Tables[0] table...

A couple of things that can slow the query down...

#1 - list the items to select instead of a * - as this may result in a table
scan
#2 - index your records if they haven't been (can increase performance
dramatically)
#3 - return the results in 'pages' if you can
#4 - if you are adding / deleteing regularly in your db - compact it

Rick Winscot
www.zyche.com



Zanna said:
Ok, but I think I'm doing something wrong (so if you have some code...)
because the .Fill() take up to a couple of minutes for reading some hundreds
of records and some seconds for a single record.

This is not acceptable. :(
Click to expand...
Click to expand...
 
Ginny said:
I think Zanna is getting the data directly from a SQL Server database using
SqlClient instead of using a local SqlCe database, but I agree with your
suggestions.
Click to expand...

Yes, I do with a connection to SQLserver, not a SQLCE server.
And the suggestions are right, but it seems a limitation of the
compactframework implementation, because the *same* app compiled ad
win32 app works fine.

Someone has noticed the same problems or they are something in my
connection/configuration of the SQLserver?
 
Zanna,

It's probably not so much the limitation of the Compact Framework as the
limitation of the hardware you're running it on. On the desktop you're
running an i86 processor at 1-2Ghz, and on the device you are not. I doubt
that your configuration is the problem since you are getting it to work, but
since the performance isn't what you'd hoped, you'll probably have to work
around it somehow. You're using the DataReader rather than the DataAdapter I
think, so you're already using the fastest approach.
 
Back
Top