SQL stored procedure for fruit checks with several joins

Question

I developed a SQL stored procedure which works lovely, but when under stress, it takes more than 2 minute to get back to the user. I am providing the stored procedure but would also provide the table structure if required.

/* Stored procedure to get fruits checks that the user has access to*/
ALTER procedure [dbo].[Fruits_GetChecksForUser]
(
    @UserID VARCHAR(200),
    @CheckUrlFilter varchar(256)
)
AS

SELECT Distinct
    Fruits_Checks.*,
    ManagementCommittees.Title As CommitteeName,
    FruitCategories.ID As CategoryID,
    FruitCategories.Title As CategoryName,
    dbo.IsUserInGroup(@UserID, FruitCategories.FruitOfficerGroupID) AS IsUserFruitOfficer,
    dbo.IsUserInGroup(@UserID, Fruits_Checks.AllocatedGroup) AS IsUserCheckResponsible,
    Policies.CompanyID,
    Policies.ID As PolicyID

FROM Groups_UserAccess
INNER JOIN FruitCategories_GroupAccess ON FruitCategories_GroupAccess.GroupID = Groups_UserAccess.GroupID
INNER JOIN FruitCategories ON FruitCategories.ID = FruitCategories_GroupAccess.FruitCategoryID
INNER JOIN Policies ON Policies.ID = FruitCategories.PolicyID
LEFT JOIN Policies_AppSettings ON Policies_AppSettings.PolicyID = Policies.ID
LEFT JOIN Clients_AppSettings ON Clients_AppSettings.ID = Policies.CompanyID
FULL OUTER JOIN Fruits ON Fruits.CategoryID = FruitCategories.ID
INNER JOIN Fruits_Checks ON Fruits_Checks.FruitID = Fruits.ID
INNER JOIN ManagementCommittees on Fruits_Checks.CommitteeID = ManagementCommittees.ID

WHERE 
    Fruits.ID IS NOT NULL 
    AND Fruits_Checks.URL LIKE '%' + @CheckUrlFilter + '%' 

ORDER BY Fruits_Checks.EndDate

Table structure seems obvious from the query. What indices exist on the tables, though, remain unknown and might influence the performance dramatically. — choroba
– choroba, Commented Dec 2, 2014 at 10:16
okay, let me put them all together and also if I could find somewhere I could post them too. — Mathematics
– Mathematics, Commented Dec 2, 2014 at 10:17
UserID is a varchar. Why? How many other "ID" columns are also varchar? — nhgrif
– nhgrif, Commented Dec 2, 2014 at 11:35
Get a SQL Server Execution Plan before attempting to diagnose performance issues. — Justin
– Justin, Commented Dec 2, 2014 at 13:17

RubberDuck · Accepted Answer · 2014-12-10 13:03:41Z

There are a couple of things slowing you down.

Two Left Outer Joins and a Full Outer Join.

There may not be much you can do about this unless you can trim down the fields that need to be returned. Start with trying to remove the Full Outer Join. It will have worse performance than the Left Outer Joins.
Scanning an entire field will also be a bit on the slow side, but again, there may be little you can do beyond making sure there are proper indices on the table if this is truly a requirement.
```
LIKE '%' + @CheckUrlFilter + '%' 
```
It looks very much like this is a requirement.
It's poor practice to select all fields from a table using the table.* notation. Doing this will force the RDBMS to lookup the table's meta data. I've found that it's pretty rare that I'll need every column from a table. It's best to be explicit about what you're selecting, even if you're selecting everything. Any performance improvement from this will be marginal, but it has the benefit of protecting your code from breaking if the underlying table changes.

I don't know enough about the data or requirements to give any real concrete advice, but nixing that Full Outer Join is the best bet for getting a performance boost. Looking at your query carefully, it doesn't seem that you ever select from Fruits so you should be able to replace the Null check on the ID with a Where Exists clause and get rid of the Full Outer Join altogether. Something like...

WHERE EXISTS(
        SELECT FRUITS.ID
        FROM FRUITS
        WHERE Fruits.CategoryID = FruitCategories.ID
)

Or if Fruits.ID is non-null, WHERE FruitCategories.ID in (SELECT CategoryID FROM Fruits). But then how does he join Fruit_Checks? — nhgrif
– nhgrif, Commented Dec 2, 2014 at 11:34
Sometimes I overlook the simplest solutions. That's a very good call @nhgrif. — RubberDuck
– RubberDuck, Commented Dec 2, 2014 at 11:50

Stack Exchange Network

SQL stored procedure for fruit checks with several joins

1 Answer 1

You must log in to answer this question.

Hot Network Questions

SQL stored procedure for fruit checks with several joins

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions