Professional Web Applications Themes

Large read-only table for queries - what index? - Microsoft SQL / MS SQL Server

I have a table with 15,000,000 records which will only be queried. The queries will use a where clause with the fields in the same order, but some may be missed out. The first field will nearly always be selected. Should I use a covering index, or a clustered index with all fields in it? Laurie...

  1. #1

    Default Large read-only table for queries - what index?

    I have a table with 15,000,000 records which will only be queried. The
    queries will use a where clause with the fields in the same order, but some
    may be missed out. The first field will nearly always be selected.

    Should I use a covering index, or a clustered index with all fields in it?

    Laurie


    fred Guest

  2. #2

    Default Re: Large read-only table for queries - what index?

    please post the query you intend to use

    --
    Regards,

    Tomislav Kralj
    tel.hr

    "fred" <com> wrote in message
    news:phx.gbl... 
    some 


    Tomislav Guest

  3. #3

    Default Re: Large read-only table for queries - what index?

    TABLE FORMAT:

    CREATE TABLE [dbo].[Students] (
    [st_ref] [char] (19) COLLATE Latin1_General_CI_AS NOT NULL ,
    [st_year] [char] (9) COLLATE Latin1_General_CI_AS NOT NULL ,
    [st_inst] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
    [st_] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL ,
    [st_postc] [char] (8) COLLATE Latin1_General_CI_AS NOT NULL ,
    [st_domic] [smallint] NOT NULL ,
    [st_ethni] [tinyint] NOT NULL ,
    [st_disab] [tinyint] NOT NULL ,
    [st_desti] [tinyint] NOT NULL ,
    [st_fsrce] [tinyint] NOT NULL ,
    [st_disef] [tinyint] NOT NULL ,
    [st_ldiff] [tinyint] NOT NULL ,
    [st_qual_] [tinyint] NOT NULL ,
    [st_mode] [tinyint] NOT NULL ,
    [st_qualt] [tinyint] NOT NULL ,
    [age] [tinyint] NOT NULL
    ) ON [PRIMARY]

    TYPICAL QUERIES:

    SELECT COUNT(*) FROM Students WHERE st_year = '2000-2001' AND st_domic = 399

    SELECT COUNT(*) FROM Students WHERE st_year = '2000-2001' AND st_disab = 1
    AND st_mode = 4

    SELECT st_year, st_inst, st_, st_domic, st_ethni, st_disab, st_desti,
    st_fsrce, st_disef, st_ldiff, st_qual_, st_mode, st_qualt, age
    FROM Students
    WHERE st_year = '2000-2001' AND st_inst='GG555' AND st_ethni=8 AND
    st_fsrce=3 AND age BETWEEN 18 AND 25

    Nearly all the fields could be in the where clause, but they will appear in
    table order, if they do.

    ----------------------------------------------------------------------
    "Tomislav Kralj" <tel.hr> wrote in message
    news:phx.gbl... 
    > some [/ref]
    it? 
    >
    >[/ref]


    fred Guest

Similar Threads

  1. query of queries and large data types
    By jim1234 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 12th, 06:38 PM
  2. DBD::Pg behavior for large queries
    By Chris in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 19th, 11:33 AM
  3. large index problem
    By Bill Peterson in forum Adobe Indesign Windows
    Replies: 0
    Last Post: June 28th, 03:24 PM
  4. mod_perl - CGI params are undef on large queries
    By Martin Glaude in forum PERL Miscellaneous
    Replies: 0
    Last Post: August 9th, 02:40 PM
  5. Avoiding large queries...
    By Christopher Stolte in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 12th, 09:48 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