condition for creating a prepared statement using cfqueryparam?

Does cfquery becomes a prepared statement as long as there's 1 c开发者_运维百科fqueryparam? Or are there other conditions?

What happen when the ORDER BY clause or FROM clause is dynamic? Would every unique combination becomes a prepared statement?

And what happen when we're doing cfloop with INSERT, with every value cfqueryparam'ed, and invoke the cfquery with different number of iterations?

Any potential problems with too many prepared statements?

How does DB handle prepared statement? Will they be converted into something similar to store procedure?

Under what circumstances should we Not use prepared statement?

Thank you!

---

I can answer some parts of your question:

a query will become a preparedStatement as long as there is one <queryparam. I have in the past added a where 1 = <cfqueryparam value="1" to queries which didn't have any dynamic parameters, in order to get them run as preparedStatements

Most DBs handle preparedStarements similarly to Stored Procedures, just held temporarily, rather than long-term, however the details are likely to be DB-specific.

Assuming you are using the drivers supplied with ColdFusion, if you turn on the 'Log Activity' checkbox in the advanced panel of the DataSource setup, then you'll get very detailed information about how CF is interacting with he DB and when it is creating a new preparedStatement and when it is re-using them. I'd recommend trying this out for yourself, as so many factors are involved (DB setup, Driver, CF version etc). If you do use the DB logging, re-start CF before running your test code, so you can see it creating the prepared statements, otherwise you'll just see it re-using statements by ID, without seeing what those statements are.

---

In addition, if you are asking about execution plans then there is more involved than just the number PreparedStatement's generated. It is a huge topic and very database dependent. I do not have a DBA's grasp on it, but I can answer a few of the questions about MS SQL.

What happen when the ORDER BY clause or FROM clause is dynamic? Would every unique combination becomes a prepared statement?

The base sql is different. So you will end up with separate execution plans for each unique ORDER BY clause.

And what happen when we're doing cfloop with INSERT, with every value cfqueryparam'ed, and invoke the cfquery with different number of iterations?

MS SQL should reuse the same plan for all iterations because only the parameters change.

The sys.dm_exec_cached_plans view is very useful for seeing what plans are cached and how often they are reused.

SELECT  p.usecounts, p.cacheobjtype, p.objtype, t.text 
FROM    sys.dm_exec_cached_plans p
        CROSS APPLY sys.dm_exec_sql_text( p.plan_handle) t
ORDER BY p.usecounts DESC

To clear the cache first, use DBCC FLUSHPROCINDB. Obviously do not use it on a production server.

DECLARE @ID int
SET @ID = DB_ID(N'YourTestDatabaseName')
DBCC FLUSHPROCINDB( @ID )