Optimizing a left outer join SQl statement

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

This SQL statement is inside a stored procedure. I pass a parameter which is what is appended to it. And run the executesql statement.

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.

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.

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.

Hey Scott! You are always 'out there'!

Riskgroupadmin - 371 rows
appuser - 698
riskitemgroup - 4

There are no indexes on the tables involved.

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

Whoops! The appuser table has an index on the primary key which is userid.

Yeah. That's the TOTAL count on each table.

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.

>> Yeah. That's the TOTAL count on each table. <<

Cripe and yikes ... no way tables that small should cause you a big performance hit.

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?

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.

The fill on the dataadapter went down to .390 and it was .409. I made it a nonclustered index. Is that correct?

I took the executesql out as recommended by Jim and got the fill down to .383.

If the table doesn't already have a clus index, clus will perform better.

Thanks for your help guys.