Stored Procedure (SP) Performance variation!
Posted on 2007-03-26
I have a stored procedure that uses FullTextSearch to look through a list of about 1/2 million records to find 100-200 matches for certain terms (like "goat"). So, when I run this sp through the QueryAnalyzer (with Profiler running as well), it takes about 35-40 milliseconds to run the query.
When I call it from my code (CSharp) though, the EXACT same query takes anywhere from 2 to 60 SECONDS to run. I can actually copy the exec command from Sql Profiler (that took 25 seconds to run), run it directly through Query Analyzer and it takes 35 Milli Seconds (as expected). Also, the CPU usage is in the thousands (11K) rather than 60-70 when I run the SP through QA. The number of reads is also off the charts (300K rather than 4-8K) when the SP is called from my code.
The bottom line is that I'm seeing RADICALLY different performance of exactly the same query depending on where it's being called from.
Because I can't reproduce the results in QA it's hard to know what the execution plan is when it's taking 30 seconds instead of 30 milliseconds. Any idea what might be going on though?
The query runs something like this:
SELECT * FROM Table1
WHERE id IN (SELECT id FROM Table2 WHERE CONTAINS(Notes, 'goat'))
Any suggestions would be greatly appreciated.