![]() |
SOLVED: 100000 records or more! |
Post Reply ![]() |
Author | |
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() 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 |
|
![]() |
|
Aaron ![]() Senior Member ![]() Joined: 29 January 2008 Status: Offline Points: 2192 |
![]() ![]() ![]() ![]() ![]() |
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.... |
|
![]() |
|
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() |
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
|
|
![]() |
|
Aaron ![]() Senior Member ![]() Joined: 29 January 2008 Status: Offline Points: 2192 |
![]() ![]() ![]() ![]() ![]() |
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:
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.... |
|
![]() |
|
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() |
Thanks Aaron. Apreciate you help. Regards. Carlos |
|
![]() |
|
Aaron ![]() Senior Member ![]() Joined: 29 January 2008 Status: Offline Points: 2192 |
![]() ![]() ![]() ![]() ![]() |
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.... |
|
![]() |
|
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() |
Hi, Tested with full data with 193000 records. Option 1 - 22042,3648177698 Nice counter :-) Think it is a bit higher! If databinding was possible it will be an instant - or not? Can I ask another thing? 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!!! Carlos Neves
|
|
![]() |
|
Aaron ![]() Senior Member ![]() Joined: 29 January 2008 Status: Offline Points: 2192 |
![]() ![]() ![]() ![]() ![]() |
Hi,
There's a big difference between option 1 and 2
![]()
|
|
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.... |
|
![]() |
|
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() |
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 |
|
![]() |
|
Aaron ![]() Senior Member ![]() Joined: 29 January 2008 Status: Offline Points: 2192 |
![]() ![]() ![]() ![]() ![]() |
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.... |
|
![]() |
|
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() |
Hi Aaron, Please find attached file with example of what I need. Carlos Neves |
|
![]() |
|
Aaron ![]() Senior Member ![]() Joined: 29 January 2008 Status: Offline Points: 2192 |
![]() ![]() ![]() ![]() ![]() |
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:
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.... |
|
![]() |
|
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() |
![]() Here it is corrected uploads/20080603_044350_PFact.zip Thanks.
Carlos
|
|
![]() |
|
Peter59 ![]() Groupie ![]() Joined: 19 July 2007 Status: Offline Points: 61 |
![]() ![]() ![]() ![]() ![]() |
Hi,
![]() uploads/20080610_111458_080610_Test_Rep.zip ![]() ![]() In addition to Aarons sample, I added the following options/features:
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 |
|
![]() |
|
ElvisM ![]() Senior Member ![]() ![]() Joined: 12 October 2006 Location: Venezuela Status: Offline Points: 129 |
![]() ![]() ![]() ![]() ![]() |
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.
|
|
![]() |
|
Peter59 ![]() Groupie ![]() Joined: 19 July 2007 Status: Offline Points: 61 |
![]() ![]() ![]() ![]() ![]() |
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 |
|
![]() |
|
Aaron ![]() Senior Member ![]() Joined: 29 January 2008 Status: Offline Points: 2192 |
![]() ![]() ![]() ![]() ![]() |
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
![]() 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.... |
|
![]() |
|
Baldur ![]() Senior Member ![]() Joined: 22 November 2006 Location: Germany Status: Offline Points: 244 |
![]() ![]() ![]() ![]() ![]() |
For DoubleClick you can use RowDblClick-Event.
|
|
![]() |
|
Peter59 ![]() Groupie ![]() Joined: 19 July 2007 Status: Offline Points: 61 |
![]() ![]() ![]() ![]() ![]() |
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. |
|
![]() |
|
Source ![]() Senior Member ![]() Joined: 19 June 2006 Status: Offline Points: 103 |
![]() ![]() ![]() ![]() ![]() |
First. Thank you ALL. 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 |
|
![]() |
|
ruffneck9876 ![]() Newbie ![]() Joined: 20 November 2008 Status: Offline Points: 1 |
![]() ![]() ![]() ![]() ![]() |
"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
|
|
![]() |
Post Reply ![]() |
|
Tweet
|
Forum Jump | Forum Permissions ![]() You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |