Improving Performance of JQGrid for millions of records : Part 1

JQGrid plugin does not require any introduction for itself. As everyone already knows the fact that JQGrid plugin is a powerful plugin for serving data from server to the client. Also it provides very good features such as sorting, paging, filtering etc.

Now lets look at how JQGrid can be used in ASP.NET for serving millions of records with out having the following effects:-

- website hanging
- memory leaks
- page time out
- taking 5 to 6 mins to serve data (only after increasing page timeout)
-
etc.

Well if you have come before you have decided on the implementation this is the right article for you.

But if you have come here after facing the issue then the changes you would have to make will be little painful but will solve your nightmares once for all.

Now enough of the explanation lets start with step by step guidelines.

This article will address more from database point of view.

Assume a simple database with 2 tables (Customer and Invoice) as follows:-

CREATE TABLE [dbo].[CUSTOMER](
   [CUSTOMERID] [bigint] IDENTITY(1,1) NOT NULL,   
   [CUSTOMERNAME] [nvarchar](255) NULL,
   [ADDRESS] [nvarchar](255) NULL
);

CREATE TABLE [dbo].[PRODUCT](
   [PRODUCTID] [bigint] IDENTITY(1,1) NOT NULL,   
   [PRODUCTNAME] [nvarchar](255) NULL,   
   [UNITPRICE] [int] NULL
);

CREATE UNIQUE NONCLUSTERED INDEX [CUSTOMER_CUSTOMERID_IDX] ON [dbo].[CUSTOMER] ([CUSTOMERID] ASC);

CREATE TABLE [dbo].[INVOICE](
   [INVOICEID] [bigint] IDENTITY(1,1) NOT NULL,      
   [CUSTOMERID] [bigint] NOT NULL,      
   [PRODUCTID] [int] NULL,
   [QUANTITY] [int] NULL
  );

CREATE UNIQUE NONCLUSTERED INDEX [INVOICE_INVOICEID_IDX] ON [dbo].[INVOICE] ([INVOICEID] ASC);
CREATE NONCLUSTERED INDEX [INVOICE_CUSTOMERID_IDX] ON [dbo].[INVOICE] ([CUSTOMERID] ASC);

Now create a view to get the data from both the tables

CREATE VIEW [dbo].[INVOICE_VIEW]  
AS  
SELECT   
 INV.[INVOICEID],  
 CUS.[CUSTOMERNAME],  
 PRD.[PRODUCTNAME],  
 PRD.[UNITPRICE],   
 INV.[QUANTITY],  
 (PRD.[UNITPRICE] * INV.[QUANTITY]) AS [TOTALPRICE]   
FROM   
 INVOICE INV,  
 CUSTOMER CUS,  
 PRODUCT PRD  
WHERE  
 INV.CUSTOMERID = CUS.CUSTOMERID AND  
 INV.PRODUCTID = PRD.PRODUCTID  

Now crucial part is to get the data from the server and the data you need to display in a page (either 10,20 etc based on the page size).

Now lets create a stored procedure to serve the data required for jqgrid.

CREATE PROCEDURE [dbo].[GETINVOICES]
 @CONDITION nvarchar(255) = NULL,
 @SORTCONDITION nvarchar(255) = NULL,
 @PAGE INT,
 @ROWS INT,
 @RECORDCOUNT INT OUTPUT
 AS 
 DECLARE
  @params nvarchar(4000),
  @sql    nvarchar(4000),
  @STARTRECORD INT,
  @ENDRECORD INT
 BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  SET @RecordCount = 0;

  SET @SQL = 'SELECT @cnt = COUNT(*) FROM [INVOICE_VIEW] WHERE ' + COALESCE(@CONDITION,' 1 = 1');
  SELECT @params = N'@cnt int OUTPUT';
  EXEC sp_executesql @sql, @params, @cnt = @RecordCount OUTPUT
  
  if(@RecordCount > 0)
  begin
  
   SET @ENDRECORD = @PAGE * @ROWS;
   SET @STARTRECORD = @ENDRECORD - @ROWS + 1;      
   
   SET @SQL = 'select INVOICE_VIEW.INVOICEID,CUSTOMERNAME,PRODUCTNAME,UNITPRICE,QUANTITY,TOTALPRICE from [INVOICE_VIEW],'; 
   SET @SQL = @SQL  + '(select row_number() over (' + COALESCE(@SORTCONDITION,' order by INVOICEID asc ') + ') as rowid,INVOICEID';   
   SET @SQL = @SQL  + ' from [INVOICE_VIEW]) INVOICEROWS';
   SET @SQL = @SQL  + ' where ' + COALESCE(@CONDITION,' 1 = 1');
   SET @SQL = @SQL  + ' and (INVOICEROWS.rowid between @PARAM_STARTRECORD and @PARAM_ENDRECORD)';
   SET @SQL = @SQL  + ' and INVOICE_VIEW.INVOICEID = INVOICEROWS.INVOICEID';
   
   SELECT @params = N'@PARAM_STARTRECORD int,
       @PARAM_ENDRECORD int';

   EXEC sp_executesql @SQL,@params,@PARAM_STARTRECORD=@STARTRECORD,@PARAM_ENDRECORD=@ENDRECORD
  end;

 END;

continue to Improving Performance of JQGrid for millions of records : Part2

Database: the information you lose when your memory crashes.
~Dave Barry, Claw Your Way to the Top