SQL Server Performance

Why my query is Slow ???

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by waqar, May 30, 2006.

  1. waqar Member

    Hi,

    MSSQL STD 2K5.

    I have two table and i wanted to join them based on 2 keys.
    Structure is as below

    table1 (70206 records)
    fld1 varchar(20)
    fld2 varchar(20)
    fld3 varchar(50)
    fld4 varchar(100)

    ASC index on fld1
    ASC index on fld2
    (these indexes are not composite)


    tbl2 (2241561 records)
    fld1 varchar(20)
    fld2 varchar(20)
    fld3 varchar(500)

    ASC index on fld1
    ASC index on fld2
    (these indexes are not composite)


    My query

    select * from tbl1 inner join tbl2 on tbl1.fld1=tbl2.fld1 or tbl1.fld2=tbl2.fld2
    this took more than 2 minutes and still executing.

    I rewrite query as
    select * from tbl1 inner join tbl2 on tbl1.fld1=tbl2.fld1
    union all
    select * from tbl1 inner join tbl2 on tbl1.fld2=tbl2.fld2
    took 15 sec

    Any suggestion what could be the issue.

    Waqar.



    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  2. Luis Martin Moderator

    I've moved to relevant forum.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. joechang New Member

    because the optimizer cannot figure out what you are trying do with the or clause,

    frequently, a union all or union makes it much more clear to the optimizer exactly what you meant
  4. ranjitjain New Member

  5. FrankKalis Moderator

  6. waqar Member

    Hi,

    Joe, ranjit and Frank thanks for your advise. I am working on this i will update what is the outcome.
    I have another question related to my output.

    When i process query

    select tbl1.fld1,tbl1.fld2 from tbl1 inner join tbl2 on tbl1.fld1=tbl2.fld1
    i get result in 1 sec.

    when i process same query (including tbl2 records)
    select tbl1.fld1,tbl1.fld2,tbl2.fld1,tbl2.fld2 from tbl1 inner join tbl2 on tbl1.fld1=tbl2.fld1
    it took 10 sec.

    any idea?

    Waqar.

    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  7. joechang New Member

    always start by looking at the execution plan,
    then look at the output of STATISTICS IO
  8. FrankKalis Moderator

    No idea without seeing the execution plans. I would guess that because you haven't referenced a column from table2 in your first query, SQL Server recognizes this and doesn't even touch table2. In your second query SQL Server must process the JOIN as you request columns from table 2. But that's just a guess. How many rows are that queries supposed to return?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  9. dhilditch New Member

    yeah - chances are in your first query tbl1.fld1 and tbl1.fld2 form part on an index - that means that in order to return that data, sql server doesn't even need to go to the data pages. When you add in the extra columns it is then having to do a bookmark lookup (most likely) and they're not the fastest in the world.

    But then it could be loads of other stuff too - you need to look at the execution plan to find out what's different between the two queries - paste them both into query analyzer (same window) and hit CTRL-L.

    Dave

    www.skyscanner.net - flight search
  10. druer New Member

    Frank/Dave are certainly right in your second query you are asking for a field from table 2 not in the index which I'd bet is changing the execution plan to doing a lookup or a table scan.

    In your initial query consider the fact that your UNION ALL might be returning rows that are duplicats and may not be what you want. if Field1 and Field2 are both equal you would get that row just 1 time in your first query, but in the second query you could get the row for both selects, that is what UNION ALL means. If you want to ensure that you only get matching rows for either/or then you need to just use UNION. You will find that if you use UNION instead of UNION ALL that the query execution plan will change and you'll get a slower time.



    Hope it helps,
    Dalton

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.

Share This Page