Query Rewrite

Question

Post reply

Query Rewrite

blacklabellover2003

SSC Eights!

Points: 875
More actions
September 29, 2010 at 11:23 am

#227216

Hi all,
How would I rewrite this query to yield all routings that end in '1' ?
Thanks all!
select top 100 * from dbsrvr2.queues.dbo.OUTqueue2 a (nolock) where result=206 and routing in (
select routing from defineroutings where aggregator in (
select distinct mtnets from defineservice where servicetypeid in (5,6,9,10) and mtnets in
(select aggregator from defineaggregators where country='za')
))

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

blacklabellover2003 (9/29/2010)
Hi all,
How would I rewrite this query to yield all routings that end in '1' ?

Would something like this work?

select top 100 *

from dbsrvr2.queues.dbo.OUTqueue2 a (nolock)

where result=206

AND routing like '%1'

AND routing in (select routing

from defineroutings

where aggregator in (

select distinct mtnets

from defineservice

where servicetypeid in (5,6,9,10)

and mtnets in (select aggregator

from defineaggregators where country='za')))

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

LutzM SSC Guru Points: 107049 More actions · Answer 2

It depends.

What's the data type of routing?

As a side note: is there a specific reason for that heavy usage of IN clauses instead of joins or CTEs?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

touchmeknot SSCommitted Points: 1769 More actions · Answer 3

This should be faster. Do Test if the joins are fine.

SELECT TOP 100 *

FROM dbsrvr2.queues.dbo.OUTqueue2 a (NOLOCK)

WHERE result = 206

AND a.routing like '%1'

INNER JOIN defineroutings d

ON d.routing = a.routing

INNER JOIN defineservice ds

ON ds.mtnets = d.addregator

INNER JOIN defineaggregators da

ON da.aggregator = ds.mtnets

WHERE da.country = 'za'

AND (ds.servicetypeid = 5

OR ds.servicetypeid = 6

OR ds.servicetypeid = 9

OR ds.servicetypeid = 10)

touchmeknot SSCommitted Points: 1769 More actions · Answer 4

Shawn, This might sound silly. how do you paste the code like that, as in I see a separate box there 🙂 ?

WayneS SSC Guru Points: 95460 More actions · Answer 5

touchmeknot (9/29/2010)
Shawn, This might sound silly. how do you paste the code like that, as in I see a separate box there 🙂 ?

When you're working in the editor, there is a box to the left titled "IFCode Shortcuts". You have two choices:

1. Paste your code into the window. Then select it all, and then click the one that says code="sql".The start/end tags will be added before/after your code.

2. Click the code="sql". The cursor will be between the start/end tags. Paste your code in between them.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

touchmeknot SSCommitted Points: 1769 More actions · Answer 6

touchmeknot

SSCommitted

Points: 1769

September 29, 2010 at 5:07 pm

#1229402

Thanks WayneS!

drew.allen SSC Guru Points: 76985 More actions · Answer 7

WayneS (9/29/2010)
touchmeknot (9/29/2010)
Shawn, This might sound silly. how do you paste the code like that, as in I see a separate box there 🙂 ?
When you're working in the editor, there is a box to the left titled "IFCode Shortcuts". You have two choices:
1. Paste your code into the window. Then select it all, and then click the one that says code="sql".The start/end tags will be added before/after your code.
2. Click the code="sql". The cursor will be between the start/end tags. Paste your code in between them.

There is actually a THIRD option which I sometimes use for the shorter codes: manually type it in. As a touch typist, I don't like moving my hands away from the keyboard.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA