Slow MDB Linked View

[Neil]

I previously posted re. this, but thought I'd try again with a summary of
facts.

I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that
is linked to the database via ODBC and has been in place for several years
without any performance problems.

Recently I added a couple of fields to the output of the view, and it became
very slow when scrolling. When just opened in the database window, the
linked view takes about a second to scroll down one screen. When opened in
the form (in Continuous Form view), it takes about 2-3 seconds. It used to
scroll just about instantaneously.

I tried removing the few fields I added to restore the view to its previous
form, but it had no effect. The view was still much slower than it had been.

The total number of records returned from the view is about 1300, so it's
not a large number of records. The view has about 25 fields.

I found that when I link the view in the MDB without specifying a unique
index, it scrolls very quickly -- almost instantaneously. But when I specify
the unique index, it is slow. Since the view needs to be edited, it needs
the unique index defined.

As noted, it's been in place for years, with a unique index defined, yet
without the slowness. Any ideas as to what might have caused this and what
might be done would be appreciated. I've included the SQL for the view
below.

Thanks,

Neil

SQL FOR MAIN VIEW:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL FOR vwInventory_Dupes:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[david epsom dot com dot au]

Q: If you open a DAO recordset on the view, how long does
it take (as a snapshot? as a dynaset with movelast?)

Normally, filling a datasheet is a background task.
According to the documentation,
http://office.microsoft.com/en-gb/as...876211033.aspx
the recordset should be filled at the rate of 100 records
every 10 seconds (!) That seems wrong! but you might
like to try changing it.

You can force form to load an entire recordset before
display. This means that there is a long pause before
the display is filled, but scrolling is then immediate.
The only way I /know/ to force this behaviour is to
put combo-boxes on the form. You haven't gotten confused
by this kind of behaviour have you?

(david)

"Neil" <no****@nospam.net> wrote in message
news:Px***************@newsread3.news.pas.earthlin k.net...

I previously posted re. this, but thought I'd try again with a summary of
facts.

I have an Access 2000 MDB with a SQL Server 7 back end. There is a view
that is linked to the database via ODBC and has been in place for several
years without any performance problems.

Recently I added a couple of fields to the output of the view, and it
became very slow when scrolling. When just opened in the database window,
the linked view takes about a second to scroll down one screen. When
opened in the form (in Continuous Form view), it takes about 2-3 seconds.
It used to scroll just about instantaneously.

I tried removing the few fields I added to restore the view to its
previous form, but it had no effect. The view was still much slower than
it had been.

The total number of records returned from the view is about 1300, so it's
not a large number of records. The view has about 25 fields.

I found that when I link the view in the MDB without specifying a unique
index, it scrolls very quickly -- almost instantaneously. But when I
specify the unique index, it is slow. Since the view needs to be edited,
it needs the unique index defined.

As noted, it's been in place for years, with a unique index defined, yet
without the slowness. Any ideas as to what might have caused this and what
might be done would be appreciated. I've included the SQL for the view
below.

Thanks,

Neil

SQL FOR MAIN VIEW:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL FOR vwInventory_Dupes:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[Neil]

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:42***********************@lon-reader.news.telstra.net...

Q: If you open a DAO recordset on the view, how long does
it take (as a snapshot? as a dynaset with movelast?)
With both a dynaset and a snapshot it takes less than a second to both open
the recordset and move to the last record (1293 records total).

Normally, filling a datasheet is a background task.
According to the documentation,
http://office.microsoft.com/en-gb/as...876211033.aspx
the recordset should be filled at the rate of 100 records
every 10 seconds (!) That seems wrong! but you might
like to try changing it.

You can force form to load an entire recordset before
display. This means that there is a long pause before
the display is filled, but scrolling is then immediate.
The only way I /know/ to force this behaviour is to
put combo-boxes on the form. You haven't gotten confused
by this kind of behaviour have you?
I'm confused by what you're asking me if I've been confused about. :-)
Haven't gotten confused by what kind of behavior?

The form does have combo boxes on it, but not based on the view itself.

Neil


(david)

"Neil" <no****@nospam.net> wrote in message
news:Px***************@newsread3.news.pas.earthlin k.net...

I previously posted re. this, but thought I'd try again with a summary of
facts.

I have an Access 2000 MDB with a SQL Server 7 back end. There is a view
that is linked to the database via ODBC and has been in place for several
years without any performance problems.

Recently I added a couple of fields to the output of the view, and it
became very slow when scrolling. When just opened in the database window,
the linked view takes about a second to scroll down one screen. When
opened in the form (in Continuous Form view), it takes about 2-3 seconds.
It used to scroll just about instantaneously.

I tried removing the few fields I added to restore the view to its
previous form, but it had no effect. The view was still much slower than
it had been.

The total number of records returned from the view is about 1300, so it's
not a large number of records. The view has about 25 fields.

I found that when I link the view in the MDB without specifying a unique
index, it scrolls very quickly -- almost instantaneously. But when I
specify the unique index, it is slow. Since the view needs to be edited,
it needs the unique index defined.

As noted, it's been in place for years, with a unique index defined, yet
without the slowness. Any ideas as to what might have caused this and
what might be done would be appreciated. I've included the SQL for the
view below.

Thanks,

Neil

SQL FOR MAIN VIEW:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL FOR vwInventory_Dupes:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[Neil]

I tried changing the fill rate per the link you provided below. I create an
MSysConf table and set it to 1000 records every second instead of 100 every
10 seconds (basically, just getting the whole recordset). I didn't see any
change in performance.

Neil
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:42***********************@lon-reader.news.telstra.net...

Q: If you open a DAO recordset on the view, how long does
it take (as a snapshot? as a dynaset with movelast?)

Normally, filling a datasheet is a background task.
According to the documentation,
http://office.microsoft.com/en-gb/as...876211033.aspx
the recordset should be filled at the rate of 100 records
every 10 seconds (!) That seems wrong! but you might
like to try changing it.

You can force form to load an entire recordset before
display. This means that there is a long pause before
the display is filled, but scrolling is then immediate.
The only way I /know/ to force this behaviour is to
put combo-boxes on the form. You haven't gotten confused
by this kind of behaviour have you?

(david)

"Neil" <no****@nospam.net> wrote in message
news:Px***************@newsread3.news.pas.earthlin k.net...

I previously posted re. this, but thought I'd try again with a summary of
facts.

I have an Access 2000 MDB with a SQL Server 7 back end. There is a view
that is linked to the database via ODBC and has been in place for several
years without any performance problems.

Recently I added a couple of fields to the output of the view, and it
became very slow when scrolling. When just opened in the database window,
the linked view takes about a second to scroll down one screen. When
opened in the form (in Continuous Form view), it takes about 2-3 seconds.
It used to scroll just about instantaneously.

I tried removing the few fields I added to restore the view to its
previous form, but it had no effect. The view was still much slower than
it had been.

The total number of records returned from the view is about 1300, so it's
not a large number of records. The view has about 25 fields.

I found that when I link the view in the MDB without specifying a unique
index, it scrolls very quickly -- almost instantaneously. But when I
specify the unique index, it is slow. Since the view needs to be edited,
it needs the unique index defined.

As noted, it's been in place for years, with a unique index defined, yet
without the slowness. Any ideas as to what might have caused this and
what might be done would be appreciated. I've included the SQL for the
view below.

Thanks,

Neil

SQL FOR MAIN VIEW:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL FOR vwInventory_Dupes:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[david epsom dot com dot au]

You can open a linked view in datasheet mode, and it
is slower than opening a DAO recordset against the
same view.

The datasheet is slower because of the overhead of
scheduling the load as a background task.

You can open a bound form as continuos forms against
the view, and it is even slower than opening the view
in datasheet mode.

The form may be slower because it has combo-boxes on
it, and the combo's are requerying.

It appears that the problem is not with the view as
such, but rather with what Access is doing when rendering
the view for display.

Did you make any changes to the form at about the
same time as you made changes to the view? If you
try with an old copy of the application, what kind
of behaviour do you get?

(david)

"Neil" <no****@nospam.net> wrote in message
news:mq*************@newsread2.news.pas.earthlink. net...

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:42***********************@lon-reader.news.telstra.net...

Q: If you open a DAO recordset on the view, how long does
it take (as a snapshot? as a dynaset with movelast?)

With both a dynaset and a snapshot it takes less than a second to both
open the recordset and move to the last record (1293 records total).

Normally, filling a datasheet is a background task.
According to the documentation,
http://office.microsoft.com/en-gb/as...876211033.aspx
the recordset should be filled at the rate of 100 records
every 10 seconds (!) That seems wrong! but you might
like to try changing it.

You can force form to load an entire recordset before
display. This means that there is a long pause before
the display is filled, but scrolling is then immediate.
The only way I /know/ to force this behaviour is to
put combo-boxes on the form. You haven't gotten confused
by this kind of behaviour have you?

I'm confused by what you're asking me if I've been confused about. :-)
Haven't gotten confused by what kind of behavior?

The form does have combo boxes on it, but not based on the view itself.

Neil

(david)

"Neil" <no****@nospam.net> wrote in message
news:Px***************@newsread3.news.pas.earthlin k.net...

I previously posted re. this, but thought I'd try again with a summary of
facts.

I have an Access 2000 MDB with a SQL Server 7 back end. There is a view
that is linked to the database via ODBC and has been in place for
several years without any performance problems.

Recently I added a couple of fields to the output of the view, and it
became very slow when scrolling. When just opened in the database
window, the linked view takes about a second to scroll down one screen.
When opened in the form (in Continuous Form view), it takes about 2-3
seconds. It used to scroll just about instantaneously.

I tried removing the few fields I added to restore the view to its
previous form, but it had no effect. The view was still much slower than
it had been.

The total number of records returned from the view is about 1300, so
it's not a large number of records. The view has about 25 fields.

I found that when I link the view in the MDB without specifying a unique
index, it scrolls very quickly -- almost instantaneously. But when I
specify the unique index, it is slow. Since the view needs to be edited,
it needs the unique index defined.

As noted, it's been in place for years, with a unique index defined, yet
without the slowness. Any ideas as to what might have caused this and
what might be done would be appreciated. I've included the SQL for the
view below.

Thanks,

Neil

SQL FOR MAIN VIEW:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL FOR vwInventory_Dupes:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[Neil]

Well, I'll have to say that you've taken the sharp metallic object and
struck it on the portion of it that's round and flat (i.e., hit the nail on
the head). The issue was with the form, not with the view after all. But
you'll never guess (and probably not believe) what the issue was.

First, as you suggested, I took an old copy of the form and tested it
against the same view. It worked fine, without the slowness. (Had never
thought to do this before, since I just assumed it was the view's issues --
augmented by the fact that the ADP view was faster than the MDB linked view;
but that's the way it's always been). Anyway, so I had two forms -- one
slow, one fast.

As you noted, combo boxes can slow things down. But in this case, there were
no combo box differences. I mean, there are two combo boxes on the form; but
they were there all along. The only additions were one text box and two
check boxes. So I added those three controls to a copy of the old form
(imported into the current mdb). Even with the additional controls, the form
was still fast. And those were the only changes I had made!

Well, *almost* the only changes I had made. There was one incidental change,
that was so incidental, in fact, that I didn't even consider it as something
to look at. But, as I compared the code in the two forms (since that was all
that was left to be different between the two), there was this one line that
was different. And that turned out to be the key!

To explain what that line is, let me explain the form a bit more. There is
actually more than one view in play here. There is one main view (that
returns the data for the form), which is the one I've been discussing here.
But there are a series of other views that are used to restrict the records
displayed using inner joins to the main view. The form itself has a series
of options, and, when the user selects one of those options (a radio button
or check box), it triggers a change in the form's recordsource. So it goes
like this:

1) User changes an option.

2) Option control's after update event is triggered, which may set other
options (a main option may set default values in certain sub-options), or
not; event then calls SetRecordSource() routine.

3) SetRecordSource() then generates a dynamic SQL statement, which is
comprised of main view, and, optionally, other views joined to it with inner
joins. Routine then sets form's recordsource to dynamic SQL.

That's what happens when an option control is modified. But when the form is
first opened, in the Form_Open event, the following happens:

1) AfterUpdate event for main option control is called, which sets
sub-option default values, and then calls SetRecordSource() (per #'s 2 & 3
above).

2) Form_Open event then calls SetRecordSource().

Now, you may notice that the call to SetRecordSource() in the Form_Open
event is redundant, since it is called indirectly when the Form_Open event
calls the main option control's AfterUpdate event, which then calls
SetRecordSource(). (That was probably just overlooked when I modified the
Form_Open event somewhere down the line.)

So, thinking nothing of it, when I modified the form some months back, I
commented out the explicit call to SetRecordSource() in the Form_Open event,
since it was being called by the main option control's AfterUpdate event.
And that turned out to be the issue.

When I replaced the explicit call to SetRecordSource() in the Form_Open
event, the form went back to being fast, as it had been! When I commented it
out again, it went back to being slow. Restore it again, fast again.

The displayed recordset was always correct (since SetRecordSource() was
always called at least once when the form was opened), and I verified that
the same records are displayed in either case. But, for some reason, when
the second, explicit call to SetRecordSource() was made from the Form_Open
event, the form was faster; when it was just called from the main option
control's AfterUpdate event, it was slow!

So, was this a question of the routine being called from the Form_Open event
explicitly, or just of it being called twice? To test it, I commented out
the call in the Form_Open event, and added a second call to the main option
control's AfterUpdate event. And, sure enough, it was fast with the two
calls to SetRecordSource() from the AfterUpdate event and none from the
From_Open event. So it was just a question of the recordsource being set
twice.

To give you an idea of the performance difference, when the form is first
opened, there are currently 157 records displayed. If I open the form with
SetRecordSource() being called twice when it is opened, then, if I hold down
the mouse button with the pointer on the down arrow of the vertical scroll
bar and scroll to the last record, then it takes about 10 seconds to scroll
to the last record (not counting the time it takes for the form to open,
which is minimal). With only one call to SetRecordSource() when the form is
opened, the same process to scroll to the last record takes 1 minute and 5
seconds! A huge difference!

The delay seems to be some sort of refresh it's doing each time the screen
changes. When SetRecordSource() is called twice in Form_Open, that refresh
time is minimal.

This is really bizarre (and especially strange and fortuitous since I only
had two calls to SetRecordSource() in the first place by accident!). There
is a clear and unmistakable difference between when the recordsource is
first set and when it's set a second time. Yet who would ever think to set a
recordsource twice with the same SQL on purpose? I know I sure wouldn't!
(Makes one question the soundness of using an MDB as a front end to SQL in
the first place.)

By the way, in case you're wondering, since the recordsource is reset each
time an option is set, does just setting an option (and, hence, setting the
recordsource a second time) accomplish the same results? The answer is yes.
Leaving one call to SetRecordSource() in Form_Open results in slowness; but
once an option is set and the recordsource is reset, the form is fine. (I
just never noticed this before because I was focusing on what had changed in
the form -- i.e., the addition of the three (now known to be blameless)
fields -- and assumed it would be the same with any options set for display
of records.)

I would be interested in if you (or anyone else) has a sound reason (or even
hunch) as to why this is happening, and what could be done to accomplish the
same thing using more standard methods. Or should we just start adding
double calls to set a form's recordsource when a form is opened?

(Wait! A brainstorm: the form is initial unbound. (I removed the
recordsource from the form, since the recordsource is going to be set in the
Form_Open event anyway. Why have the data be initially loaded only to be
immediately replaced with new data?) When I specified the main view that I
use as the recordsource in the form's recordsource property, the form works
fine when initially loaded, even with only one call to SetRecordSource(). So
apparently, one setting of the recordsource was necessary to change the form
from being unbound to bound, and another is necessary to get it to work
correctly as a bound form. Still doesn't explain *why* that would be the
case; but at least it's starting to make a little sense.)

So, anyway, THANK YOU so much for sticking with this and for pointing me in
the right direction. *This wouldn't have happened without you.*

Two lessons to be learned here:

1) Don't make forms bound to ODBC data initially unbound.

2) Don't give up on newsgroup discussions prematurely. :-)

Thanks again!

Neil
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:42***********************@lon-reader.news.telstra.net...

You can open a linked view in datasheet mode, and it
is slower than opening a DAO recordset against the
same view.

The datasheet is slower because of the overhead of
scheduling the load as a background task.

You can open a bound form as continuos forms against
the view, and it is even slower than opening the view
in datasheet mode.

The form may be slower because it has combo-boxes on
it, and the combo's are requerying.

It appears that the problem is not with the view as
such, but rather with what Access is doing when rendering
the view for display.

Did you make any changes to the form at about the
same time as you made changes to the view? If you
try with an old copy of the application, what kind
of behaviour do you get?

(david)

"Neil" <no****@nospam.net> wrote in message
news:mq*************@newsread2.news.pas.earthlink. net...

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:42***********************@lon-reader.news.telstra.net...

Q: If you open a DAO recordset on the view, how long does
it take (as a snapshot? as a dynaset with movelast?)

With both a dynaset and a snapshot it takes less than a second to both
open the recordset and move to the last record (1293 records total).

Normally, filling a datasheet is a background task.
According to the documentation,
http://office.microsoft.com/en-gb/as...876211033.aspx
the recordset should be filled at the rate of 100 records
every 10 seconds (!) That seems wrong! but you might
like to try changing it.

You can force form to load an entire recordset before
display. This means that there is a long pause before
the display is filled, but scrolling is then immediate.
The only way I /know/ to force this behaviour is to
put combo-boxes on the form. You haven't gotten confused
by this kind of behaviour have you?

I'm confused by what you're asking me if I've been confused about. :-)
Haven't gotten confused by what kind of behavior?

The form does have combo boxes on it, but not based on the view itself.

Neil

(david)

"Neil" <no****@nospam.net> wrote in message
news:Px***************@newsread3.news.pas.earthlin k.net...
I previously posted re. this, but thought I'd try again with a summary
of facts.

I have an Access 2000 MDB with a SQL Server 7 back end. There is a view
that is linked to the database via ODBC and has been in place for
several years without any performance problems.

Recently I added a couple of fields to the output of the view, and it
became very slow when scrolling. When just opened in the database
window, the linked view takes about a second to scroll down one screen.
When opened in the form (in Continuous Form view), it takes about 2-3
seconds. It used to scroll just about instantaneously.

I tried removing the few fields I added to restore the view to its
previous form, but it had no effect. The view was still much slower
than it had been.

The total number of records returned from the view is about 1300, so
it's not a large number of records. The view has about 25 fields.

I found that when I link the view in the MDB without specifying a
unique index, it scrolls very quickly -- almost instantaneously. But
when I specify the unique index, it is slow. Since the view needs to be
edited, it needs the unique index defined.

As noted, it's been in place for years, with a unique index defined,
yet without the slowness. Any ideas as to what might have caused this
and what might be done would be appreciated. I've included the SQL for
the view below.

Thanks,

Neil

SQL FOR MAIN VIEW:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL FOR vwInventory_Dupes:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[Bri]

Neil wrote:

(Wait! A brainstorm: the form is initial unbound. (I removed the
recordsource from the form, since the recordsource is going to be set in the
Form_Open event anyway. Why have the data be initially loaded only to be
immediately replaced with new data?) When I specified the main view that I
use as the recordsource in the form's recordsource property, the form works
fine when initially loaded, even with only one call to SetRecordSource(). So
apparently, one setting of the recordsource was necessary to change the form
from being unbound to bound, and another is necessary to get it to work
correctly as a bound form. Still doesn't explain *why* that would be the
case; but at least it's starting to make a little sense.)

It could be that in the unbound state there is something 'waiting' that
times out eventually?

In any case, when I do similar things to this, rather than leave the
form as unbound initially, I set the recordsource to the main
recordsource but with a where clause that forces no records to be
returned (eg WHERE myIdentityField=0). This gives you an empty recordset
that has all the fields in it. This also eliminates the initial form
displaying #NAME# in the controls.

--
Bri

[Neil]

"Bri" <no*@here.com> wrote in message
news:LP5ie.1374026$Xk.296760@pd7tw3no...

Neil wrote:

(Wait! A brainstorm: the form is initial unbound. (I removed the
recordsource from the form, since the recordsource is going to be set in
the Form_Open event anyway. Why have the data be initially loaded only to
be immediately replaced with new data?) When I specified the main view
that I use as the recordsource in the form's recordsource property, the
form works fine when initially loaded, even with only one call to
SetRecordSource(). So apparently, one setting of the recordsource was
necessary to change the form from being unbound to bound, and another is
necessary to get it to work correctly as a bound form. Still doesn't
explain *why* that would be the case; but at least it's starting to make
a little sense.)
It could be that in the unbound state there is something 'waiting' that
times out eventually?

Not sure what you mean. In it's previous, unbound state, the form was opened
and immediately populated via the Form_Open event (using default values for
the options). Not sure what would time out.

In any case, when I do similar things to this, rather than leave the form
as unbound initially, I set the recordsource to the main recordsource but
with a where clause that forces no records to be returned (eg WHERE
myIdentityField=0). This gives you an empty recordset that has all the
fields in it. This also eliminates the initial form displaying #NAME# in
the controls.
Right, that's a good idea.

Neil

--
Bri

[david epsom dot com dot au]

"Neil" <no****@nospam.net> wrote in message
news:tH*****************@newsread3.news.pas.earthl ink.net...

So, was this a question of the routine being called from the Form_Open
event explicitly, or just of it being called twice? To test it, I
commented out the call in the Form_Open event, and added a second call to
the main option control's AfterUpdate event. And, sure enough, it was fast
with the two calls to SetRecordSource() from the AfterUpdate event and
none from the From_Open event. So it was just a question of the
recordsource being set twice.

Well !!!!!!!!!!!!!!

Unbound form becomes slow bound form, then fast bound form
when Recordsource is reset. As if resetting the recordsource
does a clean requery (we new that), but setting the recordsource
only schedules a slow background process. And it's not even
/changing/ the recordsource text that is important: just setting
it to the same value again!

BTW, in A2000 fiddling with the recordsource invalidates the
RecordsetClone object (not sure about other versions). It will
be interesting to look at that again and see if "set clear set"
is different from "set reset" or "set reset reset" etc
(david)

[Neil]

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:42***********************@lon-reader.news.telstra.net...

"Neil" <no****@nospam.net> wrote in message
news:tH*****************@newsread3.news.pas.earthl ink.net...

So, was this a question of the routine being called from the Form_Open
event explicitly, or just of it being called twice? To test it, I
commented out the call in the Form_Open event, and added a second call to
the main option control's AfterUpdate event. And, sure enough, it was
fast with the two calls to SetRecordSource() from the AfterUpdate event
and none from the From_Open event. So it was just a question of the
recordsource being set twice.
Well !!!!!!!!!!!!!!

Unbound form becomes slow bound form, then fast bound form
when Recordsource is reset. As if resetting the recordsource
does a clean requery (we new that), but setting the recordsource
only schedules a slow background process. And it's not even
/changing/ the recordsource text that is important: just setting
it to the same value again!

FWIW, I also tried a requery, but the results were not the same. The form
was still slow. Only resetting the recordset a second time did the trick (or
leaving the form bound).

BTW, in A2000 fiddling with the recordsource invalidates the
RecordsetClone object (not sure about other versions). It will
be interesting to look at that again and see if "set clear set"
is different from "set reset" or "set reset reset" etc
Not following you here......

Neil



(david)

[Bri]

Neil wrote:

"Bri" <no*@here.com> wrote in message
news:LP5ie.1374026$Xk.296760@pd7tw3no...

It could be that in the unbound state there is something 'waiting' that
times out eventually?

Not sure what you mean. In it's previous, unbound state, the form was opened
and immediately populated via the Form_Open event (using default values for
the options). Not sure what would time out.

Well, a one thing that come to mind is subForms. For some reason,
subForms load before the main form and I have had all sorts of grief
from that phenomena. Having a valid yet empty recordsource defined in
the mainform has solved most of these issues.

--
Bri

[Neil]

Interesting. But, no, no subforms here. Just a single form displayed in
Continuous Forms view, with a SQL comprised of linked views as a
recordsource.

"Bri" <no*@here.com> wrote in message
news:%erie.1390496$8l.1052550@pd7tw1no...

Neil wrote:

"Bri" <no*@here.com> wrote in message
news:LP5ie.1374026$Xk.296760@pd7tw3no...

It could be that in the unbound state there is something 'waiting' that
times out eventually?

Not sure what you mean. In it's previous, unbound state, the form was
opened and immediately populated via the Form_Open event (using default
values for the options). Not sure what would time out.

Well, a one thing that come to mind is subForms. For some reason, subForms
load before the main form and I have had all sorts of grief from that
phenomena. Having a valid yet empty recordsource defined in the mainform
has solved most of these issues.

--
Bri

[Bri]

Neil wrote:

Interesting. But, no, no subforms here. Just a single form displayed in
Continuous Forms view, with a SQL comprised of linked views as a
recordsource.

Hmm, I'm out of ideas then as to what is causing this to happen.
Fortunately, there is the empty recordset source solution to fix it. It
would still be interesting to know what the actual cause of the problem
is in case it could come up in some other scenario.

Just had another thought. I have also delt with this scenario in a
completely different way in older apps (before I thought of the empty
recordset source idea). I would have the Source selection controls in
the Form header. The initial Form is unbound but the Detail section is
Visible=False (did this to hide the #NAME# in the controls). The code
that set the Rescordsource then made the Detail section visible. I
noticed some delay on the first search, but I put it down to all of the
Combos loading their rowsources now that they were visible. Maybe this
is a similar issue to what you are seeing? It was never that extreme (10
sec for first one, 3 sec subsequent ones) so I didn't investigate
further. This was against Jet data, not SQL.

--
Bri