Poor SP performance from ASP.NET

I have a stored procedure that handles sorting, filtering and paging (using Row_Number) and some funky trickery :) The SP is running against a table with ~140k rows.

The whole开发者_如何学C thing works great and for at least the first few dozen pages is super quick. However, if I try to navigate to higher pages (e.g. head to the last page of 10k) the whole thing comes to a grinding halt and results in a SQL timeout error.

If I run the same query, using the same parms inside studio manager query window, the response is instant irrespective of the page number I pass in.

At the moment it's test code that is simply binding to a ASP:Datagrid in .NET 3.5

The SP looks like this:

BEGIN
WITH    Keys 
AS (
SELECT 
     TOP (@PageNumber * @PageSize) ROW_NUMBER() OVER (ORDER BY JobNumber DESC) as rn
    ,P1.jobNumber
    ,P1.CustID
    ,P1.DateIn
    ,P1.DateDue
    ,P1.DateOut
FROM vw_Jobs_List P1 
WHERE 
    (@CustomerID = 0 OR CustID = @CustomerID) AND 
    (JobNumber LIKE '%'+@FilterExpression+'%' 
  OR OrderNumber LIKE '%'+@FilterExpression+'%' 
  OR [Description] LIKE '%'+@FilterExpression+'%' 
  OR Client LIKE '%'+@FilterExpression+'%')    
ORDER BY P1.JobNumber DESC ),SelectedKeys
AS (
SELECT 
     TOP (@PageSize)SK.rn
    ,SK.JobNumber
    ,SK.CustID
    ,SK.DateIn
    ,SK.DateDue
    ,SK.DateOut 
FROM Keys SK 
WHERE SK.rn > ((@PageNumber-1) * @PageSize) 
ORDER BY SK.JobNumber DESC)

SELECT  
    SK.rn
   ,J.JobNumber
   ,J.Description
   ,J.Client
   ,SK.CustID
   ,OrderNumber
   ,CAST(DateAdd(d, -2, CAST(isnull(SK.DateIn,0) AS DateTime)) AS nvarchar) AS DateIn
   ,CAST(DateAdd(d, -2, CAST(isnull(SK.DateDue,0) AS DateTime)) AS nvarchar) AS DateDue 
   ,CAST(DateAdd(d, -2, CAST(isnull(SK.DateOut,0) AS DateTime)) AS nvarchar) AS DateOut
   ,Del_Method
   ,Ticket#
   ,InvoiceEmailed
   ,InvoicePrinted
   ,InvoiceExported
   ,InvoiceComplete
   ,JobStatus   
FROM SelectedKeys SK 
JOIN vw_Jobs_List J ON j.JobNumber=SK.JobNumber 
ORDER BY SK.JobNumber DESC
END

And it's called via

sp_jobs (PageNumber,PageSize,FilterExpression,OrderBy,CustomerID)

e.g.

sp_Jobs '13702','10','','JobNumberDESC','0'

Can anyone shed any light on what might be the cause of the dramatic difference in performance between SQL query window and an asp.net page executing a dataset?

Check out the "WITH RECOMPILE" option

http://www.techrepublic.com/article/understanding-sql-servers-with-recompile-option/5662581

I have run into similar problems where the execution plan on stored procedures will work great for a while, but then get a new plan because the options changed. So, it will be "optimized" for one case and then perform "table scans" for another option. Here is what I have tried in the past:

1. Re-execute the stored procedure to calculate a new execution plan and then keep an eye on it.
2. Break up the stored procedure into separate stored procedures of each option such that it can be optimized and then the overall stored procedure simply calls each "optimized" stored procedure.
3. Bring in the records into an object and then perform all of the "funky trickery" in code and then it gives you the option to "cache" the results.

Obviously option #2 and #3 is better than option #1. I am honestly finding option #3 is becoming the best bet in most cases.

I just had another option 4. You could instead of performing your "inner selects" in one query, you could put the results of your inner selects into temporary tables and then JOIN on those results. I would still push for option #3 if possible, but I understand that sometimes you just need to keep working the stored procedure until it "works".

Good luck.