query association takes very long - not logical !? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

query association takes very long - not logical !?

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-27 : 05:16:28

Hello all !

I would like to understand, why the following makes suche a huge diffrence in performance.

I have a query that runs on db where RecPlus contains 4.3 millions of records and it takes 20 seconds :

SELECT DCOCOCL FROM (SELECT DISTINCT DCOCOCL FROM RecPlus) as RecPlus0 WHERE
(
( DCOCOCL in
(SELECT DCOCOCL FROM
(SELECT DCOCOCL, SUM(RecPlus.QuantityInKg) AS SumWeight, MAX(RecPlus.QuantityInKg) AS MaxWeight, COUNT(Recplus.ID) AS RecordCount FROM
(SELECT RecPlus_1.ID, Customer_ID, DepartureDate, ElementLocalCharacterised_ID, Depot_ID, Quantity_Unit_ID, Quantity_Val, ElementWeight_Unit_ID, ElementWeight_Val, RecDataSourcePeriod_ID, Article_Element_ID, RecPlus_1.Element_ID, RecPlus_1.MetaCharacteristic_ID, Origin_ID, DCOCOCL, IsClient, QuantityInKg, CASE WHEN Quantity_Unit_ID = 4 THEN Quantity_Val ELSE 0 END AS SoldPerWeightInKg, CASE WHEN Quantity_Unit_ID = 4 THEN 0 ELSE Quantity_Val END AS SoldPerItemInP FROM RecPlus AS RecPlus_1 INNER JOIN Article_Element ON RecPlus_1.Article_Element_ID = Article_Element.Id
WHERE ( (RecPlus_1.Element_ID = 392) ) AND IsClient = 1 AND QuantityInKg > 0
) AS RecPlus RIGHT OUTER JOIN RecDataSourcePeriod ON RecPlus.RecDataSourcePeriod_ID = RecDataSourcePeriod.ID GROUP BY DCOCOCL
) as RecPlus2 WHERE MaxWeight >= 1
)
)
)
)

So the structure is :

Select * from table1 Where ID in (some complex select)

I have a second query, exactly the same, bute the "some complex select" part has some changes in a WHERE clause. And this query takes also 20 seconds.

Now if I do the following

Select * from table1 Where ID in (some complex select) OR ID in (the second complex select)

It takes minutes and minutes and minutes (in finally stopped it after 8 minutes...). What is the reason for this that associating the two doesn't take less then both independently ? Because, in my opinion, if the first (some complex select) is true for an ID, no need to check the second (the second complex select) ... See what I mean ?
Or do I have to change the query in order to make it that way ? (like AND or ANDALSO in vb.net ...)

Thanks a lot for any feedback, as this is an urgent problem ... :-(

Best regards,
Fabianus

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 05:18:57

if you can post your tables with some sample data and post requirement somebody will be able to give you a more optimised query

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-27 : 05:47:43

Hello visakh16,

thanks a lot for your proposal.
I do not want you to spend so much time on my problem !
In fact, it would be enough if you could give me a conceptual advice.

If I have a query like this :

SELECT column1 FROM table1 WHERE column1 IN (a complex select) OR column1 IN (another complex select)

What is the reason that if I do

SELECT column1 FROM table1 WHERE column1 IN (a complex select)

it takes 20 seconds.

If I do

SELECT column1 FROM table1 WHERE column1 IN (another complex select)

it takes 20 seconds.

But if I do

SELECT column1 FROM table1 WHERE column1 IN (a complex select) OR column1 IN (another complex select)

it takes 12 minutes !

In my understanding the third solution should take at maximum as long as the previous two, or even less time. But it doesn't. Obviously I have a wrong understanding of SQL. So where is my logical mistake, and how could I get things to work as I want?

Thanks a lot for any help !

Regards,
Fabianus

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:06:54

Did you have a look at execution plans for two queries? What does it suggest?

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-27 : 06:37:50

Hello visakh16 ... I do not know execution plans ? This sounds exciting! Could you give me a hint where I might find this function?

Regards,
Fabianus

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:42:34

quote:
Originally posted by fabianus76

Hello visakh16 ... I do not know execution plans ? This sounds exciting! Could you give me a hint where I might find this function?

Regards,
Fabianus

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

its not a function. just click the show execution plan icon on your query analyser top menu and run your querys seperately in two windows. Spot out any differences in query cost in both cases.

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-27 : 06:43:00

oh, just found it - let me have a look ... it's totally new for me, I am not sure to understand :-)

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

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-27 : 07:01:06

so, here is the execution plan :
http://fabianus.mynetdomain.de/temp/executionplan.pdf

In fact the real structure of the query is :

SELECT column1 FROM table1 WHERE column1 IN (complex select n° 1) OR column1 IN (complex select n° 2) OR column1 IN (complex select n° 3)
OR column1 IN (complex select n° 4)

Here is what should happen in my understanding :
First sql-server should execute once and for all complex select n° 1,2,3 and 4. Once this done, the server should check each record from table1 and si if column1 is in (complex select n° 1) - if so, it should directly pass over to the next record of table1. If (complex select n° 1) does not contain the record's value of column1, then it shoudl check (complex select n° 2), etc.

This should not take more than the sum of the execution of each (complex select) independently. Because as I said :

This takes 20 seconds :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 1)

This takes 20 seconds :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 2)

This takes 20 seconds :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 3)

This takes 20 seconds :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 4)

so why this does not take more or less 80 seconds ??? :
SELECT column1 FROM table1 WHERE column1 IN (complex select n° 1) OR column1 IN (complex select n° 2) OR column1 IN (complex select n° 3)
OR column1 IN (complex select n° 4)

Thanks for any further idea !

Regards,
Fabianus

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 07:31:10

You've table Article_Element scanned thrice. so i guess you need an index on it. look for field its scanning for and put an index. also there are couple of clustered index scans. find out fields what fields its trying to search for and see if you can put a nonclustered index on it.

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-08-27 : 08:22:53

Hello visakh16, all indexes are set !

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources