Hi,

First I'm new to LLBLGen and And I really like the concept. Congratulations to the designers.

I wonder if it's possible to get the count (for GridView) in one query. In short, avoid having to make the same predicate COUNT 2 times.

Here is how I proceed actually. I'm using stored procedure and I stock the COUNT inside a OUTPUT parameters. Juste before filtering rowIndexMin and RowIndexMax.

Here is the stored procedure

ALTER PROCEDURE [dbo].[spGetUserList]
(
    @usersDesactivated BIT = NULL,
    @userFirstName NVARCHAR(50) = NULL,
    @userLastName NVARCHAR(50) = NULL,
    @userEmail NVARCHAR(200) = NULL,
    @userFonction NVARCHAR(50) = NULL,
    @userEnterprise NVARCHAR(50) = NULL,
    @userTelephone NVARCHAR(50) = NULL,
    @sortExpression VARCHAR(20) = 'uLastName',
    @sortDirection VARCHAR(4) = 'ASC',
    @startRowIndex INT,
    @maximumRow INT,
    @rowsCount INT OUTPUT
)       
AS
BEGIN
    SET NOCOUNT ON;

    -- Temporary table that will contains all result 
    -- before beeing filtred by RowIndex
    DECLARE @TempTable TABLE
    (
        RowNumber              INT,
        userCode               INT PRIMARY KEY,
        userEmail          NVARCHAR(100),
        userDesactivated          BIT,
        CompleteName               NVARCHAR(MAX),
        userFonction           NVARCHAR(100),
        userEnterprise        NVARCHAR(50),
        userTelephone         VARCHAR(32)
    )

    -- Do fetch in store it to the temporary table
    -- Use the predicats received from input parameters
    -- Order by @sortExpression and @sortDirection
    INSERT INTO @TempTable
    SELECT ROW_NUMBER() OVER
           (
                ORDER BY

                    CASE @sortDirection 
                        WHEN 'ASC' THEN  
                        CASE @sortExpression
                            WHEN 'userFonction' THEN CASE WHEN userFonction IS NULL THEN 1 ELSE 0 END
                            WHEN 'userEnterprise' THEN CASE WHEN userEnterprise IS NULL THEN 1 ELSE 0 END
                            WHEN 'userTelephone' THEN CASE WHEN userTelephone IS NULL THEN 1 ELSE 0 END

                        END 
                    END
                    ASC,

                    CASE @sortDirection 
                        WHEN 'DESC' THEN  
                        CASE @sortExpression
                            WHEN 'userFonction' THEN CASE WHEN userFonction IS NULL THEN 0 ELSE 1 END
                            WHEN 'userEnterprise' THEN CASE WHEN userEnterprise IS NULL THEN 0 ELSE 1 END
                            WHEN 'userTelephone' THEN CASE WHEN userTelephone IS NULL THEN 0 ELSE 1 END
                        END 
                    END
                    DESC,


           ) AS RowNumber,
           userCode,
           userEmail, 
           userDesactivated,
           (userFirstName + ' ' + userFirstName) AS CompleteName,
           userFonction,
           userEnterprise,
           userTelephone
    FROM user
    WHERE
            -- Predicats search (optionnal)
           (@userDesactivated IS NULL OR userDesactivated <> 1 OR @userDesactivated = userDesactivated)
            
           AND (@userEmail IS NULL OR userEmail LIKE '%' + @userEmail + '%')

           AND (@userFirstName IS NULL OR userFirstName LIKE '%' + @userFirstName + '%')

           AND (@userFirstName IS NULL OR userFirstName LIKE '%' + @userFirstName + '%')

           AND (@userFonction IS NULL OR userFonction LIKE '%' + @userFonction + '%')

           AND (@userEnterprise IS NULL OR userEnterprise LIKE '%' + @userEnterprise + '%')

           AND (@userTelephone IS NULL OR userTelephone LIKE '%' + @userTelephone + '%')

    GROUP BY
             userCode,
             userEmail, 
             userDesactivated,
             userFirstName,
             userFirstName,
             userFonction,
             userEnterprise,
             userTelephone

    -- Get count from the temporary table and store it into the output paramtere @rowsCount
    -- Validate rowIndex bounds
    SET @rowsCount = ROWCOUNT_BIG();
    IF @startRowIndex < 0
    BEGIN
        SET @startRowIndex = 0
    END
    ELSE 
    BEGIN
        IF @startRowIndex > @rowsCount
        BEGIN
            SET @startRowIndex = (CEILING(@rowsCount / @maximumRow)) * @maximumRow
        END
    END

    -- Returns wanted result
    SELECT TB1.userCode,
           TB1.userEmail, 
           TB1.userDesactivated,
           TB1.CompleteName,
           TB1.userFonction,
           TB1.userEnterprise,
           TB1.userTelephone,
           TB1.staDescription,
           TB1.CountAssignation
    FROM @TempTable AS TB1
    WHERE TB1.RowNumber > @startRowIndex AND TB1.RowNumber <= @startRowIndex + @maximumRow
    ORDER BY TB1.RowNumber

Sorry for the big bunch of code, but I need it to explain what I need.

Secondly, on my C# code behind I call the stored procedure :

    public class User : DabaseBaseClass
    {
        /// <summary>
        /// Will contains the database count on a query
        /// This field will be set by getting the output parameter @rowCount
        /// and will be return directly to the selectCount function need by the ObjectDataSource of .NET
        /// </summary>
        private int selectRowCounts = 0;
        
        /// <summary>
        /// Get list of users
        /// </summary>
        [DataObjectMethod(DataObjectMethodType.Select)]
        public DataTable GetUserList(
            bool? userDesactivated,
            string userLastName,
            string userFirstName,
            string userEnterprise,
            string userEmail,
            string userFunction,
            string userTelephone,
            string sortExpression,
            string sortDirection,
            int startRowIndex,
            int maximumRows)
        {
            // Table that will contains the resultset
            DataTable ret = new DataTable("uservenantListe");

            // Get default provider
            DbProviderFactory dbProvider = DbProviderFactories.GetFactory(base.ConnectionStringSettings.ProviderName);

            // Create database connection
            using (DbConnection cn = dbProvider.CreateConnection())
            {
                // Open database connection
                cn.ConnectionString = base.ConnectionStringSettings.ConnectionString;
                cn.Open();

                // Create command
                using (DbCommand cmd = dbProvider.CreateCommand())
                {
                    // Set stored procedure call settings
                    cmd.CommandText = "spGetUserList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = cn;

                    // Add parameters to the command
                    DbParameter paramuserDesactivated = dbProvider.CreateParameter();
                    paramuserDesactivated.DbType = DbType.Boolean;
                    paramuserDesactivated.Direction = ParameterDirection.Input;
                    paramuserDesactivated.ParameterName = base.ParameterMarker + "userDesactivated";
                    paramuserDesactivated.Value = userDesactivated ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramuserDesactivated);

                    DbParameter paramNom = dbProvider.CreateParameter();
                    paramNom.DbType = DbType.String;
                    paramNom.Direction = ParameterDirection.Input;
                    paramNom.ParameterName = base.ParameterMarker + "userLastName";
                    paramNom.Value = userLastName ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramNom);

                    DbParameter paramPrenom = dbProvider.CreateParameter();
                    paramPrenom.DbType = DbType.String;
                    paramPrenom.Direction = ParameterDirection.Input;
                    paramPrenom.ParameterName = base.ParameterMarker + "userFirstName";
                    paramPrenom.Value = userFirstName ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramPrenom);

                    DbParameter paramuserEnterprise = dbProvider.CreateParameter();
                    paramuserEnterprise.DbType = DbType.String;
                    paramuserEnterprise.Direction = ParameterDirection.Input;
                    paramuserEnterprise.ParameterName = base.ParameterMarker + "userEnterprise";
                    paramuserEnterprise.Value = userEnterprise ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramuserEnterprise);

                    DbParameter paramuserEmail = dbProvider.CreateParameter();
                    paramuserEmail.DbType = DbType.String;
                    paramuserEmail.Direction = ParameterDirection.Input;
                    paramuserEmail.ParameterName = base.ParameterMarker + "userEmail";
                    paramuserEmail.Value = userEmail ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramuserEmail);

                    DbParameter paramuserFunction = dbProvider.CreateParameter();
                    paramuserFunction.DbType = DbType.String;
                    paramuserFunction.Direction = ParameterDirection.Input;
                    paramuserFunction.ParameterName = base.ParameterMarker + "userFunction";
                    paramuserFunction.Value = userFunction ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramuserFunction);

                    DbParameter paramuserTelephone = dbProvider.CreateParameter();
                    paramuserTelephone.DbType = DbType.String;
                    paramuserTelephone.Direction = ParameterDirection.Input;
                    paramuserTelephone.ParameterName = base.ParameterMarker + "userTelephone";
                    paramuserTelephone.Value = userTelephone ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramuserTelephone);

                    DbParameter paramSortExpression = dbProvider.CreateParameter();
                    paramSortExpression.DbType = DbType.String;
                    paramSortExpression.Direction = ParameterDirection.Input;
                    paramSortExpression.ParameterName = base.ParameterMarker + "sortExpression";
                    paramSortExpression.Value = sortExpression ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramSortExpression);

                    DbParameter paramSortDirection = dbProvider.CreateParameter();
                    paramSortDirection.DbType = DbType.String;
                    paramSortDirection.Direction = ParameterDirection.Input;
                    paramSortDirection.ParameterName = base.ParameterMarker + "sortDirection";
                    paramSortDirection.Value = sortDirection ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramSortDirection);

                    DbParameter paramStartRowIndex = dbProvider.CreateParameter();
                    paramStartRowIndex.DbType = DbType.Int32;
                    paramStartRowIndex.Direction = ParameterDirection.Input;
                    paramStartRowIndex.ParameterName = base.ParameterMarker + "startRowIndex";
                    paramStartRowIndex.Value = startRowIndex;
                    cmd.Parameters.Add(paramStartRowIndex);

                    DbParameter paramMaximumRow = dbProvider.CreateParameter();
                    paramMaximumRow.DbType = DbType.Int32;
                    paramMaximumRow.Direction = ParameterDirection.Input;
                    paramMaximumRow.ParameterName = base.ParameterMarker + "maximumRow";
                    paramMaximumRow.Value = maximumRows;
                    cmd.Parameters.Add(paramMaximumRow);

                    DbParameter paramRowsCount = dbProvider.CreateParameter();
                    paramRowsCount.DbType = DbType.Int32;
                    paramRowsCount.Direction = ParameterDirection.Output;
                    paramRowsCount.ParameterName = base.ParameterMarker + "rowsCount";
                    cmd.Parameters.Add(paramRowsCount);

                    // Create adapter
                    using (DbDataAdapter adapter = dbProvider.CreateDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        adapter.Fill(ret);

                        // Here we get the COUNT return by the output parameters
                        // We store it into a private field, later the SelectCount of the ObjectDataSource 
                        // will use this field to get the count, instead of doing another query to get 
                        // the count !
                        selectRowCounts = Convert.ToInt32(paramRowsCount.Value);
                    }
                }
            }

            // Return resultset
            return ret;
        }

        /// <summary>
        /// Function use to get the database count of the query fetch by the function "GetUserListSelectCount"
        /// </summary>
        /// <returns></returns>
        public int GetUserListSelectCount(
            bool? userDesactivated,
            string userLastName,
            string userFirstName,
            string userEnterprise,
            string userEmail,
            string userFunction,
            string userTelephone,
            string sortExpression,
            string sortDirection)
        {
            // Here is the trick !
            // No need to run the query again unnecessarily just to have the count
            return selectRowCounts;
        }
}       

And here is how to call the procedure with the GridView

        // Create ObjectDataSource
        ObjectDataSource ods = new ObjectDataSource("CTAQManager.Engine.Database.User", "GetUserList");
        ods.ID = "odsUserList";
        ods.SelectCountMethod = "GetUserListSelectCount";
        ods.EnablePaging = true;

        // Add criteria parameters (optionnals)
        ods.SelectParameters.Add("userDesactivated", ckbDesactivated.Checked ? Boolean.TrueString : Boolean.FalseString);
        ods.SelectParameters.Add("userFirstName", tbxFirstName.Text.Length > 0 ? tbxFirstName.Text : null);
        ods.SelectParameters.Add("userLastName", tbxLastName.Text.Length > 0 ? tbxLastName.Text : null);
        ods.SelectParameters.Add("userEnterprise", tbxEnterprise.Text.Length > 0 ? tbxEnterprise.Text : null);
        ods.SelectParameters.Add("userEmail", tbxEmail.Text.Length > 0 ? tbxEmail.Text : null);
        ods.SelectParameters.Add("userFunction", tbxFunction.Text.Length > 0 ? tbxFunction.Text : null);
        ods.SelectParameters.Add("userTelephone", userTelephone.Text.Length > 0 ? userTelephone.Text : null);

        // Set sort expression
        ods.SelectParameters.Add("sortExpression", sortExpression.Length <= 0 ? null : sortExpression);

        // Set sort direction
        ods.SelectParameters.Add("sortDirection", sortDirection.Length <= 0 ? null : sortDirection);

        // Bind query result to GridView
        grvwUservenantListe.DataSource = ods;
        grvwUservenantListe.DataBind();

As you see, I never call the same query just to get the Count need by the ObjectDataSource to get paging working properly.

So is there a way to do it with LLBLGen ?

Best regards, Martin

Hi Martin,

If you can write the code in SQL sure you can translate that into SQL. If you really need one query for this, you can write a LINQ2LLBL query or use a DynamicList. The last field could be an aggregate that counts everything.

A side note: if you work with entities you can use LLBLGenProDataSource. You can implement a method to fetch the data and other for the count. You can pick the count from some variable you populated before (somehow).

daelmo wrote:

Hi Martin,

If you can write the code in SQL sure you can translate that into SQL. If you really need one query for this, you can write a LINQ2LLBL query or use a DynamicList. The last field could be an aggregate that counts everything.

A side note: if you work with entities you can use LLBLGenProDataSource. You can implement a method to fetch the data and other for the count. You can pick the count from some variable you populated before (somehow).

Hi daelmo, thanks for your answer.

As I am relatively new with LLBLGen, I do not understand your solution. In addition, I have to evaluate the possibility of migrating our project to LLBLGen. And now this is the problem that prevents me from doing the move.

Actually, I call stored procedure from a C # custom wrapper. And If I have to call MS SQL stored procedures using LLBLGen, so I do not see the point to make use of LLBLGen... I'm sure you understand me.

I have some query that are time consuming because of the complexity of data and there is no other way to process. The result must be shown in a GridView. I don't want LLBLGen evaluate the query first for data retreiving and then re-evaluate it for the GetDbCount that is necessary for ObjectDataSource paging. This is time overkill.

Can you explain your solution with some sample code so I can understand ?

Best regards, Martin

I don't want LLBLGen evaluate the query first for data retreiving and then re-evaluate it for the GetDbCount that is necessary for ObjectDataSource paging. This is time overkill.

What do you mean by evaluate and re-evaluate?

Basically there are 2 queries that need to be executed, as done inside your SP. Same thing is done by LLBLGen Pro, you can formulate the filters once. And then a GetDBCount() can be called only once using that filter. And then the actual fetch can be called several times using the same filter, and using Paging Parameters, to fetch each page at a time.

While in your SP case, each time you want a page you call the SP which executes both queries. One for the Count and the other for the page requested.

Did you profile both approaches, say for the first 5 pages, to see which is better or whether LLBLGen's is an overkill or not?

Walaa wrote:

I don't want LLBLGen evaluate the query first for data retreiving and then re-evaluate it for the GetDbCount that is necessary for ObjectDataSource paging. This is time overkill.

What do you mean by evaluate and re-evaluate?

Basically there are 2 queries that need to be executed, as done inside your SP. Same thing is done by LLBLGen Pro, you can formulate the filters once. And then a GetDBCount() can be called only once using that filter. And then the actual fetch can be called several times using the same filter, and using Paging Parameters, to fetch each page at a time.

While in your SP case, each time you want a page you call the SP which executes both queries. One for the Count and the other for the page requested.

Did you profile both approaches, say for the first 5 pages, to see which is better or whether LLBLGen's is an overkill or not?

I think you don't understand what I do in my first post. Please read carefully.

Notice this line in my stored procedure :

  -- Get count from the temporary table and store it into the output paramtere @rowsCount
    -- Validate rowIndex bounds
    SET @rowsCount = ROWCOUNT_BIG();

When I call the stored procedure, I return immediately the DATA and also the DB COUNT using an output parameter.

When I reveived the data inside the C# select caller function, I read the DB count and SAVE it to a private member variable.

                    // Create adapter
                    using (DbDataAdapter adapter = dbProvider.CreateDataAdapter())
                    {
                        // Get dataset result
                        adapter.SelectCommand = cmd;
                        adapter.Fill(ret);

                        // Here we get the COUNT return by the output parameters
                        // We store it into a private field, later the SelectCount of the ObjectDataSource 
                        // will use this field to get the count, instead of doing another query to get 
                        // the count !
                        // ###### HERE #######
                        selectRowCounts = Convert.ToInt32(paramRowsCount.Value);
                    }

When the ObjectDataSource will called the c# SelectCount function, instead of having to call COUNT(*) on the same query, I return the content of the private member selectRowCounts that already have the row count.

 public int GetUserListSelectCount(
            bool? userDesactivated,
            string userLastName,
            string userFirstName,
            string userEnterprise,
            string userEmail,
            string userFunction,
            string userTelephone,
            string sortExpression,
            string sortDirection)
        {
            // Here is the trick !
            // No need to run the query again unnecessarily just to have the count
            return selectRowCounts;
        }

In conclusion, the SelectCount function don't need to ask to SQL a COUNT(*) with the same filter.

With LLBLGen, the c# SelectCount function return GetDBCount. If I well understand, the GetDBCount will do a COUNT() on the same query with the same filter. If the query to get data takes 3 secondes to execute, then the COUNT() will takes probably a 3 seconds also. So at the end we takes 6 secondes with 3 seconds that could be avoided.

Do you understand ?

Best regards, Martin

Again:

Did you profile both approaches, say for the first 5 pages, to see which is better or whether LLBLGen's is an overkill or not?

Please correct me if I'm wrong, but as far as I can see your SP, will create the temp table and Insert the entire resultset to get the Count, each time it is called. And the SP is called whenever you need a different page of the resultSet.

Walaa wrote:

Again:

Did you profile both approaches, say for the first 5 pages, to see which is better or whether LLBLGen's is an overkill or not?

Please correct me if I'm wrong, but as far as I can see your SP, will create the temp table and Insert the entire resultset to get the Count, each time it is called. And the SP is called whenever you need a different page of the resultSet.

Yes, you are correct.

I would like to specify that I'm not denigrating the work of LLBLGen, but I just want to make sure the roundtrip is optimal before migrating my Application to LLBLGen. The project I'm working now has a lot of data and their complexity are making them sometimes expensive to get according the context.

I'm not entirely sure what your goal is: i.e.: do you want to rewrite the stored proc in llblgen code and you wonder how to get the same as in the proc, namely: you select data + you get the count ? Why do you need the count if the count is equal to the size of the fetched data? (i.e. datatable.Rows.Count)

Our framework has split count + paging (I think you're after paging), as count of the complete resultset is executed once and the query for each page is executed without the count, so it's faster after the first time.

Otis wrote:

I'm not entirely sure what your goal is: i.e.: do you want to rewrite the stored proc in llblgen code and you wonder how to get the same as in the proc, namely: you select data + you get the count ? Why do you need the count if the count is equal to the size of the fetched data? (i.e. datatable.Rows.Count)

Our framework has split count + paging (I think you're after paging), as count of the complete resultset is executed once and the query for each page is executed without the count, so it's faster after the first time.

I would like to get the result to LLBLGEN, not in stored procedure.

I notice that ObjectDataSource call the Select + SelectCount method each time we change page on a GridView.

S my stored procedure is called each time I change page, I get the COUNT of my Temporary table (that has all rows witout page filtering) and I return it as OUTPUT parameter via the Stored Procedure.

To enable Paging on ObjectDataSource, we need two method. A Select and a Count method.

Usually, the select retreived the data, and the count method retreived the COUNT.

It means that to recover the data there will be a roundtrip to SQL and to determine the COUNT there will be another roundtrip to SQL. Right ?

Here is the definition of the ObjectDataSource binded to the GridView

// Create ObjectDataSource
ObjectDataSource ods = new ObjectDataSource("CTAQManager.Engine.Database.User", "GetUserList");
ods.ID = "odsUserList";
ods.SelectCountMethod = "GetUserListSelectCount";
ods.EnablePaging = true;

When the user load GridView for the first time, or click on a page, the C# method "[b]GetUserList[/b]" is called.

Here is the code of this method :

/// <summary>
        /// Get list of users
        /// </summary>
        [DataObjectMethod(DataObjectMethodType.Select)]
        public DataTable GetUserList(
            bool? userDesactivated,
            string userLastName,
            string userFirstName,
            string userEnterprise,
            string userEmail,
            string userFunction,
            string userTelephone,
            string sortExpression,
            string sortDirection,
            int startRowIndex,
            int maximumRows)
        {
            // Table that will contains the resultset
            DataTable ret = new DataTable("uservenantListe");

            // Get default provider
            DbProviderFactory dbProvider = DbProviderFactories.GetFactory(base.ConnectionStringSettings.ProviderName);

            // Create database connection
            using (DbConnection cn = dbProvider.CreateConnection())
            {
                // Open database connection
                cn.ConnectionString = base.ConnectionStringSettings.ConnectionString;
                cn.Open();

                // Create command
                using (DbCommand cmd = dbProvider.CreateCommand())
                {
                    // Set stored procedure call settings
                    cmd.CommandText = "spGetUserList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = cn;

                    // Add parameters to the command
                    DbParameter paramuserDesactivated = dbProvider.CreateParameter();
                    paramuserDesactivated.DbType = DbType.Boolean;
                    paramuserDesactivated.Direction = ParameterDirection.Input;
                    paramuserDesactivated.ParameterName = base.ParameterMarker + "userDesactivated";
                    paramuserDesactivated.Value = userDesactivated ?? (object)DBNull.Value;
                    cmd.Parameters.Add(paramuserDesactivated);
...
                   // Create adapter
                    using (DbDataAdapter adapter = dbProvider.CreateDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        adapter.Fill(ret);

                        // Here we get the COUNT return by the output parameters
                        // We store it into a private field, later the SelectCount of the ObjectDataSource 
                        // will use this field to get the count, instead of doing another query to get 
                        // the count !
                        selectRowCounts = Convert.ToInt32(paramRowsCount.Value);
                    }
                }
            }

            // Return resultset
            return ret;
        }

Notice that I get the COUNT (before applying paging filter) of the resultset return by the stored procedure.

   -- Get count from the temporary table and store it into the output paramtere @rowsCount
    -- Validate rowIndex bounds
    SET @rowsCount = ROWCOUNT_BIG();

Immediately after this method call, the ObjectDataSource is calling the SelectCount method "[b]GetUserListSelectCount[/b]"

  /// <summary>
        /// Function use to get the database count of the query fetch by the function "GetUserListSelectCount"
        /// </summary>
        /// <returns></returns>
        public int GetUserListSelectCount(
            bool? userDesactivated,
            string userLastName,
            string userFirstName,
            string userEnterprise,
            string userEmail,
            string userFunction,
            string userTelephone,
            string sortExpression,
            string sortDirection)
        {
            // Here is the trick !
            // No need to run the query again unnecessarily just to have the count
            return selectRowCounts;
        }

Instead of doing another roundtrip to SQL to retreive to COUNT, I'm using a private member containing the COUNT previously get from the stored procedure.

I hope you understand what I try to explain.

The concept is just to avoid having TWO roundtrip to database, one for data retreiving and the other for the COUNT on the data.

Ok.

We have our own datasourcecontrol, which helps you with this. There are 2 calls. 1 for the count and 1 for the page data. The count is done every time the bound control needs the total number of rows, which is in general just once. The page data call is done for each page to retrieve. See the link above for the documentation on the datasourcecontrol and the events to bind to. The Perform select and perform count events are the ones you should look into.

I know this does 2 roundtrips for the first page, but for all subsequential pages, it doesn't. The idea is that for paging, you don't have to store all rows in a temp table, just pagesize*(pagenumber +1) rows. For SQL Server 2005 and up, a CTE is used for paging, which is more efficient in many cases.

A count aggregate query can be different from the main query. For example, if you use inner joins you can use the count distinct on the PK of the starting entity to get the count of the total query. This is more efficient than dumping the complete resultset in a temptable and count the rows in that table, because if the resultset is 100K rows, and you want to fetch the first page, you are still inserting 100K rows in the temptable, while our page method is inserting just the pagesize (and on the first page, it uses a TOP query, no temptable paging at all, or if you use 2005 or higher, a CTE).

But again, you show code using object datasource using a stored proc. I don't know if you want to rewrite the proc or that you simply want to call the proc.

Hi Otis,

You said :

The count is done every time the bound control needs the total number of rows, which is in general just once

This is not just once ? I don't know if your DataSourceControl has a different behavior for DataCount calll than the one in .NET Framework, but on each page change the DataSourceControl is doing two call (DataCount + Data). So a user searching inside a GridView having 500 pages (25 entry per page) will do 2 call between each page change. This can't be overkill while you have 30 users looking for a very complex data retreiving. If you control do a PageCount usually once, then how can he know that data has not change between two page change ? There something that I don't understand here.

I did some test on the Framework .NET DataSourceControl and each time I change the current page, it does a DataCount + DataCall.

Probably a temporary table is less effective than CTE. However using a CTE would withdraw the rows that are not in the range of paging. So I could not return the DbCount of the query using

SET @ rowsCount ROWCOUNT_BIG ();

because the filter page has already been applied with the CTE and the query only contains row for the asking page.

Using a temporary "in a stored procedure call data" I just have to to call ROWCOUNT_BIG() just after the data query and then return it using an OUTPUT parameter. So next time the DataSourceControl is asking me for the GetDbCount I don't ask again to SQL, I give him a variable (c# code behing) containing the ROWCOUNT_BIG previously computed in the GetData call. In this way I avoid an unnecessary roundtrip to get the GetDbCount at each change of page..

However, I understand the negative effect of my temporary table. You are right about the fact that I could insert unnecessary lines 100K and take only 10 for a page. As for the optimization you lose me a bit. Perform a "count distinct on the PK" will not require the same filter (WHERE) ? The same joins ? If so, then this is the same query, similar time consuming ?

As for the rest of the message. I don't want to use my Stored Procedure. I want to avoid them using an ORM. The reason why there StoreProcedure in this project it is because it has to be really optimized and it is an old project. Now I would like to use a ORM because because it begins to be large and hard to maintains. So I presume that I would use your own ObjectDataSource. But I need to be sure that I will not do unnecessary roundtrip to SQL. The project already exists, it is really optimzied, quick and the users don't want an efficiency or speed downgrade.

Martin

erakis wrote:

You said :

The count is done every time the bound control needs the total number of rows, which is in general just once

This is not just once ? I don't know if your DataSourceControl has a different behavior for DataCount calll than the one in .NET Framework, but on each page change the DataSourceControl is doing two call (DataCount + Data). So a user searching inside a GridView having 500 pages (25 entry per page) will do 2 call between each page change. This can't be overkill while you have 30 users looking for a very complex data retreiving. If you control do a PageCount usually once, then how can he know that data has not change between two page change ? There something that I don't understand here.

It usually requires just one DBCount call, however that depends on the bound control: if the bound control (say a GridView) requires the total row number, it will ask the datasource control (in case of LLBLGen, the LLBLGenDataSource(2)). With LLBLGenDataSource(2) you have two modes: LivePersistence= true/false. If it is set to 'true' then the datasource will care of the fetch/dbCount/saves so you don't have to write a line of code. If it's set to 'false' you implement your own fetch/dbCount/saves calls.

In your case when you want to avoid a lot of DBCounts, you could implement your own DBCount mechanism that do some cache of the count:

protected void _OrderDS_PerformGetDbCount(object sender, PerformGetDbCountEventArgs2 e)
{
    // this is a custom call you could make to save the count between postbacks
    int? currentCount = ObtainCountFromViewState();

    // if it's the first time, obtain the count from DB
    if (!currentCount.HasValue)
    {
        using (var adapter = new DataAccessAdapter())
        {
            e.DbCount = adapter.GetDbCount(e.ContainedCollection, e.Filter);                
        }
    }
}

of course this have the disadvantage that the data could change outside and your DbCount is out of sync.

erakis wrote:

Probably a temporary table is less effective than CTE. However using a CTE would withdraw the rows that are not in the range of paging. So I could not return the DbCount of the query using

SET @ rowsCount ROWCOUNT_BIG ();

because the filter page has already been applied with the CTE and the query only contains row for the asking page.

Using a temporary "in a stored procedure call data" I just have to to call ROWCOUNT_BIG() just after the data query and then return it using an OUTPUT parameter. So next time the DataSourceControl is asking me for the GetDbCount I don't ask again to SQL, I give him a variable (c# code behing) containing the ROWCOUNT_BIG previously computed in the GetData call. In this way I avoid an unnecessary roundtrip to get the GetDbCount at each change of page..

I understand, but, what you do has the same effect of not doing paging at all. For example, if you set EnablePaging="false" in the LLBLGenProDataSource then LLBLGen will retrieve the complete set of records and the paging is provided in-memory by the bound control (GridView). The GridView only ask you the TotalRowCount because if you provide it then the will try to do paging at the server. In your temp-table/SP approach, you are retrieving the DBCount and the Data in one round-trip, but there is no real paging going on (the whole data set is fetched)

erakis wrote:

However, I understand the negative effect of my temporary table. You are right about the fact that I could insert unnecessary lines 100K and take only 10 for a page. As for the optimization you lose me a bit. Perform a "count distinct on the PK" will not require the same filter (WHERE) ? The same joins ? If so, then this is the same query, similar time consuming ?

If your query involves a lot of joins, it may be that the DbCount query could be simplified, for example you just get the primary entity count, you just need the distinct count of the PKs of the main entity, instead of repeat the same data query with a Count aggregate. So you could do this in the PerformDbCount event handler.

erakis wrote:

As for the rest of the message. I don't want to use my Stored Procedure. I want to avoid them using an ORM. The reason why there StoreProcedure in this project it is because it has to be really optimized and it is an old project. Now I would like to use a ORM because because it begins to be large and hard to maintains. So I presume that I would use your own ObjectDataSource. But I need to be sure that I will not do unnecessary roundtrip to SQL. The project already exists, it is really optimzied, quick and the users don't want an efficiency or speed downgrade.

I think the best would be to do some performance tests to see what approach is better for you. If, for some reason your temp-table/SP is more convenient, you can project the SP resulset into an EntityCollection and grab the Count output parameter.