Dave Carabetta wrote: > > Gladly. Note that I have marked with a double-dash (--) where the query is > choking and where it ends. Again, if I remove the cfqueryparam, even on just > one of the two conditions (doesn't matter which), the query works fine. It's > when both are cfqueryparam'ed that it takes forever: > > WHERE p.msa = <cfqueryparam value="#client.msa#" cfsqltype="CF_SQL_CHAR" > maxlength="2"> > AND p.sector = <cfqueryparam value="#client.sector#" cfsqltype="CF_SQL_CHAR" > maxlength="3"> > AND p.id = pd.id > -- The next two conditions are where CF chokes when using cfqueryparam > AND p.x_long >= <cfqueryparam value="#Subj.Long#" > cfsqltype="CF_SQL_NUMERIC"> > AND p.y_lat >= <cfqueryparam value="#Subj.Lat#" cfsqltype="CF_SQL_NUMERIC"> > -- End Choke > AND ((p.x_long != <cfqueryparam value="#Subj.Long#" > cfsqltype="CF_SQL_NUMERIC">) OR (p.y_lat != <cfqueryparam value="#Subj.Lat#" > cfsqltype="CF_SQL_NUMERIC">)) > ORDER BY dist
That might explain a lot :) Inside a database server a query undergoes 3 stages (some say 2, but for the purpose of this explanation I'm not going to bother about that). First stage is a rewrite. Views used in the query are merged with rules/instead of triggers, case is corrected etc. to determine what the user actually wants. This stage also checks for permissions. So performing the query SELECT * FROM view1 WHERE ID > 16 on the view view1 is not what really happens. view 1 is actually: CREATE VIEW view1 AS SELECT * FROM table1 WHERE somefield = TRUE So the result is rewritten to become SELECT * FROM table1 WHERE somefield = TRUE and ID > 16 Second stage is determining the query plan. The query plan is what is actually going to happen in the database. In this case, the optimizer looks at the query, decides that although the ID field is indexed it shouldn't use the index because only 15 of the 1000 rows will not be returned. The database decides it is best to do a sequential scan of the entire table1. Third stage is performing the actual query. This can be longest part of a query execution if the query is complicated, many rows, needs to be sorted. But if it is just one lookup it can be extremely fast as well. What happens is that at the end of stage 2 the database caches the resulting query plan together with the result of stage 1. Next query comes and at the end of stage 1 the database look in the cache to see if the execution plan is already there. Needless to say that is not often the case (or you would have cached the queryu already in CF) because ID is always different. Here come the bind variables. By using bind variables the database knows on beforehand the datatype of the variable. So it can choose to cache the execution plan based on the datatype instead of the value. If at that point the only thing that is not a database identifier (column, table etc.) of some kind is a bind variable, the query will use the stored version of the query. Quite smart, because at every single time you are only getting a single record that should be found pretty fast using an index. But once you start using < and > the equation changes. Imagine 1000 equally distributed records with ID's running from 1 to 1000 and an index on the ID column and the folowing queries: SELECT * FROM table1 WHERE ID > 990 SELECT * FROM table1 WHERE ID > 10 Difference between both is the threshold value for ID. So it would be an ideal candidate for bind variables from that angle. But if you look at the actual query plan you want, it is not. In the first case you want an index scan and then return the 1% accurate records, in the second case you want a sequential scan from which you drop the inaccurate records. This tension between inequality operators and bind variables is probably causing your problem. I just invented this reasoning here since I am a little creative on the alcohol here :) But it sure sounds good. And it will probably spark enough debate to get to the answer ;) Jochem ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
