SQL Server CE and SELECT...

[Guest]

Hi,

I have in a DB, a table with 10000 rows.
I do a request : "SELECT MAX(idalt) AS id,reference FROM reffab WHERE
fab='myfab' GROUP BY fab,reference" and the time to obtain the result seems
to be long.
If i have 154 rows answer from this request, the time is 2.8 seconds for the
request and 1.2 seconds to obtain the data from the DataReader.
If i have 3600 rows answer from the request, the time is 6.7 seconds for the
request and 18 seconds to obtain the data from the DataReader.
Can someone tell me if it is a normal time?
4 seconds to extract 154 rows from a table with 10000 rows!!
24 seconds to extract 3600 rows from a table with 10000 rows!!
My device is an ARM CPU 150MHz
My portion of code:
dr = cmd.ExecuteReader(); //the time request...
while(dr.read())
{
// t[i++] = dr["id"]+","+dr["reference"];
} //the whole while time is the time to obtain data from the DataReader dr

dr.read() seems to take a lot of time.
I use Windows CE 4.2 ans VS2003

I try to use a dataadapter with a dataset, but i obtain the same time.

Thanks to help me

 

[chris-s]

Several things to consider..

1) Ensure you are using some suitable indexes
2) "Group By" hits performance
3) Reference fields by index number not name, eg dr[0]
4) Assuming you can't restructure your database schema to be more
efficient, upgrade to SQL Mobile which is faster.

Chris

 

[Guest]

hum... i don't use any index. Just an automatic primary key.
Which index should i use for my request?

Thanks

 

[chris-s]

Which index...looking at your select I would try a combined index on
"fab" and "idalt"

Chris