| Author |
Topic |
|
sfortner
Yak Posting Veteran
63 Posts |
 Posted - 2007-06-12 : 11:41:42
|
| Hi all - When I use the replace function, it reorders the results of my IRTime table which contains a clustered index on it's ID field. When I don't use the replace function (2nd query), the clustered index is used and the results are ordered appropriately. This has been working, as I use the below query with the replace function to generate insert statements. After adding a new bit column to the Models table, the reordering occurs. I also recently changed the server settings to boost sql server priority under server properties, processors page in sql server management studio. I can include the complex/long DDL if you need, but here's the two queries:select t.OnTime, t.OffTime from IRTime t (nolock) join IRCommandTimes ct (nolock) on (t.ID = ct.Time)join IRCommands c (nolock) on (ct.Command = c.ID) join Models m (nolock) on (c.Model = m.ID) join Brands b (nolock) on (m.Brand = b.ID)where replace(b.name, '''', '''''') = 'elan' and replace(m.name, '''', '''''') = 'quad_processor' and replace(c.Name, '''', '''''') = 'Zoom'----------select t.OnTime, t.OffTime from IRTime t (nolock) join IRCommandTimes ct (nolock) on (t.ID = ct.Time)join IRCommands c (nolock) on (ct.Command = c.ID) join Models m (nolock) on (c.Model = m.ID) join Brands b (nolock) on (m.Brand = b.ID)where b.name = 'elan' and m.name = 'quad_processor' and c.Name = 'Zoom'and the two result sets:129 131129 390129 9199129 221522073 5192073 1037-----------2073 1037129 131129 390129 91992073 519129 22152Not sure why this is failing all of the sudden, but maybe I'm doing something stupid? TIA, SteveSteve Fortner |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 12:12:08
|
| The query you have doesn't have an order by clause so the result will just depend on how the optimiser decides to run the query which can change from execution to execution even if the query doesn't change.try an order by ontime, offtimeat the end.And why are you doing those reaplaces anyway - it won't make any difference to the result.The reason you are seeing this is probably because you have indexes on the name columns so the server uses that to get subsets of the tables which it then joins to the time table using it's clustered index to retrieve the data.With the replace it can't use the name indexes so has to table scan the tables and uses the result of that to get rows from the time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2007-06-12 : 12:40:39
|
| Adding 'order by ct.ID' does the trick. The time order is established by the CommandTimes table, so when you want the correct order the time was inserted for the given ir command, you want to force the order there. Not sure why it was working previously, I think I was getting lucky, which is never a good thing.On those replace calls, you're absolutely right. I got replace happy when I had some trouble a while back with some of our ir commands and models having single quotes. I think all of the other replace calls are in the result set, not in the where clause, which is really bad. As you can imagine, performance is awful, and I confirmed that it was doing 2 index scans and 1 clustered index scan and was 96% of the cost when compared to a query without the replaces (which only does index seeks). Should really help on time. The rest of my code is much better - I promise. :) Thanks a bunch...Steve Fortner |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-12 : 13:06:17
|
| Always worries me when I see NOLOCK hints in a query ...Kristen |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-12 : 13:19:24
|
quote:
Originally posted by Kristen
Always worries me when I see NOLOCK hints in a query ...Kristen
It worries me sometimes when I don't see it. |
|
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2007-06-12 : 13:27:45
|
| I've had trouble with the server getting locked when I don't use nolock. I've read other articles years ago about how to properly set up sql server so that nolock is not required, but not sure on that. At this point, nolock is a safe way to go, but can you elaborate?--Steve |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-12 : 15:09:12
|
quote:
Originally posted by sfortner
I've had trouble with the server getting locked when I don't use nolock. I've read other articles years ago about how to properly set up sql server so that nolock is not required, but not sure on that. At this point, nolock is a safe way to go, but can you elaborate?--Steve
My experience has been the same as yours. I have had performance come to a grinding hault due to deadlocking. NoLock(or, SET TRANSACTION ISOLATION LEVEL TO READ UNCOMMITED) has been a must for many stored procs written on the project I've been on for the past five years. The dangers of allowing dirty reads (which is what nolock allows) means that although you have avoided a lock, you may be sacrificing data integrity because you are getting to view data that me be in the process of being changed. |
|
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2007-06-12 : 15:21:50
|
| In my case, there will be an extremely low chance it will read uncommitted data as this is going on sql server 2005 express and will be used by one user only on a laptop in the field. My previous experience for a different company was that due to bad design and high number of reads, inserts, and updates per second was kind of high. I inherited that db. Nolock was our only way to keep the server from deadlocking. I read the topic at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67294 with interest, but it appears you can't say NEVER use nolock. Even the gurus recommend it on occasion, and it appears to be faster, which is a good thing when performance is key and dirty reads aren't an issue. BTW, pulling replace out of the where clause caused the performance to jump from 11 + minutes to 8 seconds. This is something that gets run once every few months, but it's good it's fixed. I knew there was something wrong!--Steve |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-12 : 16:44:16
|
| "At this point, nolock is a safe way to go, but can you elaborate?"You may be reading data which is not "current". if you rely on this data in a subsequent operation it may fail. The query itself may fail if SQL Server has had to "move" the data (e.g. it was updated and became bigger and didn't fit back in the original position) such that SQL Server reads the same record again, a second time (in its, new, location after the Update)"there will be an extremely low chance it will read uncommitted data"IMHO the worst type of scenario. A very hard to find bug, and even more difficult to reproduce when a bug is reported."will be used by one user only on a laptop in the field"So how do you get the Blocking that you are trying to avoid with NOLOCK hint then?"it appears you can't say NEVER use"I agree, but mostly I see them used like a pepper-pot. Here we allow them only after a full debate at the highest level determines that there is no other strategy that can prevent a problem. We have some pretty big database, and huge numbers of users / transactions, and we have NOLOCK on maybe 2 or 3 Sprocs, and those are only used by Admins needing to query tables for debugging purposes in real-time.If you have agonised over using NOLOCK in this scenario fine. If you use it routinely then take note of the advice that it will bite you in the bum shortly!Clearly in this case optimising your query (by consdiering the REPLACE is preventing Index Seek) has had a far greater impact than NOLOCK will ever have!Kristen |
|
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2007-06-12 : 17:01:03
|
| This is good feedback, and it does appear dangerous to use nolock all the time. I think with my previous job I was so scared NOT to use it, that I now use it all the time because of all the trouble locking queries caused. In reality, it was well known how poor the performance of the database was. A 'get' for an item in the database used a scheme to mark it as locked by the user that retrieved it and the complexity caused retrieval times in the hundreds of ms often, and much more when things deadlocked, especially as the number of items and the number of users increased. Bad design left me doing this workaround for years. I will probably be pulling it out if I can find time. Thanks for all the help.BTW Kristen, still using your split function and working well for over a year now without any problems whatsoever. Nice UDF.--Steve |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-13 : 03:19:13
|
"I will probably be pulling it out if I can find time"Well, start with avoiding it on new code where it isn't actually needed  "BTW Kristen, still using your split function and working well for over a year now without any problems whatsoever. Nice UDF."If it was genuinely a year ago then you should look at that thread again. Turns out the performance was terrible.  I have posted a revised version:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2#305424there are performance figures a bit lower down that thread. For up to 20 items or so its fine [which may be all you use it for]. For over 1,000 items the new version takes 90ms, the old version 2,000ms  Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-13 : 09:52:26
|
| I have seen nolock read data that was never anything to do with the table - I would guess that it was in the process of allocating an extent and it read whatever happened to be there. It was very rare and I haven't seen it for the last couple of years so it may not be possible now.But then I never use nolock in a production query - but always use it when I'm looking at the system to see what's happening or diagnosing problems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-13 : 09:56:57
|
| "It was very rare"To my mind this goes to the nub of the issue with NOLOCK. The chances of finding the issues in QA is slim ... and also the chances of reproducing a problem once it occurs on Production. "but always use it when I'm looking at the system to see what's happening or diagnosing problems"I like that!Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-13 : 10:10:30
|
| My fingers just naturally hitset transaction isolation level read uncommittedOdd that virtually everything else causes keyboard problems from - formselect - selcetthe - teh...but that never does.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Replace function reorders clustered index