How much data is it returning? A few rows, a few dozen, thousands, hundreds of thousands, millions. Sql2k5 just displays the results of the query. CF transforms the query into a java.util.Map which results in processing time on the CF server in addition to the processing time on the SQL server for each query. Also adding an appropriate blockfactor may speed this up as the driver may be sending one row at a time back to CF instead of as much as it can. Are you sure it is the query that is taking a long time or is it the transformation of the query into whatever view you are making out of it? Create a template with nothing in it but a cfquery call with the sql. Do not dump it or anything and see whether the code that renders the query results into something else is the culprit.
If you can rule out CF processing as the issue, post the execution plan created. Maybe you are missing indexes etc. Try running the query with out using cfqueryparam if you are and compare the timing. -----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Friday, January 05, 2007 10:17 AM To: CF-Talk Subject: RE: slow query in cf but fast in Sql2k5 management studio. > SELECT a,b,c,d,e,f > FROM tbl1 > WHERE 1=1 > > AND > ( b = txt1 > AND c = int1 ) > OR > ( d IN (txt2,txt3) > AND e = txt4) > > ORDER BY f > > the only thing specific that i can think of regarding this > query is that columns d and e return long text (type text in sql2k5). What happens if you only select the other columns? Do you see a difference in execution speed then? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265812 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
