UPDATE & INSERT speed improvement question
-
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
-
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
Hello, Are you using Store Procedure for insert and update ? Use set nocount on/off in store procedure. Regards, S Kumar
-
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
Lior S wrote:
The main slowing reason that i have encounter is the INSERT and UPDATE command that can be processed for more then 2 minutes
What is being updated? What else is the SQL Server doing at the time of the update? It may be a poor data model, or it may be that there are so many transactions taking place at the same time that the SQL Server cannot cope. Tweaking the data model may help. So might the creation of staging tables. (Where you queue changes to the data and then have a process that implements the changes once in a while). But, you really haven't provided enough information. The reason for the slow down may be due to a million and one possible things.
*** Developer Day 4 in Reading, England on 2nd December 2006 - Registration Now Open *** Upcoming Scottish Developers events: * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
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
basically the main slowing reason is the UPDATE procedure since it is searching in more then 5 million records to fit to the conditioned row for the update. i have used the c# SqlBulkCopy which provided me a good method to insert data quickly but still i don't have any solution for the update.
-
basically the main slowing reason is the UPDATE procedure since it is searching in more then 5 million records to fit to the conditioned row for the update. i have used the c# SqlBulkCopy which provided me a good method to insert data quickly but still i don't have any solution for the update.
Lior S wrote:
basically the main slowing reason is the UPDATE procedure since it is searching in more then 5 million records to fit to the conditioned row for the update.
Sounds like the search component isn't very efficient. Are you searching on the primary key, or an index? If not make an index from the columns on which you are searching.
*** Developer Day 4 in Reading, England on 2nd December 2006 - Registration Now Open *** Upcoming Scottish Developers events: * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos