ColdFusion executing unwanted SQL statements

Question

I have an application that loads a vast amount of data from twitter. We have began to notice some performance issues and so I setup SQL Profiler for the applications database. I have noticed the following SQL statement are being executed but do not appear anywhere in my code. Therefore, I am assuming that either SQL Server 2005 or ColdFusion 8 are adding these statements in for some reason.

1. SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2. SET FMTONLY ON select Title from Links where 1=2 SET FMTONLY OFF
3. exec [sys].sp_datatype_info_90 -9,@ODBCVer=4
4. EXEC sp_executesql N'set implicit_transactions off select USER_NAME() select usertype,type,name from systypes where usertype>=257'
5. EXEC sp_execute 16,'iTunes Store'
6. exec sp_unprepare 28

• I do not have any cftransaction's in my code
• All my queries are being ran from cfqueries. (no stored procedures)
• I am never running an execute or exec statement anywhere in my code
• All these statements are being ran from my database user account specifically set up for my web application
• The statements in #5 and #6 are replicated with many different numbers
• The statement in #5 is replicated with many different strings

Why are these statements being added, are they needed, and if not how can I stop them?

Answer 1

It's by no means abnormal to see statements appear in profiler that you don't actually explicitly send yourself from the code.

But that's the key point - you don't explicitly send them, but they are done implicitly as a result of the actions you are performing/how you're doing them.

e.g. if you opened a recordset in VB6 then iterate round it, you'd see (e.g.) sp_cursor calls going through profiler - now these aren't being explicitly called by the code, but is as a result of ADO advancing through the connected recordset.

I hope I've made some sort of sense

Answer 2

1,2,3,4 can be accounted for in "normal" ColdFusion code - assuming you are on version 8 or 9. It does all sorts of mucking around in the backend to ensure the database server is capable and ready to return information automatically that ColdFusion requests as part of CFQUERY, even though it may not be explicitly called for in your actual query.

http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values

I have no idea what 5 and 6 are doing.

Also, are you using the Adobe provide MS SQL Driver or are you using the MS JDBC SQL Driver? There are some differences between using the two drivers:

http://cfsearching.blogspot.com/2008/03/cf8-ms-jdbc-12-driver-and-for-my-next.html