Strange Behavior Between ColdFusion & Database

[Asaf Peleg]

Hi Everyone,

I am running ColdFusion 8 with SQL Server 2005.  I recently decided to take a direct query against the database and simply move it into a stored procedure instead.  So before I was doing a <cfquery> I am now doing a <cfstoredproc> instead.  After I made the change, I logged onto my website and requested the data from the interface and it took a long time for database to retrieve the result.  I began digging into this by looking at the performance of my database server while the request was running.  I discovered that it was reading from the disk for the entire time.  I know your first reaction will be to tell me to check my table, index, queries etc...but here is the kicker.  After I executed the stored procedure from the front end, I logged onto the database server, opened SQL Studio Management Express, executed the same stored procedure with identical parameters and it returned within a couple of seconds (ALL WHILE the front end was still waiting on the same data!).  Does anyone have any experience with a similar problem.  Basically, I'm seeing different database behavior from Coldfusion vs. directly against the database (SQL Studio Management Express).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328059
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Simon Hooker]

Have you considered trying calling the stored procedure from within a <cfquery> tag, just put in the query used to call it directly into there.  Should see if it's something about the <cfstoredproc> tag or something between coldfusion + the db server

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328062
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Todd-3]

Can you post the cfstoredproc tag from your code as well as the arguments you're passing to it? If you can post the query to, that'd be good.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328072
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Brad Wood-3]

Do both options and run a trace both times and examine the trace.  
Also, in SQL Server 2005 you can get the execution plan of a running
statement.   Do that and compare.  

I've had the same behavior before that was linked to a cached execution
plan (that involved a table scan) associated with a pooled statement on
the CF server, but ran fine in Query Analyzer.  

~Brad


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328073
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Asaf Peleg]

Hi Guys,

Thanks for all your advice. It turns out what I thought was the cause of the problem was just a red herring.  This query was joining on a few tables and recently one of the tables it was joining on had grown two or three fold causing the result set of the query to grow exponentially.  We worked around the join and were able to greatly improve the performance of the query.  I'm still unsure why the original query ran quickly in SQL Server Management Studio vs. From Coldfusion but I think that is a harder question to answer.

Regards,
Asaf


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328078
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4