By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

sql optimisation

P: n/a
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.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;
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a


Glenn Smith wrote:
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,


"Where field In (Select....)" is gather round the water cooler while
waiting code.

Nov 12 '05 #2

P: n/a
Hi

Thanks for the reply. I was hoping for something slightly more
constructive though. Any assistance or golden rules on how to make this
SQL better?

Thanks,

G.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
Glenn Smith wrote:
Hi

Thanks for the reply. I was hoping for something slightly more
constructive though. Any assistance or golden rules on how to make this
SQL better?

Thanks,


I didn't bother going any further than viewing all the "Field In
(Select...)"

My best advice on improving your SQL is to get rid of them.

You may want to make the subqueries actual queries and then set your
relatonships with outer, inner, left, right joins.

Your query is complex enough that many people won't be spending time to
study it, let alone rewrite it for you.



Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.