SQL Server Performance Forum – Threads Archive
Why my query is Slow ???
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. *~
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.
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
check this article:
http://www.sql-server-performance.com/pk_or_clause.asp it tells about how using OR can cause performance bottlenecks
To add to Joe: Check the execution plans. You should see a much more straight plan for alternative 2 —
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)
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. *~
always start by looking at the execution plan,
then look at the output of STATISTICS IO
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)
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
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.
]]>