Hi! Let me first start by saying I am new to writing code on this site and have had issues formatting code below, sorry. Also feel free to judge/critique me openly as long as you provide insight on how to improve my approach. Thanks!
I have a rather large result set that I am trying to filter as well as page, all in the database (Sql server 2008) from my asp.net application. There are about 10 filters and 10 sorting parameters, some of which sort by other table lookups (ID references a table field like display_text and sorts by display_text alphabetically). Feel free to correct me but I believe that I don't have the option to efficiently use entity framework as I am selecting from two entirely different database that are joined via a linked server.
I have it working but it is running slow. I believe the problem is that I am creating a stored procedure with multiple case statements as well as a temp table. When I take out the case statements it changes from about 12 seconds to about 1, but then I would have to create 1 sp for each case. Or should I create inline sql that rebuilds the sql depending on parameters.
I feel my execution plan changes drastically depending on what filters are passed in. What is the best way to handle this situation? Below is the slow approach I tried. I took out some of the fields/cases for simplicity
@currPage int, @pagePize int, @reverse_sort bit, @orderByField varchar(255), @likeTicketId varchar(255), ... more parameters ... WITH UnpagedResult AS (SELECT ROW_NUMBER() OVER ( Order BY CASE WHEN @orderByField = 'ticket_id' AND @reverse_sort = 0 THEN tkt.ticket_id END ASC , CASE WHEN @orderByField = 'ticket_id' AND @reverse_sort = 1 THEN tkt.ticket_id END DESC , CASE WHEN @orderByField = 'request_type_id' AND @reverse_sort = 0 THEN rtl.display_text END ASC , CASE WHEN @orderByField = 'request_type_id' AND @reverse_sort = 1 THEN rtl.display_text END DESC , CASE WHEN @orderByField = 'application_id' AND @reverse_sort = 0 THEN app.app_name END ASC , CASE WHEN @orderByField = 'application_id' AND @reverse_sort = 1 THEN app.app_name END DESC , ... more case statements ... CASE WHEN @orderByField = 'project_status_id' AND @reverse_sort = 1 THEN pro.[status_id] END DESC ) As RowNum ,pro.[project_id] ,pro.[status_id] as project_status_id ,pro.[bus_unit_priority_id] ,rjpd.name_first as user_assigned_first_name ,rjpd.name_last as user_assigned_last_name ,tkt.[ticket_id] ,tkt.[request_type_id] ,tkt.[requester_person_id] ...more selects... ,rjp.internet_add as requester_internet_add FROM [tsc].[ticket] tkt WITH(NOLOCK) LEFT JOIN [pp].[project] pro on tkt.ticket_id = pro.ticket_id LEFT JOIN [tsc].[request_type_lku] rtl ON rtl.[request_type_id] = tkt.[request_type_id] LEFT JOIN [dbo].[app_lku] app ON app.[app_id] = tkt.[application_id] LEFT JOIN [tsc].[status_lku] sts ON sts.[status_id] = tkt.[status_id] LEFT JOIN LINKEDSERVERNAME.dbo.[person] rjp ON rjp.person_id = tkt.[requester_person_id] LEFT JOIN LINKEDSERVERNAME.dbo.[person] rjpd ON rjpd.person_id = tkt.[user_assigned_person_id] WHERE (@likeTicketId IS NULL OR CAST(tkt.[ticket_id] AS varchar(25)) LIKE '%' + @likeTicketId + '%') AND (@exactStatusId IS NULL OR tkt.[status_id] = @exactStatusId) AND (@exactApplicationId IS NULL OR tkt.[application_id] = @exactApplicationId ) AND (@exactUserAssignedPersonId IS NULL OR tkt.[user_assigned_person_id] = @exactUserAssignedPersonId ) AND (@exactTicketRequestTypeId IS NULL OR tkt.[request_type_id] = @exactTicketRequestTypeId ) AND (@excludeUserAssignedPersonId IS NULL OR tkt.[user_assigned_person_id] <> @excludeUserAssignedPersonId ) AND (@likeDepartmentNum IS NULL OR rjp.branch LIKE '%' + @likeDepartmentNum + '%') AND ((@likeRequestedByFirst IS NULL AND @likeRequestedByLast IS NULL) OR (@likeRequestedByLast IS NULL `enter code here` AND (rjp.name_first LIKE '%' + @likeRequestedByFirst + '%' OR rjp.name_informal LIKE '%' + @likeRequestedByFirst + '%' OR rjp.name_last LIKE '%' + @likeRequestedByFirst + '%')) OR (rjp.name_Last LIKE '%' + @likeRequestedByLast + '%' AND (rjp.name_first LIKE '%' + @likeRequestedByFirst + '%' OR rjp.name_informal LIKE '%' + @likeRequestedByFirst + '%'))) SELECT * from UnpagedResult WHERE RowNum BETWEEN (@pagePize * (@currPage - 1) + 1) AND (@pagePize * @currPage)