Insert/Update too slow

Hi,

I have a table with 110 nvarchar (255) columns in the database. I receive the data from the server (array) and them loop through it to insert the data into the database. The problem is it takes more than 3 minutes to insert (or update) (in memory) the 310 records it got from the array. I am reusing the Statement (with parameters). How could I improve the performance?

It's really a small quantity of data, the CPU in the device is 520Mhz... it should be alright!

Can aynone help me please?

Thanks

You can read some performance improvement topics here:

SQL Compact Edition Insert Performance

Tuning SQL Compact Edition Insert Performance

I hope these help you get the performance you want.

|||I've recently found a great article about SQL Server CE insert performance:
http://www.pocketpcdn.com/articles/articles.php?&atb.set(a_id)=11003&atb.set(c_id)=74&atb.perform(details)=&

In brief: OLE DB is the fastest method. When you want to stick to managed code try SqlCeResultSet

|||Thanks Guys!

I'll try that!

Cheers
|||I made the changes and the improvement was about 10X... amazing!!

Also the code is much clear now.

I am using this approach (ResultSet) for Updating and Seeking records as well.

I am happy now!

Insert/Update too slow

Hi,

I have a table with 110 nvarchar (255) columns in the database. I receive the data from the server (array) and them loop through it to insert the data into the database. The problem is it takes more than 3 minutes to insert (or update) (in memory) the 310 records it got from the array. I am reusing the Statement (with parameters). How could I improve the performance?

It's really a small quantity of data, the CPU in the device is 520Mhz... it should be alright!

Can aynone help me please?

Thanks

You can read some performance improvement topics here:

SQL Compact Edition Insert Performance

Tuning SQL Compact Edition Insert Performance

I hope these help you get the performance you want.

|||I've recently found a great article about SQL Server CE insert performance:
http://www.pocketpcdn.com/articles/articles.php?&atb.set(a_id)=11003&atb.set(c_id)=74&atb.perform(details)=&

In brief: OLE DB is the fastest method. When you want to stick to managed code try SqlCeResultSet

|||Thanks Guys!

I'll try that!

Cheers
|||I made the changes and the improvement was about 10X... amazing!!

Also the code is much clear now.

I am using this approach (ResultSet) for Updating and Seeking records as well.

I am happy now!

Insert with condition

Hi,
what i am trying to do is, reading from an array which has the column name and value and then insert that value to the column which is fetched from that array.And on the other hand i should use select to get the data's which have the same ID.
more detail: what i have >> (VARID,columnName,Value)
what i want>> insert to table (columnName) values (Value) while ID =VARID

any idea how i should do that?
tnx
you can resort to dynamic SQL, e.g.

EXEC('insert into table (' + @.columnName + ') VALUES (' + @.value + ') WHERE ID =' + @.id)

HTH,|||Thanks for your reply. But that was my mistake that i thought Insert is what i need. I should use Update instead.Anyway it is the same story. I am using that in an c# application(web service).
I have this in my code:
objConnect = new SqlConnection(connectionString);
string queryString = "upadte MyTable set "+col+"= "+data+" where DeviceID = "+id+";";
try
{
objConnect.Open();
SqlCommand objCommand = new SqlCommand(queryString, objConnect);
objCommand.ExecuteReader();
}
...

It doesn't work, and because it is inside a web method i get the SoapException ..
I also tried with stored procedure,like this:
ALTER PROCEDURE [dbo].[UpdateData]
@.devID varchar(30),
@.dataCol varchar(30),
@.dataVal varchar(30)
AS
BEGIN
SET NOCOUNT ON;

update DataTransmission
set @.dataCol= @.dataVal
where DeviceID = @.devID
END
and it also doen't work when i do : exec UpdateData 'id','user','me'
Do you know what is going wrong?
|||

I am not sure about the cause of failure in your C# application, but the proc UpdateData will not give you desired result because " set @.dataCol= @.dataVal" will set the variable @.dataCol to have the value of the @.dataVal. The column is not touched at all. You can use dynamic sql inside the proc to achieve what you desire.

Thanks

|||I tried to use dynamic sql in stored procedure like this:
create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end
set col2 = case @.col when 'col2' then @.value end
where id = @.id

End

do you think it is right way?
|||that's just fine... but typing will be tedious if you have many columns in your table. |||But sql server has another idea it says there is syntax error (near '=').do you know what is wrong with it? when i check CASE in books, the syntax is right..So i don't know the problem.
|||Remove your second SET statement

create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end,
col2 = case @.col when 'col2' then @.value end
where id = @.id

End

HTH,|||Thanks alot. finally works
|||no prob... glad to be of help