Slow stored procedure performance

[tmessier]

I have a stored procedure that is running fast in SQL Mgmt Studio but is very slow in CF. I've tried running it both using cfstoreproc and cfquery and it's always slow. It wouldn't be a matter of a lot of data being returned either, there are only 64 rows returned. Not sure what it could be, as far as I know we have a number of servers with the same installation and I would think the DB drivers are the same, but maybe I'm missing something. Any ideas?

[Dan_Bracuk]

To isolate the problem, I would create a very simple sp, something like

select 'x' x

Then run it with Coldfusion and see what sort of exection time you get.

[tmessier]

There are a lot of other sps in the app I'm working on, and for the most part they run fast. Just to make sure, I took one of them and put it in a simple page, and as expected it ran very fast.

[tmessier]

I've gone a step further. I took the entire content of the stored proc and put them in a cfquery, and that way it runs fast. Btw, when I say fast vs slow, I mean 1812ms vs 72160ms, so it's a big discrepancy.

[tmessier]

Figured it out. Very weird issue, all I had to do was drop the procedure and re-create it. Wonder if for some reason the proc running from CF was using some bad execution plan for some reason. Anyway, that was a simple, if strange fix.