CFQUERY times out when using cfqueryparams and MSSQL Server?

4
votes

I'm executing a query that returns about 16000 rows. Running the straight SQL in MS SQL Server Manager returns the records in a few seconds. Running the same SQL in cfquery returns it in about the same time. The query is composed with a few variables coming from a function arguments. Letting the cfquery evaluate the variables also returns the query fast.

Why is it when I add cfqueryparam for the variables, and there are only 6 of them, the query runs for over an hour and then times out. The cfsqltype are *cf_sql_integer* and *cf_sql_timestamp*. As soon as I remove them, it's done and working.

2
sql-servercoldfusioncfquerycfqueryparam
SQL Server does some wonky things if parameters are used, on occasion (I forget the term used for this situation but I think it has to do with improper estimates that, at least in my case, used LOOP JOINS instead of HASH/MERGE joins). The "fix" I used locally -- albeit for typed datasets in C# -- was to use OPTION (RECOMPILE) in the query, which was enough to get the query planner to create a good plan. – user166390
Dunno what's causing it, but you might want to grab some stack traces or something to see if anything obvious is happening. Also run a trace on the DB server to see if CF is even talking to the DB, and possibly determin where in the CF->JDBC->DB->JDBC->CF chain the delay is happening. What version of CF, btw? – Adam Cameron
See stackoverflow.com/questions/8415999/… (bad plan generated sometimes?) and stackoverflow.com/questions/2905440/… (bad types can cause slow performance). Also, if sprocs are used, that could be another issue with caching. – user166390
CF9 Dev License. Unfortunately, I don't have much access to the DB server to really take a look at the logs and goings on there. Makes things a little trickier. As far as Recompiling, I guess this is a CF specific question, but i was under the impression that CF would evaluate the variables, and then pass the full query with the evaluated parameters. EX WHERE name = '#myArgument#' would be just send to to SQL Server as WHERE name = 'dano' I'll try OPTION (RECOMPILE) in the morning and see how that does. – Danomite

2 Answers

2
votes

Generally cfqueryparam should do better - not worse - with cfqueryparam because it should result in better cache hits. However, if it was not hitting the cache it would create a new plan - the result would be a slightly slower query - not a timeout. So I would guess you actually are getting an execution plan from the cache and it's just not working as advertised.

As a test try changing the "TYPE" of cf_sql_timestamp to cf_sql_char - this would force implicit conversion and a different plan but keep cfqueryparam in the mix. If it works and you get a reasonable result then you need to clear your query cache (on the db server I mean) or recompile your plan (with recompile) etc.

0
votes

I think Mark is on the right track.

My guess is you are running into type conversion on the SQL Server. This can result is really bad performance, because SQL will not be able to use an index AND the conversion is performed on every single row.

The best way to confirm this is to run a trace on the SQL Server to see exactly what query is being executed. You could also check the column types and make sure they are aligned to the proper datatypes: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html