locking on select
-
Hi, I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result. The problem is when more users are trying to generate the report, it seems that it takes more time. My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.
update table
set status = 1
where keyId = 6I don't have any locking on my select nor update. What is the best practice to resolve it? I have 100 users nationwide aside from report users, please need help. Thanks in advance Dabuskol
Dabsukol
-
Hi, I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result. The problem is when more users are trying to generate the report, it seems that it takes more time. My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.
update table
set status = 1
where keyId = 6I don't have any locking on my select nor update. What is the best practice to resolve it? I have 100 users nationwide aside from report users, please need help. Thanks in advance Dabuskol
Dabsukol
dabuskol wrote:
I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result.
You *could* use the SP to retrieve the bare results, and do the calculations in code (on the client) - that would take some work of the server. Post the SP here, and we could have a look at optimizing it.
dabuskol wrote:
The problem is when more users are trying to generate the report, it seems that it takes more time.
That's not a problem, but expected behavior; more requests for the database means simply more work.
dabuskol wrote:
I don't have any locking on my select nor update.
What is the best practice to resolve it?Locking wouldn't help much; you'd still get a time-out if the query does not return within the set timeout-period. Depending on your needs, you could have it timeout after half an hour. Still, best idea would be to move the calculations, limit the joins and optimize the query.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
Hi, I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result. The problem is when more users are trying to generate the report, it seems that it takes more time. My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.
update table
set status = 1
where keyId = 6I don't have any locking on my select nor update. What is the best practice to resolve it? I have 100 users nationwide aside from report users, please need help. Thanks in advance Dabuskol
Dabsukol
Eddy is correct, locking is not the issue here, also check to see if you have spit triggers on the table.
Never underestimate the power of human stupidity RAH
-
Hi, I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result. The problem is when more users are trying to generate the report, it seems that it takes more time. My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.
update table
set status = 1
where keyId = 6I don't have any locking on my select nor update. What is the best practice to resolve it? I have 100 users nationwide aside from report users, please need help. Thanks in advance Dabuskol
Dabsukol
you can use lock hints.. update table WITH (TABLOCKX) set status = 1 where keyId = 6 This lock is most safe as well as it increses your performance..
-
you can use lock hints.. update table WITH (TABLOCKX) set status = 1 where keyId = 6 This lock is most safe as well as it increses your performance..
-
Well it will.. In that case u can use lock hint Select * from TableName WITH (NOLOCK) where 1=1 AND (some condition)
-
Well it will.. In that case u can use lock hint Select * from TableName WITH (NOLOCK) where 1=1 AND (some condition)
Any idea what that will do to a database that's not "well designed"? Really think it improves speed? Ever heard of the term "dirty read"?? Stop and THINK for a second - if it were a matter of adding that keyword, wouldn't it be more simple to have the dirty read by default, whether or not the keyword is included?
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
Any idea what that will do to a database that's not "well designed"? Really think it improves speed? Ever heard of the term "dirty read"?? Stop and THINK for a second - if it were a matter of adding that keyword, wouldn't it be more simple to have the dirty read by default, whether or not the keyword is included?
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
See its fine.. it it would be a draw back, sql shouldnt given it as an option.. Yes there may be dirty read or penthoms but see.. it executes on hard cases where traffic u can find as in mili seconds.. please go through it when it actually creates problem.. Transaction isolation level are used by many companies.. are they fool?? huh.. i myself use read uncommit.. this all are good if u know your architecture..
-
See its fine.. it it would be a draw back, sql shouldnt given it as an option.. Yes there may be dirty read or penthoms but see.. it executes on hard cases where traffic u can find as in mili seconds.. please go through it when it actually creates problem.. Transaction isolation level are used by many companies.. are they fool?? huh.. i myself use read uncommit.. this all are good if u know your architecture..
-
U please read the question.. he is talking about only 100 clients.. u know in a second how many number of locks sql can handle?? every time i don't belive i am good enough for all condition.. same applies to u also..
-
U please read the question.. he is talking about only 100 clients.. u know in a second how many number of locks sql can handle?? every time i don't belive i am good enough for all condition.. same applies to u also..
Aadhar Joshi wrote:
he is talking about only 100 clients..
u know in a second how many number of locks sql can handle??Yes, I do.
Aadhar Joshi wrote:
every time i don't belive i am good enough for all condition.. same applies to u also..
Making mistakes is the only way I learn; trial and error, and the forum is filled with mistakes I made. Drop the subject and the assumptions, it should not have become this personal. It's just code. Something we do. Now excuse me, the hedonist in me is calling.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
U please read the question.. he is talking about only 100 clients.. u know in a second how many number of locks sql can handle?? every time i don't belive i am good enough for all condition.. same applies to u also..
The reason I dislike the dirty read is because it's not fun if you have to debug a race-condition. Imagine the app being a huge success, and being used in another department with ten times the amount of users. Imagine a simple employee-count being incorrect, because only the employee-record has been inserted, but not yet the contract on which the query joins. Having a consistent and reliable database is "always" more important than having a speedy one. Yes, I'm always this rude, just not that touchy. Got some red wine, some red meat, it's time to relax a bit and have a party. It's good to see a dev with passion, and even better to see one defend his opinion. I hope you're gonna stick around, you'll see I'm not an arse all the time. (Sometimes I'm offline and asleep). FWIW, you'll already have seen this[^] page? That's where you must have found the "cheat" to read before all writes are done. You'll have to assume that most programmers don't use the documentation. If it does not produce any problems with your current architecture, then great - means someone put some extra effort in designing the database. If not, mark it and highlight it and explain what it does with comments in code, as most programmers will not go to MSDN. They'll not even ask here.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
The reason I dislike the dirty read is because it's not fun if you have to debug a race-condition. Imagine the app being a huge success, and being used in another department with ten times the amount of users. Imagine a simple employee-count being incorrect, because only the employee-record has been inserted, but not yet the contract on which the query joins. Having a consistent and reliable database is "always" more important than having a speedy one. Yes, I'm always this rude, just not that touchy. Got some red wine, some red meat, it's time to relax a bit and have a party. It's good to see a dev with passion, and even better to see one defend his opinion. I hope you're gonna stick around, you'll see I'm not an arse all the time. (Sometimes I'm offline and asleep). FWIW, you'll already have seen this[^] page? That's where you must have found the "cheat" to read before all writes are done. You'll have to assume that most programmers don't use the documentation. If it does not produce any problems with your current architecture, then great - means someone put some extra effort in designing the database. If not, mark it and highlight it and explain what it does with comments in code, as most programmers will not go to MSDN. They'll not even ask here.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
I apologies for what i did.. I know i should listen you first but as u said i am a small kid and i need to go far away.. I can not compete with u, your knowledge, your points but as a senior u should have helping hands.. I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional.. I have to go far away, I hope i get support from u and u help a kid to grow up.. Sorry, Aadhar
-
I apologies for what i did.. I know i should listen you first but as u said i am a small kid and i need to go far away.. I can not compete with u, your knowledge, your points but as a senior u should have helping hands.. I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional.. I have to go far away, I hope i get support from u and u help a kid to grow up.. Sorry, Aadhar
Good
-
I apologies for what i did.. I know i should listen you first but as u said i am a small kid and i need to go far away.. I can not compete with u, your knowledge, your points but as a senior u should have helping hands.. I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional.. I have to go far away, I hope i get support from u and u help a kid to grow up.. Sorry, Aadhar
Aadhar Joshi wrote:
I apologies for what i did..
So do I, my apologies for the way I acted.
Aadhar Joshi wrote:
I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional..
We both acted like fools for a few minutes. There's no need to compete; programming is a huge topic, it's impossible to cover all topics.
Aadhar Joshi wrote:
I have to go far away, I hope i get support from u and u help a kid to grow up..
Just stick with your work, it's good to see someone who reads documentation and dares defend his opinion :thumbsup:
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
Aadhar Joshi wrote:
I apologies for what i did..
So do I, my apologies for the way I acted.
Aadhar Joshi wrote:
I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional..
We both acted like fools for a few minutes. There's no need to compete; programming is a huge topic, it's impossible to cover all topics.
Aadhar Joshi wrote:
I have to go far away, I hope i get support from u and u help a kid to grow up..
Just stick with your work, it's good to see someone who reads documentation and dares defend his opinion :thumbsup:
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
Thanks
-
I apologies for what i did.. I know i should listen you first but as u said i am a small kid and i need to go far away.. I can not compete with u, your knowledge, your points but as a senior u should have helping hands.. I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional.. I have to go far away, I hope i get support from u and u help a kid to grow up.. Sorry, Aadhar
Well good for you! Being able to admit you were wrong is a BIG thing and will make you a better person. Don't let one lousy experience drive you away, stick around and learn we all do that! Oh and we all screw up occasionallyso don't let it stop you from learning.
Never underestimate the power of human stupidity RAH
-
Hi, I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result. The problem is when more users are trying to generate the report, it seems that it takes more time. My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.
update table
set status = 1
where keyId = 6I don't have any locking on my select nor update. What is the best practice to resolve it? I have 100 users nationwide aside from report users, please need help. Thanks in advance Dabuskol
Dabsukol
-
Hi, I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result. The problem is when more users are trying to generate the report, it seems that it takes more time. My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.
update table
set status = 1
where keyId = 6I don't have any locking on my select nor update. What is the best practice to resolve it? I have 100 users nationwide aside from report users, please need help. Thanks in advance Dabuskol
Dabsukol
One reason would be occurance of deadlock while executing the update query. deadlock occurs at that time you are trying to do multiple operation on the same table at the same time. e.g. select and update both operations are done on same table same time then deadlock will occurs. To avoid this situation use no lock in your select query and use update lock in your update query.