UPDATE & INSERT speed improvement question

[lior3790]

Hello,

I'm working on a project which carrying a large scale of database (over 5 millions records).
I'm looking for a way to improve the communication speed with the SQL server in any way that i can find useful.

The main slowing reason that i have encounter is the INSERT and UPDATE command that can be processed for more then 2 minutes and this amount of time is unreasonable.

can anyone please advise how to do a different process method?

thanks,
Leo S

[exterminator]

What database is it? Do you have indexes? Are these bulk inserts/bulk updates (more than one row) or single row statements..?

[lior3790]

Its a SQL server database and i didn't index it, I'm learning these implementations as we speak.

As far as the INSERT/UPDATE, i use per row command and not as bulks.

Is there a way to let the SQLServer decide if the data precessed needs to be updated only or a brand new insert?
these options will give me the ability to do it as bulks.

[exterminator]

As far as the INSERT/UPDATE, i use per row command and not as bulks.Try building a bulk query command. I don't think one single update or insert would take time in minutes. Otherwise, you have problems with indexes. Is there a primary key in your table? That will be a clustered index - every time a record is deleted or inserted (or the column value updated which is part of the index) the indexes need to be re-adjusted because clustered indexes are sorted. This might be affecting performance.
Is there a way to let the SQLServer decide if the data precessed needs to be updated only or a brand new insert?
these options will give me the ability to do it as bulks.I don't understand this question... If you issue an INSERT query - that will insert a new row into the table. If you issue an UPDATE query - that will not insert a new row but will update an existing row values of the fields as supplied to the query.

[lior3790]

What i meant is that i use a loop in my code to insert or update (depending if itemID exists or not) the data per row from my data table in the source code.

if theres a way to tell the sql server to update id itemID for example exists and to insert if the itemID doesn't exists.

Thnks

[exterminator]

You should be able to know this... whatever you have on the application side would be something that either you read from the db earlier or something new.. if it is one that was read - you update it and if not then you insert it. The ID field is kept as an identity column (read up on it).

Also, if it is the data table as in .Net - you can do that in one hit to the db. For knowing more on that post on the C# forum or the ADO.Net forum.

If that all does not suit your need - use a stored procedure.. there you can put this logic - plus you do not need to make 2 db hits one to check existence and one to fire insert/update. You can encapsulate this logic in the stored procedure which will check existence and do the needful using IF EXISTS or IF NOT EXISTS etc.

[cjard]

In oracle we would write a MERGE query. You havent told us enough about your problem to properly advise you of what to do. Are you loading data from the client in UPSERT style? How? What queries are you using? Whats the client code look like. Simplified code if possible, please.

[Alsvha]

Its a SQL server database and i didn't index it, I'm learning these implementations as we speak.

As far as the INSERT/UPDATE, i use per row command and not as bulks.

Is there a way to let the SQLServer decide if the data precessed needs to be updated only or a brand new insert?
these options will give me the ability to do it as bulks.

If you mean if you can check if a row exists and then update it, instead of inserting it?
then you can use "EXISTS"
IF EXISTS (--SELECT the row you need to find) BEGIN
--UPDATE
END ELSE BEGIN
--INSERT
END

If not, you need to try and explain your problem a bit more specific.