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 |
|
Jim Beam
Posting Yak Master
137 Posts |
 Posted - 2010-09-22 : 05:59:38
|
| Hi all,I need to rewrite these to joins, and then take a look at the execution plans to see which is faster...Cheers.--Checking for Services records for all services under each MTNetselect * from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (20))select * from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (472))-- Checking for phonelist records for all services on original shortcodeselect * from phonelist where userid in (select userid from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (20)))-- Updating defineservice to new MTNetsupdate defineservice set mtnets=472 where mtnets=20--Updating Phonelist records to new Aggregator update phonelist set aggregator=472 aggregator=20 and userid in (select userid from services (nolock) where service in (select service from defineservice (nolock) where mtnets in (20))) |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-22 : 06:12:41
|
| [code]select * from services a (nolock) inner join defineservice b (nolock) on a.service = b.servicewhere b.mtnets = 20[/code]- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-22 : 06:18:07
|
You need to wean off NOLOCK. Very dangerous for anything used by users IMHO.Consider using READ_COMMITTED_SNAPSHOT insteadUsing JOIN instead of IN won't necessarily produce the same results. The IN list may contain duplicates, but the main SELECT will only select a single record (well, it will selects as many match from the FROM and JOINed tables), so beware of that. If you JOIN another table that returns multiple rows then that will correspondingly increase the number of rows returned by the main query.IN lists which are long (hundreds? thousands? I'm not sure) tend to have poor performance. Consider using EXISTS instead (that can "stop" as soon as it finds one matching row, and only needs to concern itself with the rows in the main query, not every single matching row in the table in the IN clause)select * from services AS S (nolock) where EXISTS (SELECT * from defineservice AS DS (nolock) where DS.mtnets in (20) AND DS.service = S.service)If the IN list can currently only contain a single value then a JOIN is an alternative.Replace SELECT * with a list of required columns. No sense shipping data in columns that are not used by the application (and even if the App currently uses all columns it most probably won't, without modification, use columns that are added in the future - which might be Text columns containing massive amounts of data. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-22 : 06:56:29
|
quote:
Originally posted by Kristen
You need to wean off NOLOCK. Very dangerous for anything used by users IMHO.Consider using READ_COMMITTED_SNAPSHOT instead....
100% agree but more strongly. code with the nolock hint it almost always a good indication that the tables involved probably don't have useful index / aren't clustered / other horrors.nolock is dangerous -- you can read the same data twice. You can miss out data that you should have read (if you are in the middle of reading a page and it gets reallocated). You can't depend on data retrieved with nolock.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-22 : 07:38:50
|
quote:
Originally posted by Kristen
IN lists which are long (hundreds? thousands? I'm not sure) tend to have poor performance. Consider using EXISTS instead (that can "stop" as soon as it finds one matching row, and only needs to concern itself with the rows in the main query, not every single matching row in the table in the IN clause)
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
|
|
Rewriting subqueries to joins