• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1223
  • Last Modified:

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

0
lmred
Asked:
lmred
  • 10
  • 6
  • 3
2 Solutions
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 10
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now