Re: Poor performance when executing stored procedure

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

From: Daniel Walzenbach (daniel.walzenbach.NOSPAM_at_freudenberg.de)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 06:03:02 -0700

Hello,

Thank you all for your help. Unfortunately I have to confess that I tried to simplify the problem and misdirected you all. As a matter of fact I have a couple of sp which are all responsible for a business object. E.g. there are sps like spCustomer, spOrders, spRequest whereas each handles everything for one business object. Those sp therefore tend to grow really big. By now I understand (or at least this is how I could explain myself this behavior) that the sql optimizer tries to optimize the whole sp instead of only the exact part of the sp I want to execute (there might be a part in the sp to create a customer while another part selects customer details or searches for orders belonging to one customer). Additionally which is even worse those sps can execute each other. I now assume that the sql optimizer tried to compile lets say spCustomer, was busy analysing a whole bunch of stuff which wasn’t necessary for the query to perform, then executed spOrders which is executed in spCustomers and then got stuc
k in a loop.

By now I assume that I have to split my sps in smaller batches of sql which only do what they are supposed to do. Whereas spOrders allowed me to create orders, update or search them I should now create a spOrderCreate, a spOrderUpdate and a spOrderSearch. Could you recommend this approach? I got used to the other since it was kind of “how it is done here” and I just accepted it without scrutinizing. What would be best practices on how to write sps more efficiently? I assume I just got one: Do only what really is necessary in one sp, am I right?

Best regards and thanks again for your help!

   Daniel

"Gert-Jan Strik" wrote:

> If there is no blocking going on, then this query (with your
> description) should return instantaneous.
>
> 1. If the query
>
> select count(*) from sometable (NOLOCK) where id=@SomeID
>
> returns immediately, then you might be looking at a locking problem.
>
> 2. If the query
>
> select count(*) from sometable where id=@SomeID OPTION(MAXDOP 1)
>
> returns immediately, you might have hit the parallelism bug
>
> 3. the last interesting one would be
>
> select count(*) from sometable (INDEX=0) where id=@SomeID
>
>
> By the way: does your table by any chance have a nonclustered index, but
> no clustered index? Just checking...
>
> Hope this helps,
> Gert-Jan
>
>
>
> Daniel Walzenbach wrote:
> >
> > Hi,
> >
> > I have a stored procedure which simply does a
> >
> > Select count(*) from sometable where ID=@SomeID
> >
> > whereas @SomeID is a parameter of this sp. Since the table is tiny (5 rows) and only has a couple of columns execution should be pretty fast. Oddly though it takes the sp almost 2-3 sec. return the result. Does anybody know what could be the reason for this behaviour? It seems that query analyser waits a bit before executing the sp. I understand that the optimizer has to compile the sp first, put it into its memory and than is able to execute to sp. Is there any possibility that sql server got confused and wants to compile the sp before every execution?
> > Do you have any idea what else could be the reason for this strange behaviour?
> >
> > Thank you! Best regards
> >
> > Daniel
>
> --
> (Please reply only to the newsgroup)
>

Relevant Pages

  • Re: SQL null problem
    ... Thanks a lot Nicholas and Daniel. ... >> Regards, ... > That's the defined behavior of the SQL SUM function. ... > Assuming you are querying SQL Server, you could write the query thus to ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQL null problem
    ... Thanks a lot Nicholas and Daniel. ... >> Regards, ... > That's the defined behavior of the SQL SUM function. ... > Assuming you are querying SQL Server, you could write the query thus to ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Poor performance when executing stored procedure
    ... If there is no blocking going on, then this query (with your ... does your table by any chance have a nonclustered index, ...
    (microsoft.public.sqlserver.programming)
  • Re: DCOM 10006
    ... > Hi Daniel, ... > Best regards, ... > | Can you advise do I edit the registry on the server or the PC with the ... > | If on the PC I am struggling to find this key: ...
    (microsoft.public.windows.server.sbs)
  • by means of Youssef al Shehris reward
    ... because of the terms of the prophecy; and as regards ... seventieth week of Daniel, during the continuance of the second temple, the ... and brought to the knowledge of the God ...
    (sci.crypt)