Professional Web Applications Themes

SQL Server 7 Optimiser - Microsoft SQL / MS SQL Server

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

  1. #1

    Default 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


    Stephen Guest

  2. #2

    Default Re: SQL Server 7 Optimiser

    Are you statistics up to date?

    Net

    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.

    "Stephen Howe" <com> wrote in message
    news:OG%phx.gbl... 
    scan 


    Gert Guest

  3. #3

    Default Re: SQL Server 7 Optimiser

    > Are you statistics up to date?

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

    Stephen


    Stephen Guest

  4. #4

    Default Re: SQL Server 7 Optimiser

    > Are you statistics up to date?

    Even with

    UPDATE STATISICS Programme

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

    Stephen


    Stephen Guest

  5. #5

    Default Re: 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 doent 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

    "Stephen Howe" <com> wrote in message
    news:OG%phx.gbl... 
    scan 


    Clay Guest

  6. #6

    Default Re: 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
    [date]=date

    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


    Stephen Howe wrote: 
    Gert-Jan Guest

Similar Threads

  1. Replies: 0
    Last Post: November 27th, 06:14 PM
  2. Replies: 2
    Last Post: September 12th, 10:44 PM
  3. Replies: 0
    Last Post: August 23rd, 05:27 PM
  4. Replies: 0
    Last Post: July 6th, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139