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 cfqp value=1 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 cfqueryparam 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
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 cfquery 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 cfdump. 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
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 cfquery 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 cfdump. 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
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 = cfqueryparam 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 = 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: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?
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 = cfqueryparam 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 = 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: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?
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.comwrote: 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 = cfqueryparam 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 = 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
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 cfqueryparam / 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 muse...@virtualtrials.comwrote: ~| 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?
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 cfqueryparam / 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 muse...@virtualtrials.comwrote: ~| 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?
Thank you all for your ideas. After speaking with [Justice] over on the IRC channel I removed the cfqueryparam / 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 ( cfqueryparam ... list=yes value=org.thinkblue.TransferComplete,org.openobex.Error.ConnectionRefused,... ) 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?
Is it true that the SQL will perform better if you cfqp any static values too? So: WHERE cfqp value=1 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 cfqueryparam / 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 ( cfqueryparam ... list=yes 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?
Is it true that the SQL will perform better if you cfqp any static values too? So: WHERE cfqp value=1 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
