ADP vs. MDB: Speed

[Sylvain Lafontaine]

Bit fields are often problematic with ADP; so maybe casting them to int will
give something.

S. L.

MImage and HImage are two of the new fields, and are bit. The other new
field was AdCode, which is varchar.

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.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message 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.

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.

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))

 

[Trevor Best]

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.

 

[Albert D. Kallal]

wrote in message news:[email protected]...

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:

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]

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 Marshall]

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.

 

[Sylvain Lafontaine]

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.

 

[Neil]

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

 

[Neil]

There is no filter in the linked view. Thanks.

Albert Kallal wrote
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.

 

[jimfortune]

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/comp.databases.ms-access/msg/192953d273484b28

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/prodtechnol/sql/70/books/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

 

[Neil]

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

 

[Neil]

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

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))

 

[Joe Fallon]

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

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

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]

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.

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

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]

Refreshing the statistics of the indexes on the SQL-Server side can also be
the issue here.

S. L.

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

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]

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

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

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

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))

 

[Trevor Best]

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.

 

[Neil]

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

 

[Albert D. Kallal]

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...

 

[Trevor Best]

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

 

[Neil]

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.