SQL Server 7 Optimiser 
Author Message
 SQL Server 7 Optimiser

Don't laugh, things move slowly at my place of work, so we are still using
SQL Server 7.0 SP4...

Anyway I tried a query where the WHERE clause was a particular value

SELECT min(ProgrammeID),max(ProgrammeID) from Programme where [date]=37285

ProgrammeID is an integer and PK and clustered.
However there is also a unique index on [Date],... other field entries.

Incredibly, the PK was used to look up [Date]. That involves a whole PK scan
checking on [Date]. Channging this to

SELECT min(ProgrammeID),max(ProgrammeID) from Programme with
(index(IX_ChannelDateTime)) where [date]=37400

meant an instant response.

Seems that optimiser is not that bright. If the WHERE clause contains a
field which is a leading part of an index, the index should be used.

Any comments?

Stephen Howe

Stephen Howe

Stephen Howe



Fri, 06 Jan 2006 16:37:32 GMT
 SQL Server 7 Optimiser

Are you statistics up to date?


Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2003 All rights reserved.


Quote:
> Don't laugh, things move slowly at my place of work, so we are still using
> SQL Server 7.0 SP4...

> Anyway I tried a query where the WHERE clause was a particular value

> SELECT min(ProgrammeID),max(ProgrammeID) from Programme where [date]=37285

> ProgrammeID is an integer and PK and clustered.
> However there is also a unique index on [Date],... other field entries.

> Incredibly, the PK was used to look up [Date]. That involves a whole PK
scan
> checking on [Date]. Channging this to

> SELECT min(ProgrammeID),max(ProgrammeID) from Programme with
> (index(IX_ChannelDateTime)) where [date]=37400

> meant an instant response.

> Seems that optimiser is not that bright. If the WHERE clause contains a
> field which is a leading part of an index, the index should be used.

> Any comments?

> Stephen Howe

> Stephen Howe

> Stephen Howe



Fri, 06 Jan 2006 16:46:09 GMT
 SQL Server 7 Optimiser

Quote:
> Are you statistics up to date?

They may not be. How do I get them up to date?

Stephen



Fri, 06 Jan 2006 17:10:41 GMT
 SQL Server 7 Optimiser

Quote:
> Are you statistics up to date?

Even with

UPDATE STATISICS Programme

the example I gave still chooses the PK in prefer alternative index

Stephen



Fri, 06 Jan 2006 17:15:20 GMT
 SQL Server 7 Optimiser
Each release of SQL Server seems to improve the optimizer.  Having developed
large (>30 GB) DB's in 6.5, 7, and 2000, I've found that the execution
plans, given a uniform index, can vary greatly.  So sometimes you wind up
giving the query hints as you have done and moving on.

So my suggestion would be to leave the hint in place and document that the
hint is being used in a central location so that should you decide to
upgrade to 2000 or Yukon you can reexamine whether the hint is still
required.

- Clay


Quote:
> Don't laugh, things move slowly at my place of work, so we are still using
> SQL Server 7.0 SP4...

> Anyway I tried a query where the WHERE clause was a particular value

> SELECT min(ProgrammeID),max(ProgrammeID) from Programme where [date]=37285

> ProgrammeID is an integer and PK and clustered.
> However there is also a unique index on [Date],... other field entries.

> Incredibly, the PK was used to look up [Date]. That involves a whole PK
scan
> checking on [Date]. Channging this to

> SELECT min(ProgrammeID),max(ProgrammeID) from Programme with
> (index(IX_ChannelDateTime)) where [date]=37400

> meant an instant response.

> Seems that optimiser is not that bright. If the WHERE clause contains a
> field which is a leading part of an index, the index should be used.

> Any comments?

> Stephen Howe

> Stephen Howe

> Stephen Howe



Fri, 06 Jan 2006 18:09:40 GMT
 SQL Server 7 Optimiser
Stephen,

1. Was this the query you used, or was it a query in the following form?

SELECT min(ProgrammeID),max(ProgrammeID) from Programme where

I ask this, because the above is a different query for the query
optimizer. Then, the index selection not only depends on selectivity,
but also on data distribution.

2. What datatype is [date]? If it is not int, then datatype conversion
has to take place. In that case you might want to try

SELECT min(ProgrammeID),max(ProgrammeID) from Programme where
[date]=CAST(37285 as datetime)

3. The query optimizer creates the optimal plan based on a cold cache.
It might not be the most efficient if most (or all) table data is in
cache. It is the fastest plan if no data is in cache. You can flush the
cache with the following commands:

    --clear proc cache
    DBCC FREEPROCCACHE
    --write dirty pages to disk
    CHECKPOINT
    --free clean buffers
    DBCC DROPCLEANBUFFERS

Hope this helps,
Gert-Jan

Quote:

> Don't laugh, things move slowly at my place of work, so we are still using
> SQL Server 7.0 SP4...

> Anyway I tried a query where the WHERE clause was a particular value

> SELECT min(ProgrammeID),max(ProgrammeID) from Programme where [date]=37285

> ProgrammeID is an integer and PK and clustered.
> However there is also a unique index on [Date],... other field entries.

> Incredibly, the PK was used to look up [Date]. That involves a whole PK scan
> checking on [Date]. Channging this to

> SELECT min(ProgrammeID),max(ProgrammeID) from Programme with
> (index(IX_ChannelDateTime)) where [date]=37400

> meant an instant response.

> Seems that optimiser is not that bright. If the WHERE clause contains a
> field which is a leading part of an index, the index should be used.

> Any comments?

> Stephen Howe

> Stephen Howe

> Stephen Howe



Fri, 06 Jan 2006 20:52:16 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. statistical optimiser vs syntactical optimiser

2. HOw to know which optimiser server using

3. SQL 7 Optimiser Problems

4. MS SQL 7, SP1 and optimiser statistics

5. SQL Query optimiser

6. PL/SQL Optimisers

7. Optimiser oddity in PL./SQL

8. SQL Anywhere Optimiser through ODBC

9. SQL Optimiser - choosing indices

10. QUERY OPTIMISER

11. Worktables and the Query Optimiser

12. Why does the optimiser fail?


 
Powered by phpBB® Forum Software