Stored Procedure (SP) Performance variation!

Hi,

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'))

That's it.

Any suggestions would be greatly appreciated.

EJ
LVL 6
kalliopiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
I realize this is not the solution to your problem, but have you considered rewriting your query as:

SELECT      t1.*
FROM      Table1 t1
      Inner Join Table2 t2 On t1.id = t2.id
WHERE      CONTAINS(t2.Notes, 'goat')
0
Anthony PerkinsCommented:
But getting back to your question, the only rational reason for something like this is that the .NET app is connection to a different database.  Otherwise the only big difference between the .NET app and SQL Query Analyzer is the provider.  SQL Query Analyzer uses ODBC.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kalliopiAuthor Commented:
I agree with your premise, but here's the confusing part:

1) I can run this code from within .NET:
            SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=MyDB;Integrated Security=True");
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "bs_findMatchingVerses";
            cmd.Parameters.Add("@SearchIn", SqlDbType.UniqueIdentifier);
            cmd.Parameters.Add("@SearchTerms", SqlDbType.VarChar, 100);
            cmd.Parameters["@SearchIn"].Value = new Guid("9F0DC292-CDB2-4814-A9EA-5EE2E46427A8");
            cmd.Parameters["@SearchTerms"].Value = "families";
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

These are the results from Profiler:
RPC:Completed      
exec bs_findMatchingVerses @SearchIn='9F0DC292-CDB2-4814-A9EA-5EE2E46427A8', @SearchTerms='families'      
.Net SqlClient Data Provider      
CPU: 1890      
Reads: 47812      
Duration: 1926

I can copy that "exec" command DIRECTLY, and paste it into Query Analyzer and run it from there and these are the results from EXACTLY the same command.

SQL:BatchCompleted      
exec bs_findMatchingVerses @SearchIn='9F0DC292-CDB2-4814-A9EA-5EE2E46427A8', @SearchTerms='families'
Microsoft SQL Server Management Studio - Query
CPU: 16
Reads: 4595
Duration: 25

That's literally 100 x fewer CPU cycles (on the server), 10x fewer reads on the database (server side), and overall it's 10 x faster.  I can't figure out how the connetion mechanism would effect (to that extent) how SQL server performs the query - no?

Any additional suggestions would be greatly appreciated.  Thank you.

EJ
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anthony PerkinsCommented:
I am afraid I have no idea.
0
kalliopiAuthor Commented:
Very strange though - right?
0
LowfatspreadCommented:
do you always get the faster result  when you run in QA after running the .net code?

if so its probably buffer i/o related....

post the actual stored procedure code....


I presume from looking at your code that the stored procedure is generating dynamic sql...
do you include the with compile directive in the stored procedure?
 
SELECT * FROM Table1
WHERE id IN (SELECT id FROM Table2 WHERE CONTAINS(Notes, 'goat'))

write as

SELECT a.* FROM Table1 as a
WHERE Exists (SELECT id FROM Table2 as b  WHERE a.id = b.id and CONTAINS(Notes, 'goat'))
0
kalliopiAuthor Commented:
So I actually figured out the problem (though I still don't quite understand how/why it was happening).  Turns out that one of my queries (against table2) inside of the stored procedure was slowing things down.  For some reason, when I ran it through the Query Analyzer, SQL Server was using the index that I had setup on that table, just as it should.  But for some reason, when the SP was called from .NET it was ignoring that index and doing a table scan.

So, the solution was to put an index hint (INDEX=IDX_MyFastIndex) in the sub query and now it works like a charm.

Anyway - thanks for your help, I appreciate the feedback.  Take care.

EJ
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.