Codejock Forums Homepage
Forum Home Forum Home > Codejock Products > ActiveX COM > Report Control
  New Posts New Posts RSS Feed - SOLVED: 100000 records or more!
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

SOLVED: 100000 records or more!

 Post Reply Post Reply
Author
Message
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post Topic: SOLVED: 100000 records or more!
    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
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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....
Back to Top
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post 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
 
 
 
Thanks in advance.
 
Carlos Neves
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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:

  1. load a database table into the reportcontrol (2 different ways, DAO and ADO)
  2. column names will be loaded from the table field names.
  3. show properties of the selected record.
  4. filter on selected column with entered filter text (not case sensitiv)
  5. group selected column (and have still a filter as in 4.)
  6. 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

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....
Back to Top
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post 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

Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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....
Back to Top
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post 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

 

Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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....
Back to Top
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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....
Back to Top
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post 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.

uploads/20080602_045028_PFact.zip

Thanks for your kind help.

Carlos Neves
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post 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:
  1. Sort columns with filter
  2. Inplace editing on subitems (which are directly updated in the database table)
  3. Get properties of selected record of database table
  4. Access database directly
  5. 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...
 
 
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....
Back to Top
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post Posted: 03 June 2008 at 4:44am
 Sorry, bad zip (forget to remove some references).
 
Thanks.
Carlos
Back to Top
Peter59 View Drop Down
Groupie
Groupie


Joined: 19 July 2007
Status: Offline
Points: 61
Post Options Post Options   Thanks (0) Thanks(0)   Quote Peter59 Quote  Post ReplyReply Direct Link To This Post 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

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 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
Back to Top
ElvisM View Drop Down
Senior Member
Senior Member
Avatar

Joined: 12 October 2006
Location: Venezuela
Status: Offline
Points: 129
Post Options Post Options   Thanks (0) Thanks(0)   Quote ElvisM Quote  Post ReplyReply Direct Link To This Post 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.
 
 
Back to Top
Peter59 View Drop Down
Groupie
Groupie


Joined: 19 July 2007
Status: Offline
Points: 61
Post Options Post Options   Thanks (0) Thanks(0)   Quote Peter59 Quote  Post ReplyReply Direct Link To This Post 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


Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post Posted: 10 June 2008 at 1:25pm
Originally posted by Peter59 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....
Back to Top
Baldur View Drop Down
Senior Member
Senior Member


Joined: 22 November 2006
Location: Germany
Status: Offline
Points: 244
Post Options Post Options   Thanks (0) Thanks(0)   Quote Baldur Quote  Post ReplyReply Direct Link To This Post Posted: 07 July 2008 at 9:55am
For DoubleClick you can use RowDblClick-Event.
Back to Top
Peter59 View Drop Down
Groupie
Groupie


Joined: 19 July 2007
Status: Offline
Points: 61
Post Options Post Options   Thanks (0) Thanks(0)   Quote Peter59 Quote  Post ReplyReply Direct Link To This Post Posted: 10 July 2008 at 3:30am
Originally posted by Baldur 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.


Back to Top
Source View Drop Down
Senior Member
Senior Member


Joined: 19 June 2006
Status: Offline
Points: 103
Post Options Post Options   Thanks (0) Thanks(0)   Quote Source Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
ruffneck9876 View Drop Down
Newbie
Newbie


Joined: 20 November 2008
Status: Offline
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote ruffneck9876 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.04
Copyright ©2001-2021 Web Wiz Ltd.

This page was generated in 0.234 seconds.