Query optimization help, please.

Below are two versions of a SQL query. The first is the original query, which was not properly returning all of the required records. I modified this query to be what you see in the second SQL statement below. The problem I'm having now is the extremely long query time for this to execute (it keeps timing out or will take at least 20-30 seconds) from what originally took only a second.

The problem is obviously in where I added the "AND CONTENTID IN (SELECT DISTINCT ContentID from Contents_view WHERE ApplicationID = @.ApplicationID)" to the first portion of the WHERE statements (UserID = @.UserID); as it now has to search the associated view twice for each element instead of once.

Thanks in advance for any advice you can provide.

Original Query (did not pull back the proper results, was missing the user specific records):

CREATE PROCEDURE GetContents
(
@.UserID int,
@.Perms nvarchar(1000) = null,
@.UserGroup nvarchar(100) = null,
@.ApplicationID int
)
AS
SELECT DISTINCT * FROM Contents_view
WHERE (UserID = @.UserID OR (CHARINDEX('''' + PermissionName + '''',@.Perms)>0 ))
AND ContentID IN (SELECT DISTINCT ContentID from Contents_view
 WHERE PermissionName = @.UserGroup
 AND ApplicationID = @.ApplicationID)
ORDER BY Rank ASC

Modified Query (properly pulls back all results and user specific records):

CREATE PROCEDURE GetContents
(
@.UserID int,
@.Perms nvarchar(1000) = null,
@.UserGroup nvarchar(100) = null,
@.ApplicationID int
)
AS
SELECT DISTINCT * FROM Contents_view
WHERE ((UserID = @.UserID AND CONTENTID IN (SELECT DISTINCT ContentID from Contents_view
WHERE ApplicationID = @.ApplicationID))
OR ((CHARINDEX('''' + PermissionName + '''',@.Brands)>0 ))
AND ContentID IN (SELECT DISTINCT ContentID from Contents_view
 WHERE PermissionName = @.UserGroup
 AND ApplicationID = @.ApplicationID)
 ORDER BY Rank ASC

Not really having any idea exactly what you are doing, a guess is that the use of CHARINDEX() in your WHERE clause of your subquery is what is slowing you down. THis will force a table scan, since no index will help. Where is @.Brands coming from?

You should reqork the database if possible so that there is a direct way to determine what you are trying to determine. I presume you have a field with permission names comma delimited or similar?|||The @.Brands should actually be @.Perms, like in the first query.

What is happening, is there is a view that combines the related data of two seperate tables into one Contents_view that contains each content record in the database with all of its pertinent information.

The query itself is pulling back the content for a particilar page based on two things.
1) If the supplied userID and applicationID are present in a single record
2) If the usergroup and associated permissions are present in a single record

CHARINDEX() is searching a comma delimited permissions string that is passed to the SPROC.|||ok. Split up the string passed in, create either a temp table (SQL Server 7) or a user defined function that returns a table object (SQL 2000). Then do a join with the temp table or table object. Then you will not need CHARINDEX() which is being evaluated on each and every record in the table.|||Well ... How many rows are returned would have been a very good indicator ...

1. Since you are using an IN clause you DONOT have to use the DISTINCT in the inner co-related sub query ... These are not required.

Change the above query to:

SELECT DISTINCT * FROM Contents_view
WHERE ((UserID = @.UserID AND CONTENTID IN (SELECT ContentID from Contents_view
WHERE (ApplicationID = @.ApplicationID) or (PermissionName = @.UserGroup and ApplicationID = @.ApplicationID))
OR ((CHARINDEX('''' + PermissionName + '''',@.Brands)>0 ))
ORDER BY Rank ASC

PS: Not tested. See if this gives you the desired results and performance ?

Note that use of string operations also consume valuable CPU cycles and hence hamper performance ...