Another thing to try: put the query in a sproc and call it with cfstoredproc. Theoretically, modern SQL Server has made it so that cfquery (ad-hoc) is as fast as cfstoredproc...but...maybe...
Let us know... On Mon, Jan 19, 2009 at 7:17 AM, Al Musella, DPM <muse...@virtualtrials.com>wrote: > Try running this query by itself on a cf page, to > see if maybe one of the other querries on the page is somehow blocking it > > > > > At 06:03 AM 1/19/2009, you wrote: > >Morning Adrian, thanks for getting back to me. > > > >This is definitely not caused by the output/dump > >of the queries, even when all those are removed > >it takes the same amount of time to return the > >page. This is not a large query to return, > >simply a single row containing 2 columns. The query looks as follows: > > > >Select Sum(Case When d.LogClassName = > >'org.thinkblue.TransferComplete' Then 1 Else 0 End) As SuccessSentCount, > > Count(Distinct d.Device_ID) As UniqueDevicesCount, > > Sum(Case When d.MinDate Is NULL > > Then 0 Else 1 End) As FirstAppearanceCount > >From ( > > Select MessageLog.Device_ID, > > FirstAppearance.MinDate, > > LogClass.Name As LogClassName > > From MessageLog > > Inner Join LogClass > > On LogClass.LogClass_ID = MessageLog.LogClass_ID > > Left Outer Join ( > > Select Device_ID, > > > >Min(LogDateTime) As MinDate > > From MessageLog > > Inner Join LogClass > > On > > LogClass.LogClass_ID = MessageLog.LogClass_ID > > Where LogClass.Name In ( > > > >'org.thinkblue.TransferComplete', > > > >'org.openobex.Error.ConnectionRefused', > > > >'org.openobex.Error.Forbidden', > > > >'org.openobex.Error.NotAuthorized', > > > >'org.openobex.Error.ConnectionTimeout' > > > >) > > And > > MessageLog.ThinkTank_ID = <cfqueryparam > > value="#ARGUMENTS.ThinkTank_ID#" cfsqltype="cf_sql_integer" /> > > Group By Device_ID > > ) As FirstAppearance > > On MessageLog.Device_ID = FirstAppearance.Device_ID > > And MessageLog.LogDateTime = FirstAppearance.MinDate > > Where LogClass.Name In ( > > > >'org.thinkblue.TransferComplete', > > > >'org.openobex.Error.ConnectionRefused', > > > >'org.openobex.Error.Forbidden', > > > >'org.openobex.Error.NotAuthorized', > > > >'org.openobex.Error.ConnectionTimeout' > > ) > > And > > MessageLog.LogDateTime >= <cfqueryparam > > value="#ARGUMENTS.StartDate#" cfsqltype="cf_sql_date" /> > > And > > MessageLog.LogDateTime < <cfqueryparam > > value="#ARGUMENTS.EndDate#" cfsqltype="cf_sql_date" /> > > And ThinkTank_ID = > > <cfqueryparam value="#ARGUMENTS.ThinkTank_ID#" cfsqltype="cf_sql_integer" > /> > > ) As d > > > >Like I say, the query actually runs fine from > >SSMS but just not when run from CF. > > > >Cheers, > > > >Rob > > > > >To make sure it's not the display/debugging causing the issue, remove it > > >all. A dump of a large query in Firefox with Firebug will take a while > (I'm > > >not saying 8 minutes, but a while!). > > > > > >Post the query too. > > > > > >Adrian > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318158 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
