I've got some nasty sql that we use on a shoping cart to return
recommendations of products to customers. it looks at previous
purchases and sees who else bought those products and what else they
bought.
The code works but on access take 3.5 seconds to run, which is far
from acceptable. I was wondering if anyone had any bright ideas on how
to make it faster.
N.B. The 540 here represents the customer ID, which in the application
would obviously be added programatically .
Thanks,
G.
SELECT TOP 5 tblproducts.pro ductname, tblproducts.pro ductid,
count(tblorders _products.produ ctid) AS purchased
FROM tblproducts, tblorders_produ cts
WHERE tblorders_produ cts.productid = tblproducts.pro ductid
and tblorders_produ cts.orderid in (
select distinct tblorders_produ cts.orderid
from tblorders_produ cts
INNER JOIN tblorders
on tblorders.order id = tblorders_produ cts.orderid
where tblorders.custo merid in (
select tblcustomers.cu stomerid
from tblcustomers
INNER JOIN tblorders
on tblcustomers.cu stomerid = tblorders.custo merid, tblorders_produ cts
where tblorders.order id = tblorders_produ cts.orderid
and tblcustomers.cu stomerid <> 540
and tblorders_produ cts.productid in (
select tblorders_produ cts.productid
from tblorders_produ cts
INNER JOIN tblorders
on tblorders.order id = tblorders_produ cts.orderid
where tblorders.custo merid = 540)
)
) and
tblorders_produ cts.productid not in
(select tblorders_produ cts.productid
from tblorders_produ cts
INNER JOIN tblorders
on tblorders.order id = tblorders_produ cts.orderid
where tblorders.custo merid = 540)
and tblproducts.pro ductonline = true and
tblproducts.can order = true and
not (tblproducts.st ock_lownoorder = true and stock_level <=
stock_lowlevel)
GROUP BY tblproducts.pro ductname, tblproducts.pro ductid
ORDER BY count( tblorders_produ cts.productid) DESC;
recommendations of products to customers. it looks at previous
purchases and sees who else bought those products and what else they
bought.
The code works but on access take 3.5 seconds to run, which is far
from acceptable. I was wondering if anyone had any bright ideas on how
to make it faster.
N.B. The 540 here represents the customer ID, which in the application
would obviously be added programatically .
Thanks,
G.
SELECT TOP 5 tblproducts.pro ductname, tblproducts.pro ductid,
count(tblorders _products.produ ctid) AS purchased
FROM tblproducts, tblorders_produ cts
WHERE tblorders_produ cts.productid = tblproducts.pro ductid
and tblorders_produ cts.orderid in (
select distinct tblorders_produ cts.orderid
from tblorders_produ cts
INNER JOIN tblorders
on tblorders.order id = tblorders_produ cts.orderid
where tblorders.custo merid in (
select tblcustomers.cu stomerid
from tblcustomers
INNER JOIN tblorders
on tblcustomers.cu stomerid = tblorders.custo merid, tblorders_produ cts
where tblorders.order id = tblorders_produ cts.orderid
and tblcustomers.cu stomerid <> 540
and tblorders_produ cts.productid in (
select tblorders_produ cts.productid
from tblorders_produ cts
INNER JOIN tblorders
on tblorders.order id = tblorders_produ cts.orderid
where tblorders.custo merid = 540)
)
) and
tblorders_produ cts.productid not in
(select tblorders_produ cts.productid
from tblorders_produ cts
INNER JOIN tblorders
on tblorders.order id = tblorders_produ cts.orderid
where tblorders.custo merid = 540)
and tblproducts.pro ductonline = true and
tblproducts.can order = true and
not (tblproducts.st ock_lownoorder = true and stock_level <=
stock_lowlevel)
GROUP BY tblproducts.pro ductname, tblproducts.pro ductid
ORDER BY count( tblorders_produ cts.productid) DESC;
Comment