[CLOSED] Loading thousands rows to Grid

  1. #1

    [CLOSED] Loading thousands rows to Grid

    Hello

    please could you help me with my problem ?

    I have page similarly designed as in https://examples2.ext.net/#/GridPane...SqlDataSource/ . But there are about 20 000 records in my table and I need to display all of them to grid panel. After 30 sec the loading of data is interrupted . Please is it possible somewhere to prolong this time ?

    Thanks

    Regards

    Rosta
    Last edited by Daniil; Aug 12, 2014 at 3:56 PM. Reason: [CLOSED]
  2. #2
    Hi

    20000 rows is too much for web grid, so you have to use remote paging or buffered grid (BufferedRenderer plugin)
    But Buffered grid cannot be edited

    So, the single good solution for ediatble grid can be remote paging only
    But there are about 20 000 records in my table and I need to display all of them to grid panel.
    Can you explain why there is such limitation ("display all of them to grid panel"), just I cannot imagine scenarion when user can operate with 20000 records at one time
    Vladimir Shcheglov
    Sr. Developer
  3. #3
    Hello,

    I changed all which you wrote below. Grid is not editable and I used BufferedRenderer plugin and paging - 350 rows per page. I think that my problem is time of loading rows from database. Because when I tested it in computer with faster connection there was not problem to display all rows

    I tried to tune sql script but there is not more space because the view has many left joins .

    I tested to call before loading data javascript with
    Ext.override(Ext.data.proxy.Server, { timeout: 60000 });
    Ext.override(Ext.data.proxy.Ajax, { timeout: 60000 });
    Ext.override(Ext.form.action.Action, { timeout: 60000 });
    but it is not right way probably.

    Any idea please ?

    And why so many rows ? Users need to analyze data from one month and the easiest way was load all to grid and then apply filter (https://examples2.ext.net/#/GridPane...Header/Filter/)

    Thanks

    Regards
    Rosta
  4. #4
    Any idea please ?
    Idea for what? Unfortunatelly, we cannot improve your connection
    In any way, send all data from the server to the client is bad idea. What if your DB will have millons of records?

    And why so many rows ? Users need to analyze data from one month and the easiest way was load all to grid and then apply filter
    Use remote filtering, see the following sample
    https://examples2.ext.net/#/GridPane...Header/Remote/

    So, remote paging with remote filtering and remote sorting should help you
    Vladimir Shcheglov
    Sr. Developer
  5. #5
    By the way, did you measure response size when you return 20000 records from the server? Can you post that size?

    I stronglly reccomend to switch to remote actions (paging/sorting/filtering) because your DB can grow and you will get big problems in future, remote mode can work with any records amount. Just send small pportion of data to display it in the browser but all logic (sorting and filtering) performs on the server side
    Vladimir Shcheglov
    Sr. Developer
  6. #6
    Hi Vladimir,
    thanks. I tried to adjust sql tables and view and result is: SQL table with 20 000 rows, select response all records in computer where I have development tool is 1 sec.

    But still I have problem with loading 20 000 rows to grid. I tested 10 000 and it was OK. In development tool at once when I press button for loading to grid the error on command "this.Store1.databind()" appears.

    An exception of type 'System.OutOfMemoryException' occurred in mscorlib.dll but was not handled in user code


    Below the script for store and grid are attached.

    Thanks very much

    Regards
    Rosta

    STORE
         <ext:Store 
                ID="Store1" 
                runat="server"  
                AutoSync="false"   
                IDMode="Explicit"                           
                DataSourceID="SqlDataSource1" 
                ShowWarningOnFailure="false"                         
                PageSize="350"   
                Buffered="True"
                 >
                <Model>
                    <ext:Model ID="Model1" runat="server" IDProperty="idInfo" Name="Infopanel">
                        <Fields>
                            <ext:ModelField Name="idInfo" Type="Int" />
                            <ext:ModelField Name="idOkruh"  />
                            <ext:ModelField Name="spojeneid"  />
                             <ext:ModelField Name="PopisOkruh" Type="String"  />
                            <ext:ModelField Name="idUserZalozil" />
                            <ext:ModelField Name="UzivJmenoZalozil" Type="String" />                        
                            <ext:ModelField Name="DatumOdeslani" Type="Date"  />                                                
                            <ext:ModelField Name="Informace" Type="String" />
                           
                        </Fields>
                    </ext:Model>
                </Model>
                
                <Sorters>
                    <ext:DataSorter Property="idInfo" Direction="DESC" />
                </Sorters> 
                <Listeners>
                    <Exception Handler="Ext.Msg.alert('Operation failed', operation.getError());" />                
                  <%--  <Write Handler="Ext.Msg.alert('Write', 'Ulo?eno do datab?ze');" />--%>
                    <%--<Remove  Handler="Ext.Msg.alert('V?maz', 'Z?znam odstraněn');" />--%>                                                                         
                </Listeners>
            </ext:Store>
    GRID
                         <ext:GridPanel    
                                ID="GridPanelEdit" 
                                Name="GridPanelEdit" 
                                 MarginSpec="5 5 5 5"
                                runat="server"  
                                 Flex="5"
                                StoreID="Store1" 
                                Border="True"                                                                                
                               Region="West"
                                 IDMode="Explicit"  >                                   
                                <ColumnModel ID="ColumnModel1" runat="server"  >
                                    <Columns>                                                                 
                                        <ext:Column ID="ColIdZav" runat="server" DataIndex="idInfo" Text="" width="0"  
                                                         Visible="false">                                      
                                        </ext:Column>                                   
                                        <ext:Column ID="ColCisOkruh" runat="server" DataIndex="spojeneid" Text="OkruhID" flex="1">
                                             <HeaderItems>
                                                    <ext:TextField
                                                        ID="spojeneidFiltr" 
                                                        runat="server"
                                                        Icon="Magnifier"
                                                        TriggerAction="All"
                                                        QueryMode="Local"
                                                        DisplayField="spojeneid"
                                                        ValueField="spojeneid"
                                                         IDMode="Explicit">
                                                        <Listeners>
                                                            <Change Handler="applyFilter(this);" Buffer="250" />
                                                        </Listeners>     
                                                        <Plugins>
                                                            <ext:ClearButton runat="server" />
                                                        </Plugins>
                                                    </ext:TextField>
                                                </HeaderItems>       
                                             </ext:Column>
                                        <ext:Column ID="ColNazevOkruh" runat="server" DataIndex="PopisOkruh" Text="Okruh" flex="1">
                                             <HeaderItems>
                                                    <ext:TextField
                                                        ID="PopisOkruhFiltr" 
                                                        runat="server"
                                                        Icon="Magnifier"
                                                        TriggerAction="All"
                                                        QueryMode="Local"
                                                        DisplayField="PopisOkruh"
                                                        ValueField="PopisOkruh"
                                                         IDMode="Explicit">
                                                        <Listeners>
                                                            <Change Handler="applyFilter(this);" Buffer="250" />
                                                        </Listeners>     
                                                        <Plugins>
                                                            <ext:ClearButton runat="server" />
                                                        </Plugins>
                                                    </ext:TextField>
                                                </HeaderItems>       
                                             </ext:Column>                                                                        
                                        <ext:Column ID="ColUser" runat="server" DataIndex="UzivJmenoZalozil" Text="Zalozil" flex="1">   
                                             <HeaderItems>
                                                    <ext:TextField
                                                        ID="UzivJmenoZalozilFiltr" 
                                                        runat="server"
                                                        Icon="Magnifier"
                                                        TriggerAction="All"
                                                        QueryMode="Local"
                                                        DisplayField="UzivJmenoZalozil"
                                                        ValueField="UzivJmenoZalozil"
                                                         IDMode="Explicit">
                                                        <Listeners>
                                                            <Change Handler="applyFilter(this);" Buffer="250" />
                                                        </Listeners>     
                                                        <Plugins>
                                                            <ext:ClearButton runat="server" />
                                                        </Plugins>
                                                    </ext:TextField>
                                                </HeaderItems>                                                                               
                                        </ext:Column>                                                                       
                                        <ext:DateColumn ID="Column4" runat="server" DataIndex="DatumOdeslani" Text="Datum" flex="1"  > 
                                             <HeaderItems>
                                                   <ext:DateField 
                                                        ID="DatumOdeslaniFiltr" 
                                                        runat="server"
                                                        Icon="Magnifier"
                                                        TriggerAction="All"
                                                        QueryMode="Local"
                                                        DisplayField="DatumOdeslani"
                                                        ValueField="DatumOdeslani"
                                                        IDMode="Explicit">
                                                        <Listeners>
                                                            <Change Handler="applyFilter(this);" Buffer="250" />
                                                        </Listeners>     
                                                        <Plugins>
                                                            <ext:ClearButton runat="server" />
                                                        </Plugins>
                                                    </ext:DateField>
                                                </HeaderItems>                                                                   
                                        </ext:DateColumn>                                                                                                     
                                    </Columns>
                                </ColumnModel> 
                                                      
                                <SelectionModel>                                
                                    <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" 
                                                      Mode="Single">                                                                                                                                 
                                   </ext:RowSelectionModel>                            
                                </SelectionModel>    
                                <BottomBar>
                                        <ext:PagingToolbar 
                                            runat="server"                      
                                            DisplayInfo="true" 
                                            DisplayMsg="Počet info {0} - {1} z {2}" 
                                            EmptyMsg="Pr?zdn?" />                                           
                                </BottomBar> 
                                <Plugins>
                                    <ext:BufferedRenderer runat="server" />
                                </Plugins> 
                                                  
                            </ext:GridPanel>
    Last edited by Daniil; Jul 30, 2014 at 8:59 AM. Reason: Please use [CODE] tags
  7. #7
    Well, I have nothing to add. You should not bind all data to the store.
    You need to extract from DB only required portion of data (which will be displayed in the grid)

    Example of remote paging can be found here
    https://examples2.ext.net/#/GridPane..._Sorting/Page/
    Vladimir Shcheglov
    Sr. Developer
  8. #8
    We had the same issues with our data and found a solution that seems to work well for us. We use a lot of entity framework and if we did it as the example code shows we would have to return our entire list and then filter it. Ok it's good for the client but bad for the server since you need to return all those rows. In one case we have 40 million rows so that wouldn't work for us.


    We created a new generic method that uses IQueryable. The only requirements is that you have at least 1 ORDER BY in order to perform at take limit.


    We also used some extension methods we found that allows you to add dynamic conditions to the where clause allowing full control of the created sql statement before you process it.


    Hope it works for you, best part about this is it's all using generic objects.. :)


       /// <summary>
            /// Generic Filter Header using IQueryable for faster results.
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="start"></param>
            /// <param name="limit"></param>
            /// <param name="sort"></param>
            /// <param name="filter"></param>
            /// <param name="data"></param>
            /// <param name="count"></param>
            /// <returns></returns>
            public static List<T> ListFilterHeader<T>(int start, int limit, DataSorter[] sort, string filter, IQueryable<T> data, out int count) where T : class
            {
                //-- start filtering -----------------------------------------------------------
                FilterHeaderConditions fhc = new FilterHeaderConditions(filter);
    
    
                foreach (FilterHeaderCondition condition in fhc.Conditions)
                {
                    string dataIndex = condition.DataIndex;
                    FilterType type = condition.Type;
                    string op = condition.Operator;
                    object value = null;
    
    
                    switch (condition.Type)
                    {
                        case FilterType.String:
                            value = condition.Value<string>();
                            data = data.AddContainsCondition(dataIndex, value);
                            break;
                        case FilterType.Date:
                            value = condition.Value<DateTime>();
                            data = data.AddEqualCondition(dataIndex, value);
                            break;
                        case FilterType.Numeric:
                            value = condition.Value<string>();
                            data = data.AddContainsCondition(dataIndex, value);
                            break;
                        default:
                            throw new ArgumentOutOfRangeException();
                    }
       
                }
                //-- end filtering ------------------------------------------------------------
    
    
    
    
                //-- start sorting ------------------------------------------------------------
                if (sort.Length > 0)
                {
                    int counter = 0;
                    foreach (DataSorter s in sort)
                    {
                        data = data.AddOrderByCondition(s.Property, s.Direction.ToString(), counter);
                        counter++;
                    }
                }
                else
                {
                    throw new Exception("At least 1 data sort is required to use this function!");
                }
                //-- end sorting ------------------------------------------------------------
    
    
                count = data.Count();
    
    
                //-- start paging ------------------------------------------------------------
                data = data.Skip(start).Take(limit);
                //-- end paging ------------------------------------------------------------
    
    
    
    
                return data.ToList();
            }
    
    //Extension Methods....
    
                public static IQueryable<T> AddEqualCondition<T, V>(this IQueryable<T> queryable, string propertyName, V propertyValue)
                {
    
    
                        ParameterExpression pe = Expression.Parameter(typeof(T), "p");
                        var me = Expression.Property(pe, typeof(T).GetProperty(propertyName));
                        var ce = Expression.Constant(propertyValue, typeof(string));
                        MethodInfo method = typeof(string).GetMethod("Equal", new[] { typeof(string) });
                        var call = Expression.Call(ce, method, me);
                        var lambda = Expression.Lambda<Func<T, bool>>(call, pe);
    
    
                        IQueryable<T> x = queryable.Where<T>(lambda);
                        return (x);
                }
    
    
                public static IQueryable<T> AddContainsCondition<T, V>(this IQueryable<T> queryable, string propertyName, V propertyValue)
                {
                        ParameterExpression _pe = Expression.Parameter(typeof(T), "p");
                        var _columnExpression = Expression.Property(_pe, typeof(T).GetProperty(propertyName));
                        var _valueExpression = Expression.Constant(propertyValue.ToString().ToLower(), typeof(string));
                        MethodInfo _methodToLower = typeof(string).GetMethod("ToLower", System.Type.EmptyTypes);
                        Expression _firstCall = Expression.Call(_columnExpression, _methodToLower);
                        MethodInfo _methodContains = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                        Expression _secondCall = Expression.Call(_firstCall, _methodContains, _valueExpression);
                        
                        var _lambda = Expression.Lambda<Func<T, bool>>(_secondCall, _pe);
                        IQueryable<T> x = queryable.Where<T>(_lambda);
                        return (x);
                }
    
    
    
    
                public static IQueryable<T> AddOrderByCondition<T>(this IQueryable<T> queryable, string ordering, string sortDirection, int count)
                {
                    var type = typeof(T);
                    var property = type.GetProperty(ordering);
                    var parameter = Expression.Parameter(type, "p");
                    var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                    var orderByExp = Expression.Lambda(propertyAccess, parameter);
                    string direction;
                    if (count == 0)
                    {
                        direction = (sortDirection == "ASC") ? "OrderBy" : "OrderByDescending";
                    }
                    else
                    {
                        direction = (sortDirection == "ASC") ? "ThenBy" : "ThenByDescending";
                    }
                    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), direction, new Type[] { type, property.PropertyType }, queryable.Expression, Expression.Quote(orderByExp));
                    return queryable.Provider.CreateQuery<T>(resultExp);
                }
    Last edited by ljankowski; Aug 01, 2014 at 3:56 PM.
  9. #9
    Yes, the same approach is demonstrated in already mentioned example
    https://examples2.ext.net/#/GridPane..._Sorting/Page/

    See EntitiesAdditions.cs file (in Source Code window), GetEmployeesFilter method
    Vladimir Shcheglov
    Sr. Developer
  10. #10
    Hi,
    thanks both. I will try it to change according your advices

    Regards

    Rosta

Similar Threads

  1. Replies: 0
    Last Post: Apr 19, 2012, 8:15 PM
  2. [CLOSED] Thousands separator
    By rnfigueira in forum 1.x Legacy Premium Help
    Replies: 1
    Last Post: Apr 19, 2011, 7:07 PM
  3. GridPanel loading more than 4.000 rows at once
    By paul-2011 in forum 1.x Help
    Replies: 2
    Last Post: Jan 18, 2011, 8:25 PM
  4. [CLOSED] Thousands separator in numberfield
    By 78fede78 in forum 1.x Legacy Premium Help
    Replies: 1
    Last Post: Nov 08, 2010, 12:50 PM
  5. Replies: 3
    Last Post: Aug 04, 2009, 7:18 PM

Posting Permissions