I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the same,
but that the MDB was a more mature technology and less problematic than the
ADP technology. However, the speed difference I'm noticing with the ADP file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
60  9888 
The ways ADPs and MDBs use recordsets are very different, so sometimes one or
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection to
grab the list of keys for the records, then reads the actual records as needed
for display by doing individual selects of 10 records aby explicit key. This
can help speed when each row has a lot of data because only the rows that are
actually viewd will be read, not all of them. On the other hand, if the table
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each row
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <nj****@pxdy.com> wrote: I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the same,
but that the MDB was a more mature technology and less problematic than the
ADP technology. However, the speed difference I'm noticing with the ADP file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
You may try to add a virtual index on your linked view: http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net... I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the
same, but that the MDB was a more mature technology and less problematic
than the ADP technology. However, the speed difference I'm noticing with
the ADP file in regards to this view is significant and is very
disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote: You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including myself
have had excelent results using MDBs as front-ends to various kinds of SQL
Server back-end for many years before there was such a thing as an ADP. When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kq********************************@4ax.com... The ways ADPs and MDBs use recordsets are very different, so sometimes one
or
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a
connection to
grab the list of keys for the records, then reads the actual records as
needed
for display by doing individual selects of 10 records aby explicit key.
This
can help speed when each row has a lot of data because only the rows that
are
actually viewd will be read, not all of them. On the other hand, if the
table
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each
row
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't
get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if
each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <nj****@pxdy.com> wrote:
I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the
same,
but that the MDB was a more mature technology and less problematic than
the
ADP technology. However, the speed difference I'm noticing with the ADP
file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
> You may try to add a virtual index on your linked view: http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
If this doesn't help, then you will have to use an unbound form coupled
with SQL pass-through queries (or ADO objects) to resolve your speed
problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
From discussions in this newsgroup.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:FC***************@newsread3.news.pas.earthlin k.net... You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
If this doesn't help, then you will have to use an unbound form coupled
with SQL pass-through queries (or ADO objects) to resolve your speed
problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the
use of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
From discussions in this newsgroup.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:vv********************************@4ax.com... On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
(fill
the blanks, no spam please)> wrote:
You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled
with
SQL pass-through queries (or ADO objects) to resolve your speed problem
with
MDB. Another solution could be to go with TS to simulate a high-speed
LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including
myself
have had excelent results using MDBs as front-ends to various kinds of SQL
Server back-end for many years before there was such a thing as an ADP.
When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
Did you specify a field or set of fields to be used as the primary key for the
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <nj****@pxdy.com> wrote: The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kq********************************@4ax.com.. . The ways ADPs and MDBs use recordsets are very different, so sometimes one
or
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a
connection to
grab the list of keys for the records, then reads the actual records as
needed
for display by doing individual selects of 10 records aby explicit key.
This
can help speed when each row has a lot of data because only the rows that
are
actually viewd will be read, not all of them. On the other hand, if the
table
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each
row
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't
get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if
each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <nj****@pxdy.com> wrote:
I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the
same,
but that the MDB was a more mature technology and less problematic than
the
ADP technology. However, the speed difference I'm noticing with the ADP
file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
Well, perhaps the reason for our difference of opinion is that I never thought
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriting
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote: Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:vv********************************@4ax.com.. . On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
(fill
the blanks, no spam please)> wrote:
You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled
with
SQL pass-through queries (or ADO objects) to resolve your speed problem
with
MDB. Another solution could be to go with TS to simulate a high-speed
LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including
myself
have had excelent results using MDBs as front-ends to various kinds of SQL
Server back-end for many years before there was such a thing as an ADP.
When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the
same, but that the MDB was a more mature technology and less problematic
than the ADP technology. However, the speed difference I'm noticing with
the ADP file in regards to this view is significant and is very
disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Yes, a few things needs to cleared up here.
First, using a ADP with sql server is kind of like using a automatic truck
vs a truck with a standard shift. If you don't know trucks, and how to shift
gears, then obviously the automatic truck is going to be better. On the
other hand, an advanced driver will get the same performance out of the
standard truck....
If you look at the sql you posted, there is a number of tables involved.
If you just link a bunch of tables to sql server, and then tell ms-access to
"join" them together..you will often get poor performance. I mean, obviously
ms-access/JET has to pull data from multiple tables. With ODBC linked
tables, each table is *often* thought as a separate data source. With
a ADP, that sql is processed server side.
Remember, ms-access/JET has a difficult
time working with multiple ODBC DATA sources when you do a join on them. The
reason of course is that one table might be from Oracle, and the other might
be a local FoxPro table. When you join data through linked tables, you have
to remem ber what the Robot in Lost in space used to say:
"Danger ...Will Robison.....Danger"...
When you use a ADP, that whole sql statement is sent to sql server to be
processed.
So, of course, if you used a pass-through query, then both the ADP, and the
ODBC will performance the same.
However, you mentioned that you can't use a pass through. However, you STILL
are using a sql statement that have SEVERAL LINKED tables. ms-access is
going
to have to figure out those links for you!. This should not be a surprise if
you think about what is going on here. However, the simple solution here is
thus to simply create a view on sql server, and then link to that!
Doing this, you will get the same
performance again as the ADP.
So, the lesson here is that you simply have to be MORE conscience of sql
that joins tables together. You can often get away with at last one extra
joined table via link tables..but as a general rule, you have to build a
query
anyway, so, just built a view on the sql side..and you eliminate the
performance
problem.
So, our question is using a ADP going to be faster then a MDB with linked
tables?
answers: Yes, ADP will be faster if you don't pay attention to avoiding
bottle necks, and thinking about what is going on....
So, yes, you do need a bit more caution when using a mdb file and odbc
tables against sql server. Without question, a ADP allows you to be much
more lazy, as ALL
SQL is 100% executed on the sql server side, and thus it is MUCH harder to
screw things up. With a mdb/odbc linked tables, you need extra caution, and
extra effort to avoid bottle necks. In effect, the increased flexibility of
a
mdb/odbc setup means you have more rope and more room to hang yourself!!
However, with a small effort, and some caution, you can EASLEY get the same
performance with a mdb/odbc as you get with a
ADP.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ui**************@TK2MSFTNGP10.phx.gbl... Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate
that MDB files are much slower to access a SQL-Server backend.
The problem of slowness is not ms-access/jet, but that how the developer
uses sql server here.
When ms-access is used correctly, and thoughtfully by a developer, then the
limits of the application become that of sql server, and not ms-access at
al. There are companies out there with 1000 simultaneous ms-access users
hitting the same sql server database. using ms-access is no worse the using
VB6, c++ or any other ide. The ONLY differences here are that of poor
developers.
The use of linked views will greatly reduce the performance hit but even
with them, I don't see the benefice of creating hundred of views instead
of creating hundred of stored procedures.
HUH? You mean grabbing data from a link view vs a stored proc makes any
difference here? (it makes not ONE bit!!).
If you talking about some code that needs to update some data..then, sure,
by all means move the code from ms-access into a stored proc, but we are not
talking about bad development practices here.
Without question, execute sql on a JET based client through linked tables
needs caution. ANY time more then one table is involved, that sql should be
put on the server side, or pass through be used. If one follows this rule,
then 9 out 10 times, the odbc linked application will perform as good as the
ADP one.
The fact of the matter is that a native ole-db connection sql server vs a
odbc one has be thrashed out MANY TIMES. If I hit a database and ask the sql
server to return one record, both the ole-db, and a simply linked table in
ms-access both return one record, and produce approximate the same network
traffic.
If you want to get some decent speed over the internet, there are a lot
more of work to do using MDB than using ADP and even then, I'm not really
sure if the use of linked views will achieve the same level of
performance.
Linked views is the solution in this poster case. If the poster does this,
then no difference will be noticed. The problem here is not ms-access, but
users of ms-access who don't take the time to utilize, and effect use sql
server here.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
Compared to what? I know of people successfully using ms-access OVER DIAL UP
lines to sql server (you read that correctly!!). If you make the effort,
(and this is NO more effort then writing in VB6, or other tools, then you
get the same performance).
So, sure, if a person just lazily links multiple tables..and then tries to
build a local query based on all those linked tables..then you will not get
decent performance.
However, it is a myth that a ms-access odbc connection to sql server don't
work well. It will JUST as well as the other IDE's, and you get the same
performance....
Further, bound forms in ms-access can successful be used, but again, as long
as stupid things like opening a form to a table without some type of "where"
clause must be avoided. (I never do that in JET based solutions...let alone
sql server based ones anyway).
Again, the fact that SO many ms-access applications open up forms to a
table, and THEN let the user browse/search for a record is a HORRIBLE
design, and one that should be avoided.
So, it is not the fact of ms-access being bad, but the fact that it is SO
EASY to simply throw up a form with a large data set, and let the user "have
at it".
So, even bound forms can quite well be used over a wan.
The real problem here is bad practices...not ms-access...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal
I don't really understand your statement about poor developers: you are
telling me that if I forget about 90% (or 50, 60 or 70%, pick your number)
of the coding facilities of SQL-Server, I will be a better programmer?
I know that the use of linked views will provide some relief about the lack
of speed of linked ODBC tables; however, I don't see why this could be
considered as an advantage:
1) It will take as much time creating all the necessary views for each
form/sub-form/control using a join in their queries than it will take for
creating the relevant stored procedures.
2) When I will really need to use a stored procedure - for example for
having access to temporary tables, cursor, other stored procedures, etc. -
because of the underlying complexity of the request, I will have a pretty
leg if I've limited myself to the exclusive use of views because I'm using
ODBC linked tables.
(For obvious reasons, I won't comment on the possibility of using a sql
pass-through query in these cases.)
From what I can read from your post, it's like telling me that a car is as
good and as fast as another one for the same price, but that you have the
right to go in only half the city if you buy it. In my opinion, when you
have to pay the same price in working time (at the minimum), you should be
entitled to the same full capacity in programming capabilities and when the
price is higher, there should be more, not less, capabilities.
I don't see why limiting myself to the exclusive use of views and forgetting
about stored procedures will make me a better programmer but I you want to
go on this path, this is your right and your decision; not mine.
S. L.
"Albert D. Kallal" <ka****@msn.com> wrote in message
news:FYn%d.745819$8l.276231@pd7tw1no... "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ui**************@TK2MSFTNGP10.phx.gbl... Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate
that MDB files are much slower to access a SQL-Server backend.
The problem of slowness is not ms-access/jet, but that how the developer
uses sql server here.
When ms-access is used correctly, and thoughtfully by a developer, then
the limits of the application become that of sql server, and not ms-access
at al. There are companies out there with 1000 simultaneous ms-access
users hitting the same sql server database. using ms-access is no worse
the using VB6, c++ or any other ide. The ONLY differences here are that of
poor developers.
The use of linked views will greatly reduce the performance hit but even
with them, I don't see the benefice of creating hundred of views instead
of creating hundred of stored procedures.
HUH? You mean grabbing data from a link view vs a stored proc makes any
difference here? (it makes not ONE bit!!).
If you talking about some code that needs to update some data..then, sure,
by all means move the code from ms-access into a stored proc, but we are
not talking about bad development practices here.
Without question, execute sql on a JET based client through linked tables
needs caution. ANY time more then one table is involved, that sql should
be put on the server side, or pass through be used. If one follows this
rule, then 9 out 10 times, the odbc linked application will perform as
good as the ADP one.
The fact of the matter is that a native ole-db connection sql server vs a
odbc one has be thrashed out MANY TIMES. If I hit a database and ask the
sql server to return one record, both the ole-db, and a simply linked
table in ms-access both return one record, and produce approximate the
same network traffic.
If you want to get some decent speed over the internet, there are a lot
more of work to do using MDB than using ADP and even then, I'm not really
sure if the use of linked views will achieve the same level of
performance.
Linked views is the solution in this poster case. If the poster does this,
then no difference will be noticed. The problem here is not ms-access,
but users of ms-access who don't take the time to utilize, and effect use
sql server here.
You are entitled to your opinion but all the tests that I have done in
the past clearly indicate that the use of MDB to access a SQL-Server over
the internet is only, at its best, a kludge.
Compared to what? I know of people successfully using ms-access OVER DIAL
UP lines to sql server (you read that correctly!!). If you make the
effort, (and this is NO more effort then writing in VB6, or other tools,
then you get the same performance).
So, sure, if a person just lazily links multiple tables..and then tries to
build a local query based on all those linked tables..then you will not
get decent performance.
However, it is a myth that a ms-access odbc connection to sql server don't
work well. It will JUST as well as the other IDE's, and you get the same
performance....
Further, bound forms in ms-access can successful be used, but again, as
long as stupid things like opening a form to a table without some type of
"where" clause must be avoided. (I never do that in JET based
solutions...let alone sql server based ones anyway).
Again, the fact that SO many ms-access applications open up forms to a
table, and THEN let the user browse/search for a record is a HORRIBLE
design, and one that should be avoided.
So, it is not the fact of ms-access being bad, but the fact that it is SO
EASY to simply throw up a form with a large data set, and let the user
"have at it".
So, even bound forms can quite well be used over a wan.
The real problem here is bad practices...not ms-access...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Yes, there is a virtual primary key defined.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:fn********************************@4ax.com... Did you specify a field or set of fields to be used as the primary key for
the
view when you created the link? If not, it will be slower, and will not
be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <nj****@pxdy.com> wrote:
The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not
sure
if there's some boundary at around 24 fields, or if something else is
going
on. But the linked view is *very* slow, taking several seconds just to
bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in
scrolling.
Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kq********************************@4ax.com. .. The ways ADPs and MDBs use recordsets are very different, so sometimes
one
or
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a
connection to
grab the list of keys for the records, then reads the actual records as
needed
for display by doing individual selects of 10 records aby explicit key.
This
can help speed when each row has a lot of data because only the rows
that
are
actually viewd will be read, not all of them. On the other hand, if the
table
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for
each
row
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of
10,000
rows is applied, so if your recordset has more than that, you just won't
get
all the rows. Static means in loads all the data at once, then you
browse
through it in memeory. That's very fast once you load the data, but if
each
row has a lot of data, especially memo fields and such, it can take a
long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <nj****@pxdy.com> wrote:
I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL
7 back end. The view is scrolling very slowly. However, if I open the
view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front
end for SQL Server, the impression I got was that both were about the
same,
but that the MDB was a more mature technology and less problematic than
the
ADP technology. However, the speed difference I'm noticing with the ADP
file
in regards to this view is significant and is very disconcerting re.
using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
FWIW, in my case I'm testing the db on my laptop using MSDE, and the speed
difference is the same. So there isn't a WAN vs. LAN issue anyway. FWIW.
Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1s********************************@4ax.com... Well, perhaps the reason for our difference of opinion is that I never
thought
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or
rewriting
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca
(fill
the blanks, no spam please)> wrote:
Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate
that
MDB files are much slower to access a SQL-Server backend. The use of
linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred
of
stored procedures. If you want to get some decent speed over the
internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:vv********************************@4ax.com. .. On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
(fill
the blanks, no spam please)> wrote:
You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled
with
SQL pass-through queries (or ADO objects) to resolve your speed problem
with
MDB. Another solution could be to go with TS to simulate a high-speed
LAN.
I don't know where you got the impression that MDB were about the same
as
ADP in term of speed. It has been repeated a number of times that the
use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including
myself
have had excelent results using MDBs as front-ends to various kinds of
SQL
Server back-end for many years before there was such a thing as an ADP.
When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
Mr. Kallal:
Perhaps you misread my original post, since you wrote:
"The problem of slowness is not ms-access/jet, but that how the developer
uses sql server here."
And:
"ANY time more then one table is involved, that sql should be put on the
server side, or pass through be used. If one follows this rule,
then 9 out 10 times, the odbc linked application will perform as good as
the ADP one."
And:
"Linked views is the solution in this poster case. If the poster does this,
then no difference will be noticed."
I say that you must have misread my original post, since linked views are
what are being used here; yet you offer them as a solution.
From my original post:
"I have a situation with an ODBC linked view in an Access 2000 MDB...."
Neil
"Albert D. Kallal" <ka****@msn.com> wrote in message
news:FYn%d.745819$8l.276231@pd7tw1no... "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ui**************@TK2MSFTNGP10.phx.gbl... Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate
that MDB files are much slower to access a SQL-Server backend.
The problem of slowness is not ms-access/jet, but that how the developer
uses sql server here.
When ms-access is used correctly, and thoughtfully by a developer, then
the limits of the application become that of sql server, and not ms-access
at al. There are companies out there with 1000 simultaneous ms-access
users hitting the same sql server database. using ms-access is no worse
the using VB6, c++ or any other ide. The ONLY differences here are that of
poor developers.
The use of linked views will greatly reduce the performance hit but even
with them, I don't see the benefice of creating hundred of views instead
of creating hundred of stored procedures.
HUH? You mean grabbing data from a link view vs a stored proc makes any
difference here? (it makes not ONE bit!!).
If you talking about some code that needs to update some data..then, sure,
by all means move the code from ms-access into a stored proc, but we are
not talking about bad development practices here.
Without question, execute sql on a JET based client through linked tables
needs caution. ANY time more then one table is involved, that sql should
be put on the server side, or pass through be used. If one follows this
rule, then 9 out 10 times, the odbc linked application will perform as
good as the ADP one.
The fact of the matter is that a native ole-db connection sql server vs a
odbc one has be thrashed out MANY TIMES. If I hit a database and ask the
sql server to return one record, both the ole-db, and a simply linked
table in ms-access both return one record, and produce approximate the
same network traffic.
If you want to get some decent speed over the internet, there are a lot
more of work to do using MDB than using ADP and even then, I'm not really
sure if the use of linked views will achieve the same level of
performance.
Linked views is the solution in this poster case. If the poster does this,
then no difference will be noticed. The problem here is not ms-access,
but users of ms-access who don't take the time to utilize, and effect use
sql server here.
You are entitled to your opinion but all the tests that I have done in
the past clearly indicate that the use of MDB to access a SQL-Server over
the internet is only, at its best, a kludge.
Compared to what? I know of people successfully using ms-access OVER DIAL
UP lines to sql server (you read that correctly!!). If you make the
effort, (and this is NO more effort then writing in VB6, or other tools,
then you get the same performance).
So, sure, if a person just lazily links multiple tables..and then tries to
build a local query based on all those linked tables..then you will not
get decent performance.
However, it is a myth that a ms-access odbc connection to sql server don't
work well. It will JUST as well as the other IDE's, and you get the same
performance....
Further, bound forms in ms-access can successful be used, but again, as
long as stupid things like opening a form to a table without some type of
"where" clause must be avoided. (I never do that in JET based
solutions...let alone sql server based ones anyway).
Again, the fact that SO many ms-access applications open up forms to a
table, and THEN let the user browse/search for a record is a HORRIBLE
design, and one that should be avoided.
So, it is not the fact of ms-access being bad, but the fact that it is SO
EASY to simply throw up a form with a large data set, and let the user
"have at it".
So, even bound forms can quite well be used over a wan.
The real problem here is bad practices...not ms-access...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uN**************@TK2MSFTNGP14.phx.gbl... TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock
solid. However, this solution come with a price ($).
Not an issue with connection. I'm using the db locally for development with
MSDE and have the same situation with the linked view being slow. Definitely
not a network issue.
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will
give you the possibility to use the result of a read-only SQL pass-through
in a form but, obviously, with the obligation of having more coding work
to be done. Of course, some of this work can be partially automated. You
will find more information on that subject on books dealing with Access
and SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
Yes, I'm familiar with the approach. It would just be better to save the
development overhead. And, if one were to use unbound forms, then one might
as well just use VB.
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what
Access is doing when it communicates with the server.
Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is that
it was fine until I added three new fields. So I don't know if something
needs to be optimized, or going from 21 to 24 fields really makes that much
of a difference.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:FC***************@newsread3.news.pas.earthlin k.net... You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
If this doesn't help, then you will have to use an unbound form coupled
with SQL pass-through queries (or ADO objects) to resolve your speed
problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
I don't know where you got the impression that MDB were about the same
as ADP in term of speed. It has been repeated a number of times that
the use of MDB' linked tables and views is only a workable solution for
small databases on a fast Lan.
From discussions in this newsgroup.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...
I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open
the view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and
is very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
> If you look at the sql you posted, there is a number of tables involved. If you just link a bunch of tables to sql server, and then tell ms-access
to
"join" them together..you will often get poor performance.
Again, as noted in my other post, you misread my original post. The first
sentence in my post stated I was dealing with a linked view, and, before the
SQL I posted I said it was the view's SQL. The SQL is from a SQL view --
pure server side -- nothing done in Access except to link to the view and
display the data.
However, you mentioned that you can't use a pass through. However, you
STILL
are using a sql statement that have SEVERAL LINKED tables. ms-access is
going
to have to figure out those links for you!. This should not be a surprise
if
you think about what is going on here. However, the simple solution here
is
thus to simply create a view on sql server, and then link to that!
Which is what was done.
Doing this, you will get the same
performance again as the ADP.
Well, this gets us back to the original post: that is what was done; yet the
linked view in the MDB file is sluggish, taking a long time to scroll, and
the same linked view in the ADP file is quick. Both the MDB and ADP are
accessing a single view from SQL Server.
So, the lesson here is that you simply have to be MORE conscience of sql
that joins tables together. You can often get away with at last one extra
joined table via link tables..but as a general rule, you have to build a
query
anyway, so, just built a view on the sql side..and you eliminate the
performance
problem.
Apparently not -- at least not in this case.
In *theory* a view from SQL Server should perform the same in an ADP and
MDB, since the processing is done on the server side. But, as this situation
demonstrates, things are not always as simple as they appear in theory.
Oh, and before we go there, please note that the large-recordset issue has
already been addressed: there are only 1154 records returned by the view,
with 24 fields displayed.
So, our question is using a ADP going to be faster then a MDB with linked
tables?
answers: Yes, ADP will be faster if you don't pay attention to avoiding
bottle necks, and thinking about what is going on....
So, yes, you do need a bit more caution when using a mdb file and odbc
tables against sql server. Without question, a ADP allows you to be much
more lazy, as ALL
SQL is 100% executed on the sql server side, and thus it is MUCH harder to
screw things up. With a mdb/odbc linked tables, you need extra caution,
and
extra effort to avoid bottle necks. In effect, the increased flexibility
of
a
mdb/odbc setup means you have more rope and more room to hang yourself!!
However, with a small effort, and some caution, you can EASLEY get the
same
performance with a mdb/odbc as you get with a
ADP.
Well, I'm open to suggestions!
Neil
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
First, what are these three fields that have slowed down your application
and are Invtry.MImage and Invtry.HImage image (binary) fields?
Second, you should really take a look with the profiler. Don't forget that
we don't have your database in front of us and that it is quite possible
that only you will have the possibility of solving this problem.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:w9****************@newsread3.news.pas.earthli nk.net...
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uN**************@TK2MSFTNGP14.phx.gbl... TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or
to solve the possibility of corruption when you LAN network is not rock
solid. However, this solution come with a price ($).
Not an issue with connection. I'm using the db locally for development
with MSDE and have the same situation with the linked view being slow.
Definitely not a network issue.
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will
give you the possibility to use the result of a read-only SQL
pass-through in a form but, obviously, with the obligation of having more
coding work to be done. Of course, some of this work can be partially
automated. You will find more information on that subject on books
dealing with Access and SQL-Server. (Personally, I prefer to use ADP but
it has many bugs.)
Yes, I'm familiar with the approach. It would just be better to save the
development overhead. And, if one were to use unbound forms, then one
might as well just use VB.
By using the profiler on the SQL-Server, it is also possible that you
will see something that will give you the possibility of resolving the
speed problem of you linked views by having a better understanding of
what Access is doing when it communicates with the server.
Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is
that it was fine until I added three new fields. So I don't know if
something needs to be optimized, or going from 21 to 24 fields really
makes that much of a difference.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:FC***************@newsread3.news.pas.earthlin k.net... You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
If this doesn't help, then you will have to use an unbound form coupled
with SQL pass-through queries (or ADO objects) to resolve your speed
problem with MDB.
As noted, the reason for not using pass-through is because it needs to
be updatable.
Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
I don't know where you got the impression that MDB were about the same
as ADP in term of speed. It has been repeated a number of times that
the use of MDB' linked tables and views is only a workable solution for
small databases on a fast Lan.
From discussions in this newsgroup.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...
>I have a situation with an ODBC linked view in an Access 2000 MDB with
>a SQL 7 back end. The view is scrolling very slowly. However, if I open
>the view in an ADP file, it scrolls quickly.
>
> I needed to use an ODBC link for the view because it needs to be
> editable. Otherwise, I would have used a pass-through query.
>
> In previous discussions about using an MDB file vs. an ADP file as a
> front end for SQL Server, the impression I got was that both were
> about the same, but that the MDB was a more mature technology and less
> problematic than the ADP technology. However, the speed difference I'm
> noticing with the ADP file in regards to this view is significant and
> is very disconcerting re. using an MDB file.
>
> Any thoughts/comments/suggestions would be appreciated. I've
> reproduced the view's SQL below for reference.
>
> Thanks,
>
> Neil
>
> SQL for view in question:
>
> 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, used as subquery:
>
> 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))
>
MImage and HImage are two of the new fields, and are bit. The other new
field was AdCode, which is varchar.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eS**************@tk2msftngp13.phx.gbl... First, what are these three fields that have slowed down your application
and are Invtry.MImage and Invtry.HImage image (binary) fields?
Second, you should really take a look with the profiler. Don't forget
that we don't have your database in front of us and that it is quite
possible that only you will have the possibility of solving this problem.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:w9****************@newsread3.news.pas.earthli nk.net...
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uN**************@TK2MSFTNGP14.phx.gbl... TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or
to solve the possibility of corruption when you LAN network is not rock
solid. However, this solution come with a price ($).
Not an issue with connection. I'm using the db locally for development
with MSDE and have the same situation with the linked view being slow.
Definitely not a network issue.
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will
give you the possibility to use the result of a read-only SQL
pass-through in a form but, obviously, with the obligation of having
more coding work to be done. Of course, some of this work can be
partially automated. You will find more information on that subject on
books dealing with Access and SQL-Server. (Personally, I prefer to use
ADP but it has many bugs.)
Yes, I'm familiar with the approach. It would just be better to save the
development overhead. And, if one were to use unbound forms, then one
might as well just use VB.
By using the profiler on the SQL-Server, it is also possible that you
will see something that will give you the possibility of resolving the
speed problem of you linked views by having a better understanding of
what Access is doing when it communicates with the server.
Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is
that it was fine until I added three new fields. So I don't know if
something needs to be optimized, or going from 21 to 24 fields really
makes that much of a difference.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:FC***************@newsread3.news.pas.earthlin k.net...
> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you
for which field(s) to use as pk. It then stores that information. You
can't update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form
> coupled with SQL pass-through queries (or ADO objects) to resolve your
> speed problem with MDB.
As noted, the reason for not using pass-through is because it needs to
be updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same
> as ADP in term of speed. It has been repeated a number of times that
> the use of MDB' linked tables and views is only a workable solution
> for small databases on a fast Lan.
From discussions in this newsgroup.
Neil
>
> S. L.
>
> "Neil" <nj****@pxdy.com> wrote in message
> news:Ux*******************@newsread1.news.pas.eart hlink.net...
>>I have a situation with an ODBC linked view in an Access 2000 MDB with
>>a SQL 7 back end. The view is scrolling very slowly. However, if I
>>open the view in an ADP file, it scrolls quickly.
>>
>> I needed to use an ODBC link for the view because it needs to be
>> editable. Otherwise, I would have used a pass-through query.
>>
>> In previous discussions about using an MDB file vs. an ADP file as a
>> front end for SQL Server, the impression I got was that both were
>> about the same, but that the MDB was a more mature technology and
>> less problematic than the ADP technology. However, the speed
>> difference I'm noticing with the ADP file in regards to this view is
>> significant and is very disconcerting re. using an MDB file.
>>
>> Any thoughts/comments/suggestions would be appreciated. I've
>> reproduced the view's SQL below for reference.
>>
>> Thanks,
>>
>> Neil
>>
>> SQL for view in question:
>>
>> 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, used as subquery:
>>
>> 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))
>>
>
>
Bit fields are often problematic with ADP; so maybe casting them to int will
give something.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:lF*************@newsread1.news.pas.earthlink. net... MImage and HImage are two of the new fields, and are bit. The other new
field was AdCode, which is varchar.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eS**************@tk2msftngp13.phx.gbl... First, what are these three fields that have slowed down your application
and are Invtry.MImage and Invtry.HImage image (binary) fields?
Second, you should really take a look with the profiler. Don't forget
that we don't have your database in front of us and that it is quite
possible that only you will have the possibility of solving this problem.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:w9****************@newsread3.news.pas.earthli nk.net...
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uN**************@TK2MSFTNGP14.phx.gbl...
TS = Terminal Server; you can also use Citrix. This is a quick fix
for getting a multi-user access to an Access' MDB file over the
Internet or to solve the possibility of corruption when you LAN network
is not rock solid. However, this solution come with a price ($).
Not an issue with connection. I'm using the db locally for development
with MSDE and have the same situation with the linked view being slow.
Definitely not a network issue.
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will
give you the possibility to use the result of a read-only SQL
pass-through in a form but, obviously, with the obligation of having
more coding work to be done. Of course, some of this work can be
partially automated. You will find more information on that subject on
books dealing with Access and SQL-Server. (Personally, I prefer to use
ADP but it has many bugs.)
Yes, I'm familiar with the approach. It would just be better to save the
development overhead. And, if one were to use unbound forms, then one
might as well just use VB.
By using the profiler on the SQL-Server, it is also possible that you
will see something that will give you the possibility of resolving the
speed problem of you linked views by having a better understanding of
what Access is doing when it communicates with the server.
Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is
that it was fine until I added three new fields. So I don't know if
something needs to be optimized, or going from 21 to 24 fields really
makes that much of a difference.
Neil
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:FC***************@newsread3.news.pas.earthlin k.net...
>> You may try to add a virtual index on your linked view:
>> http://support.microsoft.com/kb/q209123/
>
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you
> for which field(s) to use as pk. It then stores that information. You
> can't update the data otherwise. So it's already there.
>
>> If this doesn't help, then you will have to use an unbound form
>> coupled with SQL pass-through queries (or ADO objects) to resolve
>> your speed problem with MDB.
>
> As noted, the reason for not using pass-through is because it needs to
> be updatable.
>
>>Another solution could be to go with TS to simulate a high-speed LAN.
>
> What is "TS"?
>
>> I don't know where you got the impression that MDB were about the
>> same as ADP in term of speed. It has been repeated a number of times
>> that the use of MDB' linked tables and views is only a workable
>> solution for small databases on a fast Lan.
>
> From discussions in this newsgroup.
>
> Neil
>
>
>>
>> S. L.
>>
>> "Neil" <nj****@pxdy.com> wrote in message
>> news:Ux*******************@newsread1.news.pas.eart hlink.net...
>>>I have a situation with an ODBC linked view in an Access 2000 MDB
>>>with a SQL 7 back end. The view is scrolling very slowly. However, if
>>>I open the view in an ADP file, it scrolls quickly.
>>>
>>> I needed to use an ODBC link for the view because it needs to be
>>> editable. Otherwise, I would have used a pass-through query.
>>>
>>> In previous discussions about using an MDB file vs. an ADP file as a
>>> front end for SQL Server, the impression I got was that both were
>>> about the same, but that the MDB was a more mature technology and
>>> less problematic than the ADP technology. However, the speed
>>> difference I'm noticing with the ADP file in regards to this view is
>>> significant and is very disconcerting re. using an MDB file.
>>>
>>> Any thoughts/comments/suggestions would be appreciated. I've
>>> reproduced the view's SQL below for reference.
>>>
>>> Thanks,
>>>
>>> Neil
>>>
>>> SQL for view in question:
>>>
>>> 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, used as subquery:
>>>
>>> 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 wrote: MImage and HImage are two of the new fields, and are bit. The other new
field was AdCode, which is varchar.
May not have anything to do with speed and you may have already done
this but with bit fields, make sure they are "not null" and default to 0.
--
This sig left intentionally blank
> Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:uJ**************@TK2MSFTNGP12.phx.gbl...
I don't really understand your statement about poor developers: you are
telling me that if I forget about 90% (or 50, 60 or 70%, pick your number)
of the coding facilities of SQL-Server, I will be a better programmer?
No, all I am saying is that if a developer uses good designs, and good
practices, then good performance will result. My only point here is that
some things such as building join queries ACROSS linked tables is prone to
problems in ms-access. A developer in VB6, c++, or in ms-access would
normally create a view on the server side, or use a pass-through query. So,
all I am saying that good developer practices that make sql server work well
applies to c++, VB, and ms-access developers. The POINT I AM MAKING here is
that it is NOT ms-access is the problem for performance with sql server as
is
WIDELY stated. Sure, joins via linked tables is a problem..but then again,
users of other development environments DO NOT have this option anyway!!
This
issue here is of POOR practices...NOT ms-access!
I know that the use of linked views will provide some relief about the
lack of speed of linked ODBC tables; however, I don't see why this could
be considered as an advantage:
1) It will take as much time creating all the necessary views for each
form
Why do you need to create a view for each form? I am not suggesting that at
all!!!! If you got a form bond to a table, and in my many years of ms-access
development, MOST FORMS are bound to ONE TABLE, then you don't have a
problem!.
Ms-access via odbc tables (no joins...right!), just works fine. In fact, you
get every bit the performance of a adp in this case!!
Sure, in this posers example, a continues form with complex join was
obviously
going to involve SEVERAL tables, and thus one needs to use a view. Again,
a person with a brain will simply make a decision here as to the best
approach
(you got complex joins...use a view...you got a single table..then using the
link to the table is fine!). However, no where do I suggest that you replace
a form bound to a linked table with a view ALL THE TIME.
I also should point out that a form/sub form works well with
linked tables.
2) When I will really need to use a stored procedure - for example for
having access to temporary tables, cursor, other stored procedures, etc. -
because of the underlying complexity of the request, I will have a pretty
leg if I've limited myself to the exclusive use of views because I'm using
ODBC linked tables.
Well, which do you need? If you need to use a stored proc, then do so!! My
gosh, do I need to get you some diapers here? I am stunned here? If you need
a stored proc, and want to pass some parameters to it, then used a stored
proc? What are earth is the problem here? You sure as the heck don't need a
store proc in place of sql view???? (I am complete missing the point
here???). Are you actually hinting to use stored procs in place of views?
Look, all I am saying here is that a store proc that returns some values
from a table has ZERO advantage over a view.
Further, what is stopping you/anyone from using stored procs here? Heck,
use ADO....it been included in the default references for the last 3
versions of ms-access. Use ado to create, and "execute" your stored procs.
It is certainly a great thing to do, and I not sure exactly where I am, or
(am not) suggestion to do such thing???????
I don't see why limiting myself to the exclusive use of views and
forgetting about stored procedures will make me a better programmer but I
you want to go on this path, this is your right and your decision; not
mine.
Gee, where did I suggest that? I most certainly did suggest that using a
store proc has zero advantages over a view to return values.
<my quote>
HUH? You mean grabbing data from a link view vs a stored proc makes any
difference here? (it makes not ONE bit!!).
</quote>
So, in fact I am CLEARLY pointing out that you don't just use a store proc
if
you don't need to!! (again, what more would one say!!!). I am saying that is
a store proc don't give you an advantage, then you don't need to use one!!
(again, how simple do we want this discussion to get!!).
Of course, if you need a store proc, or that stored proc give you an
advantage..then use it!! If I have a table with 100 names it in, and I need
to return those 100 names, how on earth does using a store proc over a view
get me those 100 names any faster? How does the store proc reduce
bandwidth?
Hence, now, you come up with this gem:
<quote> The use of linked views will greatly reduce the performance hit but even
with them, I don't see the benefice of creating hundred of views instead
of creating hundred of stored procedures.
</quote>
No, nor does making 100 store proc give you an advantage (so, then, what
exactly what YOUR point!!). MY WHOLE POINT HERE IS NEITHER gives an
advantage. So, now why are you stewing big deal about store proc then? I
stated NOTHING on this issue either way. Apparently, you seem to have big
issue here!!
As I mentioned, if a stored proc is an advantage..then use one!! (I am just
shaking my head here..trying to figure out what the problem here is!!).
I make NO claim, or NO suggestion here to NOT use store procs.
I am certainly sorry that you seem to misunderstand me. And, perhaps it is
my fault that was miss-understood.
I am very sorry if I came across the wrong way here on this issue.
It was not in any way my intention to suggest that one should not use store
procs, and even more so I did not intend to suggest that one should use a
view over a stored procs when one SHOULD use a stored proc!
however, the myth of ms-access not scaling well with sql server, or the fact
of a odbc connection to sql server no working well is a false one, and it is
NOT the fault of ms-access. The only blame here in terms of bad performance
is that of developer practices.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal
> I say that you must have misread my original post, since linked views are what are being used here; yet you offer them as a solution.
From my original post:
"I have a situation with an ODBC linked view in an Access 2000 MDB...."
Ok, that is a surprise!
(and, you are right!!..I did miss the fact that you got a linked view!!).
Are you using filters on the form? (don't..if you are!!). Use the "where"
clause, and you can even stuff directly into he continues form the sql of
the view with parameters..and it should perform very good.
Hence, you can even go:
me.RecordSouce = "select * from MyView where city = 'Edmonton'"
So, does your continuous form have any type of filtering? If the form does
not, and in both cases, the ADP, and the odbc are just data being returned
from the view, then I would have to think that he data set being returned is
very large. It is certainly possible in this case that the ADP does a better
job of "displaying" the data. Then again, I would reduce the number of
records being returned to the form. There might be some different
interaction going on here..but you are using the same network..and the data
transfer in both cases should be the same....
I find my continues forms connected via ODBC to sql server display instant
in my cases. and, in fact you can even have the luxury of stuffing the sql
into the continues form as I did above..and again with odbc..the view works
very well indeed....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal
Albert Kallal wrote
Are you using filters on the form? (don't..if you are!!). Use the "where"
clause, and you can even stuff directly into he continues form the sql of
the view with parameters..and it should perform very good.
Actually, I have found this sort of thing happening with an ODBC linked
table that has had a filter saved on it. Open the view in Access in
design view and open the table properties. Look for anything in the
Filter line.
Failing that ,just open the view in datasheet view and right-click in
any field. Select 'Remove Filter/Sort', save the table and close it.
--
Albert Marshall
Marshall Le Botmel Ltd
01242 222017
You seem to be very disturbed about protecting the use of linked table under
Access. I will stop before your heart got an attack.
S. L.
"Albert D. Kallal" <ka****@msn.com> wrote in message
news:NbE%d.750174$6l.318096@pd7tw2no... Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uJ**************@TK2MSFTNGP12.phx.gbl...
I don't really understand your statement about poor developers: you are
telling me that if I forget about 90% (or 50, 60 or 70%, pick your number)
of the coding facilities of SQL-Server, I will be a better programmer?
No, all I am saying is that if a developer uses good designs, and good
practices, then good performance will result. My only point here is that
some things such as building join queries ACROSS linked tables is prone to
problems in ms-access. A developer in VB6, c++, or in ms-access would
normally create a view on the server side, or use a pass-through query.
So,
all I am saying that good developer practices that make sql server work
well
applies to c++, VB, and ms-access developers. The POINT I AM MAKING here
is
that it is NOT ms-access is the problem for performance with sql server as
is
WIDELY stated. Sure, joins via linked tables is a problem..but then again,
users of other development environments DO NOT have this option anyway!!
This
issue here is of POOR practices...NOT ms-access!
I know that the use of linked views will provide some relief about the
lack of speed of linked ODBC tables; however, I don't see why this could
be considered as an advantage:
1) It will take as much time creating all the necessary views for each
form
Why do you need to create a view for each form? I am not suggesting that
at
all!!!! If you got a form bond to a table, and in my many years of
ms-access
development, MOST FORMS are bound to ONE TABLE, then you don't have a
problem!.
Ms-access via odbc tables (no joins...right!), just works fine. In fact,
you
get every bit the performance of a adp in this case!!
Sure, in this posers example, a continues form with complex join was
obviously
going to involve SEVERAL tables, and thus one needs to use a view. Again,
a person with a brain will simply make a decision here as to the best
approach
(you got complex joins...use a view...you got a single table..then using
the
link to the table is fine!). However, no where do I suggest that you
replace
a form bound to a linked table with a view ALL THE TIME.
I also should point out that a form/sub form works well with
linked tables.
2) When I will really need to use a stored procedure - for example for
having access to temporary tables, cursor, other stored procedures,
etc. -
because of the underlying complexity of the request, I will have a pretty
leg if I've limited myself to the exclusive use of views because I'm
using
ODBC linked tables.
Well, which do you need? If you need to use a stored proc, then do so!! My
gosh, do I need to get you some diapers here? I am stunned here? If you
need
a stored proc, and want to pass some parameters to it, then used a stored
proc? What are earth is the problem here? You sure as the heck don't need
a
store proc in place of sql view???? (I am complete missing the point
here???). Are you actually hinting to use stored procs in place of views?
Look, all I am saying here is that a store proc that returns some values
from a table has ZERO advantage over a view.
Further, what is stopping you/anyone from using stored procs here? Heck,
use ADO....it been included in the default references for the last 3
versions of ms-access. Use ado to create, and "execute" your stored procs.
It is certainly a great thing to do, and I not sure exactly where I am, or
(am not) suggestion to do such thing???????
I don't see why limiting myself to the exclusive use of views and
forgetting about stored procedures will make me a better programmer but I
you want to go on this path, this is your right and your decision; not
mine.
Gee, where did I suggest that? I most certainly did suggest that using a
store proc has zero advantages over a view to return values.
<my quote>
HUH? You mean grabbing data from a link view vs a stored proc makes any
difference here? (it makes not ONE bit!!).
</quote>
So, in fact I am CLEARLY pointing out that you don't just use a store proc
if
you don't need to!! (again, what more would one say!!!). I am saying that
is
a store proc don't give you an advantage, then you don't need to use one!!
(again, how simple do we want this discussion to get!!).
Of course, if you need a store proc, or that stored proc give you an
advantage..then use it!! If I have a table with 100 names it in, and I
need
to return those 100 names, how on earth does using a store proc over a
view
get me those 100 names any faster? How does the store proc reduce
bandwidth?
Hence, now, you come up with this gem:
<quote> The use of linked views will greatly reduce the performance hit but even
with them, I don't see the benefice of creating hundred of views instead
of creating hundred of stored procedures.
</quote>
No, nor does making 100 store proc give you an advantage (so, then, what
exactly what YOUR point!!). MY WHOLE POINT HERE IS NEITHER gives an
advantage. So, now why are you stewing big deal about store proc then? I
stated NOTHING on this issue either way. Apparently, you seem to have big
issue here!!
As I mentioned, if a stored proc is an advantage..then use one!! (I am
just shaking my head here..trying to figure out what the problem here
is!!).
I make NO claim, or NO suggestion here to NOT use store procs.
I am certainly sorry that you seem to misunderstand me. And, perhaps it is
my fault that was miss-understood.
I am very sorry if I came across the wrong way here on this issue.
It was not in any way my intention to suggest that one should not use
store procs, and even more so I did not intend to suggest that one should
use a view over a stored procs when one SHOULD use a stored proc!
however, the myth of ms-access not scaling well with sql server, or the
fact of a odbc connection to sql server no working well is a false one,
and it is NOT the fault of ms-access. The only blame here in terms of bad
performance is that of developer practices.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
> Are you using filters on the form? (don't..if you are!!).
While I'm not using filters on the form, this issue of the linked view
dragging in MDB but being zippy in the ADP occurs when the view is opened by
itself in the database window, without any form. So, does your continuous form have any type of filtering? If the form does
not, and in both cases, the ADP, and the odbc are just data being returned
from the view, then I would have to think that he data set being returned
is very large.
As noted in my other post to you (posted after this one), the view only
returns 1154 records -- very small -- and has 24 fields.
It is certainly possible in this case that the ADP does a better job of
"displaying" the data. Then again, I would reduce the number of records
being returned to the form. There might be some different interaction
going on here..but you are using the same network..and the data transfer
in both cases should be the same....
This happens even without a network -- using the database on my laptop with
MSDE I get the same results of MDB vs. ADP.
I find my continues forms connected via ODBC to sql server display instant
in my cases. and, in fact you can even have the luxury of stuffing the sql
into the continues form as I did above..and again with odbc..the view
works very well indeed....
Well, like I said, I don't know what the hangup is; all I know is what I
see -- the ADP displays and scrolls instantly, and the MDB with linked view
has a serious delay in scrolling. So something's going on here.
Neil
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
There is no filter in the linked view. Thanks.
"Albert Marshall" <al*************@stowdata.co.uk> wrote in message
news:4a**************@stowdata.demon.co.uk... Albert Kallal wrote
Are you using filters on the form? (don't..if you are!!). Use the "where"
clause, and you can even stuff directly into he continues form the sql of
the view with parameters..and it should perform very good.
Actually, I have found this sort of thing happening with an ODBC linked
table that has had a filter saved on it. Open the view in Access in design
view and open the table properties. Look for anything in the Filter line.
Failing that ,just open the view in datasheet view and right-click in any
field. Select 'Remove Filter/Sort', save the table and close it.
--
Albert Marshall
Marshall Le Botmel Ltd
01242 222017
Albert D. Kallal wrote: answers: Yes, ADP will be faster if you don't pay attention to
avoiding bottle necks, and thinking about what is going on....
I have some questions related to thinking about what is going on.
I found your discussion here particularly illuminating: http://groups-beta.google.com/group/...2953d273484b28
Are there other ways of thinking about ADP and MDB that would similarly
clarify things?
Based on [SQL Server 7.0 info]: http://www.microsoft.com/technet/pro.../c0618260.mspx
Did the idea of indexed fields causing a particular "page" to load come
from leveraging assembly language concepts?
The page split mechanism seems to imply a natural 4K memory row
limitation (i.e., 8K / 2). What is the real row memory limit in
Access? I think Trevor discussed this once somewhere in
comp.databases.ms-access. Was the way memo fields are handled by
Access influenced by the page split mechanism? E.g., must row records
be kept under 4K so that the entire row's data will fit on the data
page during a page split?
The comparison of Access' pages with memory segments made me think of
the bizarre idea of installing Access on a RAM disk. Has anyone ever
tried something like that?
I've tried not to get too far into how Access is designed but a little
more knowledge will put some sense behind some of Access' limitation
tradeoffs and help me compare design alternatives.
Disclaimer: I don't usually think this way until late at night.
James A. Fortune
Steve,
Can you give me some examples of the kind of workarounds you have to do to
get the ADP to work at all? I find this situation with the MDB dragging with
a straight attached view but the ADP zipping along very discouraging, and
I'm reconsidering using an MDB instead of an ADP. So I probably need a good
dose of ADP problem anecdotes to keep me on the MDB straight and narrow. :-)
Thanks,
Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:vv********************************@4ax.com... On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
(fill
the blanks, no spam please)> wrote:
You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled
with
SQL pass-through queries (or ADO objects) to resolve your speed problem
with
MDB. Another solution could be to go with TS to simulate a high-speed
LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including
myself
have had excelent results using MDBs as front-ends to various kinds of SQL
Server back-end for many years before there was such a thing as an ADP.
When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.
First, note that the view in question had one main table, two lookup tables,
and a subview. Again, when linked to the MDB file it would scroll very
slowly; but in the ADP file, it was zippidy fast.
I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup table
and reattached the view, it was still slow.
Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!
Great, I figured; now I have to encrypt all my views that are slow and keep
a spare copy for editing. However (and this is the really bizarre part) that
turned out to not be the case.
Though I had tried refreshing the original link before encrypting the view
copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.
In other words, though the original view was unchanged, after I made a copy
of it and encrypted that copy, it became fast again.
I don't understand what happened here, but it clearly must be some sort of
compile/optimization issue which kicked in when I encrypted that copy of the
view.
So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then delete
it?
Thanks everyone for your assistance.
Neil
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net... I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the
same, but that the MDB was a more mature technology and less problematic
than the ADP technology. However, the speed difference I'm noticing with
the ADP file in regards to this view is significant and is very
disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
Interesting.
FWIW, I stopped using Refresh link years ago.
Now I just drop all links and re-create from scratch.
The optimization you noted is one of the many benefits of this approach.
I have posted my re-linking code about a billion times in the last 7 years.
But if you want to see it, let me know.
--
Joe Fallon
Access MVP
"Neil" <nj****@pxdy.com> wrote in message
news:8K***************@newsread1.news.pas.earthlin k.net... Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.
First, note that the view in question had one main table, two lookup
tables, and a subview. Again, when linked to the MDB file it would scroll
very slowly; but in the ADP file, it was zippidy fast.
I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup
table and reattached the view, it was still slow.
Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!
Great, I figured; now I have to encrypt all my views that are slow and
keep a spare copy for editing. However (and this is the really bizarre
part) that turned out to not be the case.
Though I had tried refreshing the original link before encrypting the view
copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.
In other words, though the original view was unchanged, after I made a
copy of it and encrypted that copy, it became fast again.
I don't understand what happened here, but it clearly must be some sort of
compile/optimization issue which kicked in when I encrypted that copy of
the view.
So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then
delete it?
Thanks everyone for your assistance.
Neil
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
Here a suggestion from the past (Access 97, doesn't know if it's still
valid): make that the names of the indexes of all primary keys come first in
alphabetical order before any other indexes. The old trick was to add a lot
of letter a at the beginning, something like: aaaaaPK_MyTable_MyField.
Access 97 was using the alphabetical order to determine which index was the
primary key. Don't know if it's still valid for A2000+.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:8K***************@newsread1.news.pas.earthlin k.net... Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.
First, note that the view in question had one main table, two lookup
tables, and a subview. Again, when linked to the MDB file it would scroll
very slowly; but in the ADP file, it was zippidy fast.
I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup
table and reattached the view, it was still slow.
Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!
Great, I figured; now I have to encrypt all my views that are slow and
keep a spare copy for editing. However (and this is the really bizarre
part) that turned out to not be the case.
Though I had tried refreshing the original link before encrypting the view
copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.
In other words, though the original view was unchanged, after I made a
copy of it and encrypted that copy, it became fast again.
I don't understand what happened here, but it clearly must be some sort of
compile/optimization issue which kicked in when I encrypted that copy of
the view.
So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then
delete it?
Thanks everyone for your assistance.
Neil
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
Refreshing the statistics of the indexes on the SQL-Server side can also be
the issue here.
S. L.
"Neil" <nj****@pxdy.com> wrote in message
news:8K***************@newsread1.news.pas.earthlin k.net... Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.
First, note that the view in question had one main table, two lookup
tables, and a subview. Again, when linked to the MDB file it would scroll
very slowly; but in the ADP file, it was zippidy fast.
I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup
table and reattached the view, it was still slow.
Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!
Great, I figured; now I have to encrypt all my views that are slow and
keep a spare copy for editing. However (and this is the really bizarre
part) that turned out to not be the case.
Though I had tried refreshing the original link before encrypting the view
copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.
In other words, though the original view was unchanged, after I made a
copy of it and encrypted that copy, it became fast again.
I don't understand what happened here, but it clearly must be some sort of
compile/optimization issue which kicked in when I encrypted that copy of
the view.
So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then
delete it?
Thanks everyone for your assistance.
Neil
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
In this case, though, it didn't make any difference. When I created copies
of the view, trying different things, I would attach the new view from
scratch into the database. Still was sluggish. Only after I encrypted that
one view did things change. Strange.
Neil
"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote in message
news:eL**************@TK2MSFTNGP12.phx.gbl... Interesting.
FWIW, I stopped using Refresh link years ago.
Now I just drop all links and re-create from scratch.
The optimization you noted is one of the many benefits of this approach.
I have posted my re-linking code about a billion times in the last 7
years.
But if you want to see it, let me know.
--
Joe Fallon
Access MVP
"Neil" <nj****@pxdy.com> wrote in message
news:8K***************@newsread1.news.pas.earthlin k.net... Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.
First, note that the view in question had one main table, two lookup
tables, and a subview. Again, when linked to the MDB file it would scroll
very slowly; but in the ADP file, it was zippidy fast.
I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup
table and reattached the view, it was still slow.
Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!
Great, I figured; now I have to encrypt all my views that are slow and
keep a spare copy for editing. However (and this is the really bizarre
part) that turned out to not be the case.
Though I had tried refreshing the original link before encrypting the
view copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.
In other words, though the original view was unchanged, after I made a
copy of it and encrypted that copy, it became fast again.
I don't understand what happened here, but it clearly must be some sort
of compile/optimization issue which kicked in when I encrypted that copy
of the view.
So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then
delete it?
Thanks everyone for your assistance.
Neil
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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))
Sylvain Lafontaine wrote: Here a suggestion from the past (Access 97, doesn't know if it's still
valid): make that the names of the indexes of all primary keys come first in
alphabetical order before any other indexes. The old trick was to add a lot
of letter a at the beginning, something like: aaaaaPK_MyTable_MyField.
Access 97 was using the alphabetical order to determine which index was the
primary key. Don't know if it's still valid for A2000+.
Yes it is. I generally design tables in SQL Server and it gives the name
PK_whatever to the primary key, however now when I create an additional
unique index I give it the name uidx_whatever.
--
This sig left intentionally blank
I usually just rename the PK to "aaaaaa...." or "__......" -- then you don't
have to worry about what other indexes are named. I don't like having to
remember to name an index such and such to avoid a data problem. I prefer to
rename the PK, and then it's set whether I remember when creating other
indexes or not.
Neil
"Trevor Best" <no****@besty.org.uk> wrote in message
news:42***********************@news.zen.co.uk... Sylvain Lafontaine wrote: Here a suggestion from the past (Access 97, doesn't know if it's still
valid): make that the names of the indexes of all primary keys come first
in alphabetical order before any other indexes. The old trick was to add
a lot of letter a at the beginning, something like:
aaaaaPK_MyTable_MyField.
Access 97 was using the alphabetical order to determine which index was
the primary key. Don't know if it's still valid for A2000+.
Yes it is. I generally design tables in SQL Server and it gives the name
PK_whatever to the primary key, however now when I create an additional
unique index I give it the name uidx_whatever.
--
This sig left intentionally blank
Thanks for clarifying the issues here.
Hum...1100 records is not much...
I have to think that some "thing" is not 100% here.
This happens even without a network -- using the database on my laptop
with MSDE I get the same results of MDB vs. ADP.
Excellent observations again...and the fact that you point out this occurs
when opening the linked "view" directly without a form is also very
helpfull.
At this point, I think we have some performance problem here...and one that
is
the result of some feature that needs to be turned off...
I would first try deleting the link to this view..and then re-create it....
Perhaps there is some lookup, or something that is grabbing data from MORE
then one data source..as that would cause a problem here. Also, you should
not have ANY links that are dead...or non existing..as I seen that as a
problem
also.
As a last try, you might want to take the ONE form, and the ONE link and put
it into
another mdb file..and test it separately outside of the existing
application...
But, at this point...I am at a loss...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal
Neil wrote: I usually just rename the PK to "aaaaaa...." or "__......" -- then you don't
have to worry about what other indexes are named. I don't like having to
remember to name an index such and such to avoid a data problem. I prefer to
rename the PK, and then it's set whether I remember when creating other
indexes or not.
Well, as I said, I do in SQL Server, I have to name the index, it
doesn't spoil me the way Access does :-)
--
This sig left intentionally blank
I just know that I'm always forgetting things. So having to remember to give
an index a certain prefix is one less thing to have to remember if I rename
the PK. But everyone has their preferences.
"Trevor Best" <no****@besty.org.uk> wrote in message
news:42***********************@news.zen.co.uk... Neil wrote: I usually just rename the PK to "aaaaaa...." or "__......" -- then you
don't have to worry about what other indexes are named. I don't like
having to remember to name an index such and such to avoid a data
problem. I prefer to rename the PK, and then it's set whether I remember
when creating other indexes or not.
Well, as I said, I do in SQL Server, I have to name the index, it doesn't
spoil me the way Access does :-)
--
This sig left intentionally blank As a last try, you might want to take the ONE form, and the ONE link and
put
it into
another mdb file..and test it separately outside of the existing
application...
This was a good idea. I tried it. Same results.
Neil
Well, I was premature in stating that this had been resolved. While
encrypting the view copy *did* in fact increase performance, there was a
price to pay. Apparently, when I encrypted the view copy and then refreshed
the original view's link, the original view's link lost its virtual primary
key. (Don't know why, but that's what happened. And I was able to reproduce
this phenomenon.) And, apparently, not having the virtual primary key made
the linked view scroll faster.
To test this I created two links to the same view: one with, and one without
virtual primary key. The one without the virtual primary key scrolled fast,
just like the ADP file; the one with the virtual primary key was slow.
I ran a trace on these two and on the ADP file when opening and scrolling
the view, and I think I see what's going on here.
When opening the view in the ADP file, profiler shows the following:
SELECT * FROM "vwWebMaintDuplicates"
However, when opening the linked view with the virtual primary key in place
from the MDB file, I get the following:
SELECT "dbo"."vwWebMaintDuplicates"."Index" FROM
"dbo"."vwWebMaintDuplicates"
sp_prepare @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7
int,@P8 int,@P9 int,@P10 int', N'SELECT
"Index","TITLE","AUTHILL1","attFirstEdition","attS igned","SignedCond","YRPUB","PRICE","Web","Status" ,"WebStatusPending","ActivateDate","DeactivateDate ","WebAddedBatchID","AllowDuplicate","WebAction"," WebActionPending","DateModified","DateWebActionApp lied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10', 1 select @P1
sp_execute 4, 1364, 1971, 1978, 2303, 3140, 3480, 3605, 4962, 6747, 6749
SELECT
Index","TITLE","AUTHILL1","attFirstEdition","attSi gned","SignedCond","YRPUB","PRICE","Web","Status", "WebStatusPending","ActivateDate","DeactivateDate" ,"WebAddedBatchID","AllowDuplicate","WebAction","W ebActionPending","DateModified","DateWebActionAppl ied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10
"Index" is the name of the primary key field in the main table in the view,
and it's the field that's used as the virtual primary key. The numbers 1364,
1971, etc., above, are the Index values for the first records that are
returned.
Thus, the MDB linked view with virtual primary key first gets a list of pk
values, then grabs the records ten at a time, specifying the pk values to
get.
This would explain why it's slow in scrolling -- it only gets the records in
sets of ten and has to keep grabbing them.
When I open the linked view in the MDB without the virtual primary key,
profiler shows:
SELECT "Index" ,"TITLE" ,"AUTHILL1" ,"attFirstEdition" ,"attSigned"
,"SignedCond" ,"YRPUB" ,"PRICE" ,"Web" ,"Status" ,"WebStatusPending"
,"ActivateDate" ,"DeactivateDate" ,"WebAddedBatchID" ,"AllowDuplicate"
,"WebAction" ,"WebActionPending" ,"DateModified" ,"DateWebActionApplied"
,"JIT" ,"MImage" ,"HImage" ,"AdCode" ,"OnWeb" FROM
"dbo"."vwWebMaintDuplicates"
This is essentially the same as what's going on in the ADP file, except that
it's specifying a field list, instead of "*".
Thus, without the virtual primary key, the MDB link is fast, like the ADP
file, because it's just getting a set of records. With the virtual primary
key, though, it's getting the list of pk's, and then grabbing records ten at
a time.
This appears to be an inherent property of the way the MDB file/ODBC driver
handles virtual primary keys, and doesn't appear to be anything I can
change. So the question still remains: why is it going slowly when it used
to not, and what can I do about it? Is there anyway to tell it to get all of
it's "sets of ten" at once, when the view is opened, rather than waiting
until the view is scrolled? Are there any other workarounds or settings that
can be tried here?
Anyway, we seem to be back at square one. But at least there's a bit more
knowledge now, and at least it explains the performance increase when the
encryption took place (though not why the virtual pk was lost in the first
place).
Thanks!
Neil
"Neil" <nj****@pxdy.com> wrote in message
news:8K***************@newsread1.news.pas.earthlin k.net... Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.
First, note that the view in question had one main table, two lookup
tables, and a subview. Again, when linked to the MDB file it would scroll
very slowly; but in the ADP file, it was zippidy fast.
I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup
table and reattached the view, it was still slow.
Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!
Great, I figured; now I have to encrypt all my views that are slow and
keep a spare copy for editing. However (and this is the really bizarre
part) that turned out to not be the case.
Though I had tried refreshing the original link before encrypting the view
copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.
In other words, though the original view was unchanged, after I made a
copy of it and encrypted that copy, it became fast again.
I don't understand what happened here, but it clearly must be some sort of
compile/optimization issue which kicked in when I encrypted that copy of
the view.
So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then
delete it?
Thanks everyone for your assistance.
Neil
"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
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, used as subquery:
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,
You said it was fast before you added the last three fields? Why don't
you remove those fields again, verify that it is indeed still speedy
with the virtual PK in place and see what the profiler says its doing then.
Perhaps there is something about these three fields that is triggering a
change in the execution plan. I know in Access itself (ie an Access BE)
that there are queries that Access will only grab the first so many
records and then continue to get more in the background as you scroll
through. IIRC, there may even be documentation on what triggers this.
Leaving the view without the virtual PK is not an option if you need
to be able to edit this view.
Oh, another thought, try changing the PK field name in the view to
another name. 'Index' might be a reserved word and that might add
overhead/confusion while it resolves that (I know it can figure it out,
but why make it do it if it doesn't need to).
Good and persistant work in tracking things down so far. This is a
strange one.
--
Bri
Neil wrote: Well, I was premature in stating that this had been resolved. While
encrypting the view copy *did* in fact increase performance, there was a
price to pay. Apparently, when I encrypted the view copy and then refreshed
the original view's link, the original view's link lost its virtual primary
key. (Don't know why, but that's what happened. And I was able to reproduce
this phenomenon.) And, apparently, not having the virtual primary key made
the linked view scroll faster.
To test this I created two links to the same view: one with, and one without
virtual primary key. The one without the virtual primary key scrolled fast,
just like the ADP file; the one with the virtual primary key was slow.
I ran a trace on these two and on the ADP file when opening and scrolling
the view, and I think I see what's going on here.
When opening the view in the ADP file, profiler shows the following:
SELECT * FROM "vwWebMaintDuplicates"
However, when opening the linked view with the virtual primary key in place
from the MDB file, I get the following:
SELECT "dbo"."vwWebMaintDuplicates"."Index" FROM
"dbo"."vwWebMaintDuplicates"
sp_prepare @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7
int,@P8 int,@P9 int,@P10 int', N'SELECT
"Index","TITLE","AUTHILL1","attFirstEdition","attS igned","SignedCond","YRPUB","PRICE","Web","Status" ,"WebStatusPending","ActivateDate","DeactivateDate ","WebAddedBatchID","AllowDuplicate","WebAction"," WebActionPending","DateModified","DateWebActionApp lied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10', 1 select @P1
sp_execute 4, 1364, 1971, 1978, 2303, 3140, 3480, 3605, 4962, 6747, 6749
SELECT
Index","TITLE","AUTHILL1","attFirstEdition","attSi gned","SignedCond","YRPUB","PRICE","Web","Status", "WebStatusPending","ActivateDate","DeactivateDate" ,"WebAddedBatchID","AllowDuplicate","WebAction","W ebActionPending","DateModified","DateWebActionAppl ied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10
"Index" is the name of the primary key field in the main table in the view,
and it's the field that's used as the virtual primary key. The numbers 1364,
1971, etc., above, are the Index values for the first records that are
returned.
Thus, the MDB linked view with virtual primary key first gets a list of pk
values, then grabs the records ten at a time, specifying the pk values to
get.
This would explain why it's slow in scrolling -- it only gets the records in
sets of ten and has to keep grabbing them.
When I open the linked view in the MDB without the virtual primary key,
profiler shows:
SELECT "Index" ,"TITLE" ,"AUTHILL1" ,"attFirstEdition" ,"attSigned"
,"SignedCond" ,"YRPUB" ,"PRICE" ,"Web" ,"Status" ,"WebStatusPending"
,"ActivateDate" ,"DeactivateDate" ,"WebAddedBatchID" ,"AllowDuplicate"
,"WebAction" ,"WebActionPending" ,"DateModified" ,"DateWebActionApplied"
,"JIT" ,"MImage" ,"HImage" ,"AdCode" ,"OnWeb" FROM
"dbo"."vwWebMaintDuplicates"
This is essentially the same as what's going on in the ADP file, except that
it's specifying a field list, instead of "*".
Thus, without the virtual primary key, the MDB link is fast, like the ADP
file, because it's just getting a set of records. With the virtual primary
key, though, it's getting the list of pk's, and then grabbing records ten at
a time.
This appears to be an inherent property of the way the MDB file/ODBC driver
handles virtual primary keys, and doesn't appear to be anything I can
change. So the question still remains: why is it going slowly when it used
to not, and what can I do about it? Is there anyway to tell it to get all of
it's "sets of ten" at once, when the view is opened, rather than waiting
until the view is scrolled? Are there any other workarounds or settings that
can be tried here?
Anyway, we seem to be back at square one. But at least there's a bit more
knowledge now, and at least it explains the performance increase when the
encryption took place (though not why the virtual pk was lost in the first
place).
Thanks!
Neil
Bri wrote: Leaving the view without the virtual PK is not an option if you need to
be able to edit this view.
Not necessarily, since the view contains more than 1 base table I doubt
that SQL Server will allow an update to it without use of an "instead
of" trigger.
--
This sig left intentionally blank
"Bri" <no*@here.com> wrote in message
news:1Eg0e.761680$Xk.358633@pd7tw3no... Neil,
You said it was fast before you added the last three fields? Why don't you
remove those fields again, verify that it is indeed still speedy with the
virtual PK in place and see what the profiler says its doing then.
I tried that before, and it seems the same without those three fields. So
either I was imagining that it was faster, or something else is going on.
Oh, another thought, try changing the PK field name in the view to another
name. 'Index' might be a reserved word and that might add
overhead/confusion while it resolves that (I know it can figure it out,
but why make it do it if it doesn't need to).
The name that Access (automatically) gave the vpk is "__uniqueindex" with
"Index" as its only field.
Good and persistant work in tracking things down so far. This is a strange
one.
Indeed. Raises the question: why am I staying with the MDB? Oh, right,
because others said that ADPs are much more problematic, even if better in
some ways.
Neil
--
Bri
Neil wrote: Well, I was premature in stating that this had been resolved. While
encrypting the view copy *did* in fact increase performance, there was a
price to pay. Apparently, when I encrypted the view copy and then
refreshed the original view's link, the original view's link lost its
virtual primary key. (Don't know why, but that's what happened. And I was
able to reproduce this phenomenon.) And, apparently, not having the
virtual primary key made the linked view scroll faster.
To test this I created two links to the same view: one with, and one
without virtual primary key. The one without the virtual primary key
scrolled fast, just like the ADP file; the one with the virtual primary
key was slow.
I ran a trace on these two and on the ADP file when opening and scrolling
the view, and I think I see what's going on here.
When opening the view in the ADP file, profiler shows the following:
SELECT * FROM "vwWebMaintDuplicates"
However, when opening the linked view with the virtual primary key in
place from the MDB file, I get the following:
SELECT "dbo"."vwWebMaintDuplicates"."Index" FROM
"dbo"."vwWebMaintDuplicates"
sp_prepare @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6
int,@P7 int,@P8 int,@P9 int,@P10 int', N'SELECT
"Index","TITLE","AUTHILL1","attFirstEdition","attS igned","SignedCond","YRPUB","PRICE","Web","Status" ,"WebStatusPending","ActivateDate","DeactivateDate ","WebAddedBatchID","AllowDuplicate","WebAction"," WebActionPending","DateModified","DateWebActionApp lied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2
OR "Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR
"Index" = @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10', 1
select @P1
sp_execute 4, 1364, 1971, 1978, 2303, 3140, 3480, 3605, 4962, 6747, 6749
SELECT
Index","TITLE","AUTHILL1","attFirstEdition","attSi gned","SignedCond","YRPUB","PRICE","Web","Status", "WebStatusPending","ActivateDate","DeactivateDate" ,"WebAddedBatchID","AllowDuplicate","WebAction","W ebActionPending","DateModified","DateWebActionAppl ied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2
OR "Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR
"Index" = @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10
"Index" is the name of the primary key field in the main table in the
view, and it's the field that's used as the virtual primary key. The
numbers 1364, 1971, etc., above, are the Index values for the first
records that are returned.
Thus, the MDB linked view with virtual primary key first gets a list of
pk values, then grabs the records ten at a time, specifying the pk values
to get.
This would explain why it's slow in scrolling -- it only gets the records
in sets of ten and has to keep grabbing them.
When I open the linked view in the MDB without the virtual primary key,
profiler shows:
SELECT "Index" ,"TITLE" ,"AUTHILL1" ,"attFirstEdition" ,"attSigned"
,"SignedCond" ,"YRPUB" ,"PRICE" ,"Web" ,"Status" ,"WebStatusPending"
,"ActivateDate" ,"DeactivateDate" ,"WebAddedBatchID" ,"AllowDuplicate"
,"WebAction" ,"WebActionPending" ,"DateModified" ,"DateWebActionApplied"
,"JIT" ,"MImage" ,"HImage" ,"AdCode" ,"OnWeb" FROM
"dbo"."vwWebMaintDuplicates"
This is essentially the same as what's going on in the ADP file, except
that it's specifying a field list, instead of "*".
Thus, without the virtual primary key, the MDB link is fast, like the ADP
file, because it's just getting a set of records. With the virtual
primary key, though, it's getting the list of pk's, and then grabbing
records ten at a time.
This appears to be an inherent property of the way the MDB file/ODBC
driver handles virtual primary keys, and doesn't appear to be anything I
can change. So the question still remains: why is it going slowly when it
used to not, and what can I do about it? Is there anyway to tell it to
get all of it's "sets of ten" at once, when the view is opened, rather
than waiting until the view is scrolled? Are there any other workarounds
or settings that can be tried here?
Anyway, we seem to be back at square one. But at least there's a bit more
knowledge now, and at least it explains the performance increase when the
encryption took place (though not why the virtual pk was lost in the
first place).
Thanks!
Neil
FWIW, I've been able to update the view from the MDB file with a virtual pk.
"Trevor Best" <no****@besty.org.uk> wrote in message
news:42***********************@news.zen.co.uk... Bri wrote: Leaving the view without the virtual PK is not an option if you need to
be able to edit this view.
Not necessarily, since the view contains more than 1 base table I doubt
that SQL Server will allow an update to it without use of an "instead of"
trigger.
--
This sig left intentionally blank
"Neil" <nj****@pxdy.com> wrote Indeed. Raises the question: why am I
staying with the MDB? Oh, right,
because others said that ADPs are much
more problematic, even if better in
some ways.
There's an old adage in the computer business: if it doesn't work, it
doesn't matter how fast it is. I think it is a telling point that a number
of respected, early adopters, advocates of ADPs have now abandoned their
use.
That said, in the very modest amount of modification/update work that I have
done with ADPs, I did not encounter any instances in which they just did not
work. Perhaps some of what appeared to be rather unusual design features
were, in fact, workarounds that the original author had to employ. However,
I suspect that most were because he did not realize that the SQL Server
tables had to have a Primary Key to be updateable when used with bound
Forms.
Larry Linson
Microsoft Access MVP
> Based on [SQL Server 7.0 info]: http://www.microsoft.com/technet/pro.../c0618260.mspx
Did the idea of indexed fields causing a particular "page" to load come
from leveraging assembly language concepts?
No, the concepts of "code" page in terms of assembler, is NOT related to the
above concept in a database. A "code" page in assembly is often just a
reference to a frame of data (in memory) used for some type of "mapping".
Often this type of mapping is referring to Localization (different languages
in windows for example have a code page, one is different for French,
English etc. You "load", or set a pointer to that page..and thus all
routines now transfer different characters etc correctly).).
The page split mechanism seems to imply a natural 4K memory row
limitation (i.e., 8K / 2). What is the real row memory limit in
Access?
I am not sure what the page size used, but I do believe it is 4k. As
computers get faster, and more ram available, the page size tends to go up a
bit. (too large, and you do waste disk i/o).
Remember, access97 (JET 3.x used page locking, and did not support record
locking). JET 4.0 (access 2000 and later) does in fact support record
locking. (what actually happens is the page lock occurs only during a
update...as each record can now be locked individual).
Remember, since records in ms-access now are VARIABLE length, then you need
a means to resolve to SINGLE record. What happens is the index routines
eventually translate WHERE/what BUCKET the record you are looking for. At
this point, when the bucket if found..then a SEQUENTIAL SEARCH occurs for
your record!!. Thus, with small records, you might easily fit 10 records
into a page (note that I am using the word, page, frame, bucket interchange
here...they all mean the same thing!).
This page based systems also explain why often running some update routines
can cause serous file bloat. IF the group of records you just modified now
expands beyond the size of the bucket where the records reside..then you got
split up the page..and re-arrange the records. If you go over the page size
by just ONE byte, you need a WHOLE NEW page, and the all of the "rest" of
the page is wasted. Of course, now that you got a extra almost empty
page..some records can be added without having to expand the file. (this is
why a file grows REAL fast RIGHT after a compact (any adding will cause
spillage out of the tightly packed frame. As the application use settles
down, then each time you modify a record, it is more likely to fit in its
page..and not spill over into another "needed" page).
So, note that each of those page frames are OFTEN referred to as a bucket in
database systems. And, normally, a hash-code scheme is used to resolve the
location of a stored record to that single bucket....then at which time a
sequential search occurs. These buckets are thus the SMALLEST size of data
that the JET engine can work with.
Was the way memo fields are handled by
Access influenced by the page split mechanism? E.g., must row records
be kept under 4K so that the entire row's data will fit on the data
page during a page split?
I don't know the answer to the above. However, memo fields can be quite
large in size...and thus really are just a "list" of linked frames. The
limit of the size of a memo field is thus just a question of how many bytes
in the linked list of frames is allowed. For example, if you only allow 1
byte, then you can have from 0 to 255 frames linked sequentially. I also
don't know if the frames have a forward link + backward link (some systems
do). Anyway, lets just assume a forward link byte in each frame. Thus, you
can have 0-255 linked frames x 4k would be your max object size....which is
of course not the case here. In fact, a memo field can be 1 gig in size.
(so, the link list data must be about 32 bits in size..as 16 would not be
enough). So, a large memo is just a sequential list of frames linked
together.
The comparison of Access' pages with memory segments made me think of
the bizarre idea of installing Access on a RAM disk. Has anyone ever
tried something like that?
While the data in the disk file is organized in "pages", or so called
frames, the fact is that the operating system (windows) takes parts of the
file that JET is working on, and pulls it into ram anyway. Further, JET
maintains it own cache in ram of "frames". So, after you update, or read a
few records, then they are in ram anyway. In fact, JET can mark a frame for
update..but wait..and do something more important (like read a frame in the
pending list of frames to be read).
You certainly would see some performance improvements by placing a database
in a ram based drive, but those performance improves would be UN-related to
the fact of JET being a "frame", or page based database system.
And, for files of only a few megs, and smaller table sizes, the whole table
gets well cached in memory anyway..
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal
> That said, in the very modest amount of modification/update work that I have
done with ADPs, I did not encounter any instances in which they just did
not
work. Perhaps some of what appeared to be rather unusual design features
were, in fact, workarounds that the original author had to employ.
However,
I suspect that most were because he did not realize that the SQL Server
tables had to have a Primary Key to be updateable when used with bound
Forms.
Not sure I'm following you here. You mean the original author of the ODBC
mechanism for getting SQL records? Sorry for not following.
Neil
"Neil" <nj****@pxdy.com> wrote Not sure I'm following you here. You mean
the original author of the ODBC
mechanism for getting SQL records?
No, I am sorry that I was not clear -- I meant the author of the application
database I had to modify. Although ADP/ADO seemed a bit "quirky" to me, and
not as straightforward as DAO, it did work properly in the areas I had to
modify.
The client was primarily interested in features and function (and
controlling costs), so there was no opportunity to compare ADP/ADO versus
MDB/DAO.
In the one instance where a colleague did address a performance issue, it
was a "communication issue", not an Access issue.
Larry Linson
Microsoft Access MVP This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jeff Pritchard |
last post by:
I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a network...
|
by: diskoduro |
last post by:
Hi!!
Years ago I built a database to control the production of a little
factory.
The users wanted to work in a Windows Net workgroup so I...
|
by: Phil |
last post by:
My website has HTML and ASP codes, using database of MS ACCESS.
When i surf html coded pages, its content comes out very quickly, however,
with asp...
|
by: salad |
last post by:
I have an A97 application that is NOT split on a network. It is used by
15+ folks continually. It is quick and fast.
I split it several years...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
|
by: Naresh1 |
last post by:
What is WebLogic Admin Training?
WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
|
by: Matthew3360 |
last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function.
Here is my code.
...
|
by: AndyPSV |
last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
|
by: Arjunsri |
last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
|
by: Oralloy |
last post by:
Hello Folks,
I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA.
My problem (spelled failure) is with the...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
| |
