dcsimg
Results 1 to 21 of 21

Thread: Retrieving huge records from the database

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    7

    Question Retrieving huge records from the database

    Hello everyone,,

    I have developed a program with FE- VB 6, BE - MS ACCESS (mdb).
    Connected using ADO

    Code:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Data.mdb;Jet OLEDB:Database Password=;"
    One of the table in my database contains nearly 30 thousand records with 13 columns.
    When i try to retrieve these(30 thousand records) to my Listview control, it takes around 16 seconds.

    So i tried to change my database to MS SQL SERVER 2005, but it takes more than 4 minutes.(I didn't expect that)
    I have also tried with ODBC connection but it remains same.

    I hope you understand my problem and is there any way to solve this (make it possible to load quicker),

    Note : yes i'm using low configuration system (Win 7, 2 GB RAM, 2 GHz Dual core processor) but i can't sure my clients will have high configuration system..

    and sorry for my bad bad English...

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,584

    Re: Retrieving huge records from the database

    Well the bulk of the slow down in the first case is likely the way you are putting the records into the listview, for example if the listview is set to visible when adding the items it can take a lot more time than if it is set to not visible during the process. IMO a listview is not a good choice for holding that many records. I would use a different approach.

    As for the slow down you are seeing with SQL Server I would suspect that it is due to not having previously opened the connection. Initial connection can take a little while with SQL Server, once the connection is made and open it is generally noticeably faster than MS Access connections.

    btw 30,000 records is not huge at all unless you are using blob fields for some of those columns. It is though IMO more than you should try to display in a listview.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,351

    Re: Retrieving huge records from the database

    Trying to display a vast number of rows usually isn't the best UI decision. Some sort of "query criteria input" to focus on items of interest usually makes more sense.

    But yes, in some cases people just want at least the option of "give me everything so I can scroll through it and try to find what I need by hand."

    On those occasions you are probably better off using some sort of virtual-list control that can page data from the server on demand. Many of these are smart enough to let you go to the last page, the first page, etc. without retrieving and discarding all of the rows in between but the relies on your choice of backing storage, usually a Recordset with the appropriate options set. There are several examples of using stock ListView controls in virtual mode in the CodeBank.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    7

    Re: Retrieving huge records from the database

    Thanks Datamiser,

    if the listview is set to visible when adding the items it can take a lot more time than if it is set to not visible
    I understand. that's why i called the public procedure from the another form before showing the form which contains the listview control.


    I would suspect that it is due to not having previously opened the connection
    really not.. I have opened the connection in the module (sub main) after that i called to retrieve records..
    I googled about this issue and someone told SQL Server 2000 version will be more faster than 2005 version because the older visual basic may be familiar with that older SQL server version. Even though this statement is true i think there is no Windows 7 compatible version of SQL Server 2000 ??

    and IMO means....sorry..

    Thanks again..

  5. #5

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    7

    Re: Retrieving huge records from the database

    Thanks Datamiser,

    if the listview is set to visible when adding the items it can take a lot more time than if it is set to not visible
    I understand. that's why i called the public procedure from the another form before showing the form which contains the listview control.


    I would suspect that it is due to not having previously opened the connection
    really not.. I have opened the connection in the module (sub main) after that i called to retrieve records..
    I googled about this issue and someone told SQL Server 2000 version will be more faster than 2005 version because the older visual basic may be familiar with that older SQL server version. Even though this statement is true i think there is no Windows 7 compatible version of SQL Server 2000 ??

    and IMO means....sorry..

    Thanks again..

  6. #6

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    7

    Re: Retrieving huge records from the database

    thanks dilettante,

    i found your article from
    http://www.vbforums.com/showthread.p...l-5-0-ListView

    i'll try this control.. thank you..

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,351

    Re: Retrieving huge records from the database

    It is important that you use a Recordset with the proper options for "paging" data. For example if you are using client static Recordsets you will still fetch 100% of the data before displaying anything.

    This can get tricky because I don't believe that the Recordset.PageSize controls what you might think it does.

    Instead you may have to set provider-specific properties. For Jet for example this may be the extended properties (in the Recordset.Properties collection) "Jet OLEDB:Enable Fat Cursors" and "Jet OLEDB:Fat Cursor Cache Size" and SQL Server will have its own variantions of these.

    I don't find any for SQL Server, but in both cases you may want to assign a value to Recordset.CacheSize. This should be used with a server-side cursor and should drastically reduce round-tripping when walking through large query result rowsets.

  8. #8
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    892

    Re: Retrieving huge records from the database

    30k rows by 13 columns is miniscule, so best IMO would be to just use rs.CursorLocation = adUseClient and leave it to ADO to open fire-hose cursor to the RDBMS. I'm doing this with lists with 100k+ of row with 100+ columns.

    A reasonable performance for fetching 30k rows from SQL Server and binding them to a grid control has to be less than 2-3 seconds and IMO is very much achievable.

    cheers,
    </wqw>

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Retrieving huge records from the database

    Quote Originally Posted by wqweto View Post
    A reasonable performance for fetching 30k rows from SQL Server and binding them to a grid control has to be less than 2-3 seconds and IMO is very much achievable.
    And even that is still about factor 10 too much.

    Since it's a problem which is (in these cases of "straight transfers of plain table-content") more bound to:
    "Data-Volume and the speed of the transfer-channel" ...

    DataVolume (rough calculation):
    - in case of 30k Rows * 13 Fields ~ 400k Fields ... and 400k Fields * 25 Bytes per Field ~ 10MBytes to transfer

    TransferChannel-speed:
    - in a 1GBit-LAN that's about 100MByte/sec - so the above 10MBytes should be transferable to the Client in ~ 0.1sec
    - on a local "magnetic hard-disk", the same IO-speed (about 100MByte/sec) can be assumed
    - from a local SSD, one might expect about 300MByte/sec transfer-speed (and about 30msec Transfertime instead of 100msec then).

    Now it entirely depends on the implementation of the "used Data-Container-Objects"
    (and their "build-up + filling and serializing-mechanisms") - how large of a time you will have to add
    to the above calculated "theoretical minimum-transfertime".

    With (clientside) ADO-Recordsets one will have to consider a "build-up-factor" of about 3 (the 100msec becoming 300msec).
    With SQLite-Recordsets, the build-up-factor is much lower (1.1 to 1.3).

    But that's (even in the worst case with ADO-Rs) still only about 300msec, until you have your "10MB-Recordset-Object, fully filled" available at the client.
    Now, if that Rs will be bound *virtually* to an appropriate List- or Grid-Control, the additional rendering-time is basically zero.

    So, 16sec (as in the OPs first reported case with a JET-MDB) is of course *way* too much -
    and also the 2-3sec are too much time for such a (still mid-sized) set (of 30K Rows and 13 Fields).

    Note, that the above calculations assumed an average of "25Bytes per Field" (which is normally Ok for the typical mix of Integer-, Double-, Date-, and "normal sized Text- DB-Fields").

    If a Table contains larger Field-Content (Image-Blobs, or larger Texts as e.g. HTML-snippets with 2K-10K per Field),
    then those Fields should perhaps be "left-out from the Selects" (later loaded "on demand" via the Record-ID) -
    or the Calculation should be re-done with a new "average bytes per field" (higher than what I've assumed).

    Olaf

  10. #10
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    892

    Re: Retrieving huge records from the database

    Quote Originally Posted by Schmidt View Post
    With (clientside) ADO-Recordsets one will have to consider a "build-up-factor" of about 3 (the 100msec becoming 300msec).
    With SQLite-Recordsets, the build-up-factor is much lower (1.1 to 1.3).
    Yes, and does RC5 offer server-side cursors over sqlite result-sets (incl. complex joins etc.)?

    I'm really interested in a sqlite scenario where *not* fetching the data into memory upfront is needed.

    cheers,
    </wqw>

  11. #11
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,435

    Re: Retrieving huge records from the database

    Hi,

    your Users sure have a lot of time to scroll threw and look at 30.000 records.

    is there a good point of loading the 30.000 records ?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    7

    Re: Retrieving huge records from the database

    sorry for the delay....

    i have tried lot of methods but no use.
    finally, i just splitted the 30k records by alphabets .. hmmm....

    thank you so much for your replies..

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Retrieving huge records from the database

    Quote Originally Posted by wqweto View Post
    ... does RC5 offer server-side cursors over sqlite result-sets (incl. complex joins etc.)?
    I work with distributed systems for over 20 years now, and can tell you with confidence:
    - serverside-cursors are the spawn of evil
    - dying kittens everywhere when you use them

    ... so, no - exposing the SQLite-statement-cursor to the outside was not really a priority to me.

    @the OP at this occasion...
    Serverside cursors are sadly the default on new created ADO Connection- and Rs-instances.
    This could well be the reason for your bad (especially SQLServer-) performance.
    You will have to set the CursorLocation explicitely to adUseClient in your VB-code...

    Quote Originally Posted by wqweto View Post
    I'm really interested in a sqlite scenario where *not* fetching the data into memory upfront is needed.
    Since any computer-op involves "memory-fetching", I'd really like to see
    an example of you (for a change), where you demonstrate with VB6-code:
    - a "serverside cursor in action"
    - using it to great advantage in a scenario of your choice

    Do your best and post it as a ZIP here ...
    I promise I'll do my best in turn, to convince you in a matching scenario -
    why serverside cursors were not needed in the first place...

    Olaf

  14. #14
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Retrieving huge records from the database

    Quote Originally Posted by ChrisE View Post
    Hi,

    your Users sure have a lot of time to scroll threw and look at 30.000 records.

    is there a good point of loading the 30.000 records ?
    As calculated above - it's not really that much memory allocated in such a 30k-Recordset.
    (about 1-20MB, depending on the Col-Count and Field-Sizes).

    It's simply convenient (as long as it is in that range below 50000 records or so),
    to be able to write a single line of implementation code:
    Set VirtualGrid.DataSource = GetRs("Select Name, ...other needed fields,... From Adresses Order By Name")

    The above will perform (for 30K Records) in about 50msec to 300msec.
    And many Customers are already satisfied, when they can parse through their
    larger Address-Lists by simply dragging the Scroller along (seeing the Names "fly by") -
    or by simply starting to type the first chars of a Name, whilst the List or Grid has the focus (as e.g. Explorer.exe does it with file-lists).

    Users often prefer that "direct interaction" -
    - leaving out "setting the focus to a dedicated search Field, then typing" -
    - even if such a field exists

    So, there's quite a few advantages in such an approach:
    - less coding-efforts
    - less Server-roundtrips (especially if your "main-list" does not change all that often).
    - intuitive and often preferred "fly-by-browsing" for the Users

    One may count a mem-allocation of 10MB as a disadvantage - but usually:
    - an App does not need to hold more than 2 or 3 of these larger "main-lists" at the clientside
    - and memory is "cheap" and available in abundance on todays machines
    The whole topic was a different story 20 years ago...

    Olaf

  15. #15
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    892

    Re: Retrieving huge records from the database

    Quote Originally Posted by Schmidt View Post
    - and memory is "cheap" and available in abundance on todays machines
    Indeed. Unfortunately VB6 apps cannot deal with ADODB recordsets of more than 2GB (a single one).

    I'm hitting this limitation when some of my clients exhibit pathological behavior -- first thing in the morning to load all invoiced documents for the last 10 year so they can filter these (relatively fast) with client-side filters.

    That's why I'm considering using rs.DataSource to feed the recordsets with sqlite "server-side" cursors. Probably will have to just fetch rowids client-side (if query is table based at all) and then impl paging on these rowids for the full records.

    I have a typelib with VB-ized necessary interfaces for the data-source impl somewhere in my toy projects (found it: oledbinterfaces.idl)
    Code:
        interface IRowPosition;
        interface IRowPositionChange;
        interface IRowset;
        interface IRowsetInfo;
        interface IRowsetNotify;
        interface IRowsetLocate;
        interface IRowsetScroll;
        interface IRowsetBookmark;
        interface IColumnsInfo;
        interface IAccessor;
        interface IRowsetView;
        interface IViewChapter;
        interface IViewFilter;
        interface IViewRowset;
        interface IViewSort;
        interface IConnectionPointContainer;
        interface IConnectionPoint;
        interface IADORecordsetConstructionUnrestricted;
        interface ISourcesRowset;
    cheers,
    </wqw>

  16. #16
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,435

    Re: Retrieving huge records from the database

    Quote Originally Posted by wqweto View Post
    Indeed. Unfortunately VB6 apps cannot deal with ADODB recordsets of more than 2GB (a single one).

    I'm hitting this limitation when some of my clients exhibit pathological behavior -- first thing in the morning to load all invoiced documents for the last 10 year so they can filter these (relatively fast) with client-side filters.


    cheers,
    </wqw>
    I create what I call "DummyTables"
    rather than loading the related TableData from Invoicing =Customer;Order;OrderDetails;Article;ShipInfo etc...
    for the Infomation.

    I execute another SQL(after saving the Order Process) to the "DummyTable" only with a few Values
    CustomerNo.
    OrderDate
    OrderAmount
    etc.. depending what you want

    the Table looks really meaningless, but avoids later large complex(4 to x Tables) Querys to get a result you want.


    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  17. #17
    Addicted Member
    Join Date
    Aug 2016
    Posts
    131

    Re: Retrieving huge records from the database

    Quote Originally Posted by wqweto View Post

    That's why I'm considering using rs.DataSource to feed the recordsets with sqlite "server-side" cursors. Probably will have to just fetch rowids client-side (if query is table based at all) and then impl paging on these rowids for the full records.

    I have a typelib with VB-ized necessary interfaces for the data-source impl somewhere in my toy projects (found it: oledbinterfaces.idl)
    Code:
        interface IRowPosition;
        interface IRowPositionChange;
        interface IRowset;
        interface IRowsetInfo;
        interface IRowsetNotify;
        interface IRowsetLocate;
        interface IRowsetScroll;
        interface IRowsetBookmark;
        interface IColumnsInfo;
        interface IAccessor;
        interface IRowsetView;
        interface IViewChapter;
        interface IViewFilter;
        interface IViewRowset;
        interface IViewSort;
        interface IConnectionPointContainer;
        interface IConnectionPoint;
        interface IADORecordsetConstructionUnrestricted;
        interface ISourcesRowset;
    cheers,
    </wqw>
    Interesting.
    Can you provide a simple demo to show how to use this interface?

  18. #18
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    892

    Re: Retrieving huge records from the database

    This is very much like what you have to do with OLE DB Simple Provider (OSP) Toolkit but w/o the "simple" part :-))

    cheers,
    </wqw>

  19. #19
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Retrieving huge records from the database

    Quote Originally Posted by wqweto View Post
    Unfortunately VB6 apps cannot deal with ADODB recordsets of more than 2GB (a single one).
    Well, now we are suddenly up from ~10MB per Recordset (with ~30K records on 13 Columns) -
    to a 2GB-Rs (factor 200 - a few orders of magnitude more memory, network-traffic - and especially "build-up-time"
    ... of about 2-6 minutes for such a huge Rs ... also keep in mind, that your 1GBit-LAN would be quite
    saturated for the whole transfer-and build-up-time of the Rs - especially when multiple clients perform such
    an action in parallel "in the morning", your Network-Performance would slow to a crawl for at least 10-20minutes or so...).

    Nobody in his right mind will (or should) send Selects which retrieve such an amount of Data from a DB(Server).

    Quote Originally Posted by wqweto View Post
    I'm hitting this limitation when some of my clients exhibit pathological behavior...so they can filter these (relatively fast) with client-side filters.
    That's why I'm considering using rs.DataSource to feed the recordsets with sqlite "server-side" cursors.
    Still don't understand your scenario here - especially not, where you see an advantage in:
    - a: "clientside filtering" (compared to one, which is performed "serverside" - ideally in the DBEngine itself)
    - and b: where the "serverside cursors" come in (with all that filtering)
    The enormous amount of Network-traffic (transferring stuff for filtering to the clients) would still "cause issues".

    So, what kind of results is it, that you want to filter out from >2GB of "invoices-data" in the end?
    And that repeatedly - every morning?

    What about generating "pre-filtered" Content (perhaps in a SQLite-DB or two) - which:
    - are file-wise placed at the same machine which hosts the SQLServer-instance
    - then installing a service at that host-machine, which does that pre-filtering/processing over night
    - the whole "prefiltering-process" then:
    .... - scanning the original DB-Data only for new or changed records (not the entire amount, which was already pre-filtered)
    .... - then acting on only those "diffed records", updating the prefiltered-results in the SQLite-DB(s)
    .... - one of the SQLite-DBs could host FullText-searchable-data for all the relevant Text-Contents of the Invoice-Documents
    - avoiding Network-traffic entirely - since all the above actions happen on the same host (triggered cyclically by the service)

    What your clients then "access and request" to get their filtered results in the morning,
    would then be "readily available" from the "prefiltered, serverside SQLite-DBs" (with greatly improved response-times).
    These queries would then return only "Document-IDs" in a list (matching some of the enhanced filter-criterions).
    The reponse-times, until you receive such a "filtered list of Doc-IDs" should be in the lower msec-range (10-50msec).
    With these ID-Lists, you could then easily "call back" into your real SQLServer, getting "concrete detail-data" -
    using a: Select From TheOneOrOtherDocTable Where ID In ( comma-joined-ID-List-as-retrieved-from-SQLite ).

    In all of the above outlined solution - I don't see where your "Serverside-Cursors" would come into play.
    The "high-amounts-of-Network-traffic-in-the-morning" would be eliminitated nearly completely -
    with such "serverside-generated and serverside-sitting, pre-filtered Results".

    Olaf

  20. #20
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    892

    Re: Retrieving huge records from the database

    Quote Originally Posted by Schmidt View Post
    Nobody in his right mind will (or should) send Selects which retrieve such an amount of Data from a DB(Server).
    Olaf, call me crazy but that is what happens here with some of the clients.

    Client-side filtering is rs.Filter = "DocNo=123" using ADO which is quite fast compared to full round-trip to the database.

    The thing is (like in any distributed system) that those DB servers are the most expensive part (as licenses and as computational resources) so we cannot affort to load them with additional services. I mean, the liceses are paid per core and MS would be very happy to run additional "sqlite prefiltering services" or even their Analyses Services *on the same* cores :-))

    Anyway, distributing client cache closer to the clients scales very well in most cases for us. I am researching using sqlite to swap in-memory ADO recordsets to client local db file on principle, not only for 2GB recordsets but for smaller nomenclatures (products, clients, etc.). Client-side ADO recordsets are nice abstraction we are using throughout client code, so I have to keep using these just swapping actual data w/ sqlite table underneath w/o breaking the abstraction.

    cheers,
    </wqw>

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,903

    Re: Retrieving huge records from the database

    Quote Originally Posted by wqweto View Post
    Olaf, call me crazy but that is what happens here with some of the clients.
    The "crazy" part was meant with regards to the 2GB-Recordsets you brought up.
    And yes - one has to be "crazy" to select such an amount of "Rs-Data" from a Server -
    especially when it's retrieved via the Internet (as I've just learned from your comments).

    Quote Originally Posted by wqweto View Post
    The thing is (like in any distributed system) that those DB servers are the most expensive part (as licenses and as computational resources) so we cannot affort to load them with additional services. I mean, the liceses are paid per core and MS would be very happy to run additional "sqlite prefiltering services" or even their Analyses Services *on the same* cores :-))
    Well - in another thread I've already pointed out to you, that many cloud-services are hugely overprized (compared with "normal hosting") these days.
    In that other thread I've asked you, to give me some timing-values (for Rs-retrieval against NWind-DB for your Cloud-based WebServices) -
    but you didn't give me an answer then - maybe you'll satisfy my curiosity this time around...

    I can use (RC5-)SQLite on my cheap 15€-per-month-flatrated and "fully managed Win-Hosting"-contract
    (which also includes unlimited storage on an SSD-based SAN, as well as unlimited traffic-volume,
    as well as 25 MS-SQLServer-DBs (probably over an SQLExpress-version - but all-in-all that's still a good offer)

    Quote Originally Posted by wqweto View Post
    Anyway, distributing client cache closer to the clients scales very well in most cases for us.
    I am researching using sqlite to swap in-memory ADO recordsets to client local db file on principle,
    not only for 2GB recordsets but for smaller nomenclatures (products, clients, etc.).
    If your clientside caches come near 2GB - then just create a client-local SQLite-Cache-DB in the Users AppData-Folder.
    SQLite is not that much slower, when working against a local FileDB (compared to InMemory-DBs).

    You might try out the:
    RC5.cConnection.CreateTableFromADORs(AdoRsFromYourWebRequest) method
    (this one-liner is quite convenient for moving Cache-Data over into SQLite).

    Quote Originally Posted by wqweto View Post
    Client-side ADO recordsets are nice abstraction we are using throughout client code,
    so I have to keep using these just swapping actual data w/ sqlite table underneath w/o breaking the abstraction.
    Agreed - two approaches which would work with a local SQLite-cache come to mind:
    1) write a universal wrapper around both types of Recordsets (which on the outside then exposes the same methods as the ADO-Rs).
    2) simply use the RC5.cRecordset.GetADORsFromContent method, to create a freestanding ADO-Rs from a "Chache-Queried" SQLite-Resultset

    Olaf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width