Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Unanswered: nested select performance

    Hi

    We currently use Access as our backend database for our small online shop. I wrote some SQL (below) to return other a list of recommended products for a visitor based on their previous purchase history.

    The idea that is finds all products purchases across multiple orders, then finds all other people who bought those products and then the products they bought.

    It works fine and gives the right results, however it takes forever to run. I've not tried on the production server yet, but on two development machines it took between 3-7 seconds to run! Which considering there are only 1000 orders and 700 customers, this was some what surprising.

    The annoying thing is, when I got someone to try this on their SQL Server it ran instantly, however we currently can not afford the license.

    I'm pretty convinced this could be optimises but I don't know where to start. I've done some digging on the net but withouth much luck.

    Even if anyone can give me some pointers that would be great.

    Thanks,

    G.

    SELECT TOP 5 tblproducts.productname, tblproducts.productid,
    count(tblorders_products.productid) AS purchased
    FROM tblproducts, tblorders_products
    WHERE tblorders_products.productid = tblproducts.productid
    and tblorders_products.orderid in (


    select distinct tblorders_products.orderid
    from tblorders_products
    INNER JOIN tblorders
    on tblorders.orderid = tblorders_products.orderid
    where tblorders.customerid in (

    select tblcustomers.customerid
    from tblcustomers
    INNER JOIN tblorders
    on tblcustomers.customerid = tblorders.customerid, tblorders_products where tblorders.orderid = tblorders_products.orderid and tblcustomers.customerid <> 540 and tblorders_products.productid in (

    select tblorders_products.productid
    from tblorders_products
    INNER JOIN tblorders
    on tblorders.orderid = tblorders_products.orderid
    where tblorders.customerid = 540)
    )


    ) and
    tblorders_products.productid not in

    (select tblorders_products.productid
    from tblorders_products
    INNER JOIN tblorders
    on tblorders.orderid = tblorders_products.orderid
    where tblorders.customerid = 540)

    and tblproducts.productonline = true and
    tblproducts.canorder = true and
    not (tblproducts.stock_lownoorder = true and stock_level <= stock_lowlevel) GROUP BY tblproducts.productname, tblproducts.productid ORDER BY count( tblorders_products.productid) DESC;

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    First of all - tables indexes - put indexes on the fields that are used in join statements and where conditions.

    Than try to avoud IN statement, you can rearange your query and use Join instead of IN, especially in your case there in no sence to have a subquery for IN filter, you can put this SQL in other query (querydef), and join it to a table in your query via field you're filtering (for example you have "tblorders_products.orderid in " , so join tblorders_products.orderid to that query)
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Posts
    199
    ... also, you can use subquery not in WHERE condition, but in FROM clause
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •