Optimizing a left outer join SQl statement

I am using SQL Server 2000. I have a SQL statement in a stored procedure  that was created by another developer. Through SQL Profiler, I am finding that is killing performance. How can I modify it to make it a more efficient query and get the same results?
set @SQL = 'SELECT riskItemGroup.itemID,riskItemGroup.groupID,riskGroupAdmin.userID,appUser.eMail,appUser.lName,appUser.fName FROM appUser INNER JOIN riskGroupAdmin ON appUser.userID=riskGroupAdmin.userID LEFT OUTER JOIN riskItemGroup ON riskGroupAdmin.groupID=riskItemGroup.groupID WHERE riskItemGroup.itemID= ' + cast(@itemID as varchar(10))

Open in new window

LVL 2
lmredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimFiveCommented:
First:  Since your WHERE clause is referencing riskItemGroup.itemID it doesn't do any good to use a left join, you can use an inner join to the same effect.

Secondly:  It looks like this is being built as Dynamic SQL but I see no reason to do it that way, you can either use a Table Valued Function or a stored procedure depending on what you really need.

Third: Since this looks like dynamic sql, the Cast(@itemID as varchar(10)) isn't doing any good since you are then attaching it directly to string without quoting it.  If riskItemGroup.itemID is varchar(10) then you need to add single quotes or you are doing the conversion twice.

Fourth:  There doesn't seem to be anything terribly wrong with the statement.  Make sure you have reasonable indexes on the tables involved and your performance should be ok.

Finally:  You might try changing your from clause to:

FROM appUser
      INNER JOIN (riskGroupAdmin
                               LEFT OUTER JOIN riskItemGroup
                             ON riskGroupAdmin.groupID=riskItemGroup.groupID)
            ON appUser.userID=riskGroupAdmin.userID

but no guarantees.

--
JimFive
0
lmredAuthor Commented:
This SQL statement is inside a stored procedure. I pass a parameter which is what is appended to it. And run the executesql statement.
0
JimFiveCommented:
And then what?

Why not just:
SELECT riskItemGroup.itemID,riskItemGroup.groupID,riskGroupAdmin.userID,appUser.eMail,appUser.lName,appUser.fName FROM appUser INNER JOIN riskGroupAdmin ON appUser.userID=riskGroupAdmin.userID LEFT OUTER JOIN riskItemGroup ON riskGroupAdmin.groupID=riskItemGroup.groupID WHERE riskItemGroup.itemID= @itemID

without doing an executesql
 

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lmredAuthor Commented:
I thought using the executesql makes it faster the next time it has to be called because it uses a execution plan or something since only the parameter is different. Is that incorrect? This stored procedure is being called from a website to display information on a webpage.
0
Scott PletcherSenior DBACommented:
SELECT riskItemGroup.itemID,riskItemGroup.groupID,
    riskGroupAdmin.userID,
    appUser.eMail,appUser.lName,appUser.fName
FROM riskItemGroup
INNER JOIN riskGroupAdmin ON riskGroupAdmin.groupID=riskItemGroup.groupID
INNER JOIN appUser ON appUser.userID=riskGroupAdmin.userID
WHERE riskItemGroup.itemID= @itemID


How many rows are in each table?
What index(es) exist on each table?

For tables of any real size, you need an index to do joins efficiently.
0
JimFiveCommented:
Using executesql in this case just adds another layer into the process.  ALL SP's get an execution plan, and only the parameter is different anyway.
0
lmredAuthor Commented:
Hey Scott! You are always 'out there'!

Riskgroupadmin - 371 rows
appuser - 698
riskitemgroup - 4

There are no indexes on the tables involved.
0
Scott PletcherSenior DBACommented:
That's the *grand total* rows on those tables?  If so, an index really shouldn't be needed.

If the original tables are much larger and that's just the number of rows that match, then try creating indexes on:

riskGroupAdmin.groupID
appUser.userID
0
lmredAuthor Commented:
Whoops! The appuser table has an index on the primary key which is userid.
0
lmredAuthor Commented:
Yeah. That's the TOTAL count on each table.
0
Scott PletcherSenior DBACommented:
Sweet.  And that seems to be the largest table.

That's odd, though, since that statement should then not really hurt your performance that much.
0
Scott PletcherSenior DBACommented:
>> Yeah. That's the TOTAL count on each table. <<

Cripe and yikes ... no way tables that small should cause you a big performance hit.
0
lmredAuthor Commented:
I am also using Ants Profiler to help find bottlenecks. When I call this stored procedure I use a DataAdapter and fill it. The call to fill it (according to ANTS) takes .409 secs. IN SQL Profiler, the duration for that stored procedure is 31. I don't know if that's milliseconds or what. What's going on?
0
lmredAuthor Commented:
The Execution Plan in Query Analyzer has 41% on one table scan, 36% on another table scan 17% on Hash/Match Inner Join and 6% on appuser.userid primary key.
0
Scott PletcherSenior DBACommented:
Add this index:

riskGroupAdmin.groupID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lmredAuthor Commented:
The fill on the dataadapter went down to .390 and it was .409. I made it a nonclustered index. Is that correct?
0
lmredAuthor Commented:
I took the executesql out as recommended by Jim and got the fill down to .383.
0
Scott PletcherSenior DBACommented:
If the table doesn't already have a clus index, clus will perform better.
0
lmredAuthor Commented:
Thanks for your help guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.