Access sometimes won't use index of linked view

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
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@.gmail.com"
<meatusenet@.gmail.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
|||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...@.nomail.please> wrote:[vbcol=seagreen]
> 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...@.gmail.com"
> <meatuse...@.gmail.com> wrote:
>
>
>
>
|||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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
-Sue
On Fri, 06 Jul 2007 13:11:12 -0000, "meatusenet@.gmail.com"
<meatusenet@.gmail.com> wrote:

>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...@.nomail.please> wrote:
>
|||Thanks!
Sue Hoegemeier wrote:[vbcol=seagreen]
> 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/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> -Sue
> On Fri, 06 Jul 2007 13:11:12 -0000, "meatusenet@.gmail.com"
> <meatusenet@.gmail.com> wrote:

Access Query against SQL Server works only without criteria

Getting a weird error while trying out a query from Access 2003 on a
SQL Server 2005 table.

Want to compute the amount of leave taken by an emp during the year.
Since an emp might be off for half a day (forenoon or afternoon), have
the following computed field:

SessionOff: ([ForenoonFlag] And [AfternoonFlag])

The query works fine when there's no criterion on SessionOff.

However, when I try to get the records where the SessionOff equals 0, I
get the following error:
~~~~~
ODBC--call failed. [Microsoft][SQL Native Client][SQL server]
Incorrect syntax near the keyword 'NOT'. (#156)
~~~~~

I checked the SQL of the Access query, but there's no NOT anywhere in
it:
~~~~~
SELECT tblWorkDateAttendance.*
FROM tblWorkDate INNER JOIN tblWorkDateAttendance ON
tblWorkDate.WorkDate = tblWorkDateAttendance.WorkDate
WHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND
((tblWorkDateAttendance.WorkDate)<Date()) AND
((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND
((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND
((tblWorkDate.HolidayFlag)=False));
~~~~~

What gives?ShastriX (shastrix@.gmail.com) writes:
> However, when I try to get the records where the SessionOff equals 0, I
> get the following error:
> ~~~~~
> ODBC--call failed. [Microsoft][SQL Native Client][SQL server]
> Incorrect syntax near the keyword 'NOT'. (#156)
> ~~~~~
> I checked the SQL of the Access query, but there's no NOT anywhere in
> it:
> ~~~~~
> SELECT tblWorkDateAttendance.*
> FROM tblWorkDate INNER JOIN tblWorkDateAttendance ON
> tblWorkDate.WorkDate = tblWorkDateAttendance.WorkDate
> WHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND
> ((tblWorkDateAttendance.WorkDate)<Date()) AND
> ((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND
> ((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND
> ((tblWorkDate.HolidayFlag)=False));
> ~~~~~

Then again, this query does not run on SQL Server. (There is no
date() function in SQL Server and "= False" would yield an error
about missing column.

Presumably some transformation occurs along the way. You could use
the Profiler to see what is actually sent to SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello, ShastriX wrote

I assume that the ForenoonFlag and AfternoonFlag columns have the bit
datatype. I tried to recreate the problem on a smaller table:

CREATE TABLE Firme (
ID_Firma int PRIMARY KEY,
Nume nvarchar(50) NOT NULL UNIQUE,
Furnizor bit NOT NULL DEFAULT (0),
Client bit NOT NULL DEFAULT (0)
-- CHECK (Furnizor<>0 OR Client<>0)
)

I tried running the following Access query:

SELECT dbo_Firme.Nume, [Furnizor] And [Client] AS Expr1
FROM dbo_Firme;

and I saw that Access/Jet will first execute the following SQL query:
SELECT "dbo"."Firme"."ID_Firma" FROM "dbo"."Firme"
and then it will get the values for the Nume, Furnizor and Client
columns, for each of the rows returned, and it will compute Expr1 on
the client side.

When running the following query:

SELECT dbo_Firme.Nume, [Furnizor] And [Client] AS Expr1
FROM dbo_Firme
WHERE ((([Furnizor] And [Client])=0));

then Access/Jet will execute this SQL query:

SELECT "dbo"."Firme"."ID_Firma" FROM "dbo"."Firme" WHERE
((NOT(("Furnizor" = 0 ) ) AND NOT(("Client" = 0 ) ) ) = 0 )

The above query will give the following error: "Line 1: Incorrect
syntax near '='.". The error message is refferring to the last = sign,
because the expression before it is a boolean expression and it is not
possible to compare a boolean expression with an int value (or even a
bit value).

To convince Access to issue a correct SQL query, we can change the
Access query like this:

SELECT dbo_Firme.Nume, [Furnizor] And [Client] AS Expr1
FROM dbo_Firme
WHERE (((dbo_Firme.Furnizor)=0) AND ((dbo_Firme.Client)=0));

In this case, Access will execute the following SQL query:

SELECT "dbo"."Firme"."ID_Firma" FROM "dbo"."Firme" WHERE (("Furnizor" =
0 ) AND ("Client" = 0 ) )

and then it will get all the values for the Nume, Furnizor and Client
columns, to compute Expr1 on the client side.

Razvan|||Thanks, Erland, but I had no issue running the query in Access 2003 w/o
criteria.

The hitch came up only when I added the "0" criteria on the SessionOff
computed column.

Regards,
Shastri

Erland Sommarskog wrote:
> ShastriX (shastrix@.gmail.com) writes:
> Then again, this query does not run on SQL Server. (There is no
> date() function in SQL Server and "= False" would yield an error
> about missing column.
> Presumably some transformation occurs along the way. You could use
> the Profiler to see what is actually sent to SQL Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se|||Thanks a lot for your analysis, Razvan :-) Broke the

SessionOff: ([ForenoonFlag] And [AfternoonFlag]) = 0

into (([ForenoonFlag] = 0) OR ([AfternoonFlag] = 0)). Though this
required repeating all the other criteria for each part of the OR:

((tblWorkDateAttendance.WorkDate)<Date()) AND
((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND
((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND
((tblWorkDate.HolidayFlag)=False)

the query now works like a charm.

Thanks once again,

Shastri