Report Control - SOLVED: 100000 records or more!

Print Page | Close Window

SOLVED: 100000 records or more!

Printed From: Codejock Forums
Category: Codejock Products
Forum Name: Report Control
Forum Description: Topics Related to Codejock Report Control
URL: http://forum.codejock.com/forum_posts.asp?TID=10814
Printed Date: 24 November 2024 at 3:47pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com

Topic: SOLVED: 100000 records or more!

Posted By: Source
Subject: SOLVED: 100000 records or more!
Date Posted: 28 May 2008 at 2:38pm

As previous told, CJ Activex controls are great. The best. Period.

Report control lacks the funcionality of databinding - or not!

Question is - come on guru guys:

How to handle huge amount of records in a table/database?

As an example zip codes records in a table - 100000 or more.

Special, speed on time to populate the report.

Search and other things we already now that is not possible in virtual mode - will it be?

I need to use this to manage output data from a measure machine, orders, etc...

Thanks in advance.

Carlos Neves

-------------
Product: Xtreme SuitePro (ActiveX) version 13.1.0
Platform: Windows XP (32bit) - SP 3
Language: Visual Basic 6.0

Replies:

Posted By: Aaron
Date Posted: 28 May 2008 at 11:03pm

Hi,

Do you load the table records only once and use the reportcontrol for seaching and filtering the records?

Can you provide the database table or if this isn't possible an example of just one record of that table? I will see what I can do.

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: Source
Date Posted: 29 May 2008 at 5:28am

Hi,

Database with zip codes example table attached.

Also attached an image with what I need to reproduce:

- Load ALL the records in the report control - or other good solution!

- Double click to edit the record

- Use the move record options; first, before, next and last

- Use new, save and cancel

- It will be great to use the search and group capabilities

http://forum.codejock.com/uploads/20080529_052659_datZipCodes.zip - uploads/20080529_052659_datZipCodes.zip

Thanks in advance.

Carlos Neves

Posted By: Aaron
Date Posted: 29 May 2008 at 12:30pm

Hi,

The attached zip file contains a sample project with a smaller database than the one you provided (30000 records). I have only 256 Kb internal memory on this laptop The name of the database stays the same, if you want to use your own database, just copy/paste it in the same directory as the project.

With this sample you can:

load a database table into the reportcontrol (2 different ways, DAO and ADO)
column names will be loaded from the table field names.
show properties of the selected record.
filter on selected column with entered filter text (not case sensitiv)
group selected column (and have still a filter as in 4.)
autosize columns on their contents.

If you have any questions, just let me know. Btw the database connection is maybe different than you normally do, but you can change that. I put in two options and it seems option 2 is just a little faster. Well check it out yourself. Good luck

https://forum.codejock.com/uploads/20080529_122702_Test_ReportData.zip - uploads/20080529_122702_Test_ReportData.zip

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: Source
Date Posted: 29 May 2008 at 1:07pm

Thanks Aaron.

Tested you example with a 193000 records table and took 9 seconds to load. Not bad! Good implementation.

Apreciate you help.

Regards.

Carlos

Posted By: Aaron
Date Posted: 29 May 2008 at 1:23pm

Hi,

What was the diference in speed between the two options on your machine? On mine it was about 0.3-0.4 seconds for 30.000 records.

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: Source
Date Posted: 30 May 2008 at 5:10am

Hi,

Tested with full data with 193000 records.

Option 1 - 22042,3648177698
Option 2 - 15750,8908617802

Nice counter :-)

Think it is a bit higher! If databinding was possible it will be an instant - or not?

Can I ask another thing?
After populate the report control i use double click to edit a specific record/row.

Is there any way that after the edit/save the record NOT to populate the WHOLE report again, but ONLY the previous selected record? The same for a NEW one; using row index or something like that!!!

I ask this because I do not want to "wait" for another 15sec to see only a refresh data for one record.

Thanks again in advance.

Carlos Neves

Posted By: Aaron
Date Posted: 30 May 2008 at 6:04am

Hi,

There's a big difference between option 1 and 2

The counter will start only in the Loading records function and before this all records / columns are deleted, Database connection is made and all this takes some time more, thats why you think it's higher.
It will always take time to load the records into the ReportControl, so instant is never the case
If you change something in the reportcontrol you have to call populate in order to see the changes. But this isn't taking that much time. If you change or add a record you only have to call populate and save ONLY the changed / added record into the database and you don't have to load the records from the database again because they are already updated in the reportcontrol.
Add a timer function to see how much time it takes to call .Populate (it's about 0,1 second for 30.000 in my case)
I noticed you have a dialog for changing / adding records, it is possible to have inplace editing on subitems as well, maybe an option?

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: Source
Date Posted: 30 May 2008 at 7:15am

Hi,

Can I prepare a test version (only with the zip codes) of the application that I have and see what you can do with it; just to test it in a "real environment"?

I have to go out to a client. Monday I will send you a zip file. OK?

Regards.
Carlos Neves

Posted By: Aaron
Date Posted: 30 May 2008 at 7:23am

Hi,

Yes, you can do that, I will see what I can do

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: Source
Date Posted: 02 June 2008 at 5:01am

Hi Aaron,

Please find attached file with example of what I need.

Read de readme.txt file - it has all the info and please use the database provided on the previous topic.

http://forum.codejock.com/uploads/20080602_045028_PFact.zip - uploads/20080602_045028_PFact.zip

Thanks for your kind help.

Carlos Neves

Posted By: Aaron
Date Posted: 02 June 2008 at 7:29am

Hi,

Maybe this is what you are looking for. It will be much faster since you are "connected" to the table in the database. All operations will be done on the recordset itself. I managed to make a sample project that will populate the ReportControl in virtual mode and allows you to:

Sort columns with filter
Inplace editing on subitems (which are directly updated in the database table)
Get properties of selected record of database table
Access database directly
Tip: you can change the SQLString as you wish (now it's in the code but you could "build" your own sql string if necessary)

Note, if you are going to test this, add a Field in your own database with the name: AutoNumber and set datatype to autonumber(this way you can see the sorting)

It looks like databinding, it's not finished yet but it's a start

Check it out yourself, maybe I gave you a jumpstart with this.

BTW, I tried to open your project but it gave a lot of errors.

But, maybe I don't need to open your project...

https://forum.codejock.com/uploads/20080602_072905_ReportVirtualDa.zip - uploads/20080602_072905_ReportVirtualDa.zip

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: Source
Date Posted: 03 June 2008 at 4:44am

Sorry, bad zip (forget to remove some references).

Here it is corrected http://forum.codejock.com/uploads/20080603_044350_PFact.zip - uploads/20080603_044350_PFact.zip

Thanks.

Carlos

Posted By: Peter59
Date Posted: 10 June 2008 at 11:17am

Hi,

if speed has first priority you can look at the attached sample: I added two new options. The first one ("Load from database 3") with ADO and the GetRows() method, the other uses the same method but with DAO ("Load from database 4").

uploads/20080610_111458_080610_Test_Rep.zip - uploads/20080610_111458_080610_Test_Rep.zip

I'm disappointed too that there is no DblClick() event for the ReportControl, but I needed it urgent, so I subclassed the ReportControl with the famous ssubtmr6.dll from vbAccelerator. Take a look in the attached files.

In addition to Aarons sample, I added the following options/features:

The button "Load from database 3" uses ADO to connect to the database and instead of iterating through the recordset with method MoveNext it uses GetRows to retrieve all records at a time and stores it in a two dimensional variant array. It depends on the count of records how much memory is used.
The button "Load from database 4" uses DAO to connect to the database and uses the GetRows method too, but not all records at a time but rather 1000 records after another
The Caption of a grouprow is extended by some information using the BeforeDrawRow event (see screenshot above)
To capture ReportControl DblClick events I use a subclassing method provided by http://www.vbAccelerator.com - vbAccelerator

Please keep in mind that I'm pretty new with the ReportControl, so any suggestion is welcome.

Best regards, Peter

Product: Xtreme SuitePro (ActiveX) version 11.2.2/12.0
Platform: Windows 2K/XP/Vista (32bit)
Language: Visual Basic 6.0 SP6

Posted By: ElvisM
Date Posted: 10 June 2008 at 11:25am

The problem with this method (getrows - variant arrays) is the limit in the phisical memory. If the recordset has a lot of records with many columns... the program could crash.

Posted By: Peter59
Date Posted: 10 June 2008 at 11:44am

Of course, the GetRows method needs a lot of memory, but under certain conditions ("if speed has first priority") or when recordcount is known within a range, it could be an alternative. Furthermore you can limit the need of memory by using the optional parameters of the GetRows method, such as 100 records at a time: varData = rs.GetRows(1000).

At least a good example for showing another way of populating ReportControl.

Peter

Posted By: Aaron
Date Posted: 10 June 2008 at 1:25pm

Peter59 wrote:

Hi,

if speed has first priority you can look at the attached sample: I added two new options. The first one ("Load from database 3") with ADO and the GetRows() method, the other uses the same method but with DAO ("Load from database 4").

Product: Xtreme SuitePro (ActiveX) version 11.2.2/12.0
Platform: Windows 2K/XP/Vista (32bit)
Language: Visual Basic 6.0 SP6

Peter,

Can you make a list of what is possible with your sample project? Like I did... This way you know what to expect from a sample

(ps you can edit your previous post with sample project)

Thanks

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: Baldur
Date Posted: 07 July 2008 at 9:55am

For DoubleClick you can use RowDblClick-Event.

Posted By: Peter59
Date Posted: 10 July 2008 at 3:30am

Baldur wrote:

For DoubleClick you can use RowDblClick-Event.

Hi Baldur,

of course you are right, but did you ever try to capture a DblClick in the group area? Long time ago I offered my users to doubleclick in the group area to collapse (or expand) all groups at a time - using the Janus grid with builtin DblClick event. No I want to switch to CJs ReportControl and certainly I do not want to degrade functionality because of ReportControl limitations.

Posted By: Source
Date Posted: 10 July 2008 at 7:07am

First. Thank you ALL.

After some consideration and tests (even tried with Janus Grid and TGrid) I will stick with CJ report control; always my first choice

My current application will use previous example with Peter59 LoadFromDatabase4 example with getrows(100) option.
193.000 records in 6 seconds.

Since this amount of records will only be used on 3 of 23+ or more tables I will (for now) stay with this option - until CJ guys implement some "direct access to database".

Grouping and filter on all records (filtertext) are ok - see Peter59 example and use filtertext option on a label for example.

Preview/Print as usual.

RowDblclick to edit the record on a subform with navigation buttons (previous,next,first,last) and new,save,cancel ones also.

Prevent RowDblclick on group header row - see CJ outlook2003 example (exapand or collapse the group).

So. I think this is ok for me right for now.

Regards.

Carlos Neves

-------------
Product: Xtreme SuitePro (ActiveX) version 13.1.0
Platform: Windows XP (32bit) - SP 3
Language: Visual Basic 6.0

Posted By: ruffneck9876
Date Posted: 20 November 2008 at 4:24am

"Hi Baldur,

of course you are right, but did you ever try to capture a DblClick in the group area? Long time ago I offered my users to doubleclick in the group area to collapse (or expand) all groups at a time - using the Janus grid with builtin DblClick event. No I want to switch to CJs ReportControl and certainly I do not want to degrade functionality because of ReportControl limitations. "

have you tried the activebar feature? using the active bar i can use the right mouse click to open a menu where i can select if i want to expand collapse or even see a print preview or generate HTML.

this can be activated on a groupcaption, header and if u just click anywhere in your form all depends on your settings.

activebar21 really helped me out look it up :P