a conceptual question on "OR" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

a conceptual question on "OR"

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-29 : 15:20:48

Hello all,

I have lunched a discussion here : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109568

And from this I would like to start a new thread with a more simple question :

I have a query that takes 20 seconds :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 1)

Here the "complex select" is slithly diffrrent, but still it takes 20 seconds :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 2)

Now I do this :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 1) OR column1 IN (complex select n° 2)

But instead of taking more or less 40 seconds, it takes 8 minutes ! How could this be ?

I would be very thankfull if someone could give me a conceptual explenation, and - if possible - a correction of my presented tsql (should I use a diffrent operator than OR for example?).

Thanks a lot for any feedback !

Regards,
Fabianus
Thanks for any further idea !

my favorit hoster is ASPnix : www.aspnix.com !

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-29 : 17:52:08

Can you please post sample data and table structures. Your query can be written in a much better way.
Post everything nicely and someone I am sure will help you out.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-29 : 17:53:28

Ah, you just need an answer as to why its taking long..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:11:40

have you tried using LEFT JOINs instead of OR conditions in second query?

SELECT column1 FROM table1 t1
LEFT JOIN (complex select n° 1) q1
ON q1.column1=t1.column1
LEFT JOIN (complex select n° 2) q2
ON q2.column1=t1.column1
WHERE q1.column1 IS NOT NULL
OR q2.column1 IS NOT NULL

Also consider putting indexes on column1

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-30 : 04:08:09

wwwwooooooowwwww !
Man, this is absolutly terrific. I spent hours on this problem ...

You know what, now it doesn't take
(nb. of complex select queries) x 20 seconds
as I hoped it to be ... but it takes only 20 seconds, no matter how many ORs I test !

Thanks so many for your help. I never would have figured that out alone.

It would be great to understand the diffrence in performance of these two approaches. Perhaps you could drop some words ?

Thank you very very much,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources