Stuart:
Whenever you start using 'IN' in your queries they become increasingly
slower as the list that you are checking IN gets bigger. Plus it is doing
this check for every record of your main query. I would try this instead:
SQL =
select vhs.*, p.price, s.studio, s.ID as sID, s.studioID
from vhs,pricing p,studios s
where p.priceID = vhs.price
and s.ID = vhs.studio
and (select count(videoID) from performersRel where videoID=vhs.ID and
performerID = 1627) > 0
Andrew Hewitt
Web Application Developer
webworld studios, inc.
www.wwstudios.com
-----Original Message-----
From: Stuart Duncan [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 17, 2000 4:17 PM
To: CF-Talk
Subject: Speeding up a multi table query
I was wondering if anyone knows of a way to speed up this query? If "Joins"
would be better, and how? That sorta thing.
The actors, studios and prices are all in separate tables. "vhs" "pricing"
"studios" and "performersRel"
There has to be a faster way to get 22 records back.
resultsbyperformer (Records=22, Time=328ms)
SQL =
select vhs.*, p.price, s.studio, s.ID as sID, s.studioID
from vhs,pricing p,studios s
where p.priceID = vhs.price
and s.ID = vhs.studio
and vhs.ID in (select videoID from performersRel where performerID = 1627)
Any help would be greatly appreciated.
Stuart Duncan
MaracasMedia Inc.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
