View Optimization?

[Neil]

I just resolved a strange situation I was having with an ODBC linked SQL 7
view in an Access 2000 MDB file, and I'm trying to get some understanding as
to what happened.

The linked view was scrolling very slowly in the MDB file; however, it
scrolled very quickly when accessed through an ADP file. Theoretically,
since it's a server-side object, it should appear the same in both files,
but it didn't.

Not knowing what was causing this, I tried various options. When I decided
to try encrypting the view, I first made a copy of it, and encrypted the
copy, and linked that copy to the MDB file. It scrolled without hanging as
the original one did.

But, strangely enough: once I encrypted the copy of the view, the original
view also now scrolled quickly (about 5-10 times faster than it had been). I
even deleted the encrypted copy, and the original view still scrolled very
quickly in the MDB file.

So, it seems that there was some sort of compile/optimization issue that
kicked in when I encrypted the copy of the view -- and not just for that one
view, but for the whole database (or at least the original view and its
copy). Whatever happened, it resolved the problem with the view hanging when
scrolled in the MDB file.

So, my questions are: a) what happened? and b) is there a way to get
whatever happened to happen without having to make a copy of a view, encrypt
it, and then delete it??

Thanks for any assistance.

Neil

 

[XMVP]

So, my questions are: a) what happened? and b) is there a way to get
whatever happened to happen without having to make a copy of a view,
encrypt it, and then delete it??

a) Whatever happened to happen happened.

b) Try blowing on it.

 

[Neil]

Here's an additional thought regarding this. The view in question contained
a group-by subview. When I copied the main view, the copy contained the same
subview. So perhaps when I encrypted the copy of the view, something got
optimized with the subview, and, hence, the original view worked better.

That would explain why encrypting one view would affect another. But it
still wouldn't explain:

- why encrypting would cause optimization in the first place;

- why the original view had problems when attached to an MDB file, but
didn't have any problems in an ADP file;

- why I am writing this at all, since there's probably no logical
explanation for any of this.

Neil

 

[Albert D. Kallal]

- why I am writing this at all, since there's probably no logical
explanation for any of this.

Actually, I very much appreciate you sharing that a solution was found. Glad
to see that the linked view "can" perform ok...

It is certainly possible some setting, or something was cached, or saved
somewhere.

You could run through Tony's FAQ of performance issues (very few of the
apply to odbc linked tables..but you might just take a quick read...).

http://www.granite.ab.ca/access/performancefaq.htm

(I would check the track name autocorrect...and sub-data sheets settings).

Once again..thanks for provoking the discussions on this...it been well
worth the show!!!

 

[Neil]

Actually, my posting was premature. Encrypting the view copy *did* in fact
increase performance; but I just found out why. 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.

So, apparently, with the virtual pk in place, the view link is slow; with
it, it's fast. I have two links to the same view in my MDB -- one with, and
one without virtual pk. One is slow, one is fast. So the virtual pk is
apparently it.

Anyway, I created this post when I thought it was a SQL optimization issue.
Now that I see it's an ODBC/linked table issue, I'm going to continue the
discussion in the original thread, "ADP vs. MDB: Speed." I will be posting
more information about this there, so if you could respond in that thread,
it would be appreciated.

Thanks,

Neil