OK how to optimize restrictions in the WHERE claus

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 2005 Forums

Transact-SQL (2005)

OK how to optimize restrictions in the WHERE claus

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-04-06 : 17:22:38

CREATE VIEW PRQQueryView_1 AS

SELECT

s.AgencyID AS AgencyID,

s.PersonID AS PersonID,

s.EntryLevelCode AS EntryLevelCode,

s.DateFirstIntake AS IntakeDate,

dbo.fnGetFYAge(d.BirthDate, dbo.fnFyStartdate(s.AgencyID, GetDate()), s.DateFirstIntake) AS age,

oe.ShortDescription AS Ethnicity,

oe.OptionCode AS EthnicityCode,

og.ShortDescription AS Gender

FROM dbo.student s

INNER JOIN dbo.Demographic d ON (s.PersonID = d.PersonID)

INNER JOIN dbo.Program p ON (s.ProgramID = p.ProgramID)

LEFT OUTER JOIN dbo.OT_Gender og ON (d.GenderCode = og.OptionCode AND d.AgencyID = og.AgencyID)

LEFT OUTER JOIN dbo.OT_Ethnicity oe ON d.PrimaryEthnicityCode = oe.OptionCode AND d.AgencyID = oe.AgencyID

JOIN ReportOnSelectionTempID on (s.PersonID = ReportOnSelectionTempID.ID)

JOIN TempAgency ON (s.AgencyID = TempAgency.AgencyID)

WHERE EntryLevelCode NOT IN (1, 114, 107, 109, 120) AND CurFYInstructHours >= 12 AND d.GenderCode > 1 AND

d.PrimaryEthnicityCode > 1 AND dbo.fnGetFYAge(d.BirthDate, dbo.fnFyStartdate(s.AgencyID, GetDate()), s.DateFirstIntake)>=16

AND (s.LevelCode <> 114 AND s.LevelCode > 1) AND

s.ProgramID NOT IN

(

SELECT ProgramID

From Program

WHERE AgencyID = s.AgencyID AND ProgramTypeCode=401

) /** End of s.ProgramID difference operation **/

AND

s.PersonID NOT IN

(

SELECT PersonID

FROM goals

WHERE goalcode = 105 AND goalstatuscode IN (101, 102)

AND DateSet between dbo.fnFYStartDate(s.AgencyID, GetDate())

AND dbo.fnFYEndDate(dbo.fnFYStartDate(s.AgencyID, GetDate()))

) /** End of s.PersonID difference operation **/

AND

s.PersonID IN

(

SELECT a.PersonID --,c.DataValue AS CurrentFY, dbo.OT_FiscalYear.ShortDescription AS FiscalYear

FROM Assessment a

INNER JOIN TempAgency ON (a.AgencyID = TempAgency.AgencyID)

INNER JOIN Configuration c ON (TempAgency.AgencyID = c.AgencyID)

INNER JOIN dbo.OT_FiscalYear ON (a.FiscalYearCode = dbo.OT_FiscalYear.OptionCode AND

a.AgencyID = dbo.OT_FiscalYear.AgencyID)

WHERE (c.ConfigurationName = 'CurrentFY' AND DataValue = ShortDescription)

)/*End fiscal year check*/

) /*End AND in where clause */

Would something like this be better suited in a stored procedure with temp tables? I'm running this on about 44,000 records and it seems to be taking about 24 seconds to execute with a whole lot of logical reads.....anything I can do to up performance?
--Nick

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-06 : 17:49:47

I am not seeing a definitive reason for creating view, but I do not fully understand how often or how this information is goin to be used, so I may be wrong. I wrote this as a stored procedure because it made the most sense for this type of query. I wrote it the way I would write the query, however I can not see the code behind your functions, so it is very possible that that code can slow down the performance time if it is not correct. Also make sure all your index's are accurate for each table. Other then that, it looks as if it is written correctly. The unfortuanant fact is you have a ton of conditions in this statment and also a ton of joins and subqueries. You may find that your query is not going to imporove dramatically unless you standardize the data. Let me know if this query runs any better. Also a poorly written function can dramatically slow down the performance of a query so verify those are all accurate.

[code]
Create Procedure Sproc_GetMyRecords
as
SELECT
s.AgencyID AS AgencyID,
s.PersonID AS PersonID,
s.EntryLevelCode AS EntryLevelCode,
s.DateFirstIntake AS IntakeDate,
dbo.fnGetFYAge(d.BirthDate, dbo.fnFyStartdate(s.AgencyID, GetDate()), s.DateFirstIntake) AS age,
oe.ShortDescription AS Ethnicity,
oe.OptionCode AS EthnicityCode,
og.ShortDescription AS Gender
FROM dbo.student s
INNER JOIN dbo.Demographic d ON (s.PersonID = d.PersonID)
INNER JOIN dbo.Program p ON (s.ProgramID = p.ProgramID)
LEFT OUTER JOIN dbo.OT_Gender og ON (d.GenderCode = og.OptionCode AND d.AgencyID = og.AgencyID)
LEFT OUTER JOIN dbo.OT_Ethnicity oe ON d.PrimaryEthnicityCode = oe.OptionCode AND d.AgencyID = oe.AgencyID
JOIN ReportOnSelectionTempID on (s.PersonID = ReportOnSelectionTempID.ID)
JOIN TempAgency ON (s.AgencyID = TempAgency.AgencyID)
WHERE EntryLevelCode NOT IN (1, 114, 107, 109, 120)
AND CurFYInstructHours >= 12
AND d.GenderCode > 1
AND d.PrimaryEthnicityCode > 1
AND dbo.fnGetFYAge(d.BirthDate, dbo.fnFyStartdate(s.AgencyID, GetDate()), s.DateFirstIntake)>=16
AND (s.LevelCode <> 114 AND s.LevelCode > 1)

AND NOT exists (
SELECT *
From Program aa
WHERE aa.AgencyID = s.AgencyID AND
aa.ProgramTypeCode=401 and
aa.ProgramID = s.programID
) /** End of s.ProgramID difference operation **/

AND NOT exists
(
SELECT *
FROM goals aa
WHERE aa.goalcode = 105 AND aa.goalstatuscode IN (101, 102)
AND aa.DateSet between dbo.fnFYStartDate(s.AgencyID, GetDate())
AND dbo.fnFYEndDate(dbo.fnFYStartDate(s.AgencyID, GetDate()))
and aa.PersonID = s.PersonID
) /** End of s.PersonID difference operation **/

AND exists
(
SELECT * --,c.DataValue AS CurrentFY, dbo.OT_FiscalYear.ShortDescription AS FiscalYear
FROM Assessment aa
INNER JOIN TempAgency bb ON (aa.AgencyID = bb.AgencyID)
INNER JOIN Configuration cc ON (bb.AgencyID = cc.AgencyID)
INNER JOIN dbo.OT_FiscalYear dd ON (aa.FiscalYearCode = dd.OptionCode AND
aa.AgencyID = dd.AgencyID)
WHERE (c.ConfigurationName = 'CurrentFY'
AND DataValue = ShortDescription
and aa.PersonID = s.PersonID)
)/*End fiscal year check*/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-06 : 18:02:18

I just looked through your query, you can also likley not need the Exist statments by setting the conditions off the original tables in the view. This will spead things up as well.

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-04-06 : 18:51:42

Awesome, took out the exists things like you said and it looks to be running my faster. Thanks.
--Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources