Re: Access sometimes won't use index of linked view

#1

Being that the problem goes away when you drop and recreate
the index and then it comes back again in a couple of
months, I'd start looking into the possibility that the
index is fragmented when you start hitting the problems.

-Sue

On Fri, 29 Jun 2007 07:29:19 -0000, "meatusenet (AT) gmail (DOT) com"
<meatusenet (AT) gmail (DOT) com> wrote:

Quote:

Hello,

I have a weird problem with sql server 2000. I have a view wich
combines 2 tables like this:

SELECT foo
FROM dbo.tblA LEFT OUTER JOIN
dbo.tblB ON dbo.A.ID = dbo.B.ID

Table B contains a field named fldSorting, wich has an index. It is
used - you have already guessed it by now

- for sorting.

The view, linked with odbc, is the datasource on a form in ms access
like this: SELECT * FROM foo ORDER BY fldSorting;
This does work. However, for some weird reason, this view becomes
very, VERY slow from time to time (> 1 minute). This happens once in
three months or something. I have looked with the profiler what
exactly access is querying, and that is just normal: SELECT * FROM foo
ORDER BY fldSorting. If i execute this in the query analyzer, it is as
fast as always (<2 seconds)! This is extremely weird, right?
I have found a way to make it quick again, but only for a couple of
months: remove the index on field fldSorting and create it again.

How comes? I have absolutely no clue whatsoever.
1. Why does this problem pop up sometimes?
2. Why does sometimes the exact same query seems to not use an index
if access executes it via odbc, while executed via the query analyzer,
all is ok?

This is a very unpleasant problem since it is our main sql server, and
our ERP system is also running on this server. So every time this
happens, everybody has a break....

Does anybody has some ideas? I will be very happy with all thoughts...

Thanks in advance,

B. Lowsma
Netherlands

#2

Sure...read up on the subject in books online under the help
topic for DBCC SHOWCONTIG. You want to be doing some type of
index maintenance on a regular basis but you do want to take
other things into consideration with defragmentation of
indexes. This is a good source of information on the
subject, what to consider, etc:
Microsoft SQL Server 2000 Index Defragmentation Best
Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

-Sue

On Fri, 06 Jul 2007 13:11:12 -0000, "meatusenet (AT) gmail (DOT) com"
<meatusenet (AT) gmail (DOT) com> wrote:

Quote:

Thanks for your reply. So if this occurs i can possibly fix it with
DBCC DBREINDEX or DBCC INDEXDEFRAG? I will try that the first time it
happens again... If this is the case, is there anything i can do to
prevent this from happening?

TIA,

B. Lowsma

On 6 jul, 05:27, Sue Hoegemeier <S... (AT) nomail (DOT) please> wrote:
Being that the problem goes away when you drop and recreate
the index and then it comes back again in a couple of
months, I'd start looking into the possibility that the
index is fragmented when you start hitting the problems.

-Sue

On Fri, 29 Jun 2007 07:29:19 -0000, "meatuse... (AT) gmail (DOT) com"

meatuse... (AT) gmail (DOT) com> wrote:
Hello,

I have a weird problem with sql server 2000. I have a view wich
combines 2 tables like this:

SELECT foo
FROM dbo.tblA LEFT OUTER JOIN
dbo.tblB ON dbo.A.ID = dbo.B.ID

Table B contains a field named fldSorting, wich has an index. It is
used - you have already guessed it by now

- for sorting.

The view, linked with odbc, is the datasource on a form in ms access
like this: SELECT * FROM foo ORDER BY fldSorting;
This does work. However, for some weird reason, this view becomes
very, VERY slow from time to time (> 1 minute). This happens once in
three months or something. I have looked with the profiler what
exactly access is querying, and that is just normal: SELECT * FROM foo
ORDER BY fldSorting. If i execute this in the query analyzer, it is as
fast as always (<2 seconds)! This is extremely weird, right?
I have found a way to make it quick again, but only for a couple of
months: remove the index on field fldSorting and create it again.

How comes? I have absolutely no clue whatsoever.
1. Why does this problem pop up sometimes?
2. Why does sometimes the exact same query seems to not use an index
if access executes it via odbc, while executed via the query analyzer,
all is ok?

This is a very unpleasant problem since it is our main sql server, and
our ERP system is also running on this server. So every time this
happens, everybody has a break....

Does anybody has some ideas? I will be very happy with all thoughts...

Thanks in advance,

B. Lowsma
Netherlands

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Access sometimes won't use index of linked view

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

Re: Access sometimes won't use index of linked view - 07-05-2007 , 10:27 PM

Re: Access sometimes won't use index of linked view - 07-06-2007 , 09:46 PM