Re: cfqueryparam, cached execution plans, and changing table structure
well one simple solution is to only do updates during a scheduled maintenance period daily. During that time you have the services bounce for CF and SQL. Its not pretty but its effective. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296815 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
from: http://msdn2.microsoft.com/en-us/library/ms181055.aspx --- Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query. The conditions that invalidate a plan include the following: Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW). Changes to any indexes used by the execution plan. Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically. Dropping an index used by the execution plan. An explicit call to sp_recompile. Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query). For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly. Executing a stored procedure using the WITH RECOMPILE option. - sql 2005 fyi. I think 2000 operates slightly different if I remember right? anyhow farther down:: - When the AUTO_UPDATE_STATISTICS database option is SET to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution - might wanna check that one if you're on 2005 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296823 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
On Friday 18 Jan 2008, Mark Kruger wrote: Not pretty...and often not possible. Consider the many many sites on shared hosts. How do they handle this situation? Assuming it happens, I guess they have a query in a non-application file they can alter and run. -- Tom Chiverton Helping to revolutionarily transform next-generation e-business on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296824 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam, cached execution plans, and changing table structure
Dana, Not pretty...and often not possible. Consider the many many sites on shared hosts. How do they handle this situation? -mark -Original Message- From: Dana Kowalski [mailto:[EMAIL PROTECTED] Sent: Friday, January 18, 2008 8:24 AM To: CF-Talk Subject: Re: cfqueryparam, cached execution plans, and changing table structure well one simple solution is to only do updates during a scheduled maintenance period daily. During that time you have the services bounce for CF and SQL. Its not pretty but its effective. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296818 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
you can use DBCC DROPCLEANBUFFERS to clean the execution buffers and DBCC FREEPROCCACHE to free the stored procedure cache. warning! it clears all the plans and may have detrimental effects on your production gear if people are actively doing stuff etc etc usual disclaimers! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296762 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
Hi Mark, In this particular case, the query used the * command. I know it's a no-no. however, when I ran my simple test to try and re-create on my laptop, I tried both with the * command and by specifying fieldnames. I couldn't recreate in either case. I'll keep at it. Ben Does your query do select * ... or select fieldname, ...? We always avoid select * ... and that avoids lots of potential issues with caching in CF or in the SQL server (and I think it makes for easier-to-maintain code, even though it is more verbose). Thanks Mark structure We hit a problem with morning relating to cfqueryparam. A query using cfqueryparam referenced a table whose structure had been changed (we added a column). It appears the execution plan had been cached, and either CF7 or SQL Server 2000 wasn't smart enough to figure out that the table had changed, and so the query bombed. I found an old post on it here: http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B 270-3056B422E2F6FCAB and it seems the only solutions are to cycle the CF service or change any query (in some minor way) to force CF to create a new execution plan. Frankly, both of those solutions blow. We're constantly updating our site, and if we have to cycle the CF service on our production servers every time we make a database change, we'll simply be forced to abandon cfqueryparam entirely. Somebody please tell me there's some other solution here. Thanks in advance, Ben ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296759 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam, cached execution plans, and changing table structure
Does your query do select * ... or select fieldname, ...? We always avoid select * ... and that avoids lots of potential issues with caching in CF or in the SQL server (and I think it makes for easier-to-maintain code, even though it is more verbose). Thanks Mark -Original Message- From: Ben Mueller [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 16, 2008 11:17 AM To: CF-Talk Subject: cfqueryparam, cached execution plans, and changing table structure We hit a problem with morning relating to cfqueryparam. A query using cfqueryparam referenced a table whose structure had been changed (we added a column). It appears the execution plan had been cached, and either CF7 or SQL Server 2000 wasn't smart enough to figure out that the table had changed, and so the query bombed. I found an old post on it here: http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B 270-3056B422E2F6FCAB .and it seems the only solutions are to cycle the CF service or change any query (in some minor way) to force CF to create a new execution plan. Frankly, both of those solutions blow. We're constantly updating our site, and if we have to cycle the CF service on our production servers every time we make a database change, we'll simply be forced to abandon cfqueryparam entirely. Somebody please tell me there's some other solution here. Thanks in advance, Ben ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296757 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam, cached execution plans, and changing table structure
I tested both DBCC DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS as well and it does not solve the cached schema problem. Here's my approach: I created a table called tmpTable with the columns username, and email address, date added and a tmp_id (int). I then ran the following query successfully. cfquery name=test datasource=test select * from tmpTable Where username = cfqueryparam cfsqltype=CF_SQL_CHAR value=bill/ /cfquery Then I added a column fname between the tmp_id and the username. I reran the query and generated the error value cannot be converted to requested type. Then I opened query analyzer and tried the 2 DBCC routines. The query continued to throw the error until I added a space or something to it. So whatever is going on JDBC is not re-requesting the table schema and MS SQL is not flagging the table as changed in a way that JDBC can recognize. This does not seem to affect the same query run in query analyzer. So... Does anyone have any input into this test or can anyone think of a better one? This is a long standing problem that I have blogged about before. The solution of altering queries is a real hack IMO. I really wish I had a better one. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296785 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
On Wednesday 16 Jan 2008, Ben Mueller wrote: and if we have to cycle the CF service on our production servers every time we make a database change, we'll simply be forced to abandon cfqueryparam entirely. If all you need to is run a query against the new table, why not just do that from a plain old CFML page ? -- Tom Chiverton Helping to autoschediastically entrench leading-edge information on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296660 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
And, in addition to what Tom is saying, I don't think you have to do this all the time. Just one time? That's what I'm gathering from all the comments on Ray's page. On Jan 16, 2008 11:30 AM, Tom Chiverton [EMAIL PROTECTED] wrote: On Wednesday 16 Jan 2008, Ben Mueller wrote: and if we have to cycle the CF service on our production servers every time we make a database change, we'll simply be forced to abandon cfqueryparam entirely. If all you need to is run a query against the new table, why not just do that from a plain old CFML page ? -- Tom Chiverton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296661 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam, cached execution plans, and changing table structure
You should be able to do it with code. Using the Admin API you can switch a flag (can't remember which one) off and on again for a given datasource. This should clear the cached plan. I had a function to do it but it's at home. If you wait about 5 hours! :OD I also didn't get around to testing it so I was never 100% sure it worked. Adrian -Original Message- From: Ben Mueller Sent: 16 January 2008 16:17 To: CF-Talk Subject: cfqueryparam, cached execution plans, and changing table structure We hit a problem with morning relating to cfqueryparam. A query using cfqueryparam referenced a table whose structure had been changed (we added a column). It appears the execution plan had been cached, and either CF7 or SQL Server 2000 wasn't smart enough to figure out that the table had changed, and so the query bombed. I found an old post on it here: http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270- 3056B422E2F6FCAB .and it seems the only solutions are to cycle the CF service or change any query (in some minor way) to force CF to create a new execution plan. Frankly, both of those solutions blow. We're constantly updating our site, and if we have to cycle the CF service on our production servers every time we make a database change, we'll simply be forced to abandon cfqueryparam entirely. Somebody please tell me there's some other solution here. Thanks in advance, Ben ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296662 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
Tom, Thanks for the reply, but I don't think that would solve the problem. The issue is that the execution plan itself is cached, and so it doesn't know about the underlying database changes. If I run a different query, it won't affect this execution plan. And even if it did, that's still not a solution I would run with. That would mean that for every database change I make, I would have to be the first one to hit the associated cached query. Our site has enough traffic that it's very likely a user would hit one of these queries before me, and would therefore see an error. Ben If all you need to is run a query against the new table, why not just do that from a plain old CFML page ? -- Tom Chiverton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296663 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
Ben Mueller wrote: We hit a problem with morning relating to cfqueryparam. A query using cfqueryparam referenced a table whose structure had been changed (we added a column). It appears the execution plan had been cached, and either CF7 or SQL Server 2000 wasn't smart enough to figure out that the table had changed, and so the query bombed. The execution plan is cached in the database so there is no way CF could possible know it is cached, let alone the cache needs to be flushed. and it seems the only solutions are to cycle the CF service or change any query (in some minor way) to force CF to create a new execution plan. What you need is to flush the cache in the database server. Look into DBCC FREEPROCCACHE and its cousins. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:29 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam, cached execution plans, and changing table structure
Have a look at this: cffunction name=clearConnectionPool cfargument name=password required=true cfargument name=datasource required=true cfargument name=host required=true cfargument name=database required=true cfset var admin = createObject(component, cfide.adminapi.administrator).login(ARGUMENTS.password) cfset var ds = CreateObject(component, cfide.adminapi.datasource) cfset ds.setMSSQL(name = ARGUMENTS.datasource, host = ARGUMENTS.host, database = ARGUMENTS.database, pooling = false) cfset ds.setMSSQL(name = ARGUMENTS.datasource, host = ARGUMENTS.host, database = ARGUMENTS.database, pooling = true) /cffunction cfset clearConnectionPool(myPassword, datasource, 127.0.0.1, database) But, I think the last I left it, it wasn't working. It was timing out if I remember rightly. Also, you might not need to pass in that much data, but instead glean it from the datasource. Just an idea. Adrian http://www.adrianlynch.co.uk/ -Original Message- From: Adrian Lynch Sent: 16 January 2008 16:33 To: CF-Talk Subject: RE: cfqueryparam, cached execution plans, and changing table structure You should be able to do it with code. Using the Admin API you can switch a flag (can't remember which one) off and on again for a given datasource. This should clear the cached plan. I had a function to do it but it's at home. If you wait about 5 hours! :OD I also didn't get around to testing it so I was never 100% sure it worked. Adrian -Original Message- From: Ben Mueller Sent: 16 January 2008 16:17 To: CF-Talk Subject: cfqueryparam, cached execution plans, and changing table structure We hit a problem with morning relating to cfqueryparam. A query using cfqueryparam referenced a table whose structure had been changed (we added a column). It appears the execution plan had been cached, and either CF7 or SQL Server 2000 wasn't smart enough to figure out that the table had changed, and so the query bombed. I found an old post on it here: http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270- 3056B422E2F6FCAB ..and it seems the only solutions are to cycle the CF service or change any query (in some minor way) to force CF to create a new execution plan. Frankly, both of those solutions blow. We're constantly updating our site, and if we have to cycle the CF service on our production servers every time we make a database change, we'll simply be forced to abandon cfqueryparam entirely. Somebody please tell me there's some other solution here. Thanks in advance, Ben ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296667 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
Hi Ben, In the ColdFusion administration there is an option for caching or keeping the connection open... I can't remember what it's called. If you disable this option and then re-run the query you can re-enable the option. I've seen other query errors caused by ColdFusion caching the data structures as well. -- Edward A Savage Jr - Sonny Senior Software Engineer Creditdiscovery, LLC ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296669 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
Thanks, Jochem, for the reply. I glanced at that call just briefly; does it clear *all* stored execution plans, or just a specified one? It seems like the only way it would be useful in this context is if it cleared them all, since we have no way of knowing which plans are cached. A larger question is this: isn't this a huge headache for everybody? We update our site a lot--sometimes several times a day--and if having cfqp around means we need to add an extra step to our process of getting stuff out to our production servers, that just seems like a big pain. Maybe I'm asking for too much here, but I suppose I would expect that if a cached execution plan bombed in SQL Server, it would automatically attempt to re-compile, under the assumption that the cached plan is out of date. Thanks again, Ben The execution plan is cached in the database so there is no way CF could possible know it is cached, let alone the cache needs to be flushed. and it seems the only solutions are to cycle the CF service or change any query (in some minor way) to force CF to create a new execution plan. What you need is to flush the cache in the database server. Look into DBCC FREEPROCCACHE and its cousins. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296672 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
Ben Mueller wrote: Thanks, Jochem, for the reply. I glanced at that call just briefly; does it clear *all* stored execution plans, or just a specified one? It seems like the only way it would be useful in this context is if it cleared them all, since we have no way of knowing which plans are cached. I don't know, you might evenneed one of the other DBCC FREE. commands. Try it :) A larger question is this: isn't this a huge headache for everybody? We update our site a lot--sometimes several times a day If by update you mean a schema change I think you are the exception rather then the rule. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296673 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
Even when we have updated a schema in the past the execution plan is trashed and a new one is prepared and we do not experience any problems. Why would a schema change multiple times in a day, there is agile and there is ridiculous that seems to be treading on ridiculous. I've never experienced an issue with local development either which I could see the schema going through rapid changes during spikes. Adam Haskell On Jan 16, 2008 1:21 PM, Ben Mueller [EMAIL PROTECTED] wrote: Thanks, Jochem, for the reply. I glanced at that call just briefly; does it clear *all* stored execution plans, or just a specified one? It seems like the only way it would be useful in this context is if it cleared them all, since we have no way of knowing which plans are cached. A larger question is this: isn't this a huge headache for everybody? We update our site a lot--sometimes several times a day--and if having cfqp around means we need to add an extra step to our process of getting stuff out to our production servers, that just seems like a big pain. Maybe I'm asking for too much here, but I suppose I would expect that if a cached execution plan bombed in SQL Server, it would automatically attempt to re-compile, under the assumption that the cached plan is out of date. Thanks again, Ben The execution plan is cached in the database so there is no way CF could possible know it is cached, let alone the cache needs to be flushed. and it seems the only solutions are to cycle the CF service or change any query (in some minor way) to force CF to create a new execution plan. What you need is to flush the cache in the database server. Look into DBCC FREEPROCCACHE and its cousins. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296674 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
Thanks, Jochem, for the reply. I glanced at that call just briefly; does it clear *all* stored execution plans, or just a specified one? It seems like the only way it would be useful in this context is if it cleared them all, since we have no way of knowing which plans are cached. I don't know, you might evenneed one of the other DBCC FREE. commands. Try it :) Yeah, I'll try it all right. Just was hoping you might know off the top of your head. A larger question is this: isn't this a huge headache for everybody? We update our site a lot--sometimes several times a day If by update you mean a schema change I think you are the exception rather then the rule. Well, it's a mix. Sometimes, we just make CF code changes, but we do make changes to our database with some regularity. It sounds like the only time this is a problem is under the following circumstance: alterations to an existing DB table where a related execution plan is stored in memory. If we create a new table, or if (by luck) no referencing execution plan isn't stored in memory, then we're okay. I'd say we make changes like that as often as a few times a week? There might be a week or so that goes by where we don't make a change like that, and then a week will come along where we make a host of such changes. Is this really the exception rather than the rule? I would think that most sites are under constant evolution, database included. I suppose I'm trying to get a sense of how the community at large deals with these issues. Do people take databases offline when making structural changes, or make a habit of cycling the SQL service (which would probably clear the cache, but I'm not sure), or do people routinely call the SQL procedure you highlighted above? I'm mostly annoyed that this doesn't just work. Maybe that's unrealistic of me, but I don't think it's unreasonable of me. Thanks again, Ben ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296675 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
Ben Mueller wrote: It sounds like the only time this is a problem is under the following circumstance: alterations to an existing DB table where a related execution plan is stored in memory. Correct. I'm mostly annoyed that this doesn't just work. Maybe that's unrealistic of me, but I don't think it's unreasonable of me. It is a MS SQL Server issue. Oracle, PostgreSQL etc. do not have this problem, they flush their cache on schema changes. So I agree that it is not unrealistic to expect that it would be handled automatically. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296677 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
God, so annoying. So, let me see if I really have this straight. Let's pretend I have a table called user that has two columns: firstname and email. I write a query like this (shortened syntax): cfquery SELECT firstname FROM user WHERE email = cfqueryparam cfsqltype=varchar value=#form.email# /cfquery MS SQL Server caches the execution plan. Then, I add a lastname column to the user table. I don't change the query at all. I re-hit the query while the execution plan is cached. It breaks? Thanks again. It sounds like the only time this is a problem is under the following circumstance: alterations to an existing DB table where a related execution plan is stored in memory. Correct. I'm mostly annoyed that this doesn't just work. Maybe that's unrealistic of me, but I don't think it's unreasonable of me. It is a MS SQL Server issue. Oracle, PostgreSQL etc. do not have this problem, they flush their cache on schema changes. So I agree that it is not unrealistic to expect that it would be handled automatically. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296679 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam, cached execution plans, and changing table structure
Jochem, So... If I ran the DBCC operation on the SQL server to flush the execution plan cache immediatley after a shema change - I would not have to fiddle with the queries or restarts or other techniques to draw it over to CF? If so - that's a great tip. -Mark -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 16, 2008 1:33 PM To: CF-Talk Subject: Re: cfqueryparam, cached execution plans, and changing table structure Ben Mueller wrote: It sounds like the only time this is a problem is under the following circumstance: alterations to an existing DB table where a related execution plan is stored in memory. Correct. I'm mostly annoyed that this doesn't just work. Maybe that's unrealistic of me, but I don't think it's unreasonable of me. It is a MS SQL Server issue. Oracle, PostgreSQL etc. do not have this problem, they flush their cache on schema changes. So I agree that it is not unrealistic to expect that it would be handled automatically. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296680 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
Ben Mueller wrote: cfquery SELECT firstname FROM user WHERE email = cfqueryparam cfsqltype=varchar value=#form.email# /cfquery MS SQL Server caches the execution plan. Then, I add a lastname column to the user table. I don't change the query at all. I re-hit the query while the execution plan is cached. It breaks? Probably not if you add a column, only if you delete one and keep referencing it: cfquery CREATE TABLE user (A, B, C) /cfquery cffunction name=test cfquery SELECT * FROM user WHERE A = cfqueryparam cfsqltype=varchar value=#form.email# /cfquery /cffunction cfset test() cfquery ALTER TABLE user DROP COLUMN C /cfquery cfset test() Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296682 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam, cached execution plans, and changing table structure
Now that I would *almost* understand and forgive. But we seem to have gotten the error when we added a column that was *not* referenced in the query. So, all we did was add a column--we didn't alter the query at all. Of course, I can't recreate on my laptop (running CF8 and SQL2005 locally), but I'll try to get it to happen again. Thanks again, Ben Ben Mueller wrote: cfquery SELECT firstname FROM user WHERE email = cfqueryparam cfsqltype=varchar value=#form. email# /cfquery MS SQL Server caches the execution plan. Then, I add a lastname column to the user table. I don't change the query at all. I re-hit the query while the execution plan is cached. It breaks? Probably not if you add a column, only if you delete one and keep referencing it: cfquery CREATE TABLE user (A, B, C) /cfquery cffunction name=test cfquery SELECT * FROM user WHERE A = cfqueryparam cfsqltype=varchar value=#form.email# /cfquery /cffunction cfset test() cfquery ALTER TABLE user DROP COLUMN C /cfquery cfset test() Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296684 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
The situations I've seen it most is when doing a SELECT * FROM table, and not getting a newly added column in the query result. I've had issues when changing the column order as well. On Jan 16, 2008 3:01 PM, Ben Mueller [EMAIL PROTECTED] wrote: Now that I would *almost* understand and forgive. But we seem to have gotten the error when we added a column that was *not* referenced in the query. So, all we did was add a column--we didn't alter the query at all. Of course, I can't recreate on my laptop (running CF8 and SQL2005 locally), but I'll try to get it to happen again. Thanks again, Ben -- Edward A Savage Jr - Sonny Senior Software Engineer Creditdiscovery, LLC ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296693 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam, cached execution plans, and changing table structure
Jochem, I tried the DBCC FREEPROCCACHE routine after making a schema change - but it does not prevent the 'invalid data type error that sometimes occurs. Any other useful routines you can think of? -mark -Original Message- From: Mark Kruger [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 16, 2008 1:49 PM To: CF-Talk Subject: RE: cfqueryparam, cached execution plans, and changing table structure Jochem, So... If I ran the DBCC operation on the SQL server to flush the execution plan cache immediatley after a shema change - I would not have to fiddle with the queries or restarts or other techniques to draw it over to CF? If so - that's a great tip. -Mark -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 16, 2008 1:33 PM To: CF-Talk Subject: Re: cfqueryparam, cached execution plans, and changing table structure Ben Mueller wrote: It sounds like the only time this is a problem is under the following circumstance: alterations to an existing DB table where a related execution plan is stored in memory. Correct. I'm mostly annoyed that this doesn't just work. Maybe that's unrealistic of me, but I don't think it's unreasonable of me. It is a MS SQL Server issue. Oracle, PostgreSQL etc. do not have this problem, they flush their cache on schema changes. So I agree that it is not unrealistic to expect that it would be handled automatically. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296699 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam, cached execution plans, and changing table structure
Mark Kruger wrote: I tried the DBCC FREEPROCCACHE routine after making a schema change - but it does not prevent the 'invalid data type error that sometimes occurs. Any other useful routines you can think of? No, but I just realized where there is an error in my logic. I was assuming that once you flush the execution plan cache it would solve the problem. But that is only true if automatic recompilation is supported. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296701 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
