Re: Slow performance when counting rows

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hey Mathew,

Statistics are maintained to do the cost based optimization of query
plans. sp_show_statistics is the interface to retrieve the stats. As with
any query processor, statistics are updated at "appropriate" time. The
appropriateness in SQL CE is tied with number of changes to table after the
last statistics calculation. You can also make SQL CE collect statistics by
explicitly triggering "sp_update_statistics".

Thanks,
Laxmi

"matvdl" <matvdl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2707FC3E-B525-4824-9BFE-20CB6BBB2DCB@xxxxxxxxxxxxxxxx
Thankyou for that.

I am using sqlCE 3.5.

The following code gives me "the specified table does not exist"

Dim cmd As New SqlCeCommand
cmd.CommandText = "sp_show_statistics 'datData'"
'This will open the connection
Con.Connection = Me.LocalConnection
Dim rec As SqlCeDataReader = Command.ExecuteReader

What would I be doing wrong?

--
matthew


"João Paulo Figueira" wrote:

If you are using SQL Compact 3.0 or 3.5 you can try executing the
following
command:

sp_show_statistics 'datData'

The result set contains a column named 'ROWS' of type UInt32 containing
the
number of rows in the table.

Regards,
João Paulo Figueira

"matvdl" <matvdl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9D7E40DD-C068-42BD-B0CD-17AB91E98ED8@xxxxxxxxxxxxxxxx
Hello,

I am using a winCE Device to develop a data application. I am using
SQLCe
to hold the data that is collected on the device and send this data
back
to
the office.

The data is collected on a number of stream at 1min intervals - the
data
that is stored on the device can therefore grow very quickly. After a
few
days it's easy to get 100,000 rows of data in the table.

When I run the following statement
Select Count(*) from datData

It can take a very long time - sometimes 5 or more seconds.

Is there anyway (other than deleting rows) to speed this up?
--
matthew



.