can the loading of a large number of records go as quick as in DOS 
Author Message
 can the loading of a large number of records go as quick as in DOS

Hello

I have build a windows-copy of a DOS-accounting program.
All works nice, but the only thing that bothers me is the fact
that loading a large number of records is not as quick as in DOS.
How can I solve that problem. The data are shown in a MSHFlexGrid and
comes from an Access-database.

The diference between the DOS-and Windows program is probarbly caused
by using a different technic. It looks as DOS reads all the time from
and to the hard disk (and thus has only the visible records /data in
the memory) as Windows first loads all the records and so has all
these records all the time in memory.

Am I right and is there a solution for the delaying loading in the
windows-
program?
Thanks
Catharinus van der Werf



Tue, 01 Nov 2011 20:08:19 GMT  
 can the loading of a large number of records go as quick as in DOS
If you are using OBDC/DAO/whatever to read from a MDB then it should be OK.
From your posting it seems that the old program only loaded data as it was
needed, why can't you use a similar philosophy here?
Secondly is there an apparent delay because the program does not display
until the data is loaded? if so move the data load out of the Form_Load, let
the program show itself then add then records.
There are at least as many different ways to do this as there are
programmers, but as you have given us virtually zero information as to how
you are doing this now, it's not easy to offer firm suggestions as to how to
improve matters.

Regards
Dave O.


Quote:
> Hello

> I have build a windows-copy of a DOS-accounting program.
> All works nice, but the only thing that bothers me is the fact
> that loading a large number of records is not as quick as in DOS.
> How can I solve that problem. The data are shown in a MSHFlexGrid and
> comes from an Access-database.

> The diference between the DOS-and Windows program is probarbly caused
> by using a different technic. It looks as DOS reads all the time from
> and to the hard disk (and thus has only the visible records /data in
> the memory) as Windows first loads all the records and so has all
> these records all the time in memory.

> Am I right and is there a solution for the delaying loading in the
> windows-
> program?
> Thanks
> Catharinus van der Werf




Tue, 01 Nov 2011 20:36:08 GMT  
 can the loading of a large number of records go as quick as in DOS

Quote:
> If you are using OBDC/DAO/whatever to read from a MDB then it should be OK.
> From your posting it seems that the old program only loaded data as it was
> needed, why can't you use a similar philosophy here?
> Secondly is there an apparent delay because the program does not display
> until the data is loaded? if so move the data load out of the Form_Load, let
> the program show itself then add then records.
> There are at least as many different ways to do this as there are
> programmers, but as you have given us virtually zero information as to how
> you are doing this now, it's not easy to offer firm suggestions as to how to
> improve matters.

> Regards
> Dave O.



> > Hello

> > I have build a windows-copy of a DOS-accounting program.
> > All works nice, but the only thing that bothers me is the fact
> > that loading a large number of records is not as quick as in DOS.
> > How can I solve that problem. The data are shown in a MSHFlexGrid and
> > comes from an Access-database.

> > The diference between the DOS-and Windows program is probarbly caused
> > by using a different technic. It looks as DOS reads all the time from
> > and to the hard disk (and thus has only the visible records /data in
> > the memory) as Windows first loads all the records and so has all
> > these records all the time in memory.

> > Am I right and is there a solution for the delaying loading in the
> > windows-
> > program?
> > Thanks
> > Catharinus van der Werf

> - Tekst uit oorspronkelijk bericht weergeven -

Oke Dave.

you' re right. I didn;t explain enough. I load the data via Form_load
and use ADO
I open a ADO-connection in which I try to select only the records
needed, but that is
and stays a problem when having 3000 records, because during the
loading I can't exercise
any action

I do this for example as follows:

dim conConnection as adodb.connection
dim mrstRecordSet as adodb.recordset
set ConConnection=new ADODB.Connection
conConnection.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;
Data Source =\\Database.mdb"
conConnection.Open conConnection.ConnectionString
set mrstRecordSet=Conconnection.execute("Select * from TABELA order by
Number")
do while not mrstRecordSet.EOF

    me.text1(0).text=mrstRecordSet.fields(0).value
mrstRecordSet.movenext
loop

This takes tooo long when I need to load 3000 records. Is there a
faster way??
Thanks

Catharinus van der Werf



Tue, 01 Nov 2011 20:59:52 GMT  
 can the loading of a large number of records go as quick as in DOS
There is a flag in MSHflexGrid, (repaint ?) that you can set to false when
you load the data, and set it to tru when you are done. It might speed
things up.


Quote:
> Hello

> I have build a windows-copy of a DOS-accounting program.
> All works nice, but the only thing that bothers me is the fact
> that loading a large number of records is not as quick as in DOS.
> How can I solve that problem. The data are shown in a MSHFlexGrid and
> comes from an Access-database.

> The diference between the DOS-and Windows program is probarbly caused
> by using a different technic. It looks as DOS reads all the time from
> and to the hard disk (and thus has only the visible records /data in
> the memory) as Windows first loads all the records and so has all
> these records all the time in memory.

> Am I right and is there a solution for the delaying loading in the
> windows-
> program?
> Thanks
> Catharinus van der Werf




Tue, 01 Nov 2011 21:03:29 GMT  
 can the loading of a large number of records go as quick as in DOS


Quote:
> Hello

> I have build a windows-copy of a DOS-accounting program.
> All works nice, but the only thing that bothers me is the fact
> that loading a large number of records is not as quick as in DOS.
> How can I solve that problem. The data are shown in a MSHFlexGrid and
> comes from an Access-database.

> The diference between the DOS-and Windows program is probarbly caused
> by using a different technic. It looks as DOS reads all the time from
> and to the hard disk (and thus has only the visible records /data in
> the memory) as Windows first loads all the records and so has all
> these records all the time in memory.

> Am I right and is there a solution for the delaying loading in the
> windows-
> program?

Ignoring for the moment your assumption of the difference in File I/O, the
answer is no, as one can make the case that in general a desktop Windows
application will be slower than its DOS counterpart.

But you are really comparing Apples and Oranges. A DOS application has some
advantages - the most important and obvious one is that it has the complete
attention of the hardware - there is always a more "direct" route or fewer
layers of stuff between here and there. This is true of honoring disk
requests and of displaying data. So it isn't just the File I/O that will be
slightly faster (and not because of "delay") but also the presentation -
writing to a screen buffer with character I/O is faster than submitting data
to a window'd control.

However, Windows also has many advantages. Everything is a trade-off.

But with that said, it doesn't necessarily mean a Window application has to
be horribly slower than its DOS counterpart. It means we often need to
change the way we architect/design the program. In many cases a DOS to
Windows conversion can actually produce an application that provides the
illusion of being just as fast or faster than its DOS counterpart.

In your case - do you actually need all those rows at one time?
Are you fetching more fields than you need?
How is your database design?
How are you 'loading' that Grid?
Do you have to have that huge grid?
Can you rework the presentation, perhaps spreading the "load" to other
faster controls?
What data access library are you using?

For a desktop application using VB6 and a local Jet database the fastest
solution is to use DAO.

-ralph



Tue, 01 Nov 2011 21:12:16 GMT  
 can the loading of a large number of records go as quick as in DOS
Catharinus,

It is answered already by Phil, but do you have the piece of code where you
load the MSFlexgrid in the Dos program.

However, as it is about reading: There is no need to read more then is
needed by using the right "where" clause for the SQL part of your recordset.

Cor



Tue, 01 Nov 2011 21:16:35 GMT  
 can the loading of a large number of records go as quick as in DOS

Quote:
> There is a flag in MSHflexGrid, (repaint ?) that you can set to false when
> you load the data, and set it to tru when you are done. It might speed
> things up.



> > Hello

> > I have build a windows-copy of a DOS-accounting program.
> > All works nice, but the only thing that bothers me is the fact
> > that loading a large number of records is not as quick as in DOS.
> > How can I solve that problem. The data are shown in a MSHFlexGrid and
> > comes from an Access-database.

> > The diference between the DOS-and Windows program is probarbly caused
> > by using a different technic. It looks as DOS reads all the time from
> > and to the hard disk (and thus has only the visible records /data in
> > the memory) as Windows first loads all the records and so has all
> > these records all the time in memory.

> > Am I right and is there a solution for the delaying loading in the
> > windows-
> > program?
> > Thanks
> > Catharinus van der Werf

> - Tekst uit oorspronkelijk bericht weergeven -

Oke Phil
I think you mean 'Redraw'
I remember and i helps, but not enough

Catharinus



Tue, 01 Nov 2011 21:25:10 GMT  
 can the loading of a large number of records go as quick as in DOS

Quote:


> > Hello

> > I have build a windows-copy of a DOS-accounting program.
> > All works nice, but the only thing that bothers me is the fact
> > that loading a large number of records is not as quick as in DOS.
> > How can I solve that problem. The data are shown in a MSHFlexGrid and
> > comes from an Access-database.

> > The diference between the DOS-and Windows program is probarbly caused
> > by using a different technic. It looks as DOS reads all the time from
> > and to the hard disk (and thus has only the visible records /data in
> > the memory) as Windows first loads all the records and so has all
> > these records all the time in memory.

> > Am I right and is there a solution for the delaying loading in the
> > windows-
> > program?

> Ignoring for the moment your assumption of the difference in File I/O, the
> answer is no, as one can make the case that in general a desktop Windows
> application will be slower than its DOS counterpart.

> But you are really comparing Apples and Oranges. A DOS application has some
> advantages - the most important and obvious one is that it has the complete
> attention of the hardware - there is always a more "direct" route or fewer
> layers of stuff between here and there. This is true of honoring disk
> requests and of displaying data. So it isn't just the File I/O that will be
> slightly faster (and not because of "delay") but also the presentation -
> writing to a screen buffer with character I/O is faster than submitting data
> to a window'd control.

> However, Windows also has many advantages. Everything is a trade-off.

> But with that said, it doesn't necessarily mean a Window application has to
> be horribly slower than its DOS counterpart. It means we often need to
> change the way we architect/design the program. In many cases a DOS to
> Windows conversion can actually produce an application that provides the
> illusion of being just as fast or faster than its DOS counterpart.

> In your case - do you actually need all those rows at one time?
> Are you fetching more fields than you need?
> How is your database design?
> How are you 'loading' that Grid?
> Do you have to have that huge grid?
> Can you rework the presentation, perhaps spreading the "load" to other
> faster controls?
> What data access library are you using?

> For a desktop application using VB6 and a local Jet database the fastest
> solution is to use DAO.

> -ralph- Tekst uit oorspronkelijk bericht niet weergeven -

> - Tekst uit oorspronkelijk bericht weergeven -

Thank you Ralph

I was only trying to find an easy way to solve the problem, but there
i'sn't as I read your comment. I just have to change the architecture
and that is ashame. I htis point I am disappointed in Windows and
there solutions in reading data. In fact, there is not an easy way of
reading a very big amount of data in a simple way like the DOS-account
program I mentioned does. It is time that some creates an easy way,
maybe I wil try.
Catharinus van der Werf



Tue, 01 Nov 2011 21:29:32 GMT  
 can the loading of a large number of records go as quick as in DOS
catharinus

I doubt very much if you ever *need* to load 3000 records because unless you
are using something like a 50" monitor (in portrait mode!) there is no way
that all 3000 can be shown on the screen at once, so only get what's
visible. Let's say you can show 30 records on screen at once then all you
need to do is keep track of where you are and just load what's needed,
grabbing 30 records by ADO should be almost instantaneous. You also may want
to look at the database for optimization there, do you index any fields you
want to use in a WHERE clause, does the database need regular
defragmentation, if so does it get it.

If you feel you must load all 3000 and want stop the program from seeming
locked, execute a DoEvents every now and then, on a tight loop like you
gave, about once every 50 loops would be about the right order of magnitude.

Also locking the grid as suggested by Phil is well worth investigating, this
is a common trick to speed up responsiveness, for other controls it's common
to set the control to Visible=False while loading then reset to True once
filled, as the program is not given a chance to redraw the control never
actually disappears to the eye but it does to the program. Another method is
using the LockWindowUpdate API but to that is not without issues.

Dave O.


Quote:

>> If you are using OBDC/DAO/whatever to read from a MDB then it should be
>> OK.
>> From your posting it seems that the old program only loaded data as it
>> was
>> needed, why can't you use a similar philosophy here?
>> Secondly is there an apparent delay because the program does not display
>> until the data is loaded? if so move the data load out of the Form_Load,
>> let
>> the program show itself then add then records.
>> There are at least as many different ways to do this as there are
>> programmers, but as you have given us virtually zero information as to
>> how
>> you are doing this now, it's not easy to offer firm suggestions as to how
>> to
>> improve matters.

>> Regards
>> Dave O.



>> > Hello

>> > I have build a windows-copy of a DOS-accounting program.
>> > All works nice, but the only thing that bothers me is the fact
>> > that loading a large number of records is not as quick as in DOS.
>> > How can I solve that problem. The data are shown in a MSHFlexGrid and
>> > comes from an Access-database.

>> > The diference between the DOS-and Windows program is probarbly caused
>> > by using a different technic. It looks as DOS reads all the time from
>> > and to the hard disk (and thus has only the visible records /data in
>> > the memory) as Windows first loads all the records and so has all
>> > these records all the time in memory.

>> > Am I right and is there a solution for the delaying loading in the
>> > windows-
>> > program?
>> > Thanks
>> > Catharinus van der Werf

>> > weergeven -

>> - Tekst uit oorspronkelijk bericht weergeven -

> Oke Dave.

> you' re right. I didn;t explain enough. I load the data via Form_load
> and use ADO
> I open a ADO-connection in which I try to select only the records
> needed, but that is
> and stays a problem when having 3000 records, because during the
> loading I can't exercise
> any action

> I do this for example as follows:

> dim conConnection as adodb.connection
> dim mrstRecordSet as adodb.recordset
> set ConConnection=new ADODB.Connection
> conConnection.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;
> Data Source =\\Database.mdb"
> conConnection.Open conConnection.ConnectionString
> set mrstRecordSet=Conconnection.execute("Select * from TABELA order by
> Number")
> do while not mrstRecordSet.EOF

>    me.text1(0).text=mrstRecordSet.fields(0).value
> mrstRecordSet.movenext
> loop

> This takes tooo long when I need to load 3000 records. Is there a
> faster way??
> Thanks

> Catharinus van der Werf




Tue, 01 Nov 2011 21:45:33 GMT  
 can the loading of a large number of records go as quick as in DOS


Quote:

> I was only trying to find an easy way to solve the problem, but there
> i'sn't as I read your comment. I just have to change the architecture
> and that is ashame. I htis point I am disappointed in Windows and
> there solutions in reading data. In fact, there is not an easy way of
> reading a very big amount of data in a simple way like the DOS-account
> program I mentioned does. It is time that some creates an easy way,
> maybe I wil try.

Are you committed to using Jet? There are other "database options".

For example, CodeBase (a library wrapper for xBase) can sort a million
records in less than a second.

Most accounting applications (with a few glaring exceptions) use a custom
database, with a custom access library.

I noticed you said you were using ADO. Unless your database is remote use
DAO.

-ralph



Tue, 01 Nov 2011 21:51:02 GMT  
 can the loading of a large number of records go as quick as in DOS


Quote:
>  In fact, there is not an easy way of
> reading a very big amount of data in a simple way like the DOS-account
> program I mentioned does.
> Catharinus van der Werf


Not true, certainly if you load a file line by line or record by record then
it is slow, but no programmer beyond neophyte would even consider loading a
large file in that manner, you set a buffer to the file size or if really
huge a large chunk of the file then you use GET to fill that buffer in a
single operation. With the file in memory it's now easy and fast to split
into lines or records.
You can also use PUT to write the whole file or large chunks in a single
operation. These are native to VB6, there are API file operations that
should be even faster but unless you really need every last millisecond Get
& Put should suffice.

Example:

Dim Buff as String
Dim ff     as integer

ff = FreeFile
Open "SomeFile.Txt" For Binary As ff
Buff = Space(LOF(ff))
Get ff,,Buff
Close ff

Dave O.



Tue, 01 Nov 2011 21:57:20 GMT  
 can the loading of a large number of records go as quick as in DOS



Quote:
> I was only trying to find an easy way to solve the problem,

The "easy way" is, to use the builtin DataBinding-Features of
a certain Grid-Control, meaning - the Grids usually have
builtin methods, to fill their internal "Data-Rendering-structures"
more efficiently, if you pass the Container (the ADO-Recordset)
directly to them ... and that in only one line of code!
e.g. Set HFlex.DataSource = Rs
or... Set VBDataGrid.DataSource = Rs

The latter one (the VB-DataGrid-Ctl) is the faster one
in this case, since it does not make an internal copy of
the data, which are already there in the Rs (and it
only renders its currently visible lines, depending on the
current Scroll-Pos - directly from the Rs-Container).

So in case of the VB-DataGrid-Control the over-all-timings
you can achieve, mainly depend only on the time until your
Select-Call returns with the (properly filled) ADO-Rs.

So please stop that out first - how long it takes to retrieve
a completely filled-up ADO-Recordset from your DB.
Please use clientside-cursors for that task (meaning:
Cnn.Cursorlocation = adUseClient).

The time, to set the VB6-DataGrids DataSource to
your Rs (implicitely rendering only your first visible
Records from the Rs) is nearly negligible - and takes
usually only 1-3msec, depending more or less only
on the internal Field-Count of the Rs (depending on
what you've specified in your Select).

Quote:
> but there i'sn't as I read your comment.

As just told, you are using your tools probably not in
the right way - e.g. the time to retrieve an ADO-Rs
from the "ubiquitous" NWind.mdb - for example 2155 Records
and 5 Columns ("Select * From [Order Details]") takes
only ca. 50msec here on the first run - (on not yet cached
table-data) and on the second run only ca. 7msec.
This is the timing for only the plain Rs-Select (containing
all the records already after returning) - now add the
1-3msec (for setting the Grids DataSource) on top of
that - and you will get ca. 52msec total in case of the first
run - and ca. 9msec only in case of repeated runs.

So rendering (visualizing in a Grid) is in now way a timing-
problem nowadays (in case the Grid plays well with the
already existent Rs-Data).

So, you can reduce your problem to the time it takes, to
completely fill up your ADO-Rs from your DB.
And (as you wrote) when your Select only retrieves ca.
3000 records (doing a normal Table-Scan with only a
simple Order By), then you should expect "fill-up-times"
for an (clientside Cursor) ADO-Rs of ca. 75-150msec
on the first run (uncached scan) and ca. 15-30msec on
a second run (working against cached data) - depending
only somewhat on your Column-Size.

And these maximum-times of ca. 150msec (for selecting ca.
3000 records against properly indexed DB-tables) is in no
way something I would consider "a user-visible" timeout ...
And regarding DOS->Windows. Of course there are a
few more layers you will have to go through compared
with DOS (not to mention the multitasking of the newer
OSes) - but the IO-layers of the new OSes are usually
tuned in a great way - also making use of DMA and all
the new stuff available on the modern IDE/SATA subsystems
and drivers, which should outperform any "DOS-Box" out
there, if we talk about IO-throughput and modern cache-
strategies on top of that.

So, please tell us your timings for your plain select (and
do switch-on the adUseClient on your JET-connection).
Don't do any looping over your retrieved Rs so far - just
tell us the time you need for a plain Rs.Open "Select ...", ...

Olaf



Tue, 01 Nov 2011 22:33:24 GMT  
 can the loading of a large number of records go as quick as in DOS

Quote:
> Oke Dave.

> you' re right. I didn;t explain enough. I load the data via Form_load
> and use ADO
> I open a ADO-connection in which I try to select only the records
> needed, but that is
> and stays a problem when having 3000 records, because during the
> loading I can't exercise
> any action

> I do this for example as follows:

> dim conConnection as adodb.connection
> dim mrstRecordSet as adodb.recordset
> set ConConnection=new ADODB.Connection
> conConnection.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;
> Data Source =\\Database.mdb"
> conConnection.Open conConnection.ConnectionString
> set mrstRecordSet=Conconnection.execute("Select * from TABELA order by
> Number")
> do while not mrstRecordSet.EOF

>    me.text1(0).text=mrstRecordSet.fields(0).value
> mrstRecordSet.movenext
> loop

> This takes tooo long when I need to load 3000 records. Is there a
> faster way??

Don't use Execute method. It creates forward-only cursor, meaning that you
will get run time errors when you try to move backward after you have moved
forward. CacheSize property affects this, allowing you to move backward few
rows(the default is 10 rows), but then you get errors after moving back 11
rows. Forward-only cursors are only suitable for reports, or when you want
to do some calculations. In these cases you only move forward.

As far as I know, but I haven't tested it extensively, the solution is only
retrieve what's being viewed, and to do that, you need to use
"con.CursorLocation = adUseServer". When you use server side cursors, the
records are selected on the server side and stay there. They are retrieved
on demand,  rs.CacheSize property determine how many are retrieved when
scrolling, so set this to 50 or 100 before "rs.Open". When you use
"con.CursorLocation = adUseClient", all records are transported to the
workstation(in memory and/or temp files), which takes time, so avoid it for
large number of records.

Also, you need to set CursorType to adOpenKeyset. When you set CursorType,
you need to check its value after "rs.Open" because the provider can change
it to something else that it supports without warning. This is normal and
documented in CursorType property description. In my testing with ADO 2.80,
it seems that adOpenKeyset is the only type that Jet provider supports. If
you set it to any other type, Jet changes it to adOpenKeyset after you call
"rs.Open".

So, try the following:

dim conConnection as adodb.connection
dim mrstRecordSet as adodb.recordset
Dim sql As String
Dim t As Single

set ConConnection=new ADODB.Connection
conConnection.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;
Data Source =\\Database.mdb"
' Use server side
conConnection.CursorLocation = adUseServer
conConnection.Open conConnection.ConnectionString
'set mrstRecordSet=Conconnection.execute(
' "Select * from TABELA order by Number")

sql = "Select * from TABELA order by Number"
t = Timer
mrstRecordSet.Open sql, conConnection, adOpenKeyset, adLockOptimistic
Debug.Print "Time taken: " & Timer - t
Debug.Print "RecordCount = " & mrstRecordSet.RecordCount
Debug.Print "CursorType = " & mrstRecordSet.CursorType
Debug.Print "Supports(adBookmark) = " & mrstRecordSet.Supports(adBookmark)

Set MSHFlexGrid1.Recordset = mrstRecordSet

In my tests with an MDB file in the local computer, it shows 0 seconds for
adUseServer, and 15 ms for adUseClient when selecting 830 records. Here is
the code I am using:

Option Explicit

Private Sub Command1_Click()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ConStr As String
    Dim sql As String
    Dim t As Single

    Const sDBFileName As String = "C:\Test\NWIND2000.MDB"

    ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBFileName
    Set con = New ADODB.Connection
    Debug.Print con.Version
    con.CursorLocation = adUseServer
    con.Open ConStr

    Set rs = New ADODB.Recordset

    sql = "SELECT * FROM Orders"
    Debug.Print "sql = " & sql

    t = Timer
    rs.Open sql, con, adOpenKeyset, adLockOptimistic
    Debug.Print "Time taken: " & Timer - t
    Debug.Print "RecordCount = " & rs.RecordCount
    Debug.Print "CursorType = " & rs.CursorType
    Debug.Print "Supports(adBookmark) = " & rs.Supports(adBookmark)

    rs.Close
    con.Close

    Set rs = Nothing
    Set con = Nothing

End Sub

Finally, what bound control you are using matters. It's possible that the
particular bound control that you are using is making requests to retrieve
everything at once, creating delays. If this is the case, try another bound
control, or third party controls. I don't know if MSHFlexGrid supports this,
but I think that True DBGrid supports it:

http://www.componentone.com/SuperProducts/StudioActiveX/
http://www.componentone.com/



Wed, 02 Nov 2011 00:26:05 GMT  
 can the loading of a large number of records go as quick as in DOS

Quote:


> > Oke Dave.

> > you' re right. I didn;t explain enough. I load the data via Form_load
> > and use ADO
> > I open a ADO-connection in which I try to select only the records
> > needed, but that is
> > and stays a problem when having 3000 records, because during the
> > loading I can't exercise
> > any action

> > I do this for example as follows:

> > dim conConnection as adodb.connection
> > dim mrstRecordSet as adodb.recordset
> > set ConConnection=new ADODB.Connection
> > conConnection.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;
> > Data Source =\\Database.mdb"
> > conConnection.Open conConnection.ConnectionString
> > set mrstRecordSet=Conconnection.execute("Select * from TABELA order by
> > Number")
> > do while not mrstRecordSet.EOF

> > ? ?me.text1(0).text=mrstRecordSet.fields(0).value
> > mrstRecordSet.movenext
> > loop

> > This takes tooo long when I need to load 3000 records. Is there a
> > faster way??

> Don't use Execute method. It creates forward-only cursor, meaning that you
> will get run time errors when you try to move backward after you have moved
> forward. CacheSize property affects this, allowing you to move backward few
> rows(the default is 10 rows), but then you get errors after moving back 11
> rows. Forward-only cursors are only suitable for reports, or when you want
> to do some calculations. In these cases you only move forward.

> As far as I know, but I haven't tested it extensively, the solution is only
> retrieve what's being viewed, and to do that, you need to use
> "con.CursorLocation = adUseServer". When you use server side cursors, the
> records are selected on the server side and stay there. They are retrieved
> on demand, ?rs.CacheSize property determine how many are retrieved when
> scrolling, so set this to 50 or 100 before "rs.Open". When you use
> "con.CursorLocation = adUseClient", all records are transported to the
> workstation(in memory and/or temp files), which takes time, so avoid it for
> large number of records.

> Also, you need to set CursorType to adOpenKeyset. When you set CursorType,
> you need to check its value after "rs.Open" because the provider can change
> it to something else that it supports without warning. This is normal and
> documented in CursorType property description. In my testing with ADO 2.80,
> it seems that adOpenKeyset is the only type that Jet provider supports. If
> you set it to any other type, Jet changes it to adOpenKeyset after you call
> "rs.Open".

> So, try the following:

> dim conConnection as adodb.connection
> dim mrstRecordSet as adodb.recordset
> Dim sql As String
> Dim t As Single

> set ConConnection=new ADODB.Connection
> conConnection.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;
> Data Source =\\Database.mdb"
> ' Use server side
> conConnection.CursorLocation = adUseServer
> conConnection.Open conConnection.ConnectionString
> 'set mrstRecordSet=Conconnection.execute(
> ' "Select * from TABELA order by Number")

> sql = "Select * from TABELA order by Number"
> t = Timer
> mrstRecordSet.Open sql, conConnection, adOpenKeyset, adLockOptimistic
> Debug.Print "Time taken: " & Timer - t
> Debug.Print "RecordCount = " & mrstRecordSet.RecordCount
> Debug.Print "CursorType = " & mrstRecordSet.CursorType
> Debug.Print "Supports(adBookmark) = " & mrstRecordSet.Supports(adBookmark)

> Set MSHFlexGrid1.Recordset = mrstRecordSet

> In my tests with an MDB file in the local computer, it shows 0 seconds for
> adUseServer, and 15 ms for adUseClient when selecting 830 records. Here is
> the code I am using:

> Option Explicit

> Private Sub Command1_Click()
> ? ? Dim con As ADODB.Connection
> ? ? Dim rs As ADODB.Recordset
> ? ? Dim ConStr As String
> ? ? Dim sql As String
> ? ? Dim t As Single

> ? ? Const sDBFileName As String = "C:\Test\NWIND2000.MDB"

> ? ? ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBFileName
> ? ? Set con = New ADODB.Connection
> ? ? Debug.Print con.Version
> ? ? con.CursorLocation = adUseServer
> ? ? con.Open ConStr

> ? ? Set rs = New ADODB.Recordset

> ? ? sql = "SELECT * FROM Orders"
> ? ? Debug.Print "sql = " & sql

> ? ? t = Timer
> ? ? rs.Open sql, con, adOpenKeyset, adLockOptimistic
> ? ? Debug.Print "Time taken: " & Timer - t
> ? ? Debug.Print "RecordCount = " & rs.RecordCount
> ? ? Debug.Print "CursorType = " & rs.CursorType
> ? ? Debug.Print "Supports(adBookmark) = " & rs.Supports(adBookmark)

> ? ? rs.Close
> ? ? con.Close

> ? ? Set rs = Nothing
> ? ? Set con = Nothing

> End Sub

> Finally, what bound control you are using matters. It's possible that the
> particular bound control that you are using is making requests to retrieve
> everything at once, creating delays. If this is the case, try another bound
> control, or third party controls. I don't know if MSHFlexGrid supports this,
> but I think that True DBGrid supports it:

> http://www.componentone.com/SuperProducts/StudioActiveX/http://www.co... Tekst uit oorspronkelijk bericht niet weergeven -

> - Tekst uit oorspronkelijk bericht weergeven -

Hello Nobody

thanks for your work, but I don;t see any code that fill the flexgrid
Or am I wrong
Catharinus



Wed, 02 Nov 2011 00:50:05 GMT  
 can the loading of a large number of records go as quick as in DOS

Quote:
> thanks for your work, but I don;t see any code that fill the flexgrid

This line:

Set MSHFlexGrid1.Recordset = mrstRecordSet



Wed, 02 Nov 2011 01:32:04 GMT  
 
 [ 25 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Inserting large number of records into a large table with SQL/DAO

2. Explination of how ADODB deals with a large number of records

3. Trouble retrieving a fairly large number of records from a database

4. MS SQL Server, ADO, Large Number of Records, timeout

5. Managing/processing a large number of large arrays

6. going to next record after inserting a new record

7. How do I make it go quicker?

8. ok... here we go (quick question)

9. Quick way to sort a large collection.

10. going through large recordsets

11. Quick Basic 4.5 for DOS Y2K??

12. How to run Quick Basic 4.5 in a Dos Window

 

 
Powered by phpBB® Forum Software