Post reply

Query Rewrite

  • 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')

    ))

  • September 29, 2010 at 12:25 pm

    #1229265

    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

  • September 29, 2010 at 12:32 pm

    #1229271

    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]

  • September 29, 2010 at 4:31 pm

    #1229392

    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)

  • September 29, 2010 at 4:41 pm

    #1229394

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

  • September 29, 2010 at 4:55 pm

    #1229398

    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

  • September 29, 2010 at 5:07 pm

    #1229402

    Thanks WayneS!

  • September 30, 2010 at 7:14 am

    #1229594

    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

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

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