[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.vb.general.discussion

VsFlexGrid 7.0 (OLEDB). Loading MUCH slower if recordset contains a Boolean value

(Mike Mitchell)

6/26/2012 1:37:00 PM

Using the VirtualData property I have been able to get VsFlexGrid to
load even a large recordset (80,000 rows) very quickly with DataMode
set to flexDMBound.

Now I have added one field, DX_Checkbox, to the mdb. It is a Boolean
field (True/False, default false). According to the VsFlexGrid docs
this automatically makes the grid display such a field as a checkbox.

This is indeed the case, but as soon as I include the checkbox field
in the query, the grid no longer loads quickly. It's now as slow as a
snail. If I temporarily remove the checkbox field from the query, it's
fast again.

Anybody got any ideas why? I've tried Googling, but there are so many
questions and answers on this ActiveX control, I'll be browsing for
weeks to cover them all.

The table BTW contains 13 fields, the last one being the newly added
Boolean field. I don't select all the fields for constructing the
recordset, so the SQL looks like this:

With the Boolean field:
=====================
SQL = "SELECT DX_Checkbox, DX_MsgNum As [Article Number], DX_Subject
As Subject, " _
& "DX_From As Author, DX_Date As [Date Posted], DX_MsgID As
[Message ID] FROM Data"

Without the Boolean field (as it was originally, before the Boolean
field was added to the table)
========================
SQL = "SELECT DX_MsgNum As [Article Number], DX_Subject As Subject, "
_
& "DX_From As Author, DX_Date As [Date Posted], DX_MsgID As
[Message ID] FROM Data"

Here is how I open the ADODB recordset:
rs.Open SQL, Conn, adOpenDynamic, adLockOptimistic, adAsyncFetch

NB: adAsyncFetch doesn't seem to make any difference. I thought it did
originally, but now the grid loads fast with or without adAsyncFetch,
provided the Boolean field isn't included, and it loads slowly with or
without adAsyncFetch, if the Boolean field IS included.

The grid's salient properties are set as follows:
..DataMode = flexDMBound
..Editable = flexEDKbdMouse
..ScrollTrack = True
..VirtualData = True

I'm switching off Redraw before binding the recordset, i.e.:

With vsFlexHeaders
.Redraw = flexRDNone
Set .DataSource = rs
.Redraw = True
End With

So I don't know what else to try to get my speed back!

If merely adding a Boolean field slows down the loading to such an
extent it's a real showstopper. But what is so special about a Boolean
field in an mdb?

MM
10 Answers

(Mike Mitchell)

6/26/2012 1:53:00 PM

0

On Tue, 26 Jun 2012 14:37:18 +0100, MM <kylix_is@yahoo.co.uk> wrote:

Further information:

If I change the Boolean field type to Integer, keep everything else
the same, the grid loads just as fast as it always did.

It's just the Boolean field type that is causing the loading speed to
fall dramatically.

Is this because the grid is having to draw all the little checkboxes
and this takes quite some time?

MM

DaveO

6/26/2012 2:32:00 PM

0


"MM" <kylix_is@yahoo.co.uk> wrote in message
news:tifju71aqcevc9jau4pi27cp45jfrmc0ra@4ax.com...

> Is this because the grid is having to draw all the little checkboxes
> and this takes quite some time?

I would suspect this is almost certainly the case. Can you substitute "Yes"
or "No", "True" or "False", 1 or 0 or some similar text based display and
see how that goes. If there is a mechanism for inserting icons into the grid
then a couple of icons of checkboxes ,one ticked, might be faster. This one
is probably a suck-it-and-see problem without a generic answer to help.

Good luck
DaveO.

(Mike Mitchell)

6/27/2012 12:54:00 PM

0

On Tue, 26 Jun 2012 15:32:23 +0100, "DaveO" <djo@dial.pipex.com>
wrote:

>
>"MM" <kylix_is@yahoo.co.uk> wrote in message
>news:tifju71aqcevc9jau4pi27cp45jfrmc0ra@4ax.com...
>
>> Is this because the grid is having to draw all the little checkboxes
>> and this takes quite some time?
>
>I would suspect this is almost certainly the case. Can you substitute "Yes"
>or "No", "True" or "False", 1 or 0 or some similar text based display and
>see how that goes. If there is a mechanism for inserting icons into the grid
>then a couple of icons of checkboxes ,one ticked, might be faster. This one
>is probably a suck-it-and-see problem without a generic answer to help.
>
>Good luck
>DaveO.

Must have been a bug, because I've just replaced the version 7
flexgrid with the version 8 and the problem has disappeared. Even with
a Boolean field in the mdb, displaying the recordset is instantaneous.

To make absolutely sure, I created a new test app, placed a version 7
and a version 8 grid on frmMain, set the relevant properties the same
for each grid and used exactly the same code (except for the names of
the two grids) in two separate cmdbutton events to connect to the same
mdb.

When I click on the "Version 7" cmdbutton, it's as slow as the dickens
before anything appears. When I unload the app, then run it again and
click on the "Version 8" cmdbutton, it displays immediately.

MM

ObiWan

6/27/2012 2:18:00 PM

0


> This is indeed the case, but as soon as I include the checkbox field
> in the query, the grid no longer loads quickly. It's now as slow as a
> snail. If I temporarily remove the checkbox field from the query, it's
> fast again.

I suspect it's due to the fact that the grid "pre-converts" the field
or either "pre-renders" it, so, removing the field or turning it into a
regular "int" will speed things up... or either you may load your data
in "chunks" and only when needed; for example ...


nPage = nNumPage - 1
nRecLo = (nPage * nRecPerPage) + 1
nRecHi = (nPage * nRecPerPage) + nRecPerPage

If Len(sWhere) > 0 Then
sSQL = sSQL & " WHERE " & sWhere
End If

sSQL = "SELECT " & sFields & " FROM " & _
"(SELECT " & sFields & "," & _
" ROW_NUMBER() OVER(ORDER BY " + sOrderBy & ") AS RecNo" & _
" FROM " & sSource & " tmpRS" & sWhere & ")" & _
" AS pagedRS WHERE RecNo BETWEEN" & _
" (" & nRecLo & ") AND (" & nRecHi & ")"


you may encapsulate the above inside a function like, for example

Function PgSQL(sSource, sFields, sWhere, sOrderBy, nPage, nRecPerPage)

where:

sSource is the source table/view/...
sFields is the list of columns
sWhere are the SQL "WHERE" conditions
sOrderBy are the ordering columns
nPage is the requested page number
nRecPerPage is the number of records making a page

so, calling the function using the following

sSource = "AUTHORS"
sFields = "*"
sWhere = "au_lname LIKE 'A%'"
sOrderBy = "au_lname, au_fname"
nPage = 2
nRecPerPage = 10

would return a query to fetch the second data page (made up by 10
records) from the AUTHORS table where the author last name begins with
"A" and order the results by lastname, firstname

Now... using such an approach, assuming your grid shows 10 records at a
time, you may start by fetching the first two pages of data (20
records) and then fetch other pages only when needed (e.g. after the
user scrolls behind a given row) so, populating the grid on the fly;
notice that the above approach will work with SQL server and Oracle,
will need a workaround with MySQL but won't probably work (didn't try
it) if you're using an "Access database" (the problem is the presence
of the ROW_NUMBER and OVER statements)

ObiWan

6/27/2012 2:31:00 PM

0


> Function PgSQL(sSource, sFields, sWhere, sOrderBy, nPage, nRecPerPage)

just in case

'=======================================================================
Private Function PgSQL(ByVal sSource As String, _
ByVal sFields As String, _
ByVal sWhere As String, _
ByVal sOrderBy As String, _
ByVal nNumPage As Long, _
ByVal nRecPerPage As Long) As String

Dim nPage As Long, nRecLo As Long, nRecHi As Long
Dim sSQL As String, sWhereCond

' set page# to base zero, calculate the
' lower and upper bound for records #
nPage = nNumPage - 1
nRecLo = (nPage * nRecPerPage) + 1
nRecHi = (nPage * nRecPerPage) + nRecPerPage

' checks if there's any where condition
If Len(sWhere) > 0 Then
sWhereCond = " WHERE " & sWhere
End If

' builds the SQL select string
sSQL = "SELECT " & sFields & " FROM " & _
"(SELECT " & sFields & "," & _
" ROW_NUMBER() OVER(ORDER BY " + sOrderBy & ") AS RecNo" & _
" FROM " & sSource & " tmpRS" & sWhereCond & ")" & _
" AS pagedRS WHERE RecNo BETWEEN" & _
" (" & nRecLo & ") AND (" & nRecHi & ")"

' all done return the query string
PgSQL = sSQL
End Function
'=======================================================================

and, calling the above this way

sQuery = PgSQL("AUTHORS", _
"*", _
"au_lname LIKE 'A%'", _
"au_lname, au_fname", _
2, _
10)

would result into an SQL string like the following

SELECT * FROM (
SELECT
*,
ROW_NUMBER() OVER(ORDER BY au_lname, au_fname) AS RecNo
FROM AUTHORS tmpRS
WHERE au_lname LIKE 'A%'
) AS pagedRS
WHERE
RecNo BETWEEN (11) AND (20)

which you may then use to query your DB and fetch the desired page
of data ... easy, isn't it :) ?

Ralph

6/27/2012 3:23:00 PM

0

On Wed, 27 Jun 2012 13:54:28 +0100, MM <kylix_is@yahoo.co.uk> wrote:

>On Tue, 26 Jun 2012 15:32:23 +0100, "DaveO" <djo@dial.pipex.com>
>wrote:
>
>>
>>"MM" <kylix_is@yahoo.co.uk> wrote in message
>>news:tifju71aqcevc9jau4pi27cp45jfrmc0ra@4ax.com...
>>
>>> Is this because the grid is having to draw all the little checkboxes
>>> and this takes quite some time?
>>
>>I would suspect this is almost certainly the case. Can you substitute "Yes"
>>or "No", "True" or "False", 1 or 0 or some similar text based display and
>>see how that goes. If there is a mechanism for inserting icons into the grid
>>then a couple of icons of checkboxes ,one ticked, might be faster. This one
>>is probably a suck-it-and-see problem without a generic answer to help.
>>
>>Good luck
>>DaveO.
>
>Must have been a bug, because I've just replaced the version 7
>flexgrid with the version 8 and the problem has disappeared. Even with
>a Boolean field in the mdb, displaying the recordset is instantaneous.
>
>To make absolutely sure, I created a new test app, placed a version 7
>and a version 8 grid on frmMain, set the relevant properties the same
>for each grid and used exactly the same code (except for the names of
>the two grids) in two separate cmdbutton events to connect to the same
>mdb.
>
>When I click on the "Version 7" cmdbutton, it's as slow as the dickens
>before anything appears. When I unload the app, then run it again and
>click on the "Version 8" cmdbutton, it displays immediately.
>
>MM

Glad you resolved the problem.

For the future:
It was not clear if this "loading delay" was observed in the VBIDE or
in a compiled application.

Anytime you are doing extensive development with a designer within the
VBIDE and notice a change in the time to load, the first suspect
should be the cache file (.dsa or .oca). Deleting all occurances of
this file then allowing the VBIDE to recreate it often brings about
improvement.

(Recreating a missing cache file will be slower than subsquent runs.)

-ralph

(Mike Mitchell)

6/27/2012 4:27:00 PM

0

On Wed, 27 Jun 2012 10:23:28 -0500, ralph <nt_consulting64@yahoo.com>
wrote:

>On Wed, 27 Jun 2012 13:54:28 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>
>>On Tue, 26 Jun 2012 15:32:23 +0100, "DaveO" <djo@dial.pipex.com>
>>wrote:
>>
>>>
>>>"MM" <kylix_is@yahoo.co.uk> wrote in message
>>>news:tifju71aqcevc9jau4pi27cp45jfrmc0ra@4ax.com...
>>>
>>>> Is this because the grid is having to draw all the little checkboxes
>>>> and this takes quite some time?
>>>
>>>I would suspect this is almost certainly the case. Can you substitute "Yes"
>>>or "No", "True" or "False", 1 or 0 or some similar text based display and
>>>see how that goes. If there is a mechanism for inserting icons into the grid
>>>then a couple of icons of checkboxes ,one ticked, might be faster. This one
>>>is probably a suck-it-and-see problem without a generic answer to help.
>>>
>>>Good luck
>>>DaveO.
>>
>>Must have been a bug, because I've just replaced the version 7
>>flexgrid with the version 8 and the problem has disappeared. Even with
>>a Boolean field in the mdb, displaying the recordset is instantaneous.
>>
>>To make absolutely sure, I created a new test app, placed a version 7
>>and a version 8 grid on frmMain, set the relevant properties the same
>>for each grid and used exactly the same code (except for the names of
>>the two grids) in two separate cmdbutton events to connect to the same
>>mdb.
>>
>>When I click on the "Version 7" cmdbutton, it's as slow as the dickens
>>before anything appears. When I unload the app, then run it again and
>>click on the "Version 8" cmdbutton, it displays immediately.
>>
>>MM
>
>Glad you resolved the problem.
>
>For the future:
>It was not clear if this "loading delay" was observed in the VBIDE or
>in a compiled application.

It was the same in the compiled app.

>Anytime you are doing extensive development with a designer within the
>VBIDE and notice a change in the time to load, the first suspect
>should be the cache file (.dsa or .oca). Deleting all occurances of
>this file then allowing the VBIDE to recreate it often brings about
>improvement.
>
>(Recreating a missing cache file will be slower than subsquent runs.)

But this time it was merely the addition of a Boolean field to the
mdb. It was very obvious. Remove the field from the query and loading
was fast again. Put it back in and it was slow (orders of magnitude
slow!). I reckon the coders forgot about VirtualData = True when they
were drawing the checkboxes and drew them for the *entire* recordset.

Anyway, version 8 fixed it, so now we move on!

MM

(Mike Mitchell)

6/27/2012 4:30:00 PM

0

On Wed, 27 Jun 2012 16:18:10 +0200, ObiWan
<alb.20.trashsink@spamgourmet.com> wrote:

>
>> This is indeed the case, but as soon as I include the checkbox field
>> in the query, the grid no longer loads quickly. It's now as slow as a
>> snail. If I temporarily remove the checkbox field from the query, it's
>> fast again.
>
>I suspect it's due to the fact that the grid "pre-converts" the field
>or either "pre-renders" it, so, removing the field or turning it into a
>regular "int" will speed things up... or either you may load your data
>in "chunks" and only when needed; for example ...

But loading the data "in chunks" is supposed to happen anyway, when
VirtualData = True.

And it does, provided (a) with the version 7 grid you're not including
a Boolean field in the data bound recordset; or (b) you're using the
version 8 grid, in which case there's no problem.

MM

ObiWan

6/27/2012 4:35:00 PM

0


> But loading the data "in chunks" is supposed to happen anyway, when
> VirtualData = True.

in /theory/ you're correct; in practice... the grid may be fetching and
"prerendering" rows in the background and this, in turn, will slow
things down, I didn't fathom the whole thing btw, so what I was just
trying to offer a more "general" solution (which btw would also work
for other types of "scrollable views")

ObiWan

6/27/2012 4:37:00 PM

0

> slow!). I reckon the coders forgot about VirtualData = True when they
> were drawing the checkboxes and drew them for the *entire* recordset.

which is exactly what I was suspecting so, either move to version 8 of
the grid or... use a "paged" approach so that the grid rows rendering
will only happen for newly loaded rows of data :)