Loading Data Into Grid Lasts Too Much

  • Thread starter Diego Armando Maradona
  • Start date
D

Diego Armando Maradona

Hello,

I have a DEVEXPRESS grid on my form

When I try to load data into the form,

If row count is huge (this example : 45000 row is returned, column count =~
10)

This line waits for a long time to load data into grid

grdMats.DataMember = "MATS"; // THIS LINE WAITS FOR 1 MINUTE TO LOAD
ALL DATA INTO DEVEXRESS GRID

How can it be faster ?

My Code is there;



private void ReLoadData()
{
using (SqlConnection ConnLocal = new
SqlConnection(Program.GetSQLConnectionString()))
{
ConnLocal.Open();

string FilterKeyword = edSearch.Text;

FilterKeyword = FilterKeyword.Replace("*", "%");

using (SqlCommand CommLocal = ConnLocal.CreateCommand())
{
CommLocal.CommandText += "SELECT * ";
CommLocal.CommandText += "FROM PR_MATS MATS ";
CommLocal.CommandText += "WHERE ( (MATS.MCODE LIKE '" +
FilterKeyword + "%') OR (MATS.MDEFN LIKE '" + FilterKeyword + "') ) ";
CommLocal.CommandText += "ORDER BY MATS.R_MODATE DESC
";

using (SqlDataAdapter adLocal = new
SqlDataAdapter(CommLocal))
{
DataSet tbLocal = new DataSet();

int MatsCount = adLocal.Fill(tbLocal, "MATS"); //
MATSCOUNT = 45000, This line lasts 2 second.

int MatsRow = vvMats.FocusedRowHandle;

grdMats.DataSource = tbLocal;

grdMats.DataMember = "MATS"; // THIS LINE
WAITS FOR MORE THAN 1 MINUTE TO LOAD ALL DATA INTO DEVEXRESS GRID

vvMats.FocusedRowHandle = MatsRow;

vvMats.BestFitColumns();
}
}

if (ConnLocal.State != ConnectionState.Closed)
{
ConnLocal.Close();
}
}
}
 
M

Marc Gravell

That sounds like a question for "devexpress" support, as it isn't code
C#/CLR...

However, one thing to look for is "virtual mode"; this allows much greater
performance on large lists. Some list/grid controls offer a virtual mode,
some don't. DataGridView does, for example.

Marc
 
L

Lasse Vågsæther Karlsen

Diego said:
Hello,

I have a DEVEXPRESS grid on my form

When I try to load data into the form,

If row count is huge (this example : 45000 row is returned, column count
=~ 10)

This line waits for a long time to load data into grid

grdMats.DataMember = "MATS"; // THIS LINE WAITS FOR 1 MINUTE TO
LOAD ALL DATA INTO DEVEXRESS GRID

How can it be faster ?

My Code is there;



private void ReLoadData()
{
using (SqlConnection ConnLocal = new
SqlConnection(Program.GetSQLConnectionString()))
{
ConnLocal.Open();

string FilterKeyword = edSearch.Text;

FilterKeyword = FilterKeyword.Replace("*", "%");

using (SqlCommand CommLocal = ConnLocal.CreateCommand())
{
CommLocal.CommandText += "SELECT * ";
CommLocal.CommandText += "FROM PR_MATS MATS ";
CommLocal.CommandText += "WHERE ( (MATS.MCODE LIKE '"
+ FilterKeyword + "%') OR (MATS.MDEFN LIKE '" + FilterKeyword + "') ) ";
CommLocal.CommandText += "ORDER BY MATS.R_MODATE DESC ";

using (SqlDataAdapter adLocal = new
SqlDataAdapter(CommLocal))
{
DataSet tbLocal = new DataSet();

int MatsCount = adLocal.Fill(tbLocal, "MATS");
// MATSCOUNT = 45000, This line lasts 2 second.

int MatsRow = vvMats.FocusedRowHandle;

grdMats.DataSource = tbLocal;

grdMats.DataMember = "MATS"; // THIS LINE
WAITS FOR MORE THAN 1 MINUTE TO LOAD ALL DATA INTO DEVEXRESS GRID

vvMats.FocusedRowHandle = MatsRow;

vvMats.BestFitColumns();
}
}

if (ConnLocal.State != ConnectionState.Closed)
{
ConnLocal.Close();
}
}
}
Click to expand...

Why exactly are you loading 45000 rows into the grid?
Wouldn't it be better to provide the user with a search system to
pinpoint the rows the user is interested in?
 
D

Diego Armando Maradona

There is a pin system, but lets say someone enters "4" into search box and
push search button, there are 45000 records starts with "4", I can prevent
entereing one chars, allowing minimum 2 chars for searching, but its not
fari I think

may I use paging, working on paging now :)
 
J

Jon Skeet [C# MVP]

Diego Armando Maradona said:
I have a DEVEXPRESS grid on my form

When I try to load data into the form,

If row count is huge (this example : 45000 row is returned, column count =~
10)

This line waits for a long time to load data into grid

grdMats.DataMember = "MATS"; // THIS LINE WAITS FOR 1 MINUTE TO LOAD
ALL DATA INTO DEVEXRESS GRID

How can it be faster ?
Click to expand...

Well, you could ask one of your users to enter some text which would
launch a SQL injection attack and drop all the data. It'd be really
fast after that :)

(Seriously, use parameterised SQL rather than putting user input
directly into SQL statements.)

As others, I doubt that *actually* displaying 45000 rows is a good
idea. Limit the display to (say) 500 and only fetch that many. Perhaps
fetch 501, display 500, and if 501 rows are returned indicate that
there's more data that you haven't displayed. That's a pretty quick way
to educate the user into searching more thoroughly.
 
P

Paul E Collins

Diego Armando Maradona said:
There is a pin system, but lets say someone enters "4" into search box
and push search button, there are 45000 records starts with "4", I can
prevent entereing one chars, allowing minimum 2 chars for searching,
but its not fari I think
Click to expand...

Use SQL like "SELECT TOP 100" and tell the user, "Stopped after 100
results. Type a long search string if you want more specific results."

No human being wants to see 45,000 records.

Eq.
 
Top