t-sql execute faster - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

t-sql execute faster

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2011-08-17 : 22:29:34

I would like your suggestions on how to make a sql server 2008 r2 stored procedure to run more efficiently. The stored procdure is used as a 'search' for users to see the hitory of information
before the user decide what to do with the records they are currently working with. The following is the stored procedure as it exists currently:

alter PROCEDURE [dbo].[brw_Rec]
@rId numeric(18,0),
@rId2 numeric(18,0) = NULL
AS
BEGIN

select RID,PA_ID
from app_table
WHERE (PA_ID= @rId) or (RID between @rId and @rId2) or
(PA_ID= @rId) or (PA_ID between @rId and @rId2)
END
go

This stored procedure takes too long to execute when either of the 'between' parameters are used. The between is picking a range of values. This stored procedure can not be split up into two stored procedures since it is called by a C#.net 2008 desktop application.

Basically the same parameters a used here. The PA_ID column is the parent record and the RID column is the child record. The PA_ID column can never have the same value that the RID column has. This is programmed into the C#.net 2008 desktop application.

Let me know how you think this stored procedure can be changed so it executes faster.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 23:13:00

why is the condition repeated ?


WHERE (PA_ID= @rId) or (RID between @rId and @rId2) 
or    (PA_ID= @rId) or (PA_ID between @rId and @rId2)

Do you have index on the PA_ID & RID ?

KH
[spoiler]Time is always against us[/spoiler]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 23:48:54

do you have indexes on PA_ID, RID?

If you don't have the passion to help people, you have no passion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 00:08:58

quote:

Originally posted by jassie


       select RID,PA_ID 
       from app_table	
       WHERE (PA_ID= @rId) or (RID between @rId and @rId2) or
             (PA_ID= @rId) or (PA_ID between @rId and @rId2)

BETWEEN is inclusive, so this is the same query


       select RID, PA_ID 
       from app_table	
       WHERE (RID between @rId and @rId2) or
             (PA_ID between @rId and @rId2)

Still, this OR calls for a SCAN in your table.
Try this instead.


       select RID, PA_ID 
       from app_table	
       WHERE RID between @rId and @rId2

       UNION 

       select RID, PA_ID 
       from app_table	
       WHERE PA_ID between @rId and @rId2

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources