August 17, 2011 at 8:32 pm
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.
August 17, 2011 at 10:06 pm
There's just not enough to go on to make any recommendations, Wendy. Take a peak at the second link in my signature line below ... doing those things will help us to help you.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 10:12 pm
On second thought, there is some duplication of conditions that you could get rid of... here's your original code...
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
You list PA_ID=@rID twice. You could certainly get rid of one of those for starters. Still, providing the information asked for in the second link of my signature would still be beneficial.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 1:14 am
It's a fairly obvious one, but have you tried this?
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)
UNION /* not all */
SELECT RID,PA_ID
FROM app_table
WHERE (RID between @rId and @rId2)
UNION /* not all */
SELECT RID,PA_ID
FROM app_table
WHERE (PA_ID between @rId and @rId2)
END
go
August 18, 2011 at 5:11 am
Just can't tell you a thing beyond what Jeff has suggested without seeing the execution plan.
Numeric 19,0? Not an integer or a bigint? Is that data type the same as the database data type?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2011 at 11:47 am
R.P.Rozema (8/18/2011)
It's a fairly obvious one, but have you tried this?
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)
UNION /* not all */
SELECT RID,PA_ID
FROM app_table
WHERE (RID between @rId and @rId2)
UNION /* not all */
SELECT RID,PA_ID
FROM app_table
WHERE (PA_ID between @rId and @rId2)
END
go
Since @rId2 can be NULL, two additional UNION statements would probably need to be included:
UNION /* not ALL */
SELECT RID, PA_ID
FROM app_table
WHERE RID = @rId
AND @rId2 IS NULL
UNION /* not ALL */
SELECT RID, PA_ID
FROM app_table
WHERE PA_ID = @rId
AND @rId2 IS NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply