Stored procedure slow performance

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

General SQL Server Forums

New to SQL Server Programming

Stored procedure slow performance

Author

Topic

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-06-29 : 17:47:48

The below is my procedure its taking the huge time (more than 20 seconds) to run the procedure. Please help me any one. how to increase the performance

--create procedure
Declare

@User_ID INT,
@From_Date DATETIME,
@To_Date DATETIME,
@Search_Mode VARCHAR(1),
@Form_ID INT,
@Form_Definition_ID INT,
@Status_ID INT,
@Pend_Reason_ID INT,
@CustomerName VARCHAR(255),
@Queue_ID INT,
@Priority_ID INT,
@ReferenceData VARCHAR(100),
@Requestor VARCHAR(100),
@QuestionDetails VARCHAR(75),
@SupervisorID INT,
@DateRequested DATETIME,
@CheckedOutTo INT,
@ReviewerID INT,
@DateCompleted DATETIME,
@PendDate DATETIME,
@PSTicket VARCHAR(50),
@Defect VARCHAR(50),
@RecordCount INT,
@PendTime VARCHAR(10)

BEGIN

set @User_ID = 595
set @From_Date = '01-Jan-2009'
set @To_Date = '29-May-2009'
set @Search_Mode = 'B'
set @Form_ID = NULL
set @Form_Definition_ID = NULL
set @Status_ID = NULL
set @Pend_Reason_ID = NULL
set @CustomerName = NULL
set @Queue_ID =NULL
set @Priority_ID =NULL
set @ReferenceData = NULL
set @Requestor = NULL
set @QuestionDetails = NULL
set @SupervisorID = NULL
set @DateRequested = NULL
set @CheckedOutTo = NULL
set @ReviewerID = NULL
set @DateCompleted = NULL
set @PendDate = NULL
set @PSTicket = NULL
set @Defect = NULL
set @RecordCount = NULL
set @PendTime = NULL

-- local variables
DECLARE @iUserID INT
DECLARE @Query VARCHAR(8000)
DECLARE @Query_Basic VARCHAR(8000)
DECLARE @SQLSTATUS AS VARCHAR(100)

--check fromdate and todate
IF (@From_Date IS NULL OR @To_Date IS NULL)
BEGIN
--raise the error if fromdate or todate is null
RAISERROR('From date and/or the to date must be specified.',14,1)
RETURN
END

--check the requestor
IF (@Requestor IS NOT NULL)
BEGIN
SET @iUserID = @Requestor
END

SET @Query = ''
SET @Query_Basic = ''

--check the recordcount
IF ((@RecordCount<>0)AND(@RecordCount IS NOT NULL))
BEGIN
--set top n record value
SET @Query = ' Top '+CAST(@RecordCount AS VARCHAR)+' '
END

SET @CustomerName = '%' + (@CustomerName) + '%';
SET @ReferenceData = '%' + (@ReferenceData) + '%';

--check the status id
SET @SQLSTATUS = ''
IF (@Status_ID is not null)
BEGIN
IF (@Status_ID = -1)
BEGIN
--if status id equal to 1, search all status id except status id 4
SELECT @SQLSTATUS=@SQLSTATUS + ',' + CONVERT(VARCHAR,status_id) FROM dbo.status (NOLOCK) WHERE status_id<>4
SELECT @SQLSTATUS='('+SUBSTRING(@SQLSTATUS,2,LEN(@SQLSTATUS)) + ')'

END
ELSE
BEGIN
--if status id not equal to 1, search specific status id
SELECT @SQLSTATUS=@SQLSTATUS + ',' + CONVERT(VARCHAR,status_id) FROM dbo.status (NOLOCK) WHERE status_id=@Status_ID
SELECT @SQLSTATUS='('+SUBSTRING(@SQLSTATUS,2,LEN(@SQLSTATUS)) + ')'
END
END
ELSE
BEGIN
--if status id equal to null, search all status id
SELECT @SQLSTATUS=@SQLSTATUS + ',' + CONVERT(VARCHAR,status_id) FROM dbo.status (NOLOCK)
SELECT @SQLSTATUS='('+ SUBSTRING(@SQLSTATUS,2,LEN(@SQLSTATUS)) + ')'
END

SET @Query_Basic = 'SELECT distinct '

--generate dynamic search query
IF ((@RecordCount<>0)AND(@RecordCount IS NOT NULL))
BEGIN
SET @Query_Basic = @Query_Basic + @Query
END

SET @Query_Basic =
@Query_Basic + ' frm.form_master_id AS Form_Id,' +
'fd.form_name AS Form_Name,' +
'fs.status_name AS Status_Name ,' +
'fp.pend_reason_name AS Reason_Name,' +
'fq.Queue_Name AS Queue_Name,' +
'fpr.Priority_Name AS Priority_Name,' +
'frm.Reference_Data AS ReferenceData,' +
'frm.Customer_Name AS Customer_Name,'+
'fu.User_Name '+'AS [Requestor_Name],'+
'CAST(frm.brief_Answer AS VARCHAR(75)) AS [Question_Details],'+
'fu1.User_Name '+'AS [Supervisor_Name],'+
'CONVERT(VARCHAR,frm.created_date,101) AS [Date_Requested],'+
'fu3.User_Name '+'AS [CheckedOutTo],'+
'fu2.User_Name '+'AS [Reviewer_Name],'+
'CONVERT(VARCHAR,fm.Completed_Date,101) AS [Date_Completed],'+
-- 'dbo.usp_fn_GetTimeQueued(fm.Created_Date,fm.Completed_Date,GETDATE(),fm.Status_ID) AS [Time_Queued],' +
-- 'dbo.usp_fn_GetTimeQueued(fm.created_date,fm.Completed_Date,GETDATE(),fm.Status_ID) AS [Time_Queued],' +
'ROUND(DATEDIFF(HOUR,fm.created_date, CASE WHEN fm.Status_ID=4 THEN fm.Completed_Date ELSE GETDATE() END) ,2) AS [Time_Queued],' +
'CONVERT(VARCHAR,fm.pending_Date,101) AS [Pend_Date],'+
'fm.PS_Ticket_Num AS [PS_Ticket_Num],'+ 'fm.Defect_Num AS [Deffect_Num], '+
'fm.pending_time AS [Pend_Time] '+
'FROM dbo.Form fm (NOLOCK)' +
'INNER JOIN dbo.form_master frm WITH (NOLOCK) ' +
'ON frm.form_master_id = fm.form_master_id ' +
'INNER JOIN dbo.form_definition fd WITH (NOLOCK) ' +
'ON frm.form_definition_id = fd.form_definition_id ' +
'INNER JOIN dbo.status fs WITH (NOLOCK) ' +
'ON fm.Status_ID = fs.Status_ID ' +
'LEFT OUTER JOIN dbo.pend_reason fp WITH (NOLOCK) ' +
'ON fm.pending_reason_id = fp.pend_reason_id ' +
'INNER JOIN dbo.Priority fpr WITH (NOLOCK) ' +
'ON fm.Priority_ID = fpr.Priority_ID ' +
'INNER JOIN dbo.[User_Version] fu WITH (NOLOCK) '+
'ON frm.Created_user=fu.[User_Id] '+
'LEFT OUTER JOIN dbo.[User_Version] fu1 WITH (NOLOCK) '+
'ON fu1.[User_Id] = fu.Supervisor_ID '+
'LEFT OUTER JOIN dbo.[User_Version] fu2 WITH (NOLOCK) '+
'ON frm.Modified_USER = fu2.[User_Id] '+
'LEFT OUTER JOIN dbo.[User_Version] fu3 WITH (NOLOCK) '+
'ON fm.CheckedOut_By = fu3.[User_Id] '+
'INNER JOIN dbo.User_Queue fuq WITH (NOLOCK) ' +
'ON fm.Queue_ID = fuq.Queue_ID AND fuq.active_flag = 1 ' +
'INNER JOIN dbo.Queue fq WITH (NOLOCK) ' +
'ON fq.Queue_ID = fuq.Queue_ID ' +
'WHERE fm.created_date BETWEEN '+'''' + convert(varchar,@From_Date,101) + '''' +' AND '+ ''''+ convert(varchar,@To_Date,101) + ' 11:59 PM''' +
' AND fm.active_flag = 1 '

SET @Query_Basic = @Query_Basic + ' AND fuq.User_ID = ' + CAST(@User_ID as VARCHAR)

IF(@Form_ID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND frm.form_master_id = ' + CAST(@Form_ID as VARCHAR)
END

IF(@Form_Definition_ID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND frm.Form_Definition_ID = ' + CAST(@Form_Definition_ID as VARCHAR)
END

IF(@SQLSTATUS <> '')
BEGIN
SET @Query_Basic = @Query_Basic + ' AND fm.Status_ID in ' + @SQLSTATUS
END

IF(@Pend_Reason_ID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND fm.pending_reason_id = ' + CAST(@Pend_Reason_ID as varchar)
END

IF(@Queue_ID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND fm.Queue_ID = ' + CAST(@Queue_ID as varchar)
END

IF(@Priority_ID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND fm.Priority_ID = ' + CAST(@Priority_ID as varchar)
END

IF(@iUserID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND frm.Created_user = ' + CAST(@iUserID as VARCHAR)
END

IF(@ReviewerID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND frm.Modified_USER = ' + CAST(@ReviewerID as varchar)
END

IF(@ReferenceData IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND (frm.Reference_Data = ' + ''''+ @ReferenceData + ''''+ ' or frm.Reference_Data LIKE ' + '''' + @ReferenceData + '''' + ')'
END

IF(@DateRequested IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic +' AND CONVERT(CHAR(10),frm.created_date,101) = ' + '''' + CONVERT(CHAR(10),@DateRequested,101)+''''
END

IF(@PendDate IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND CONVERT(CHAR(10),fm.pending_Date,101) = ' + ''''+CONVERT(CHAR(10),@PendDate,101)+''''
END

IF(@PSTicket is NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND fm.PS_Ticket_Num = ' + ''''+ @PSTicket + ''''
END

IF(@Defect IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND fm.Defect_Num = ' + '''' + @Defect + ''''
END

IF(@QuestionDetails IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic +' AND (frm.brief_Answer = ' + '''' + @QuestionDetails + '''' + ' OR frm.brief_Answer LIKE ' + ''''+ '%' + @QuestionDetails + '%'+''''+')'
END

IF(@SupervisorID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic +' AND fu.Supervisor_ID = ' + CAST(@SupervisorID AS VARCHAR)
END

IF(@CustomerName IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic +' AND (frm.Customer_Name = ' + ''''+CAST(@CustomerName AS VARCHAR)+''''+' OR frm.Customer_Name LIKE '+''''+CAST(@CustomerName AS VARCHAR) + '''' +')'
END

IF(@CheckedOutTo IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic +' AND fm.CheckedOut_By = ' + CAST(@CheckedOutTo AS VARCHAR)
END

IF(@DateCompleted IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic +' AND CONVERT(CHAR(10),fm.Completed_Date,101)= ' + '''' + CONVERT(CHAR(10),@DateCompleted,101)+''''
END

IF(@PendTime IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic +' AND fm.pending_time = ' + ''''+CAST(@PendTime AS VARCHAR)+''''
END
--execute @Query_Basic
EXEC(@Query_Basic)

-- Print @Query_Basic

END

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-29 : 18:03:52

With a query as large as yours, performance will definitely be an issue. You need to make sure you have the proper indexes setup on your tables - that will make most of the difference.

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:23:28

why are you using dynamic sql here?

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-06-30 : 14:12:56

Is there any other option to modify this procedure plz modify the proce and help me i have very urgent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 14:15:15

you can replace each of below conditions like

IF(@Form_ID IS NOT NULL)
BEGIN
SET @Query_Basic = @Query_Basic + ' AND frm.form_master_id = ' + CAST(@Form_ID as VARCHAR)
END

to form
(AND frm.form_master_id =@Form_ID OR @Form_ID IS NULL)

to avoid dynamic sql

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-06-30 : 14:22:58

Thanks for your support. But i have tried the same method what you sent, But still the performance is an issues. Please tell me any other solutions.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:40:20

Drop all dynamic sql, follow the suggestion posted /30/2009 : 14:15:15
and add "OPTION (WITH RECOMPILE)" at the end of query.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:45:42

This is what is killing your query!

SET @CustomerName = '%' + (@CustomerName) + '%';
SET @ReferenceData = '%' + (@ReferenceData) + '%';

It forces a table scan on the tables because you can't use an index.

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

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-06-30 : 15:26:24

Here i have changed the query with out dynamic, But it getting more than the dynamic query time. Then if i use 'OPTION (WITH RECOMPILE)'
the below error is displayed. I am using MS-SQL Server 2000

Server: Msg 156, Level 15, State 1, Line 287
Incorrect syntax near the keyword 'WITH'.

Please tell me solution for this issue

Here the procedure

--create procedure
Declare

@User_ID INT,
@From_Date DATETIME,
@To_Date DATETIME,
@Search_Mode VARCHAR(1),
@Form_ID INT,
@Form_Definition_ID INT,
@Status_ID INT,
@Pend_Reason_ID INT,
@CustomerName VARCHAR(255),
@Queue_ID INT,
@Priority_ID INT,
@ReferenceData VARCHAR(100),
@Requestor VARCHAR(100),
@QuestionDetails VARCHAR(75),
@SupervisorID INT,
@DateRequested DATETIME,
@CheckedOutTo INT,
@ReviewerID INT,
@DateCompleted DATETIME,
@PendDate DATETIME,
@PSTicket VARCHAR(50),
@Defect VARCHAR(50),
@RecordCount INT,
@PendTime VARCHAR(10)

BEGIN

set @User_ID = 595
set @From_Date = '01-Jan-2009'
set @To_Date = '29-May-2009'
set @Search_Mode = 'B'
set @Form_ID = NULL
set @Form_Definition_ID = NULL
set @Status_ID = NULL
set @Pend_Reason_ID = NULL
set @CustomerName = NULL
set @Queue_ID =NULL
set @Priority_ID =NULL
set @ReferenceData = NULL
set @Requestor = NULL
set @QuestionDetails = NULL
set @SupervisorID = NULL
set @DateRequested = NULL
set @CheckedOutTo = NULL
set @ReviewerID = NULL
set @DateCompleted = NULL
set @PendDate = NULL
set @PSTicket = NULL
set @Defect = NULL
set @RecordCount = NULL
set @PendTime = NULL

-- local variables
DECLARE @iUserID INT
DECLARE @Query VARCHAR(8000)
DECLARE @Query_Basic VARCHAR(8000)
DECLARE @SQLSTATUS AS VARCHAR(100)

--check fromdate and todate
IF (@From_Date IS NULL OR @To_Date IS NULL)
BEGIN
--raise the error if fromdate or todate is null
RAISERROR('From date and/or the to date must be specified.',14,1)
RETURN
END

--check the requestor
IF (@Requestor IS NOT NULL)
BEGIN
SET @iUserID = @Requestor
END

SET @Query = ''
SET @Query_Basic = ''

--check the recordcount
IF ((@RecordCount<>0)AND(@RecordCount IS NOT NULL))
BEGIN
--set top n record value
SET @Query = ' Top '+CAST(@RecordCount AS VARCHAR)+' '
END

SET @CustomerName = '%' + (@CustomerName) + '%';
SET @ReferenceData = '%' + (@ReferenceData) + '%';

--check the status id
SET @SQLSTATUS = ''
IF (@Status_ID is not null)
BEGIN
IF (@Status_ID = -1)
BEGIN
--if status id equal to 1, search all status id except status id 4
SELECT @SQLSTATUS=@SQLSTATUS + ',' + CONVERT(VARCHAR,status_id) FROM dbo.status (NOLOCK) WHERE status_id<>4
SELECT @SQLSTATUS='('+SUBSTRING(@SQLSTATUS,2,LEN(@SQLSTATUS)) + ')'

END
ELSE
BEGIN
--if status id not equal to 1, search specific status id
SELECT @SQLSTATUS=@SQLSTATUS + ',' + CONVERT(VARCHAR,status_id) FROM dbo.status (NOLOCK) WHERE status_id=@Status_ID
SELECT @SQLSTATUS='('+SUBSTRING(@SQLSTATUS,2,LEN(@SQLSTATUS)) + ')'
END
END
ELSE
BEGIN
--if status id equal to null, search all status id
SELECT @SQLSTATUS=@SQLSTATUS + ',' + CONVERT(VARCHAR,status_id) FROM dbo.status (NOLOCK)
SELECT @SQLSTATUS='('+ SUBSTRING(@SQLSTATUS,2,LEN(@SQLSTATUS)) + ')'
END

SELECT frm.form_master_id AS Form_Id,
fd.form_name AS Form_Name,
fs.status_name AS Status_Name ,
fp.pend_reason_name AS Reason_Name,
fq.Queue_Name AS Queue_Name,
fpr.Priority_Name AS Priority_Name,
frm.Reference_Data AS ReferenceData,
frm.Customer_Name AS Customer_Name,
fu.User_Name AS [Requestor_Name],
CAST(frm.brief_Answer AS VARCHAR(75)) AS [Question_Details],
fu1.User_Name AS [Supervisor_Name],
CONVERT(VARCHAR,frm.created_date,101) AS [Date_Requested],
fu3.User_Name AS [CheckedOutTo],
fu2.User_Name AS [Reviewer_Name],
CONVERT(VARCHAR,fm.Completed_Date,101) AS [Date_Completed],
ROUND(DATEDIFF(HOUR,fm.created_date,
CASE WHEN fm.Status_ID=4 THEN fm.Completed_Date
ELSE GETDATE()
END) ,2) AS [Time_Queued],
CONVERT(VARCHAR,fm.pending_Date,101) AS [Pend_Date],
fm.PS_Ticket_Num AS [PS_Ticket_Num],
fm.Defect_Num AS [Deffect_Num],
fm.pending_time AS [Pend_Time]

FROM dbo.Form fm (NOLOCK)
INNER JOIN dbo.form_master frm WITH (NOLOCK) ON frm.form_master_id = fm.form_master_id
INNER JOIN dbo.form_definition fd WITH (NOLOCK) ON frm.form_definition_id = fd.form_definition_id
INNER JOIN dbo.status fs WITH (NOLOCK) ON fm.Status_ID = fs.Status_ID
LEFT OUTER JOIN dbo.pend_reason fp WITH (NOLOCK) ON fm.pending_reason_id = fp.pend_reason_id
INNER JOIN dbo.Priority fpr WITH (NOLOCK) ON fm.Priority_ID = fpr.Priority_ID
INNER JOIN dbo.[User_Version] fu WITH (NOLOCK) ON frm.Created_user=fu.[User_Id]
LEFT OUTER JOIN dbo.[User_Version] fu1 WITH (NOLOCK) ON fu1.[User_Id] = fu.Supervisor_ID
LEFT OUTER JOIN dbo.[User_Version] fu2 WITH (NOLOCK) ON frm.Modified_USER = fu2.[User_Id]
LEFT OUTER JOIN dbo.[User_Version] fu3 WITH (NOLOCK) ON fm.CheckedOut_By = fu3.[User_Id]
INNER JOIN dbo.User_Queue fuq WITH (NOLOCK) ON fm.Queue_ID = fuq.Queue_ID AND fuq.active_flag = 1
INNER JOIN dbo.Queue fq WITH (NOLOCK) ON fq.Queue_ID = fuq.Queue_ID

WHERE fm.created_date BETWEEN convert(varchar,@From_Date,101) AND convert(varchar,@To_Date,101) + ' 11:59 PM '
AND fm.active_flag = 1 and fuq.User_ID = CAST(@User_ID as VARCHAR) and (frm.form_master_id = @Form_ID or @Form_ID is null)
AND (frm.Form_Definition_ID = CAST(@Form_Definition_ID as VARCHAR) or @Form_Definition_ID is null)
and (fm.Status_ID <> 4 AND fm.Status_ID = CAST(@Status_ID as varchar) or @Status_ID is null )
AND (fm.pending_reason_id = CAST(@Pend_Reason_ID as varchar) or @Pend_Reason_ID IS NULL)
AND (fm.Queue_ID = CAST(@Queue_ID as varchar) Or @Queue_ID IS NULL)
AND (fm.Priority_ID = CAST(@Priority_ID as varchar) OR @Priority_ID IS NULL)
AND (frm.Created_user = CAST(@iUserID as VARCHAR) OR @iUserID IS NULL)
AND (frm.Modified_USER = CAST(@ReviewerID as varchar) OR @ReviewerID IS NULL)
AND (frm.Reference_Data = @ReferenceData or frm.Reference_Data LIKE '%' + @ReferenceData Or @ReferenceData IS NULL)
AND (CONVERT(CHAR(10),frm.created_date,101) = CONVERT(CHAR(10),@DateRequested,101) Or @DateRequested IS NULL)
AND (CONVERT(CHAR(10),fm.pending_Date,101) = CONVERT(CHAR(10),@PendDate,101) OR @PendDate IS NULL)
AND (fm.PS_Ticket_Num = @PSTicket OR @PSTicket IS NULL)
AND (fm.Defect_Num = @Defect OR @Defect IS NULL)
AND (frm.brief_Answer = @QuestionDetails OR frm.brief_Answer LIKE '%' + @QuestionDetails + '%' Or @QuestionDetails IS NULL)
AND (fu.Supervisor_ID = CAST(@SupervisorID AS VARCHAR) OR @SupervisorID IS NULL)
AND (frm.Customer_Name = CAST(@CustomerName AS VARCHAR) OR frm.Customer_Name LIKE CAST(@CustomerName AS VARCHAR) OR @CustomerName IS NULL)
AND (fm.CheckedOut_By = CAST(@CheckedOutTo AS VARCHAR) OR @CheckedOutTo IS NULL)
AND (CONVERT(CHAR(10),fm.Completed_Date,101)= CONVERT(CHAR(10),@DateCompleted,101) OR @DateCompleted IS NULL)
AND (fm.pending_time = CAST(@PendTime AS VARCHAR) OR @PendTime IS NULL)

--OPTION (WITH RECOMPILE)

END

Help me any one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:18:33

it should be used after your create procedure

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-07-01 : 13:55:03

I have checked. The Same Error is displayed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:56:50

you used like this?

CREATE PROC YourProcName WITH RECOMPILE

AS

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 14:12:53

Wait..are you doing a DROP and CREATE

OR...

an ALTER

And correct me if I'm wrong...but a drop and create will force a recomplie

In any case your performance issues are, as PESO points out, that you have a stage 2 predicate, which will force a scan regardless of any indexes you have

Why do you have

SET @CustomerName = '%' + (@CustomerName) + '%';
SET @ReferenceData = '%' + (@ReferenceData) + '%';

Unless yuo remove the leading %, it will scan

How many rows are in form_master?

Also get rid of (NOLOCK)

Did someone tell you that would increase performance?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-07-01 : 14:35:00

4 Laks rows in form_master table

I removed the Line With (NOLOCK)

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-07-01 : 14:54:37

i did not use index for all the fields were i have used in where clause. It may be the reason for performance issues? I don't know please tell me we should create index for all the fields which i have used in where clause.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 16:11:43

quote:
Originally posted by Prabu.Ramaiah

4 Laks rows in form_master table

Laks?

quote:

I removed the Line With (NOLOCK)

Good...it only allows dirty reads

Index on Created_Date?

And


AND (frm.Customer_Name = CAST(@CustomerName AS VARCHAR) OR frm.Customer_Name LIKE CAST(@CustomerName AS VARCHAR) OR @CustomerName IS NULL)

Isn't @CustomerName varchar already?

So many bad things here

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 16:17:33

Hi Brett!
http://en.wikipedia.org/wiki/Lakh
Greetings
Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 16:20:34

I just feel all warm and fuzzy inside now

I'm gonna go and down 1 Latka's worth of margaritas

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 16:24:48

You shouldn't if you already feel all warm and fuzzy inside NOW...

No, you're never too old to Yak'n'Roll if you're too young to die.

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2009-07-01 : 16:45:32

There is no index for Created_Date field and only default index is created (Primary key field)

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 16:58:17

NO, i'm saying to create and index and get rid of the leading % in the LIKE

Please explain your understanding of why you need dynamic sql?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources