Enhanced Performance and Paging with Stored Procedure and LINQ to SQL

[Chase Florell]

is there any way to write a stored procedure that has enhanced performance ability with the RadGrid?

I have a masive stored procedure for a search function I am using but the problem I am up against is that if ALL search fields are left blank, the search is built to return ALL records from the database.  This will eventually be thousands if not tens of thousands of records.

How can I build my stored procedure (and wire up the Grid) to only pull the necessary page at a time rather than have to pull the entire recordset every time?

-- =============================================
-- Author: Chase Florell
-- Create date: 08/24/2008
-- Update date: 11/14/2008
-- Description: Business For Sale Search
-- =============================================

ALTER PROCEDURE [dbo].[bt_BizForSale_Search]
(
    @ID                         int,
    @TakeBack                   bit,
    @Extension                  nvarchar(50),
    @City                       nvarchar(50),
    @Province                   int,
    @Country                    int,
    @Area                       nvarchar(50),
    @Relocatable                nvarchar(50),
    @VendorType                 nvarchar(50),
    @SaleStructure              nvarchar(50),
    @SellingPriceHigh           Money,
    @SellingPriceLow            Money,
    @DownLow                    money,
    @DownHigh                   money,
    @AnnualLow                  money,
    @AnnualHigh                 money,
    @EBITDALow                  money,
    @EBITDAHigh                 money,
    @BrokerName                 nvarchar(50),
    @A                          nvarchar(50),
    @A1                         nvarchar(50),
    @A2                         nvarchar(50),
    @A3                         nvarchar(50),
    @B                          nvarchar(50),
    @B1                         nvarchar(50),
    @B2                         nvarchar(50),
    @B3                         nvarchar(50),
    @C                          nvarchar(50),
    @C1                         nvarchar(50),
    @C2                         nvarchar(50),
    @C3                         nvarchar(50)
)

AS
SET NOCOUNT ON
SET ANSI_NULLS ON


SELECT     dbo.bt_Regions.Region AS Province,
           bt_Regions_1.Region AS Country,
           dbo.bt_BizForSale.ID,
           dbo.bt_BizForSale.[45],
           dbo.bt_BizForSale.[46],
           dbo.bt_BizForSale.[118],
           dbo.bt_BizForSale.[isSold]

FROM       dbo.bt_BizForSale INNER JOIN
           dbo.bt_Regions AS bt_Regions_1 ON dbo.bt_BizForSale.[61] = bt_Regions_1.ID INNER JOIN
           dbo.bt_Regions ON dbo.bt_BizForSale.[62] = dbo.bt_Regions.ID

WHERE
(
            (dbo.bt_BizForSale.[ID] = COALESCE(@ID, dbo.bt_BizForSale.[ID]))
    And     (@TakeBack Is Null Or dbo.bt_BizForSale.[130] = @TakeBack)
    And     (@Extension Is Null Or dbo.bt_BizForSale.[45] Like '%' + @Extension + '%')
    And     (@City Is Null or dbo.bt_BizForSale.[63] Like '%' + @City + '%')
    And     (@Province Is Null Or dbo.bt_BizForSale.[62] = @Province)
    And     (@Country Is Null Or dbo.bt_BizForSale.[61] = @Country)
    And     (@Area Is Null Or dbo.bt_BizForSale.[65] Like '%' + @Area + '%')
    And     (@Relocatable Is Null Or dbo.bt_BizForSale.[66] = @Relocatable)
    And     (@SaleStructure Is Null Or dbo.bt_BizForSale.[95] = @SaleStructure)
    And     (@VendorType Is Null Or dbo.bt_BizForSale.[VendorType] = @VendorType)
    And     (@SellingPriceLow Is Null Or @SellingPriceHigh Is Null Or dbo.bt_BizForSale.[118] Is Null Or dbo.bt_BizForSale.[118] Between @SellingPriceLow And @SellingPriceHigh)
    And     (@EBITDALow Is Null Or @EBITDAHigh Is Null Or dbo.bt_BizForSale.[92] Is Null Or dbo.bt_BizForSale.[92] Between @EBITDALow And @EBITDAHigh)
    And     (@AnnualLow Is Null Or @AnnualHigh Is Null Or dbo.bt_BizForSale.[91] Is Null Or dbo.bt_BizForSale.[91] Between @AnnualLow And @AnnualHigh)
    And     (@DownLow Is Null Or @DownHigh Is Null Or dbo.bt_BizForSale.[135] Is Null Or dbo.bt_BizForSale.[135] Between @DownLow And @DownHigh)
    And     (@BrokerName Is Null or dbo.bt_BizForSale.[31] Like '%' + @BrokerName + '%')
    And     (@A Is Null Or dbo.bt_BizForSale.[46] = @A Or dbo.bt_BizForSale.[47] = @A Or dbo.bt_BizForSale.[48] = @A)
    And     (@A1 Is Null Or dbo.bt_BizForSale.[49] = @A1 Or dbo.bt_BizForSale.[50] = @A1 Or dbo.bt_BizForSale.[51] = @A1 Or dbo.bt_BizForSale.[52] = @A1 Or dbo.bt_BizForSale.[53] = @A1 Or dbo.bt_BizForSale.[54] = @A1 Or dbo.bt_BizForSale.[55] = @A1 Or dbo.bt_BizForSale.[56] = @A1 Or dbo.bt_BizForSale.[57] = @A1)
    And     (@A2 Is Null Or dbo.bt_BizForSale.[49] = @A2 Or dbo.bt_BizForSale.[50] = @A2 Or dbo.bt_BizForSale.[51] = @A2 Or dbo.bt_BizForSale.[52] = @A2 Or dbo.bt_BizForSale.[53] = @A2 Or dbo.bt_BizForSale.[54] = @A2 Or dbo.bt_BizForSale.[55] = @A2 Or dbo.bt_BizForSale.[56] = @A2 Or dbo.bt_BizForSale.[57] = @A2)
    And     (@A3 Is Null Or dbo.bt_BizForSale.[49] = @A3 Or dbo.bt_BizForSale.[50] = @A3 Or dbo.bt_BizForSale.[51] = @A3 Or dbo.bt_BizForSale.[52] = @A3 Or dbo.bt_BizForSale.[53] = @A3 Or dbo.bt_BizForSale.[54] = @A3 Or dbo.bt_BizForSale.[55] = @A3 Or dbo.bt_BizForSale.[56] = @A3 Or dbo.bt_BizForSale.[57] = @A3)
    And     (@B Is Null Or dbo.bt_BizForSale.[46] = @B Or dbo.bt_BizForSale.[47] = @B Or dbo.bt_BizForSale.[48] = @B)
    And     (@B1 Is Null Or dbo.bt_BizForSale.[49] = @B1 Or dbo.bt_BizForSale.[50] = @B1 Or dbo.bt_BizForSale.[51] = @B1 Or dbo.bt_BizForSale.[52] = @B1 Or dbo.bt_BizForSale.[53] = @B1 Or dbo.bt_BizForSale.[54] = @B1 Or dbo.bt_BizForSale.[55] = @B1 Or dbo.bt_BizForSale.[56] = @B1 Or dbo.bt_BizForSale.[57] = @B1)
    And     (@B2 Is Null Or dbo.bt_BizForSale.[49] = @B2 Or dbo.bt_BizForSale.[50] = @B2 Or dbo.bt_BizForSale.[51] = @B2 Or dbo.bt_BizForSale.[52] = @B2 Or dbo.bt_BizForSale.[53] = @B2 Or dbo.bt_BizForSale.[54] = @B2 Or dbo.bt_BizForSale.[55] = @B2 Or dbo.bt_BizForSale.[56] = @B2 Or dbo.bt_BizForSale.[57] = @B2)
    And     (@B3 Is NulL Or dbo.bt_BizForSale.[49] = @B3 Or dbo.bt_BizForSale.[50] = @B3 Or dbo.bt_BizForSale.[51] = @B3 Or dbo.bt_BizForSale.[52] = @B3 Or dbo.bt_BizForSale.[53] = @B3 Or dbo.bt_BizForSale.[54] = @B3 Or dbo.bt_BizForSale.[55] = @B3 Or dbo.bt_BizForSale.[56] = @B3 Or dbo.bt_BizForSale.[57] = @B3)
    And     (@C Is Null Or dbo.bt_BizForSale.[46] = @C Or dbo.bt_BizForSale.[47] = @C Or dbo.bt_BizForSale.[48] = @C)
    And     (@C1 Is Null Or dbo.bt_BizForSale.[49] = @C1 Or dbo.bt_BizForSale.[50] = @C1 Or dbo.bt_BizForSale.[51] = @C1 Or dbo.bt_BizForSale.[52] = @C1 Or dbo.bt_BizForSale.[53] = @C1 Or dbo.bt_BizForSale.[54] = @C1 Or dbo.bt_BizForSale.[55] = @C1 Or dbo.bt_BizForSale.[56] = @C1 Or dbo.bt_BizForSale.[57] = @C1)
    And     (@C2 Is Null Or dbo.bt_BizForSale.[49] = @C2 Or dbo.bt_BizForSale.[50] = @C2 Or dbo.bt_BizForSale.[51] = @C2 Or dbo.bt_BizForSale.[52] = @C2 Or dbo.bt_BizForSale.[53] = @C2 Or dbo.bt_BizForSale.[54] = @C2 Or dbo.bt_BizForSale.[55] = @C2 Or dbo.bt_BizForSale.[56] = @C2 Or dbo.bt_BizForSale.[57] = @C2)
    And     (@C3 Is Null Or dbo.bt_BizForSale.[49] = @C3 Or dbo.bt_BizForSale.[50] = @C3 Or dbo.bt_BizForSale.[51] = @C3 Or dbo.bt_BizForSale.[52] = @C3 Or dbo.bt_BizForSale.[53] = @C3 Or dbo.bt_BizForSale.[54] = @C3 Or dbo.bt_BizForSale.[55] = @C3 Or dbo.bt_BizForSale.[56] = @C3 Or dbo.bt_BizForSale.[57] = @C3)
    And     (dbo.bt_BizForSale.[isEnabled] = 1)
    And     (dbo.bt_BizForSale.[DateExpires] > GetDate())
)
ORDER BY [OrderID]

Update bt_Categories
Set [FSSearchCount] = [FSSearchCount] + 1
Where [Category] = @A

Update bt_Categories
Set [FSSearchCount] = [FSSearchCount] + 1
Where [Category] = @B

Update bt_Categories
Set [FSSearchCount] = [FSSearchCount] + 1
Where [Category] = @C

Update bt_Regions
Set [FSSearchCount] = [FSSearchCount] + 1
Where [ID] = @Province

Thanks in advance.

[Rosen]

Hi Chase Florell,

Here are some links which illustrates how to achieve custom paging on the backend:

• Efficiently Paging Through Large Amounts of Data
• Custom Paging in ASP.NET 2.0 with SQL Server 2005 

A possible implementation of SqlServer paging in action can be viewed at our live demos here.

Kind regards,
Rosen
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.