It takes a long time to insert the first record each time when the program start

I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

However, when I give up the dataset and using the following code:

cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount

I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

Last example does not include looking for updated rows in the DataSet and generating appropriate commands. Since it does less, it's faster.

sql

Issue with SqlCeParameter (SqlCE 3.1)

Hi,

I have a simple query as follows:

SELECT COUNT(ID) FROM AI_DTREE DT WHERE PARENT =@.pPID AND

CARTRIDGE_ID = @.pCID AND COMMAND =@.pCMD AND OBJECT =@.pObj

Where @.pPID=16700130,@.pCID=43000000,@.pCMD=”=”, and

@.pObj=”the cecum, identified by appendiceal orifice & IC valve”

The filed OBJECT in AI_DTREE is of nvarchar(30)(of course the length of @.pObj is more than 30 in my current query).

I have build the SqlCeCommand sccmd object with the above sql text and the parameters.

returnval = sccmd.ExecuteScalar();

When I execute the above statement I am getting the following error:

ex.Message = "@.pObj : String truncation: max=30, len=55, value='the cecum, identified by appendiceal orifice & IC valve'."

But when I execute the same in Sql Server Management Studio against SqlCE db, it works fine and the result returnval =0.

How to overcome this SqlCeParameter issue?, for me it is difficult to messure the length of the filed before I exeuting the command.

Thanks

G Sreenaiah

You must ensure that the length of @.pObj is no more than 30 chars, or make the field bigger!|||

Hi Erik,
Thanks for your prompt reply.

I am just executing the above command from my C# code. If I want to ensure the length of the parameter is less than the length of field, then I should go for one more database hit to fetch field’s length first.

This is not the way happening with i) OleDbCommand, OleDbParameter, ii) OracleParameter, OracleCommand

Please let me know if anybody else has an idea how this can be resolved with workout having one more db hit to fetch fields’ length.

Why it is happening only with SqlCeCommand, SqlCeParameter? why not with OracleParameter, OracleCommand ?

Thanks
G Sreenaiah