Stored procs run slow

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Danny J. Lesandrini (dlesandrini_at_hotmail.com)
Date: 05/13/04 

Date: Wed, 12 May 2004 19:17:34 -0600

Briefly, stored procs that run fast on other machines are taking 20
to 30 times longer on one client's server. If I extract the actual
SQL Select from the proc and run it with hard coded parameter
values, it returns quickly, but the proc takes forever.

Anyone ever heard of this before? Do I need to recompile the
whole batch of stored procs? If so, how do I do that.

<<< The more verbose question, if you need more information >>>

I've deployed a .Net-Crystal reporting tool on a number of clients. The
data is always in SQL Server and is returned using Stored Procs.

I'm not doing anything fancy. The procs are simple. For example ...

    Each proc builds a temp table of ProjectIDs for filtering results, but
    there are no cursors at all. I'm joining 4 tables or less and doing
    some aggregations with calculations, such as converting seconds to
    minutes and summing across projects and Agents.

I've run these procs on various sized databases and they usually return
results in less than 30 seconds. Yesterday I ran across a server that
required 7 minutes to return the same results on a similar number of rows.

This problem database is large, being about 6 gig. It's a dual processor
with a ton of RAM. There's plenty of disk space. I've checked for and
found all required indexes. I ran sp_updatestats and even ran the
update statistics on the problem table with the option to do a full update.

As I mentioned, when I extract the actual SQL SELECT and run it with
filter criteria hard coded, the results returns very, very quickly. It's
just
the proc that runs slow ... and ALL of them run slow.

Any ideas?

Thanks

Danny J. Lesandrini
dlesandrini@hotmail.com
http://www.amazecreations.com/datafast

Relevant Pages

  • Re: script for trace form Query analyzer ?
    ... I think the server side trace stored procs are implemented as extended ... Vyas, MVP (SQL Server) ...
    (microsoft.public.sqlserver.server)
  • Re: LINQ Queries vs Stored Procs
    ... Stored Procedures, you can limit the user to only executing stored ... most sql injection attacks, which are more possible with dynamic sql. ... Also about the security aspects: it's not as you claim it to be: I can ... I as a user in Marketing have to use your app which uses procs and I ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Critique my LINQ to SQL strategy using stored procs, please
    ... LINQ to SQL seems like a good option for our data access needs, ... All business logic will reside in the stored procs (company ... Using LINQ to SQL, create a single LINQ to SQL dbml ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Planning on going with dynamic SQL, but...
    ... I'm leaning toward dynamic sql mostly because it would mean one ... I was leaning toward procs, but I have to admit it would be nice not to have ... stored procs can be a big win. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Security level to run SP_OA... Procedures
    ... I'm not sure why you are executing sp_OA* procs here since you can ... sp_OA* procs as well as other master database stored procedures. ... Configure the SQL Agent proxy account. ...
    (microsoft.public.sqlserver.security)