Optimize Query With Table Scans 1

[jtrapat1]

I need help with rewriting a query.
I'm using SQL Server with crystal reports 8.

I ran my query thru sql query analyzer and checked the Estimated Execution Plan; I also got advice from some contributors to tek-tips.
My query is not optimized because of the number of Table Scans it is performing.

Particularly, it's taking a long time to process this WHERE clause:

WHERE ((BillAssignments.BillNum IS NOT NULL AND
Requests.BillNum IS NOT NULL) OR
(Bill.Sponsor <> 'Budget' AND (Bill.Bill99Code = 'Y' OR
(Bill.InWAM = 'Y' AND (Bill.BAMCode IN ('R', 'X', 'U')))) AND
CommitteeActions.ActionCode IS NULL))
ORDER BY Bill.BillNum, BillAssignments.Analyst

Is there some way I can rewrite this and maximize performance?

I thought I could take out the first two NOT NULL statements because I looked in the database and both of these fields had Allow Nulls unchecked. I thought this meant that this field could never be NULL.

But when I went back to run the query, I got back about half as many records: about 600.

Can someone explain this or suggest alternatives to checking for IS NOT NULL or IS NULL?

Thanks in advance.

 

[tlbroadbent]

It is not easy to optimize the Where clause alone. We need to see the remainder of the query to understand relationships between tables.

Checking for Nulls is required if the query has OUTER joins because Null indicates no match in the Join rather Null in the column. Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[jtrapat1]

Thanks for the quick response.

Here's the full query:

SELECT dbo.Bill.BillNum, dbo.Bill.Sponsor,
dbo.Bill.Title, dbo.Bill.OriginalCommittee,
dbo.Bill.CommitteeDate, dbo.Bill.BAMCode,
dbo.Bill.BillMemoCode, dbo.CommitteeActions.ActionCode,
dbo.BillAssignments.Analyst, dbo.Bill.Bill99Code,
dbo.Bill.SenateNum, dbo.Requests.Requester,
dbo.Requests.Comments
FROM dbo.Bill INNER JOIN
dbo.BillAssignments ON
dbo.Bill.BillNum = dbo.BillAssignments.BillNum LEFT JOIN
dbo.Requests ON
dbo.Bill.BillNum = dbo.Requests.BillNum LEFT JOIN
dbo.CommitteeActions ON
dbo.Bill.BillNum = dbo.CommitteeActions.BillNum LEFT JOIN
dbo.BillComment ON
dbo.Bill.BillNum = dbo.BillComment.BillNum
WHERE (dbo.BillAssignments.BillNum IS NOT NULL) AND
(dbo.Requests.BillNum IS NOT NULL) OR
(dbo.Bill.Sponsor <> 'Budget') AND (dbo.Bill.Bill99Code = 'Y')
AND (dbo.CommitteeActions.ActionCode IS NULL) OR
(dbo.Bill.Sponsor <> 'Budget') AND
(dbo.CommitteeActions.ActionCode IS NULL) AND
(dbo.Bill.InWAM = 'Y') AND (dbo.Bill.BAMCode IN ('R', 'X', 'U'))

 

[redlam]

not sure if this is your entire issue but it has been documented that negative operators, i.e. 'NOT IN' and '<>' will cause SQL to ignore some (if not all) table indexes - this 'could' be contributing to your problem. is it possible to rewrite this, reversing some of the negative operators?

 

[tlbroadbent]

redlam makes a good point.

It appears that the Where clause needs to be refined. I'm not sure how the various criteria need to be grouped but have made a guess. You'll need to adjust for my lack of knowledge.

[tt]WHERE (dbo.BillAssignments.BillNum IS NOT NULL
AND dbo.Requests.BillNum IS NOT NULL)
OR (dbo.Bill.Sponsor <> 'Budget'
AND dbo.Bill.Bill99Code = 'Y'
AND dbo.CommitteeActions.ActionCode IS NULL)
OR (dbo.Bill.Sponsor <> 'Budget')
AND dbo.CommitteeActions.ActionCode IS NULL
AND dbo.Bill.InWAM = 'Y'
AND dbo.Bill.BAMCode IN ('R', 'X', 'U')) [/tt]

Most of the parentheses on the original were unnecessary and made the query harder to read. By removing the parentheses and aligning the ANDs and ORs with the Where clause I got a clearer picture of the criteria. I've added parentheses to indicate groupings. This may not improve performance. I don't know if the query parser will handle the criteria any differently with the revised query. Try it and let me know what happens.

If the query still performs poorly, post the indexed columns. That may provide enough info to make another wild guess! X-) Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[redlam]

not sure if you've been here yet but you might check out some of these articles in microsoft's knowledge base:
Q243589
Q110352
both have some interesting suggestions on what to look for in slow queries.
good luck!