Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
neeraj1401
Starting Member
36 Posts |
 Posted - 2009-07-14 : 02:48:37
|
| I'm facing the performance issue with following query, It is working fine when 2 or 3 end user are updating the record but it is taking lot of time when 15 or 20 end user updating the record from front end application. Could you please suggest how to improve it. StrSql= "update master SET Starttime='"&Starttime2&"', Endtime='"&now&"', CC1='"&arrVals1(i)&"', CC2='"&arrVals2(i)&"',CC3='"&arrVals3(i)&"', Inputdate='"&Date&"', Status='"&t2&"',GroupID='"&arrVals(0)&"' where "str= "ECCUS_master.ID=" &arrVals(i)str2=StrSql & strthanks in advance |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-07-14 : 10:34:14
|
| You really didn't provide enough info to diagnose the problem. Since this is updating a single row in a table by ID (I'll assume that [ID] is the primary key or at least has a unique index) it should be very fast.How many vals are typically in arrVals()?Are you using any transaction control in your frontend code and perhaps writing to other tables within the transaction?Will some users be updating the same rows as other users concurrently?Do you have unrelated queries hitting this table at the same time? They could be blocking the updates.Are there any triggers on this table?Are opening your db connection just long enough to make the call or are you waiting for some user actions to complete the call?Have you done anything to monitor the server while the problem is happening to see if there is blocking or deadlocking?Be One with the OptimizerTG |
 |
|
|
neeraj1401
Starting Member
36 Posts |
Posted - 2009-07-15 : 02:42:37
|
| Thanks for reply. Yes Id key is index key. typically in arrVals() 15 to 20 value will be there. there is no query hint in the query. I did'nt monitor the query perfomance for deadlocks. please suggest what should i do ? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-07-15 : 09:37:48
|
| First you should address the rest of the questions I asked - not necessarily back to me but just for yourself.>>please suggest what should i do ? The first thing I would do is check for blocking: (in a query window) run "sp_who2 active" while the problem is happening. Pay attention to [blkby] column. That would be the spid of the process that is blocking a process. If you see any values there then take the value from the [spid] column and use it in this command: "dbcc inputbuffer(<spid>)" The results will be the command that is being blocked. The same command using the blkby value will be the the command that is blocking. If nothing shows up then its probably not a blocking problem. But first elliminate that...Be One with the OptimizerTG |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-15 : 17:50:43
|
| How big is the table? How many records are getting updated at one time? Transaction will lock the table for other users...you may choose not to lock the table, but you will face dirty read issue. |
|
|
|
neeraj1401
Starting Member
36 Posts |
Posted - 2009-07-16 : 02:51:05
|
| Thanks for reply. Table is not to big it is having 45k to 50k data. A user can update 10 records once from front end application. could you please suggest query hint for this update statement and the query with that query hint. |
|
|
|
|
|
|
How to improve query performance