PDA

View Full Version : [CLOSED] Loading thousands rows to Grid



Rosta
Jul 29, 2014, 11:27 AM
Hello

please could you help me with my problem ?

I have page similarly designed as in http://examples2.ext.net/#/GridPanel/Update/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

Vladimir
Jul 29, 2014, 11:39 AM
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

Rosta
Jul 29, 2014, 4:11 PM
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 (http://examples2.ext.net/#/GridPanel/MultiHeader/Filter/)

Thanks

Regards
Rosta

Vladimir
Jul 29, 2014, 5:13 PM
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
http://examples2.ext.net/#/GridPanel/FilterHeader/Remote/

So, remote paging with remote filtering and remote sorting should help you

Vladimir
Jul 29, 2014, 5:20 PM
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

Rosta
Jul 30, 2014, 8:13 AM
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>

Vladimir
Jul 30, 2014, 8:38 AM
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
http://examples2.ext.net/#/GridPanel/Paging_and_Sorting/Page/

ljankowski
Aug 01, 2014, 3:49 PM
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().ToLow er(), 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);
}

Vladimir
Aug 01, 2014, 4:01 PM
Yes, the same approach is demonstrated in already mentioned example
http://examples2.ext.net/#/GridPanel/Paging_and_Sorting/Page/

See EntitiesAdditions.cs file (in Source Code window), GetEmployeesFilter method

Rosta
Aug 08, 2014, 7:26 AM
Hi,
thanks both. I will try it to change according your advices

Regards

Rosta