Michael Dinowitz said: > It's a Mach II app where a chunk of output data is stored in a query > that is in turn stored in a session var. The queries come from the > Mach II xml document so changing them there is out. The big problem > with your suggestion (which is the logical one) is that there is no > QueryDeleteRow() function in CF. In order to delete a row in a > query, you have to build a new query and replace the old one with > the new. If I'm going to do that, I may as well just build the new > query with the rows I want. > As for replacing the IN with multiple OR statements, I'd think that > would be a lot more inefficient based on logic but someone better > in SQL can tell me that I'm wrong.
That is 100% implementation defined: SQL is only about what the result should be, not how that result should be achieved internal to the SQL server. There are plenty of database implementations that will translate an IN to a list of OR and will hence be equally efficient in the execution. What I suspect is happening here is that every row in your cached query is compared to every element in the IN list. That means the number of comparisons add up pretty quickly. Normally you would go the usual route of finding a good execution plan and adding indexes etc. to allow the database to use that plan. But the internals of CF are not comparable to the internals of a database. There are no indexes, there are not even datatypes so you have to look at what this does at the Java level. That is not really my area of expertise, but what I suspect is happening is that first you are having a whole lot of typecasting overhead, second you are doing string comparisons. Both are well known to be relatively slow. You might be able to speed up the type comparisons by using cfqueryparam, but if you really want to be faster you have to change the mechanism to something like a hash lookup instead of repeated comparisons. So what I would try is to change your queries to structures and your IN code to StructKeyExists(). Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238629 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
