Optimizing query

Question

Optimizing query

kenksoftware

SSC Enthusiast

Points: 113
More actions
July 21, 2008 at 1:05 pm

#189793

I have just gotten back from vacation so my brain is not fully back into work mode yet and I came across the following stored procedure. My concern is that the inner Select might be slower than just adding the tblUsers table to the query. Can someone explain if the inner select will cause a full table scan and wouldn't it be faster to just include the table in the main query? I am pretty sure that it is but I need to know the actual reason it is bad.
Select distinct tblReportCategories.CategoryID, Category
from tblReports, tblUsersReports, tbl1
where tblUsersReports.UserId = (select UserId from tblUsers where UserName = @userid )
and tblUsersReports.ReportId = tblReports.ReportId
and tblReports.CategoryID = tblReportCategories.CategoryID

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I actually just re-read an article today that recommends using JOINS instead of subqueries. Here is the link, http://www.builderau.com.au/program/sqlserver/soa/Optimise-SQL-Server-queries-with-these-advanced-tuning-techniques/0,339028455,339283995,00.htm

You can actually eliminate the reference to the tblUsers table altogether and just do:

[font="Courier New"]SELECT DISTINCT

TBLREPORTCATEGORIES.CATEGORYID,

CATEGORY

FROM

TBLREPORTS TR JOIN

TBLUSERSREPORTS TUR ON

TR.ReportID = TUR.ReportId JOIN

TBLREPORTCATEGORIES TRC

TR.CategoryID = TRC.CategoryID

WHERE

TBLUSERSREPORTS.USERID = @USERID [/font]

I also converted it to use ANSI standard join syntax as it is easier to read (IMHO) and is not going to go away like the old syntax is.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Using a sub-query like this will not always cause an unnecessary scan, but using a join give the optimizer more choices and it will often generate a better execution plan.

kenksoftware SSC Enthusiast Points: 113 More actions · Answer 3

I guess I mainly don't like it because it looks ugly. Finding the right words to verbalize that stuff like this irritates me is harder than just fixing it.

Thanks for the help.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jack Corbett (7/21/2008)
You can actually eliminate the reference to the tblUsers table altogether and just do:

Actually, Jack, [font="Courier New"]@UserID[/font] matches to [font="Courier New"]UserName[/font], not [font="Courier New"]UserID[/font].

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Duh! That's what I get for ASSuming that the variable name and column names are consistent.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Yes, what were you thinking that @user-id = UserID? 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

kenksoftware SSC Enthusiast Points: 113 More actions · Answer 7

So you can see right off that I have my work cut out for me. So step 1 should be for me to convince them to store the userid in the userid cookie on the website instead of storing the username in the userid variable and then none of the queries would require accessing the tblUsers table.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

kenksoftware (7/22/2008)
So you can see right off that I have my work cut out for me. So step 1 should be for me to convince them to store the userid in the userid cookie on the website instead of storing the username in the userid variable and then none of the queries would require accessing the tblUsers table.

That would be nice, but the first thing is to name variables what they are and that one is UserName so should be @UserName. Short of getting them to use the actual userid the join is still preferred ove rthe subquery.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question