Solved

Stored Procedure (SP) Performance variation!

Posted on 2007-03-26
7
207 Views
Last Modified: 2013-12-17
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
0
Comment
Question by:kalliopi
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18794782
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18794800
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
 
LVL 6

Author Comment

by:kalliopi
ID: 18794948
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18795046
I am afraid I have no idea.
0
 
LVL 6

Author Comment

by:kalliopi
ID: 18795068
Very strange though - right?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18795093
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
 
LVL 6

Author Comment

by:kalliopi
ID: 18795245
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now