Post reply

Query Tune

  • February 13, 2009 at 2:43 pm

    #943945

    ALTER VIEW [dbo].[Budget]

    AS

    Select *

    from Budget_R

    union all

    select *

    from Budget_PA

    ACtually in my query vwBud and Budget are the views, you can check the view above.

  • February 13, 2009 at 3:04 pm

    #943956

    Mike Levan (2/13/2009)

    ALTER VIEW [dbo].[Budget]

    AS

    Select *

    from Budget_R

    union all

    select *

    from Budget_PA

    ACtually in my query vwBud and Budget are the views, you can check the view above.

    What happens if you change the UNION ALL to UNION?

    Also, I hope you aren't actually using select * in you view definition, because you really should explicitly indentify each column you are returning.

  • February 13, 2009 at 3:39 pm

    #943976

    I think union all is always beter than union.

    UNION ALL looks for and discards duplicate rows in the result set, whereas the UNION statement does not.

  • February 13, 2009 at 3:40 pm

    #943978

    Mike Levan (2/13/2009)

    I think union all is always beter than union.

    UNION ALL looks for and discards duplicate rows in the result set, whereas the UNION statement does not.

    Actually you have that backwards. UNION ALL keeps all duplicates, UNION does not.

  • February 13, 2009 at 3:42 pm

    #943980

    ohh yeah i agree. its my mistake.

    yeah i need to keep duplicates.

  • February 13, 2009 at 3:51 pm

    #943984

    Well, I have to bail at this point. You haven't been able to provide me with enough information to really help. No DDL for the tables and views (CREATE statements), sample data (as INSERT statements) for the tables, and no expected results based on the sample data.

    Can't really help with improving performance if we can't work with something to give you something to try.

  • February 14, 2009 at 12:24 am

    #944045

    I've got to agree with Lynn. Have you tried combining the second query with the first? There doesn't seem any reason to keep them seperate. Once they're combined, I think you can eliminate the DISTINCT clause.

    "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

  • February 14, 2009 at 9:07 am

    #944088

    i tred combining then but no use. How DISTINCT is eliminated if you are combining, i dont think so.

    also when i tried combining them does the whole result set go into temp table and will the tempdb grow bigger?

    thanks

  • February 14, 2009 at 10:06 am

    #944101

    Andrew Reilly (2/13/2009)

    But Jeff

    NOTE: This problem does not apply to SQL Server 7.0 and later.

    Heh... that's what I said... it's NOT a problem anymore.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 14, 2009 at 10:20 am

    #944108

    Guys...what do u mean by that.

    " this probloem deos not apply to sql 7 or later"

  • February 14, 2009 at 10:48 am

    #944116

    Here is the update...]

    I combined 2 sql statements as 1 query in to second and got rid of DISTINCTm then i was able get same numer of records with 30min less than before.

    select *

    into #Rev_Bud

    from Budget

    where RevID in (select a.RevID

    from vwBud as a inner join dbo.datesJi as b

    on a.RevID=b.RevID

    where (JoinDt between '12/4//2001' and '8/31/2008')

    and

    (JoinDt between effectivedate and expirationdate)

    and

    (

    (left(research,3) between '800' and '994')

    or

    (research like '9955%')

    or

    (research between '99580' and '99585')

    )

    )

    and

    (JoinDt between '1/1/2004' and '8/31/2008')

    I would like to know how IN is replcing DISTINCT, I think if i had any duplicates then my result wud have been different.

  • February 14, 2009 at 11:59 am

    #944130

    Mike Levan (2/14/2009)

    Guys...what do u mean by that.

    " this probloem deos not apply to sql 7 or later"

    It means that the myth of SELECT/INTO locking sysobjects so it can't be used during the SELECT/INTO was a problem that they fixed in 6.5 sp1 and that there is no problem with using SELECT/INTO since then.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 14, 2009 at 2:27 pm

    #944159

    Sorry Jeff I misread what you wrote 🙂

  • February 14, 2009 at 5:42 pm

    #944171

    No problem... looking back at it, I can see how someone might mistake it. Thanks for the feedback, Andrew.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 16 through 28 (of 28 total)

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