Trying to Optimize query - 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 2000 Forums

Transact-SQL (2000)

Trying to Optimize query

Author

Topic

mfemenel
Professor Frink

1421 Posts

Posted - 2004-01-09 : 15:23:55

I'm trying to performance tune a query and could use some suggestions. First, a few ground rules, I can't change anything in the tables, indexes, structure etc. Can't touch. ALL I can do is try to write this thing better. Here's an example of what an assignmentnumber looks like '001902-01-000943-3DD' and here's the code. I thought about using a left(assignmentnumber,6) instead of like, but that doesn't seem to help at all. I'm convinced that last part is the pig but I'm out of ideas on how to make it better.

SELECT a.AssignmentID,
a.AssignmentNumber,
s.ClientSiteNumber,
p.PhaseName,
sh.QuestionnaireID
FROM MShop..tblAFAssignment a (NOLOCK)
inner join MShop..tblPMSite s (NOLOCK) on a.companyid=s.companyid and a.clientid=s.clientid and a.siteid=s.siteid
inner join MShop..tblPMPhase p (NOLOCK) on a.companyid=p.companyid and a.clientid=p.clientid and a.phaseid=p.phaseid
inner join MShop..tblPmShop sh (NOLOCK) on a.shopid=sh.shopid

WHERE a.ClientID = 84
AND a.AssignmentStatusID = 5
AND a.iQueryPosted IS NULL
AND (
(a.AssignmentNumber LIKE '001744%'
AND p.PhaseName NOT LIKE '9%')
OR
(a.AssignmentNumber LIKE '001775%'
AND p.PhaseName NOT IN ('01','02'))
OR
(a.AssignmentNumber LIKE '00190[23]%'))

Mike
"oh, that monkey is going to pay"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-09 : 15:31:03

With those LIKE statements you might be better off running this into a temp table for the fragment of AssignmentNumber that's relevant to the query, then index the temp table, and run a better query from that.

Jonathan
codesmith templates

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-09 : 15:42:28

I don't know...

what does this do?


SELECT * INTO #TEMP FROM MShop..tblAFAssignment a
WHERE 	  a.ClientID = 84
  AND a.AssignmentStatusID = 5
  AND a.iQueryPosted IS NULL
  AND (	(      a.AssignmentNumber LIKE '001744%'
	   AND p.PhaseName NOT LIKE '9%')
	OR 
	(      a.AssignmentNumber LIKE '001775%'
	   AND p.PhaseName NOT IN ('01','02')
	)
	OR
	(       a.AssignmentNumber LIKE '00190[23]%'
	)
     )


     SELECT  	, a.AssignmentID,
		, a.AssignmentNumber,
		, s.ClientSiteNumber,
		, p.PhaseName,
		, sh.QuestionnaireID
      FROM 	  #TEMP a
INNER JOIN 	  MShop..tblPMSite s  
	ON	  a.companyid=s.companyid and a.clientid=s.clientid and a.siteid=s.siteid
INNER JOIN	  MShop..tblPMPhase p
	ON	  a.companyid=p.companyid and a.clientid=p.clientid and a.phaseid=p.phaseid
INNER JOIN 	  MShop..tblPmShop sh 
	on 	  a.shopid=sh.shopid

You can add indexes to #TEMP, but I don't think itll matter because it'll be the driver and get scanned anyway...

EDIT: What does sp_help tell you about table "a"

Brett

8-)

Lewie
Starting Member

42 Posts

Posted - 2004-01-12 : 05:08:05

If you have an index on a.ClientID, a.AssignmentStatusIDand and a.iQueryPosted then your query could be.

SELECT z.AssignmentID,
z.AssignmentNumber,
s.ClientSiteNumber,
p.PhaseName,
sh.QuestionnaireID
FROM (

SELECTAssignmentID,
AssignmentNumber,
ClientID,
Siteid,
companyid
FROM MShop..tblAFAssignment (NOLOCK)
WHERE ClientID = 84
AND AssignmentStatusID = 5
AND iQueryPosted IS NULL) z

inner join MShop..tblPMSite s (NOLOCK) on z.companyid=s.companyid and z.clientid=s.clientid and z.siteid=s.siteid
inner join MShop..tblPMPhase p (NOLOCK) on z.companyid=p.companyid and z.clientid=p.clientid and z.phaseid=p.phaseid
inner join MShop..tblPmShop sh (NOLOCK) on z.shopid=sh.shopid
WHERE (a.AssignmentNumber LIKE '001744%'AND p.PhaseName NOT LIKE 9%')
OR (a.AssignmentNumber LIKE '001775%' AND p.PhaseName NOT IN ('01','02'))
OR (a.AssignmentNumber LIKE '00190[23]%')

In other words, what ever you have indexes on goes into you derived table.
This might help but it really depends on what your current index’s are.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources