SQLCE speed AGAIN - What is wrong???

  • Thread starter Thread starter news
  • Start date Start date
N

news

Hello all.

How do you do that you are able to read 20.000 records in a few seconds (I
have read somewhere 20 or 30seconds)???

1) My Configuration is SQLCE 2.0, CF 1.0 SP1, iPaq 5450, PPC2002
2) My table has 8.000 records only and 15 columns
3) I have indexes there.

Here is definition of my table
-------------------------------------
2 ApplicationID nchar 2 0
2 ProductID nchar 25 0
0 ProductCategoryRepID nchar 6 1
0 ProductCategoryID nchar 1 1
0 ProductGroupID nchar 6 1
0 ProductTypeID nchar 6 1
0 TesterCode nchar 25 1
0 Description nchar 40 1
0 EAN nchar 13 1
0 ItemsCount int 4 1
0 Price numeric 9 1
0 AdvisedPrice numeric 9 1
0 Tax int 4 1
0 Status nchar 1 1
0 DeliveryDate nchar 6 1
0 StoreAmount nchar 15 1
0 SortKey int 4 1
0 IsNew bit 1 0
1 rowguid uniqueidentifier 16 0
-------------------------------------

4) This construction takes about 84 seconds

SqlCeCommand sqlCmd = new SqlCeCommand("SELECT ProductID,
ProductCategoryRepID, ProductCategoryID, ProductGroupID, ProductTypeID,
TesterCode, Description, EAN, Price, Status, SortKey, IsNew FROM
tblProducts", AppDatabase.AppSqlConn);
SqlCeDataReader dReader = sqlCmd.ExecuteReader();
ArrayList aList = new ArrayList();
while(dReader.Read())
{
object[] row = new object[12];
dReader.GetValues(row);
aList.Add(row);
}

5) This construction takes 127 seconds

SqlCeDataAdapter daProducts = new SqlCeDataAdapter("SELECT ProductID,
ProductCategoryRepID, ProductCategoryID, ProductGroupID, ProductTypeID,
TesterCode, Description, EAN, Price, Status, SortKey, IsNew FROM
tblProducts", AppDatabase.AppSqlConn);
daProducts.Fill(AppDatabase.AppDataSet,"tblProducts");

WHAT is wrong?????
Please help me, DeathLine is comming....

Thanks
(e-mail address removed)
 
Second construction with DataSet takes much more times than
aList.Add(row);..it is not the problem....



Barkodas said:
maybe problem is

' aList.Add(row);' ?



[email protected] said:
Hello all.

How do you do that you are able to read 20.000 records in a few seconds (I
have read somewhere 20 or 30seconds)???

1) My Configuration is SQLCE 2.0, CF 1.0 SP1, iPaq 5450, PPC2002
2) My table has 8.000 records only and 15 columns
3) I have indexes there.

Here is definition of my table
-------------------------------------
2 ApplicationID nchar 2 0
2 ProductID nchar 25 0
0 ProductCategoryRepID nchar 6 1
0 ProductCategoryID nchar 1 1
0 ProductGroupID nchar 6 1
0 ProductTypeID nchar 6 1
0 TesterCode nchar 25 1
0 Description nchar 40 1
0 EAN nchar 13 1
0 ItemsCount int 4 1
0 Price numeric 9 1
0 AdvisedPrice numeric 9 1
0 Tax int 4 1
0 Status nchar 1 1
0 DeliveryDate nchar 6 1
0 StoreAmount nchar 15 1
0 SortKey int 4 1
0 IsNew bit 1 0
1 rowguid uniqueidentifier 16 0
-------------------------------------

4) This construction takes about 84 seconds

SqlCeCommand sqlCmd = new SqlCeCommand("SELECT ProductID,
ProductCategoryRepID, ProductCategoryID, ProductGroupID, ProductTypeID,
TesterCode, Description, EAN, Price, Status, SortKey, IsNew FROM
tblProducts", AppDatabase.AppSqlConn);
SqlCeDataReader dReader = sqlCmd.ExecuteReader();
ArrayList aList = new ArrayList();
while(dReader.Read())
{
object[] row = new object[12];
dReader.GetValues(row);
aList.Add(row);
}

5) This construction takes 127 seconds

SqlCeDataAdapter daProducts = new SqlCeDataAdapter("SELECT ProductID,
ProductCategoryRepID, ProductCategoryID, ProductGroupID, ProductTypeID,
TesterCode, Description, EAN, Price, Status, SortKey, IsNew FROM
tblProducts", AppDatabase.AppSqlConn);
daProducts.Fill(AppDatabase.AppDataSet,"tblProducts");

WHAT is wrong?????
Please help me, DeathLine is comming....

Thanks
(e-mail address removed)
Click to expand...
Click to expand...
 
Just don't try and read that much data at once. Its just not the way you do
things with Sql.

There is no way you need 8,000 records all at one time.

If you want to display records in a grid, and give the impression of
browsing a large list then you will have to write a cache class,.that loads
a couple of screenfuls at one time. If the user doesn't get tired on
scrolling through the records, then read again another batch of records. You
can have your cake and eat, but you need to change your approach.

Graham
 
Graham is right, trying to display that many records at once is not as
performant as grabbing them in blocks. Also, the DataSet is always less
performant than the DataReader. The DataSet must load all of the data into
RAM. It also recompiles each query run, though this shouldn't be a problem
for you here.

Kevin Boske
([email protected])
SQL Server CE Team
Microsoft
----------------------------------------------------------------------------
----------------------------------------
Everything you need to know about SQL Server CE:

http://www.microsoft.com/sql/ce/techinfo/default.asp
----------------------------------------------------------------------------
----------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
----------------------------------------------------------------------------
----------------------------------------
--------------------
From: "Graham McKechnie" <[email protected]>
References: <O2x#[email protected]>
Subject: Re: SQLCE speed AGAIN - What is wrong???
Date: Thu, 4 Sep 2003 22:53:28 +1000
Lines: 80
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups:
microsoft.public.dotnet.framework.compactframework,microsoft.public.sqlserve
r.ce
NNTP-Posting-Host: cpe-144-132-15-69.vic.bigpond.net.au 144.132.15.69
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:9203
microsoft.public.dotnet.framework.compactframework:32750
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Just don't try and read that much data at once. Its just not the way you do
things with Sql.

There is no way you need 8,000 records all at one time.

If you want to display records in a grid, and give the impression of
browsing a large list then you will have to write a cache class,.that loads
a couple of screenfuls at one time. If the user doesn't get tired on
scrolling through the records, then read again another batch of records. You
can have your cake and eat, but you need to change your approach.

Graham


[email protected] said:
Hello all.

How do you do that you are able to read 20.000 records in a few seconds (I
have read somewhere 20 or 30seconds)???

1) My Configuration is SQLCE 2.0, CF 1.0 SP1, iPaq 5450, PPC2002
2) My table has 8.000 records only and 15 columns
3) I have indexes there.

Here is definition of my table
-------------------------------------
2 ApplicationID nchar 2 0
2 ProductID nchar 25 0
0 ProductCategoryRepID nchar 6 1
0 ProductCategoryID nchar 1 1
0 ProductGroupID nchar 6 1
0 ProductTypeID nchar 6 1
0 TesterCode nchar 25 1
0 Description nchar 40 1
0 EAN nchar 13 1
0 ItemsCount int 4 1
0 Price numeric 9 1
0 AdvisedPrice numeric 9 1
0 Tax int 4 1
0 Status nchar 1 1
0 DeliveryDate nchar 6 1
0 StoreAmount nchar 15 1
0 SortKey int 4 1
0 IsNew bit 1 0
1 rowguid uniqueidentifier 16 0
-------------------------------------

4) This construction takes about 84 seconds

SqlCeCommand sqlCmd = new SqlCeCommand("SELECT ProductID,
ProductCategoryRepID, ProductCategoryID, ProductGroupID, ProductTypeID,
TesterCode, Description, EAN, Price, Status, SortKey, IsNew FROM
tblProducts", AppDatabase.AppSqlConn);
SqlCeDataReader dReader = sqlCmd.ExecuteReader();
ArrayList aList = new ArrayList();
while(dReader.Read())
{
object[] row = new object[12];
dReader.GetValues(row);
aList.Add(row);
}

5) This construction takes 127 seconds

SqlCeDataAdapter daProducts = new SqlCeDataAdapter("SELECT ProductID,
ProductCategoryRepID, ProductCategoryID, ProductGroupID, ProductTypeID,
TesterCode, Description, EAN, Price, Status, SortKey, IsNew FROM
tblProducts", AppDatabase.AppSqlConn);
daProducts.Fill(AppDatabase.AppDataSet,"tblProducts");

WHAT is wrong?????
Please help me, DeathLine is comming....

Thanks
(e-mail address removed)
Click to expand...
 
while(dReader.Read())
{
object[] row = new object[12];
dReader.GetValues(row);
aList.Add(row);
}
Click to expand...

how long does it take if you comment out the "aList.Add(row)"
(so you won't see any data at all but get the pure speed of reading the
data)

Then you know how "fast" the reader is and what time it takes to display
all data.

i think, on a PPC you have to think about if you _really_ like to display
8000 records... or if you like to display - say - 20 records and after
pressing a button showing the next 20 records... (like we have done in good
old DOS-Times ;) )

Boris
 
Back
Top