CODEWITHSUNDEEP
sqlsql-servercoldfusioncoldfusion-9
CFML_Developer
CFML_Developer

Reputation: 1605

Slow results from cfstoredproc

I have a SP which run in 0.01-0.03 sec in management studio (SQl server 2008). There are many other SP too which runs in similar time frame. While the other SP also returns almost similar number of records (varying from 10 to 500 rows) they show execution time between 0-15ms under CF debugging info, but this one SP shows execution time between 500ms-5000ms. What could be wrong?

This is how SP is being called from CF:

<cfstoredproc procedure="dbo.Some_Proc" datasource="#SomeDSN#" blockfactor="100">
  <cfprocparam type="in" dbvarname="@FirstParam" value="#Variables.value1#" cfsqltye="cf_sql_numeric"> 
  <cfprocparam type="in" dbvarname="@SecondParam" value="#Variables.value2#" cfsqltye="cf_sql_numeric"> 
  <cfprocresult name="qryResult">
</cfstoredproc>

This returns about 30 columns and upto 500 rows. It used to work alright till few days ago. It still runs smooth in DB, but CF call takes more time.

Upvotes: 2

Views: 152

Answers (1)

sdurette
sdurette

Reputation: 71

There are many things that could be causing the issue. I would suggest using something such as FusionReactor to monitor the call while it is running.

Also, make sure that your database connection is set up to maintain connections, that can make a major difference.

One other thing that you might want to look at that people sometimes overlook is to make sure that when you connect to SQL Server using SSMS that you use the same login and password that ColdFusion is using. Sometimes security settings can be different and can make a difference (an admin login can get more priority than a limited login).

Adding the version of ColdFusion might be helpful at troubleshooting.

Finally, cf911.com has a lot of resources for troubleshooting and CF in general.

Upvotes: 1

Related Questions