April 27, 2010 at 11:21 am
Sometimes I'm using SQL Analyzer to review the database, SELECT statements, and only need read only. Was wondering if I need to be concerned about SQL Server performance - if there's any overhead with locks. Today I started out reviewing and ended up cleaning up some data. Today is a good example of my question:
Assuming there is an overhead cost when issuing SELECT statements that could be averted by indicating the execution only needs Read Only...
How can I execute SELECTS with Read Only, then be able to execute an update statement.
Example:
SELECT country FROM ShipToAddresses WHERE country IS NOT NULL ORDER BY country
... Review the values for the 'country' field, let's assume someone entered 'canada' in one or more records.
BEGIN TRANSACTION
UPDATE ShipToAddresses SET country = 'Canada' WHERE country = 'canada'
COMMIT or ROLLBACK (after SELECTING ... WHERE country = 'canada' to verify the data's OK)
SELECT country FROM ShipToAddresses WHERE country IS NOT NULL AND country > 'canada' ORDER BY country
... Review ... oops, someone misspelled Mexico.
BEGIN TRANSACTION
UPDATE ShipToAddresses SET country = 'Mexico' WHERE country = 'Mexxico'
COMMIT or ROLLBACK (after SELECTING ... WHERE country > 'Mex' to verify the data's OK)
SELECT country FROM ShipToAddresses WHERE country IS NOT NULL AND country > 'Mexico' ORDER BY country
... Review the values, and so on
I'm conscious of performance to other users and my activity usually has virtually no impact. But, if I'm in the habit of executing for optimal performance, it will make a difference, sometime, somewhere to somebody.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
April 27, 2010 at 11:31 am
Not sure if this is what you mean, but if you use the nolock hint (look it up in BOL), it will prevent locking from happening
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 27, 2010 at 1:15 pm
NOLOCK will allow the SELECT statements to process regardless of locks help by other processes (a dirty read) but would have no affect on the UPDATE statements.
Actually, I'm wondering why you aren't automating these data checks and updates. That is where I'd put my effort.
April 27, 2010 at 2:30 pm
Sounds like it... WITH (NOLOCK)
Thanks - and thanks for guiding me to documentation rather than just dumping the information.
Fair question - "Why don't I automate ..."
I'm usually working on another project when I notice 'something about the database that could be cleaned up'. So, while I'm there I clean it up. Doesn't take long, today's was about 35 minutes. Probably won't be for a couple years that I may look at these addresses. In the meantime, user documentation /instructions are revised to include reminders/standards for what I discover. So, other greater priorities are getting the bulk of my time.
Today's case was one where I was selecting and then updating.
Other times I'm simply selecting to get information for someone's ad hoc request or searching for test data for the (Access) reports and utilities I develop - lots of selects to find those odd ball data cases no one thinks about until you find something and show them ... "Oh, yeah. I forgot about that. Yes, that happens once in a while."
If you meant to automate this in the app - third party ERP app. It would be nice for them to create a drop down list for the field. I haven't developed the skills to use triggers and sp's ... yet. Again, it gets back to the priorities.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
April 28, 2010 at 6:16 am
Yes, but...
NOLOCK hints can cause you to get erroneous data. Not only will you see columns that have been changed but not yet committed within a transaction, but you can see duplicate or missing rows of data. Be very judicious about the use of NOLOCK hints in your queries. They are not magic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2010 at 6:57 am
Grant Fritchey (4/28/2010)
Yes, but...NOLOCK hints can cause you to get erroneous data. Not only will you see columns that have been changed but not yet committed within a transaction, but you can see duplicate or missing rows of data. Be very judicious about the use of NOLOCK hints in your queries. They are not magic.
Agreed, nolock is only useful on *very* large tables where the integrity of the results has to be considered along with the volume of data to be parsed and the amount of locks that will be applied.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 28, 2010 at 11:09 am
but you can see duplicate or missing rows of data
Well that's enough to avoid using it.
All I'm trying to do is retrieve the current (and complete) data - a snapshot. Nothing like seeing, and reporting, 4 rows when there should be 5 - 20% error.
Thanks for pointing that out, I appreciate it.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply