T-sql execute faster

15y2.1k

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
Accepted answer
I don't know whether it will be any faster but, as well as eliminating the surplus condition which Jitendra identified, you could try replacing the BETWEEN operator with a pair of >= and <= operators in case the former is actually implemented in a more complicated way:

select RID,PA_ID from app_table
WHERE (PA_ID= @rId)
or (@rId <= RID and RID <= @rId2)
or (@rId <= PA_ID and PA_ID <= @rId2)
0
Jiteendra Sampathirao15y ago
hi,

your select query is like this

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)

you wrote pa_id=@rid two times...

try this query....

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

hope it help you...
0