Loading grid with 200 records takes 70 seconds

Nov 23, 2009 at 7:52 PM
Edited Nov 23, 2009 at 7:54 PM

My utlility uses about 10 tables and forms. When I attempt to load about 200 records into one of the grids for displaying, it takes just over 70 seconds. I will eventually have some tables with thousands of records. So, I am concerned about the loading times.

My VB.NET function LoadGridFromTable reads the data from a ADODB.Recordset and sends each record to the function AddRow().

Function AddRow()

Grid1.Redim(iRow + 1, ColumnCount)

Grid1(iRow, 0)=

New SourceGrid.Cells.Cell(iCallTypeCode, GetType(Integer))

Grid1(iRow, 1)=

New SourceGrid.Cells.Cell(iSubType, GetType(Integer))

Grid1(iRow, 2)=

New SourceGrid.Cells.Cell(sDestPattern, GetType(String))

Grid1(iRow, 3)=New SourceGrid.Cells.Cell(sDestPattern, GetType(String))<font size="2"> New DestPattern_ValueChangedEvent(Grid1))

Grid1(iRow, 3).AddController(

Grid1(iRow, 3).View=tmpViewLeft

... There are 18 columns in this grid with varying complexity

I noticed that in the beginning of the 212 record load, it loads the rows at about 10 records per second, but by the end of the 200 records it is at about 10 records per 6 seconds. So, I am wondering if there is a way to disable the grid refresh until all of the records are loaded or if there is another manner I should be utilitizing in populating the grid.

I have really enjoyed working with this grid control and do use ColumnSpanning and RowSpanning, changing background colors, and some other great features. I do have several eventhandler implemented and it has been a great tool to do custom work behind the scene.

Thanks,

Cary H.

 

Coordinator
Nov 24, 2009 at 3:46 PM

Try using the latest version. SourceGrid 4.21 had some severe problems with performance.  Please tell if it will work

Nov 24, 2009 at 4:34 PM

Thanks for the suggestion. I was originally using 4.21.3441.17573.  Yesterday, I downloaded the version 4.22.3606.15103, but didn't notice an improvement in performance.

I removed different features in my AddRows function to isolate the cause and found that the Grid1.AutoSizeCells() function is the cause for the delay. When I removed that call then it all loads in 2 seconds, versus 75 seconds. I only have three columns that will contain text of varying lengths, so I will work on that issue more directly.

Thanks for moderating.

 

 

Nov 25, 2009 at 2:16 AM

I have test ADODB (COM+)... and i think the problem is from ADODB.. try using ADO.NET, is better.

What Database Engine are you using?

Nov 25, 2009 at 2:18 AM

Yes, definitly is ADODB problem, when the recordcount increases the speed decrease significallly With ADODB. ..

 

Coordinator
Nov 25, 2009 at 9:08 AM

2 seconds for 200 rows is still very very long time.  AFAIK, it should be at least 10k-20k rows per second

Dec 7, 2009 at 10:02 PM
Edited Dec 7, 2009 at 10:03 PM
Thanks for your tips. I did create a test project and used this code to extract the data from my SQL Server. 
This seems to be as clean as I can get it. I have over 3 Mil records in this particular table. 
When I selected 1000 records, I average 3.4 seconds per thousand. 
I was running this method next to our standard ADODB logic and they continue to be pretty much the same times. 
Now, my database is not local to my WinXP system, so I am not sure if that is affecting these numbers or not.
	Using objConnection As New SqlConnection
                objConnection.ConnectionString = "Persist Security Info=False;Integrated Security=true;Initial Catalog=IFWBOA;server=SQUIRREL"
                objConnection.Open()
                Dim objCommand As New SqlCommand(String.Format("SELECT top {0} * FROM Processed", SampleSize))
                objCommand.Connection = objConnection
                Dim objReader As SqlDataReader = objCommand.ExecuteReader()
                Dim RowCnt As Integer = 1
                Grid1.Redim((SampleSize) + 1, ColCnt)
                Do While objReader.Read()
                    Grid1(RowCnt, 0) = New SourceGrid.Cells.Cell(varToStr(RowCnt.ToString), GetType(String))
                    For i As Integer = 1 To ColCnt - 1
                        Grid1(RowCnt, i) = New SourceGrid.Cells.Cell(varToStr(objReader.Item(i).ToString), GetType(String))
                    Next
                    RowCnt += 1
                Loop
                TotalRows = RowCnt
            End Using
Dec 8, 2009 at 12:45 AM

Try This: Im Already using something similar and its works really fast... but i have not test your example...
Maybe you are using the codeplex code, you need to try the the bitbucket code, it newer.

Using
objConnection As New SqlConnection objConnection.ConnectionString = "Persist Security Info=False;Integrated Security=true;Initial Catalog=IFWBOA;server=SQUIRREL" objConnection.Open() Dim objCommand As New SqlCommand(String.Format("SELECT top {0} * FROM Processed", SampleSize)) objCommand.Connection = objConnection Dim objReader As SqlDataReader = objCommand.ExecuteReader() Dim RowCnt As Integer = 1 Grid1.Redim(0, 0) 'Add this line Grid1.ColumnsCount = 9 'Add this line REPLACE WITH THE COLUMN COUNT NUMBER 'Grid1.Redim((SampleSize) + 1, ColCnt) 'Comment this line Do While objReader.Read() Grid1.Rows.Insert(RowCnt) 'Add This Line Grid1(RowCnt, 0) = New SourceGrid.Cells.Cell(varToStr(RowCnt.ToString), GetType(String)) 'Try Comment this line too For i As Integer = 1 To ColCnt - 1 'Grid1(RowCnt, i) = New SourceGrid.Cells.Cell(varToStr(objReader.Item(i).ToString), GetType(String)) 'Comment This Line Grid1.Item(RowCnt, i) = New SourceGrid.Cells.Cell(objReader.Item(i).ToString) 'Add This Line Next RowCnt += 1 Loop TotalRows = RowCnt End Using

 

Dec 8, 2009 at 4:39 PM

Much Thanks Braian87b!! That little change cut my times in half.

I also tested using different quantities of columns. My forms might have between 2 and 15 columns, which obviously would affect load times.

While it isn't 1 second per 10,000 rows, it is much better than 10 seconds per 1,000.

2 cols x 10,000 rows = .6011/1,000 rows

5 cols x 10,000 rows = .6600/1,000 rows

15 cols x 10,000 rows = .9897/1,000 rows

It's almost 20 times faster than my initial code. Thanks again.

 

 

 

 

Coordinator
Dec 9, 2009 at 8:02 AM

locarvey, have you looked at examples in SourceGrid? Specifically, there is (probably) an example 53, which loads 1 millions rows into grid. On my computer it runs more or less 20k rows per second,

I would really love to increase performance even more

 

Dec 9, 2009 at 12:10 PM
Edited Dec 9, 2009 at 12:10 PM
locarvey wrote:

Much Thanks Braian87b!! That little change cut my times in half.

I also tested using different quantities of columns. My forms might have between 2 and 15 columns, which obviously would affect load times.

While it isn't 1 second per 10,000 rows, it is much better than 10 seconds per 1,000.

2 cols x 10,000 rows = .6011/1,000 rows

5 cols x 10,000 rows = .6600/1,000 rows

15 cols x 10,000 rows = .9897/1,000 rows

It's almost 20 times faster than my initial code. Thanks again.

Your Welcome... i recommend too, to try the example 53... since i have runned and its very fast, but i need to read the code too, i will do that, right now.

Dec 10, 2009 at 10:44 PM

I ran Example 53 on my computer and it takes 49,671ms to load the 1,000,000 rows. 20,000 rows per second = 50 seconds per 1,000,000 rows. So, this looks close.

I converted this project over to VB.NET and ran it on my system. It loaded 10,001 rows in 8,062ms.

Maybe my 2005 WinXP with 1GB RAM is part of the problem?

Dec 10, 2009 at 11:50 PM

Keep in mind: that values are generated. and if you run on your system, the Data Layer (ADO.Net, datareaders and Database) have their times too, if you count only putting data values from memory to a new cell the timing values not have to Change, inclusive with the differentiation of C Sharp Against VB.Net because both are compiled to the same Intermediate Language code. Also remember to use the Using clause with ADO.net Object because they have to be Disposed, the "End Using" is equals to do a "obj.Dispose : obj =Nothing" and is useful when you have middle breaks like Try-Catch or Returns. (so far i know because i read)

Im working too in a System, i have to solve yet problems like can move Columns and set a Specific datatype ( for numbers ordering for example and formatting), i have tried with the classic method of GetType and Editor, but i need a ReadOnly grid, and i dont know how to do that!! (except to giving the data alone haha), i have to read more about it and examine the example proyects.

I think we need to create together a project that contains a database and a couple of tables, without using xml, datatables or databinding, but represents something that is sturdy and classic enriched populate a table with lots of data, using DataReaders, always one or have to paint a cell of a certain color depending on the value, or add an icon for another value, etc.. What do you think?
We could implement things like general search or filter fields, and even with conditions depending on the type of data or ranges of values, etc.. Also to highlight words or cells, I tried to do this (to highlight a word within a cell and have not succeeded, I think I would need to do something more complex as overwrite the Draw event or something)

Coordinator
Dec 11, 2009 at 6:20 AM

Example project would be great. We could include that as part of oficial samples in SourceGrid.

I would suggest using some database, which would not require any installation (e.g. an embbedded database). I know that both SQLite and FireBird greatly support that.

Also, a decent ORM tool would help too, so people would not say then that hey, this example work great only because it uses that databse or whatever. I would suggest using FluentNHibernate and NHibernate for that. Both projects are very well known, and are open source, so we would have no problems including them.

Dec 17, 2009 at 4:19 AM
Edited Dec 17, 2009 at 8:48 AM

Darius: yes, of course... i prefer SQLlite, is almost equal to mysql, ( i use sql server and mysql). Firebird has some things that i dont like.

Is dificult to use NHibernate? is Useful?

I have mentioned to use the old-school method (manual filling using datareaders) because i use that method...
I dont know how to bind easily to a datatable preserving the posibility to format specific fields or join 2 fields
into one column of grid (String.Format("{0} x {1}", field1, field2)), or similar things etc (yes i know that i can use a view or
sql concat function or operator but i dont want to overload the server). That the reason.

i thing that is necesary to have a generic model that take in care a few important things:

Speed Loading: At least 50.000 rows (also nobody gonna scroll more than that)
Sorting
: keping in mind the datatypes
Formatting:
(by values, the cell caption, the cell view, another cell in the same row with an icon, etc.
              at least put a value on that cell and depending of the value represent or not the icon)
Search
: (setting the focus on cell or highligh the text caption inside the cell)
Filtering
: (hiding rows that not contain matches with the filtering terms)
>Excel Like Filtering: (A specific filter depending of the datatype and distinct elements of the chosen
               column for filtering, ex: "{column1} [greather than] '10' ", "{column2} [equals to] {cell(2,3).value} " )
              
this can be done using context menu and using pseudolanguage (in the example) for filtering or using the datasource, etc.
Dinamic Update: Update the Grid whiout have to refill all the grid, adding new rows, updating cell values on updated rows, and deleting rows deleted.
               (Like Outlook Express or Outlook when receiving mails, or Windows Explorer whens folder browsed has new files.)
Ability to do Massive Deletion (select more than one row and clic on delete for delete all)
Show Hierarquical-like data in some manner: (at least showing only in the cell caption in the first row of each group)

Example:

Category | SubCategory     | ThingName
Fruits        Apples Family     Apple
                                         Pear
                Others               Orange
                                         Peach
Objects     Living Room       Chair
                                         Table

Instead of:


Category | SubCategory     | ThingName
Fruits        Apples Family     Apple
Fruits        Apples Family     Pear
Fruits        Others               Orange
Fruits        Others               Peach
Objects     Living Room       Chair
Objects     Living Room       Table

Any other ideas are welcome!!!

 

 

 

 

Coordinator
Dec 17, 2009 at 5:02 AM
braian87b wrote:

Darius: yes, of course... i prefer SQLlite, is almost equal to mysql, ( i use sql server and mysql). Firebird has some things that i dont like.

Is dificult to use NHibernate? is Useful?

I have mentioned to use the old-school method (manual filling using datareaders) because i use that method...
I dont know how to bind easily to a datatable preserving the posibility to format specific fields or join 2 fields
into one column of grid (String.Format("{0} x {1}", field1, field2)), or similar things etc (yes i know that i can use a view or
sql concat function or operator but i dont want to overload the server). That the reason.

i thing that is necesary to have a generic model that take in care a few important things:

Speed Loading: At least 50.000 rows (also nobody gonna scroll more than that)
Sorting
: keping in mind the datatypes
Formatting:
(by values, the cell caption, the cell view, another cell in the same row with an icon, etc.
              at least put a value on that cell and depending of the value represent or not the icon)
Search
: (setting the focus on cell or highligh the text caption inside the cell)Filtering: (hiding rows that not contain matches with the filtering terms)
>Excel Like Filtering: (A specific filter depending of the datatype and distinct elements of the chosen
               column for filtering, ex: "{column1} [greather than] '10' ", "{column2} [equals to] {cell(2,3).value} " )
              
this can be done using context menu and using pseudolanguage (in the example) for filtering or using the datasource, etc.
Dinamic Update: Update the Grid whiout have to refill all the grid, adding new rows, updating cell values on updated rows, and deleting rows deleted.
               (Like Outlook Express or Outlook when receiving mails, or Windows Explorer whens folder browsed has new files.)
Ability to do Massive Deletion (select more than one row and clic on delete for delete all)
Show Jerarquical-like data in some manner: (at least showing only in the cell caption in the first row of each group)

Any other ideas are welcome!!!

 

 

 

 

SQLite does not have stored procedures, and MySQL has it only from 5.0. 

NHibernate is actually a MUST :) Really.  What it basically gives you is ability to work with you data like with your classes. Also - many caching possibilities, dynamic query generation, and databse independence. NHibernare supports MS SQL, Firebird, SQLite, MySQL, Postgre, and others.    Instead of using directly NHibernate, i suggest you to use either FluentNHibernate, or Castle.ActiveRecord. Choose whichever you want.

 

All functionality is possible with SourceGrid. When i worked in my previous company, i have made half of the things you mentioned.

 

What is Jerarquical-like data?

Dec 17, 2009 at 8:41 AM
Edited Dec 17, 2009 at 8:49 AM

"What is Jerarquical-like data?" >> Im SORRY! i mean: "hierarquical data"

Yes i know that SQLLite dont have stored procedures, but doesnt are necessary -i think- for this proyect (correct me if i wrong)

Then I will read about Nhibernate... But you mention also FluentNHibernate and Castle.ActiveRecord, i know Hibernate from Java and "by name" the ActiveRecord model, but i ve never used them

What are the reasons you saying those are better? im interested to know why for learn.

Thanks!

Coordinator
Dec 19, 2009 at 9:23 AM

FluentNH and Castle.ActiveRecord are not better. They simply provide a layer on top of NHibernate.  That layer will simply allow you to more easily work with NH, and much faster.

I would suggest going with FluentNH

Dec 19, 2009 at 2:48 PM

Im gonna read "Nhibernate in Action" book, and the Nhibernate documentation PDF in the next days. I have downloaded a few examples, but i dont understand how to use and configuring etc.

I will read then. Thanks for all!

 

Dec 19, 2009 at 3:20 PM

I will read then "NHibernate In Action" Book and the NHibernate Documentation PDF.

Thanks!