Post reply

Index Desc???

  • January 27, 2005 at 11:08 am

    #92213

    i have Table1 that inner joins Table2 on FK, it one to Many relation ship

    table1:200k(thousend) records

    table2:20m(million) records

    in most cases i need to get around ~1000 last records from

    table1 inner join table2, so question is will it help seting

    Index Desc for FK????

    Thanks for help???

    Borik

  • January 27, 2005 at 11:22 am

    #539404

    Might, just test it, but also test impact on any other queries as well.

  • January 27, 2005 at 12:40 pm

    #539415

    Well i was hoping for more solid answere then that...

    i don't want to test it on real DB and kind of hard to test

    this on smaller DB....

  • January 27, 2005 at 12:53 pm

    #539417

    I've tested changing the sort orders of my indexes and never saw any performance gains with them.  As long as you have the join field indexed it should be doing a range scan not a table scan.  Depending on your statistics and the number of rows your bringing back.

    Tom

     

  • January 27, 2005 at 1:33 pm

    #539427

    It should make no difference. SQL Server will traverse the index comparing the key to your criteria, to determine if the criteria exists, and then finds the data associated with the value.

    Quand on parle du loup, on en voit la queue

  • January 27, 2005 at 3:15 pm

    #539449

    It will depend greatly on the meaning of "last 1000" and how indexes are built on the column or columns that together define "last 1000".

    Also depends somewhat on SqlServer version. Prior to SQL2K, the common thinking was to avoid clustering on column/columns like an Identity that monotonically increase, in order to avoid "hot spots" on index pages. Not so in SQL2K - if your 200 million row table has an Identity, or some other increasing column, and you build a clustered index on that column, you can reduce the index page IO required.

    If your clustered index does not support this query, and you rely instead on a non-clustered index, you should ensure that it has at least 95% selectivity or the optimizer may elect not to use it and tablescan instead. You may also elect to build a non-clustered covering index on the 200 million row table, so that all data elements in the Select/Where are satisfied by the index and no data page lookups are required.

     

  • January 27, 2005 at 4:18 pm

    #539457

    Hmmm... Let me Just Explaine Little more about stracture,

    i am running SQL2K Standart...

    Table1

    id -idedntity/autoincrement - PK - Clustered

    Table2

    aaa - idedntity/autoincrement - PK - clustered

    idFK - FK - not clustered index...

    usualy for every record in table1 on i have ~250 in table2

    so ration is 1:250... so when i want last 1000 in table1 it will need to get a 250000 from table2...

    let assume i have 200k records in table1

    i need to optimize the return of this query

    Select * from table1 inner join table2 on table1.id = table2.idFK where table1.id > 199000...

    my guess would have been that bookmark look up would be faster

    if FK index on table 2 would be in DESC order...

    also i would love to make in table2 clustered index on aaa+id

    but i don't know how to do it on replicated table...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply