Posts
19
Comments
28
Trackbacks
1
June 2005 Blog Posts
Pagination using Stored Procedure

There are different approach on how to do pagination, but the I personally uses the dynamic sql pagination using subquery, and this I think is very efficient when doing a pagination in which all the fields to be filtered and sorted are all in the same table:

EXEC  ( 'SELECT ' + @sqlQuery +
    ' WHERE [ID] IN ' +
   '  (SELECT TOP ' + @strPageSize + ' [ID] FROM ' + @strBaseTable + @strFilter +
             @Connector +  '  [ID] NOT IN ' + '
                (SELECT TOP ' + @strSkippedRoW + ' [ID] FROM ' + @strBaseTable + @strFilter + @SortBy + ') '
   + @SortBy + ') ' + @SortBy)


Example we have our SalesOrderTable which comprises of SalesID, Date, ItemID, WholeSalerID, CustomerID and   the other table we have are Item Table, Wholesaler Table, CustomerTable . In this example lets take in mind these are the number of records per table:

SalesOrderTable = 2,300,000  |  ItemTable = 670,000  |  WholeSaler Table = 310,000 |  Customer Table = 900,000

What if the user wanted the data sorted using ItemName, CustomerName, WholeSaler Name which is on different table, in order to achive this we are going to use JOINS right??.  using the dynamic subquery will take forever.  I came up with more optimized approach which runs less than 8 secs..

 

1. Checks the orderby statement. If the sorting is based on fields that is on the same table or fields that are located in different table.

SET @strBaseTable =  ( CASE
   WHEN (@SortBy IS NOT NULL AND @SortBy!='') THEN
    (CASE
    WHEN    (     @SortByFilter='ID' 
              OR @SortByFilter='SalesOrderTable.WholeSalerTableCode' 
              OR @SortByFilter= 'SalesOrderTable.HPCode' 
              OR @SortByFilter='SalesOrderTable.ITEM'
              OR @SortByFilter='QTY'
              OR @SortByFilter='OEDate' )
     THEN ' SalesOrderTable'  
    WHEN @SortByFilter=  'a.CNAME' THEN ' SalesOrderTable INNER JOIN  dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable  '
    WHEN @SortByFilter=  'a.CustomerTablePRV' THEN ' SalesOrderTable INNER JOIN  dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable '
    WHEN @SortByFilter= 'a.CustomerTableCITY' THEN ' SalesOrderTable INNER JOIN  dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable '
    WHEN @SortByFilter= 'b.CNAME' THEN ' SalesOrderTable INNER JOIN dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable  '  
    WHEN @SortByFilter= 'b.CustomerTablePRV' THEN ' SalesOrderTable INNER JOIN dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable  '  
    WHEN @SortByFilter=  'b.CustomerTableCITY' THEN ' SalesOrderTable INNER JOIN dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable  '  
    WHEN @SortByFilter=  'ITEMDES' THEN ' SalesOrderTable     INNER JOIN     dbo.ItemTable ON dbo.SalesOrderTable.ITEM = dbo.ItemTable.ITEM '
    ELSE ' SalesOrderTable '
    END)  
   ELSE  ' SalesOrderTable '
             END )

2. Inserts only the ID with proper sorting to the temp table. I use #temp table because the filtering is dynamic.

 --- Inserts all ID to #TempTable with sorting
 SET @sql1 =   'INSERT INTO #TempTable ([ID])' +
             ' SELECT SalesOrderTable.[ID] FROM ' + @strBaseTable  + @strFilter + @SortBy

  EXEC @sql1

SET @rowCount = @@rowcount

3. Finally Get the proper data by linking all tables + temp table's ID and ROW

 --- Retrieves All Information Needed
           SELECT
    @rowCount AS RecordCount,
    dbo.SalesOrderTable.[ID],
                                         a.CustomerTablePRV AS WholeSalerTablePrv,
                                         a.CustomerTableCITY AS WholeSalerTableCity,
                                         dbo.SalesOrderTable.WholeSalerTableCode,
                                         a.CNAME AS WholeSalerTableName,
                                         b.CustomerTablePRV AS HPPrv,
                                         b.CustomerTableCITY AS HPCity,
                                         dbo.SalesOrderTable.HPCode,
                                         b.CNAME AS HPName,
                                        dbo.SalesOrderTable.ITEM,
                                        dbo.ItemTable.ITEMDES,
                                        dbo.SalesOrderTable.QTY,
                                        dbo.SalesOrderTable.OEDate,
                                        dbo.SalesOrderTable.CFM,
                                        dbo.SalesOrderTable.SalesID,
               dbo.SalesOrderTable.InpDate
              FROM      dbo.SalesOrderTable
   INNER JOIN     dbo.CustomerTable a ON dbo.SalesOrderTable.WholeSalerTableCode = a.CustomerTable
               INNER JOIN     dbo.CustomerTable b ON dbo.SalesOrderTable.HPCode = b.CustomerTable
               INNER JOIN     dbo.ItemTable ON dbo.SalesOrderTable.ITEM = dbo.ItemTable.ITEM
  INNER JOIN    #TempTable t ON dbo.SalesOrderTable.[ID] = t.[ID]
 WHERE (t.ROW >= @Start) AND (t.ROW <= @END)

Conclusion:

I do many test with the given scenario and I think this is the fastest approach I've ever had.

 

 

 

posted @ Thursday, June 23, 2005 1:18 PM
Multi-Language Support in Classic ASP

I was ask to create COM+ that will return data from DB2 to Classic ASP that has multi-language support.

At first, it was really a headache on how I can accomplish this project. I've try to just passed the data comming from DB2 directly to ASP but it was totally garbage.I get data from DB2 using Dataset and I loop each rows in the dataset to construct a string, since string datatype is compatible with Classic ASP, My delimiter per row is  "___NLINE__" and per column is 
"N$XITM_". Also in order to connect to DB2, I used Ritmo Driver for iSeries, its a third-party driver that supports .NET.

Well the solution is seems simple, I connect to DB2 server passed the query and then convert the result to Ascii Codes, I set a delimiter for every row, every item, and every letters from COM+ and pass it to ASP then convert again to characters by using spit() , Chr & ChrW function to convert to character since  the charset and code page is already declared.

C# Code for converting to ASCII - delimited by & per character:

public static string Chr(string str)
  {
   if (str.Length > 0)
    {
    StringBuilder retStr = new StringBuilder();
    char[] arrychr = str.ToCharArray(); 
    foreach (char chr in arrychr)
    {
     int asciicode = (int)chr;
     retStr.Append("&" + asciicode.ToString());
    }
    return retStr.ToString();
   }
   return "";
  }

                    Function in ASP to convert from ASCII to Characters:

Function ConvertToChar(byval str)
'delimiters
dim delrows, delitems, delcols
delrows  = "___NLINE__"
delcols  = "N$XITM_"
delitems = "&"
'********************************
'return string
dim strConverted
'arrays
dim arryRows, arryCols, arryItems
'counters
dim ctrrows, ctrcol, ctritems
'Ubound Var
dim UarryRows,UarryCols,UarryItems
strConverted = ""
dim ichr
'Get the Rows
 arryRows = split(str,delrows)
 UarryRows = Cint(Ubound(arryRows))
 for ctrrows = 0  to UarryRows
 'Get Columns
 arryCols = split(arryRows(ctrrows),delcols)
 UarryCols = Cint(ubound(arryCols))
 for ctrCol = 0 to UarryCols
  'Get Items
  arryItems = split(arryCols(ctrCol),delitems)
  UarryItems = Cint(UBound(arryItems)) 
   for ctrItems = 0 to UarryItems
    'checks if Item is ASCII Code
    if len(arryItems(ctrItems)) > 1 then 
     ichr =  cLng(arryItems(ctrItems))
     select case ichr
     case 219,254,221: 'this characters are whitespace
     case else:
      if ichr > 254 then
       strConverted = strConverted & Chrw(ichr)
      else
       strConverted = strConverted & Chr(ichr)
      end if
     end select
    else
     strConverted = strConverted & arryItems(ctrItems)
    end if
   next
   'Adds NextItem Attrib
      if (ctrCol <> UarryCols) then strConverted = strConverted & delcols
  next
  'Adds NextLine Attrib
   if (ctrrows <> UarryRows) then strConverted = strConverted & delrows
next
ConvertToChar = strConverted
End Function

 

 

posted @ Thursday, June 09, 2005 11:35 AM | Feedback (1)