Re: Slow execution of a stored procedure

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

From: Roji. P. Thomas (thomasroji_at_gmail.com)
Date: 02/17/05 

Date: Thu, 17 Feb 2005 18:57:35 +0530

Uri,

    I dont think the index on Surname will be used when you have a
    '%' sign in front of the @strField parameter (as mentioned by theop) 

-- 
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@iscar.co.il> wrote in message 
news:uYd2QMPFFHA.624@TK2MSFTNGP15.phx.gbl...
> Hi
> No, it is not local variable. Its parameter that you provide to.
> I'd try something this
>
> CREATE Table #Test
> (
> [id] INT NOT NULL PRIMARY KEY,
> FirstName VARCHAR(50) NOT NULL,
> LastName VARCHAR(50) NOT NULL,
> SurName AS FirstName+' '+ LastName
> )
> GO
> CREATE INDEX my_ind ON #Test(Surname)
>
> SELECT Table1.*
> FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
> WHERE  SurName Like  @strField
> ORDER BY SurName
>
>
>
>
> "pep_castefa" <pepcastefa@discussions.microsoft.com> wrote in message
> news:A838F49F-6919-464F-AE15-DF036741CFD8@microsoft.com...
>> Yes, it is a local variable declared in the SP... The actual SP is very
>> similar to this:
>>
>> Create Procedure spGetSomething
>> (
>> @strField varChar(60)
>> )
>> As
>>
>> SELECT Table1.*
>> FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
>> WHERE (Table1.Field1 + ' ' + Table1.Field2 + ' ' + Table1.Field3 Like
>> @strField)
>> ORDER BY Table1.Field1, Table1.Field2, Table1.Field3
>>
>> I have to do something like this because Field1 stands for  NAME, Field2
> for
>> SURNAME and Field3 for SECOND SURNAME. I want to be able to ask for
> people
>> whose name cotains certain parameter (for example %SMITH%). I also need
> the
>> JOIN because I just need people who are contained in Table2.
>>
>> "Uri Dimant" wrote:
>>
>> > Hi
>> > I was based on your narrative ,so I did not know that it is not actual
> your
>> > stored procedure. For the first time your run SP SQL Server determine
> that
>> > the query plan  is not in cache so it go to physical disk to read the
> data
>> > and 'save it' in the cache.
>> > Does @strField is local variable?
>> >
>> > When SQL Server compiles the SP it does not know the value of the local
>> > variable within SP and it may lead to bad execution plan .
>> >
>> >
>> >
>> > "pep_castefa" <pepcastefa@discussions.microsoft.com> wrote in message
>> > news:9F3DA6AA-D672-40C0-8257-C2020501D0B6@microsoft.com...
>> > > I have discovered something... the first time I execute the SP the
>> > variable
>> > > 'physical reads' is high (a lot of reads from physical disk). All the
>> > other
>> > > executions have this variable to 0. Does that mean it has all the
>> > 1.300.000
>> > > rows in memory? If so, is there any way to initialize this table in
> memory
>> > > the first time SQL Server executes? (there is an ugly way, which 
>> > > would
> be
>> > > executing the SP the first time SQL starts just to make it bring the
> table
>> > to
>> > > memory...)
>> > > By the way, Uri... the table doesn't have many columns. I dont think
> that
>> > is
>> > > the problem.
>> > > What I have seen in the execution plan is that it first  uses a
> clustered
>> > > index seek, then it filters the values. The clustered index seek is
>> > because
>> > > the SP I wrote for this post was not really the one Im using. The one
> Im
>> > > using in production has a JOIN with another table. What I dont know 
>> > > is
> if
>> > it
>> > > would be better to do the filter first (passing from 1.300.000
> registers
>> > to
>> > > no more than 50), using the index, and then do the clustered index
> seek
>> > (to
>> > > do the join with the other table). Is there any way to tell SQL 
>> > > Server
> to
>> > do
>> > > the filtering first and then the JOIN?
>> > >
>> >
>> >
>> >
>
>

Relevant Pages

  • Re: Slow execution of a stored procedure
    ... Create Procedure spGetSomething ... > Does @strField is local variable? ... > When SQL Server compiles the SP it does not know the value of the local ... >> executing the SP the first time SQL starts just to make it bring the table ...
    (microsoft.public.sqlserver.programming)
  • Validation Text
    ... I have set up constraints for a table in SQL Server 2000 Enterprise ... Manager, The consraints are only simple ie: ([Surname] is not null) we ...
    (microsoft.public.sqlserver.programming)
  • Re: Creating index on two columns
    ... >and I use the following stored procedure to do a SELECT which I use on ... >My first thought was to index the Name and Surname columns, ... That index would only be useful if you would make it the clustered index ... faster than using the index on, so SQL Server should ...
    (microsoft.public.sqlserver.server)
  • Re: Retrieving Table ID
    ... Assuming you are using SQL Server, ... Customer(ForeName, SurName) VALUES; ... Change your update statement from ... Forename and Surname. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Passing a Parameter to a Form when Opening
    ... The best thing is first to look at the capabilities of the SQL server ... > CustID, Surname, Forename and address of all people with that surname. ... > The correct customer is then selected from the datagrid and I want to open ...
    (microsoft.public.dotnet.general)