Re: Coldfusion killed my query?
On Mon, Jan 19, 2009 at 5:37 PM, Adrian Lynch wrote: > Is it true that the SQL will perform better if you cfqp any static values > too? So: > > WHERE > > over: > > WHERE id = 1 I'm sure you can design scenario's where that will be faster (mainly where you are approaching the maximum number of prepared statements cached), but in general it will be slower for two reasons: 1. More work at runtime. Prepared statements reduce the amount of work in the time-critical query execution path by caching the query execution plan. When executed the database only has to fill out the parameters and execute the statement and doesn't have to parse and optimize the query. Filling out fewer variables is faster, both on the database side and on the CF side. (The CF side might actually be the dominant factor here.) 2. Less optimized execution plans. If you provide the value at compile time the optimizer has more information on how to optimize the query. This is especially relevant for queries where the optimal execution plan is dependent on the values of the variables. Examples of such queries are queries with a very skewed value distribution in a join / filter column or queries with inequality operators. For instance, if you have the predicate WHERE column < for some values you will be selecting almost every row and want a heap scan, while for other values you will only select very few rows and want an index scan. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:318231 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Coldfusion killed my query?
> Is it true that the SQL will perform better if you cfqp any static values > too? So: > > WHERE > > over: > > WHERE id = 1 I don't think so, generally. But query performance can be all over the map - there are a lot of contributing factors. So I wouldn't be entirely surprised if it did perform better in some cases. 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! ~| 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:318179 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Coldfusion killed my query?
Is it true that the SQL will perform better if you cfqp any static values too? So: WHERE over: WHERE id = 1 Adrian > -Original Message- > From: Dave Watts [mailto:dwa...@figleaf.com] > Sent: 19 January 2009 16:31 > To: cf-talk > Subject: Re: Coldfusion killed my query? > > > Thank you all for your ideas. After speaking with [Justice] over on > the IRC channel I > > removed the and found it ran smooth as silk. Seems > that there was > > some form of datatype mismatch going on, after changing the > cf_sql_type on the > > queryparam we've got it running at proper speed again!! > > While you're doing that, you might want to make your lists with > CFQUERYPARAM too. > >Where LogClass.Name In ( > > 'org.thinkblue.TransferComplete', > > 'org.openobex.Error.ConnectionRefused', > > 'org.openobex.Error.Forbidden', > > 'org.openobex.Error.NotAuthorized', > > 'org.openobex.Error.ConnectionTimeout' >) > > would be > > Where LogClass.Name In ( > > value="org.thinkblue.TransferComplete,org.openobex.Error.ConnectionRefu > sed,..."> >) > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ ~| 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:318173 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Coldfusion killed my query?
> Thank you all for your ideas. After speaking with [Justice] over on the IRC > channel I > removed the and found it ran smooth as silk. Seems that > there was > some form of datatype mismatch going on, after changing the cf_sql_type on the > queryparam we've got it running at proper speed again!! While you're doing that, you might want to make your lists with CFQUERYPARAM too. Where LogClass.Name In ( 'org.thinkblue.TransferComplete', 'org.openobex.Error.ConnectionRefused', 'org.openobex.Error.Forbidden', 'org.openobex.Error.NotAuthorized', 'org.openobex.Error.ConnectionTimeout' ) would be Where LogClass.Name In ( ) 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! ~| 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:318171 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Coldfusion killed my query?
What the heck?! Never would have guessed that. You changed cf_sql_type on which queryparam from what to what? On Mon, Jan 19, 2009 at 7:55 AM, Robert Rawlins < robert.rawl...@thinkbluemedia.co.uk> wrote: > Hello Guys, > > Thank you all for your ideas. After speaking with [Justice] over on the IRC > channel I removed the and found it ran smooth as silk. > Seems that there was some form of datatype mismatch going on, after changing > the cf_sql_type on the queryparam we've got it running at proper speed > again!! > > Thanks for your suggestions. > > Rob > > >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 > >wrote: > > > >> > > ~| 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:318161 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Coldfusion killed my query?
Hello Guys, Thank you all for your ideas. After speaking with [Justice] over on the IRC channel I removed the and found it ran smooth as silk. Seems that there was some form of datatype mismatch going on, after changing the cf_sql_type on the queryparam we've got it running at proper speed again!! Thanks for your suggestions. Rob >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 >wrote: > >> ~| 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:318159 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Coldfusion killed my query?
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 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 > > FromMessageLog > > Inner Join LogClass > > On LogClass.LogClass_ID = MessageLog.LogClass_ID > > Left Outer Join ( > > Select Device_ID, > > > >Min(LogDateTime) As MinDate > > FromMessageLog > > 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 = > value="#ARGUMENTS.ThinkTank_ID#" cfsqltype="cf_sql_integer" /> > > Group ByDevice_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 >= > value="#ARGUMENTS.StartDate#" cfsqltype="cf_sql_date" /> > > And > > MessageLog.LogDateTime < > value="#ARGUMENTS.EndDate#" cfsqltype="cf_sql_date" /> > > And ThinkTank_ID = > > /> > > ) 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
Re: Coldfusion killed my query?
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 > FromMessageLog > Inner Join LogClass > On LogClass.LogClass_ID = MessageLog.LogClass_ID > Left Outer Join ( > Select Device_ID, > >Min(LogDateTime) As MinDate > FromMessageLog > 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 = value="#ARGUMENTS.ThinkTank_ID#" cfsqltype="cf_sql_integer" /> > Group ByDevice_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 >= value="#ARGUMENTS.StartDate#" cfsqltype="cf_sql_date" /> > And > MessageLog.LogDateTime < value="#ARGUMENTS.EndDate#" cfsqltype="cf_sql_date" /> > And ThinkTank_ID = > > ) 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:318157 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Coldfusion killed my query?
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 FromMessageLog Inner Join LogClass On LogClass.LogClass_ID = MessageLog.LogClass_ID Left Outer Join ( Select Device_ID, Min(LogDateTime) As MinDate FromMessageLog 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' ) AndMessageLog.ThinkTank_ID = Group ByDevice_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 >= And MessageLog.LogDateTime < And ThinkTank_ID = ) 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:318154 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Coldfusion killed my query?
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 > -Original Message- > From: Robert Rawlins [mailto:robert.rawl...@thinkbluemedia.co.uk] > Sent: 19 January 2009 10:23 > To: cf-talk > Subject: Coldfusion killed my query? > > Morning Guys and Girls, > > I'm running ColdFusion 8 Standard and SQL Server 2005 Workgroup > edition. I'm using The standard SQL Server driver that comes with CF8. > > I'm having a strange performance issue with a particular query. When > running the query from SSMS it returns the dataset in less than a > second, which I'm more than happy with and kind of confirms that the > query itself, the database and the table indexes are all correct. > However, when I run the same query from a block it takes 8 > minutes to return!?!!!?! > > I also ran some tests where I would start the ColfFusion page request > and then jump into SSMS and run the query from there at the same time, > the query continued to return in less than a second in SSMS but still > sat for upto 8 minutes before returning to CF. > > There are a bunch of other queries on the page, all of which run > exactly as I would expect them too, just this one single query which is > massively under performing. I can confirm that this performance > decrease is not caused by slow rendering or anything as the 8 minute > time is taken from the 'execution time' of the query displayed in its > . > > Any suggestions as to what might be causing this beef? > > Cheers all, > > Rob ~| 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:318152 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Coldfusion killed my query?
Morning Guys and Girls, I'm running ColdFusion 8 Standard and SQL Server 2005 Workgroup edition. I'm using The standard SQL Server driver that comes with CF8. I'm having a strange performance issue with a particular query. When running the query from SSMS it returns the dataset in less than a second, which I'm more than happy with and kind of confirms that the query itself, the database and the table indexes are all correct. However, when I run the same query from a block it takes 8 minutes to return!?!!!?! I also ran some tests where I would start the ColfFusion page request and then jump into SSMS and run the query from there at the same time, the query continued to return in less than a second in SSMS but still sat for upto 8 minutes before returning to CF. There are a bunch of other queries on the page, all of which run exactly as I would expect them too, just this one single query which is massively under performing. I can confirm that this performance decrease is not caused by slow rendering or anything as the 8 minute time is taken from the 'execution time' of the query displayed in its . Any suggestions as to what might be causing this beef? Cheers all, Rob ~| 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:318150 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
