Solved

Any way to improve the performance of SQL query with optional parameters ?

Posted on 2009-07-10
5
221 Views
Last Modified: 2012-05-07

This query returns correct data, just appears to be a bit slower than hoped (about 0.9s in testing on development box)
I will be calling it up to 150 times in succession from my app, using different DocId's
The idea is to determine docrevision with highest priority, including testing a requested docrevision to see what it's priority would be.

ProjectId is alway available. usually only one of DocId and DocNo will be supplied.
I need the RevStyles as a LIKE operation, but am looking at removing the option for LIKE testing of DocId

Query is in a stored proc. Currently SQLS2000. 2005 is available.
declare @ProjectId varchar(8)

   declare @DocNo int

   declare @DocId varchar(50)

   declare @TestRev varchar(5)

   

   set @ProjectId = '07074'

   set @DocId = 'Q1251-ME-BWSE-1000'

   set @TestRev = 'D'

   

   SELECT  rtrim(Proj_DocRev.RevisionId) AS RevId, Proj_RevStyles.Priority as Priority

   FROM dbo.Proj_Documents  , dbo.Proj_DocRev , dbo.Proj_Codes , dbo.Proj_RevStyles 
 

   WHERE proj_documents.Projectno =  @ProjectId 

        AND ((@DocNo IS NULL) OR (Proj_Documents.DocNo = @DocNo))

        AND ((@DocId IS NULL) OR (Proj_Documents.DocId LIKE @DocId))

        AND Proj_Documents.DocNo = Proj_DocRev.DocNo

        AND Proj_Documents.ProjectNo = Proj_Codes.ProjectNo 

        AND Proj_Codes.RevisionStyle = Proj_Revstyles.Id

        AND Proj_DocRev.RevisionId LIKE Proj_RevStyles.Tag

	UNION 

		 SELECT rtrim(@TestRev) as RevId, Proj_RevStyles.Priority as Priority

		   FROM dbo.Proj_Documents  , dbo.Proj_DocRev , dbo.Proj_Codes , dbo.Proj_RevStyles 

		   WHERE Proj_Codes.ProjectNo =  @ProjectId

				AND Proj_Codes.RevisionStyle = Proj_Revstyles.Id

				AND ((@TestRev IS NOT NULL) AND (@TestRev LIKE Proj_Revstyles.Tag))

		

   ORDER BY Priority DESC,RevId DESC;

Open in new window

0
Comment
Question by:Robberbaron (robr)
5 Comments
 
LVL 32

Author Comment

by:Robberbaron (robr)
ID: 24821171
just realised i had too many tables in second query.  Removing them makes a significant improvement by itself.

   UNION                

       SELECT rtrim(@TestRev) as RevId, Proj_RevStyles.Priority as Priority

                   FROM dbo.Proj_Codes , dbo.Proj_RevStyles 

                   WHERE Proj_Codes.ProjectNo =  @ProjectId

                                AND Proj_Codes.RevisionStyle = Proj_Revstyles.Id

                                AND ((@TestRev IS NOT NULL) AND (@TestRev LIKE Proj_Revstyles.Tag))

                

Open in new window

0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 150 total points
ID: 24821247
btw, can try removing "RTrim" function? it may give you good boost up, rather you can use something like this:

set @TestRev = rtrim('D')

there may be few more reason of low speed, like not having appropriate index, not having updated states but this is just a first look.
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 300 total points
ID: 24821668
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 50 total points
ID: 24836581
Try...

WHERE (col1 = ISNULL(@col1, col1))  AND
(col2 = ISNULL(@col2, col2) ) AND...
0
 
LVL 32

Author Closing Comment

by:Robberbaron (robr)
ID: 31601974
none made any major improvement though the query may well be impacted by the parameter sniffing issue so have impleneted that workaround and added an index to assist with another process.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now