[SQLServer / Oracle] Welke SQL constructie is sneller?

[EfBe]

Als ik de execution plans bekijk van de 2 volgende queries afgevuurd op SQLServer 2000 dan word ik er niet echt wijs uit welke nu sneller is. Heeft iemand meer info welke query sneller is (dus een linkje naar text waar uitgelegd staat waarom is genoeg) en ook of dit op oracle ook zo is? (Oracle heeft nog niet zo lang een real-time optimizer dus ik weet niet of die al up to par is)

Query werkt op northwind database
query 1:

SQL:

SELECT  DISTINCT
    Customers.*
FROM    Customers INNER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
    INNER JOIN [Order Details]
    ON Orders.OrderID = [Order Details].OrderID
    INNER JOIN Products
    ON [Order Details].ProductID = Products.ProductID
WHERE   Products.ProductName = 'Tofu'

Query 2:

SQL:

SELECT  *
FROM    Customers
WHERE   CustomerID IN
    (
        SELECT  CustomerID FROM Orders
        WHERE   OrderID IN
        (
            SELECT  OrderID FROM [Order Details]
            WHERE   ProductID IN
            (
                SELECT ProductID FROM Products
                WHERE ProductName = 'Tofu'
            )
        )
    )

De distinct in query 1 is nodig anders krijg je dubbele rijen. (is op zich een nadeel). Ik ben geneigd te zeggen dat query 1 sneller is, want de operaties zijn simpeler, echter dit kan schijn zijn. Ik ben bezig met een dynamic query builder voor mn ORM framework generator dus dat je in code via simpele statements een query kunt bouwen ala:

Query q = new Query(CustomerEntity, Relations.Customers.Orders.Order_Item.Products, ProductsEntity.ProductName, Operators.Equals, "Tofu");

en het gaat er mij nu om wat het beste is hoe ik die query genereer: (ja dat 'tofu' wordt een parameter ) middels joins of middels subqueries.

bedankt alvast

[whoami]

Mijn eerste idee - zo op het zicht - zou zijn dat JOINS sneller zijn dan subqueries.

Ik heb nog niet direct een relevant artikel gevonden, maar misschien vind je deze site wel interessant:
Sql Server Performance Tuning

offtopic:
Ik vind die execution plan die getoond wordt in Sql Server zowiezo al onduidelijk/moeilijk leesbaar.

[whoami]

Ondertussen heb ik op eerder vernoemde site dit gevonden:

If you have the choice of using a JOIN or a subquery to perform the same task, generally the JOIN (often an OUTER JOIN) is faster. But this is not always the case. For example, if the returned data is going to be small, or if the are no indexes on the joined columns, then a subquery may indeed be faster.

The only way to really know for sure is to try both methods and then take a look at their query plans. If this operation is run often, you should seriously consider writing the code both ways, and selecting the code that is most efficient. [6.5, 7.0, 2000] Updated 9-12-2001

[EfBe]

whoami schreef op 01 May 2003 @ 12:13:
Mijn eerste idee - zo op het zicht - zou zijn dat JOINS sneller zijn dan subqueries.
Ik heb nog niet direct een relevant artikel gevonden, maar misschien vind je deze site wel interessant:
Sql Server Performance Tuning

Ah bedankt, ik zal er ff gaan rondneuzen.

Ik denk nu echter dat die subqueries toch sneller zijn. De subquery variant doet een indexseek op de products table en is daar even lang mee bezig (qua i/o costs en cpu costs) als de indexseek in de join variant. Echter in de subquery variant is dat 8% van de totale tijd en in de join variant is dat 4% van de totale tijd, wat mij zoiets vertelt als dat de subquery variant dus sneller is.

[EfBe]

whoami schreef op 01 May 2003 @ 12:16:
Ondertussen heb ik op eerder vernoemde site dit gevonden:
[lalala er is geen oplossing die altijd werkt]

hmmm in dit geval ben ik dan geneigd de simpelste variant om te genereren te kiezen. Bedankt voor je tijd!

[tijn]

Als je voor de subqueryvariant gaat kun je het ook nog eens proberen met WHERE EXISTS(...) . Ik weet niet hoe SQL Server er mee omgaat, maar bij bijvoorbeeld PostgreSQL maakt dat aardig veel uit voor de performance.

[justmental]

Oracle zal in beide gevallen ongeveer hetzelfde doen.
De eerste variant is waarschijnlijk iets trager vanwege de extra sort die uitgevoerd moet worden vanwege de distinct.

[EfBe]

tijn schreef op 01 May 2003 @ 12:33:
Als je voor de subqueryvariant gaat kun je het ook nog eens proberen met WHERE EXISTS(...) . Ik weet niet hoe SQL Server er mee omgaat, maar bij bijvoorbeeld PostgreSQL maakt dat aardig veel uit voor de performance.

zoiets bedoel je?
Query 3:

SQL:

SELECT  DISTINCT 
    *
FROM    Customers
WHERE   EXISTS 
    (
        SELECT  CustomerID 
        FROM    Orders
        WHERE   EXISTS  
            (
                SELECT  OrderID 
                FROM    [Order Details]
                WHERE   EXISTS
                    (
                        SELECT  ProductID FROM Products
                        WHERE   ProductName = 'Tofu'
                            AND     
                            [Order Details].ProductID = ProductID
                    )
                    AND 
                    Orders.OrderID = [Order Details].OrderID
            )
            AND Customers.CustomerID = Orders.CustomerID
    )

Dit is volgens de query analyzer even snel als de subquery variant.

Joins hebben wel als nadeel dat ze leunen op indices en daardoor vrij traag kunnen worden, volgens die sql tweak site die whoami aangaf, maar volgens mij moet je bij subqueries ook columns af waar indices op moeten staan anders is het traag. De where exists is wel een stuk complexer te genereren...

[EfBe]

justmental schreef op 01 May 2003 @ 12:37:
Oracle zal in beide gevallen ongeveer hetzelfde doen.
De eerste variant is waarschijnlijk iets trager vanwege de extra sort die uitgevoerd moet worden vanwege de distinct.

Is op SqlServer ook zo. Het rare is alleen dat je ondanks de inner joins dubbele rijen krijgt anders. Wellicht met wat extra join clauses erbij dat het efficienter kan...

[justmental]

EfBe schreef op 01 mei 2003 @ 12:44:
Is op SqlServer ook zo. Het rare is alleen dat je ondanks de inner joins dubbele rijen krijgt anders. Wellicht met wat extra join clauses erbij dat het efficienter kan...

Bij Oracle is dat altijd zo, de 'inner join' syntax wordt ook pas sinds 9i ondersteund.
Overigens kun je in Oracle middels een explain met de cost based optimizer eenvoudig zien welk statement het 'duurst' is.

[tijn]

EfBe schreef op 01 May 2003 @ 12:43:
[...]


zoiets bedoel je?
Query 3: ...

Dit is volgens de query analyzer even snel als de subquery variant.

Idd, dit bedoelde ik. Wat maakt SQL Server er intern eigenlijk van? Gebruikt ie intern de WHERE IN (...) variant of de EXISTS (...) variant? Of kun je dat niet zien in de Query Analyzer?

De where exists is wel een stuk complexer te genereren...

Dat klopt. Wellicht is die alleen zinvol als je ook nog non-SQL Server wilt targetten. Overigens zal op Access Query 1 veruit het snelste zijn. Access ondersteunt de volledige subquery syntax in principe wel, maar de performance daarvan is om te huilen (zoals wel meer zaken in Access ).

[EfBe]

tijn schreef op 01 May 2003 @ 13:20:
Idd, dit bedoelde ik. Wat maakt SQL Server er intern eigenlijk van? Gebruikt ie intern de WHERE IN (...) variant of de EXISTS (...) variant? Of kun je dat niet zien in de Query Analyzer?

Ik had er niet zo op gelet, maar nu je het zegt, de execution plans zijn exact hetzelfde:
1) Seek: Products.ProductName = 'Tofu'
2) Seek: Products.ProductID = [Order Details].ProductID
3) Outer references 1) en 2) (nested loop)
4) Distinct sort resultaten 3) op [Order details].OrderID ASC
5) Seek: Orders.OrderID = [Order Details].OrderID
6) Outer references 4) en 5) (nested loop)
7) Distinct sort resultaten 6 op Orders.CustomerID (ASC)
8 ) Seek: Orders.CustomerID = Customers.CustomerID
9) Outer references 7) en 8 )

van binnen naar buiten. Op zich wel slim, natuurlijk

[...]
Dat klopt. Wellicht is die alleen zinvol als je ook nog non-SQL Server wilt targetten. Overigens zal op Access Query 1 veruit het snelste zijn. Access ondersteunt de volledige subquery syntax in principe wel, maar de performance daarvan is om te huilen (zoals wel meer zaken in Access ).

Access ga ik niet supporten, ik vind access een tool die gebruikt moet worden met de omgeving die er in zit, niet als losse database.