Unnecessary DB calls and Inefficient paging logic

CodeOnTime generated too many unnecessary database calls. For example if you consider the OrderForm tutorial, on browsing to the OrderForm page, it will display the first page of orders.

However the genrated DB calls are:

- select count(*) from Shippers
- Select count(*) from Orders .. with joins on Customers, Employees and Shippers
- select count(*) from OrderDetails where orderid is null ... with joins on various tables
- Db call to get the first page of orders, using a common table expression

is the call on Shippers tables necessary? And can we not wait for a selection on Orders before trying to read OrderDetails (Currently being read for OrderId is NULL)

if an option is provided to do paging using just the previous/next buttons, then we will not need a call on count(*) to get the total record count. The overall record count and page count need not be displayed.

Also the paging logic is not efficient. It generates

with page_cte__ as (
select
row_number() over (...) as row_number__
... from

)
select * from page_cte__ where row_number__ > @PageRangeFirstRowNumber and row_number__
1 person likes
this idea
+1
Reply
  • Sample OrderForm described at http://codeontime.com/learn/sample-ap... will select shippers since the shippers are presented as a regular drop down. The drop down needs to be pre-filled with data. Change the lookup type from Drop Down List to Auto Complete or Lookup to delay retrieval of data until user starts interacting with the lookup.

    Order Details are selected upon arriving to the page since the data view property Auto Hide is not set to Self.

    Every data view will always select a "count" of records before retrieving the first page of data. This is done once until the filters are changes. Paging will not require count to execute. The exact count is needed to display a pager. There is no other reasonable way to predict the number of available records. The exact page count is used by the data sheet view to display a "true" vertical scroll bar - http://codeontime.com/learn/data-cont....

    The application framework needs to know the count of records even if you use only Next and Previous paging buttons since there is a possibility of your app misleading users about availability of the next page.

    Common table expressions is the industry standard way of efficient server-side paging that allows fastest possible access to a particular page of data even in a very large data set. The same expression presented above will select the desired number of records without physically fetching records to the application server tier from the database. Ten records will be fetched if you set parameters to 1 and 10. Ten records will be fetched if you jump to the range 1001 - 1010.

    Note that Microsoft Linq and many other frameworks use common table expressions in exactly the same fashion.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

  • Is it an issue if a "Next" button is displayed and no records are fetched?

    Even though only the pages size will be fetched from DB to the application layer, the database will have created a complete copy of the table in Temporary storage with the entire table data+added row number.

    If tables have IDs as primary keys, it would be more efficient to base the paging logic on that.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

  • Hello,

    just to add my contribute having spent several hours on paging logic.

    COT paging is not efficient over Oracle large tables.
    This is not a COT fault but an Oracle fault in my opinion.
    Please look at my post
    http://community.codeontime.com/codeo...

    The same is true for select count(*): may be interesting to have a paging logic for large tables not using "select count(*)" and only have "move next" and "move previous"
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

  • I checked COT paging peformance over a large table (1 Million rows) in SQL Server.

    It was very slow.
    • Sudhanshu, web apps created with Code On Time are classical multi-tier apps. The application tier (the actual generated code) is always performing at the same speed regardless of the back-end database engine and database size. The application tier composes SQL statements and passes them to the database engine for execution.

      The database engine is doing the actual work. If there are no indexes and the memory of the database server is low then one may expect a poor overall performance.

      If your business requirements spell that users must be able to browse and search at will through millions of records, then a certain amount of database optimization and tuning will be required to make it possible.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

  • The same in Oracle;
    I patched ConfigureCommandForSelect to use an efficient paging logic on Oracle;for SQL Server I cannot suggest anything at this moment.

    It is impossible in my opinion to implement one efficient paging logic working on many databases; each databases needs a different implementation
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

  • I think one approach would be to use paging based on Identity values for tables that have an identity column. Most large tables would have an identity column so this would work effeciatently.

    For tables without identity columns, the current logic could be applied.
    • Sudhanshu, identity columns do not make paging any easier. Application users will be selecting data rows in an order different from the identity values. For example, sorting by last name will make identity values of little value when implementing paging.

      GB has suggested a few Oracle-specific optimizations that we have placed on our wish list.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

  • To disable the record count as an option would be appreciated.
    The count(*) takes a lot of time especially if a lot of left joins are used.
    (left join forces full table scan in oracle even with correct indexes!)

    I have a controller referencing 2 million + records (5 left joins)
    Selecting first 10 records takes about 2 secs but count(*) takes about 2 minutes !
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly

  • Found a way to speed up things a little bit (oracle).
    Adding ...
    where rownum <= :PageRangeFirstRowNumber
    in ConfigureCommandForSelect function when creating the select statement forces Oracle to use STOPKEY optimizing
    -> about 5 times faster in case of my 2million+ table with 5 left joins !
    Can this be implemented in the library please ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly