sql server odbc: View Optimization? -- sql server odbc

You're in the

sql server odbc

group:

View Optimization?

Re: View Optimization?

XMVP
3/21/2005 9:14:24 PM

sql server odbc:
[quoted text, click to view]

a) Whatever happened to happen happened.

b) Try blowing on it.

View Optimization?

Neil
3/22/2005 2:59:57 AM

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

Re: View Optimization?

Neil
3/22/2005 6:30:12 AM

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

[quoted text, click to view]

Re: View Optimization?

Albert D. Kallal
3/22/2005 9:41:41 AM

[quoted text, click to view]

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

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

Re: View Optimization?

Neil
3/23/2005 1:04:13 AM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server odbc group.

View Other Months
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007

home · blog · groups

Questions? Comments? Contact the dn