Results 1 to 7 of 7

Thread: [RESOLVED] Applying an index to a field

  1. May 13th, 2007, 08:11 AM #1
    the182guy
    the182guy is offline

    Thread Starter
    Frenzied Member the182guy's Avatar
    Join Date
    Nov 2005
    Location
    Cheshire, UK
    Posts
    1,473

    Resolved [RESOLVED] Applying an index to a field

    I've got a table like this

    SalesOrderId(PK)
    OrderDate
    SalesPersonId (FK1, l1)
    StoreID (FK3, l3)
    TerritoryID (FK2, l2)

    I want to apply an index to make my query faster/more effecient.

    The query is pulling out the SalesOrderId,OrderDate and StoreID WHERE StoreID=X

    Would it be best to apply an index to StoreID, would it improve performance?

    Cheers
    Chris
    Reply With Quote Reply With Quote
  2. May 13th, 2007, 10:23 AM #2
    si_the_geek
    si_the_geek is offline
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,974

    Re: Applying an index to a field

    It should make that query faster... but it will slow down any insert/update, and may slow down queries that don't use the same field.

    If that kind of query forms the majority of work your database will do with that table, it would be a good move to add the index.. if not it is hard to tell, so try it and see.
    (Microsoft MVP from July 2007 to June 2017) . . . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums

    Database Development FAQs/Tutorials (updated May 2011) (includes fixing common DB related errors, and [VB.Net] ADO.Net Tutorial, and [Classic VB] ADO tutorial /further steps)
    other useful DB bits: . Connection strings (alternative copy).•. MDAC/Jet/ACE downloads .•. SQL Server downloads .

    Classic VB FAQs (updated Oct 2010) (includes fixing common VB errors)
    some of my Classic VB bits: . Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker .
    Reply With Quote Reply With Quote
  3. May 13th, 2007, 11:22 AM #3
    szlamany
    szlamany is offline
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Applying an index to a field

    Is this MS SQL Server?

    Are there going to be truly many, many rows in this table??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:     [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008
    Reply With Quote Reply With Quote
  4. May 13th, 2007, 11:50 AM #4
    the182guy
    the182guy is offline

    Thread Starter
    Frenzied Member the182guy's Avatar
    Join Date
    Nov 2005
    Location
    Cheshire, UK
    Posts
    1,473

    Re: Applying an index to a field

    yup, 10,000 rows expected. Haven't decided on a RDBMS yet, just planning. Cheers all.
    Chris
    Reply With Quote Reply With Quote
  6. May 13th, 2007, 11:57 AM #5
    szlamany
    szlamany is offline
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Applying an index to a field

    10,000 rows is not a lot.

    If it was a huge figure I was going suggest clustered primary index - which would require a different table layout.

    Also - MS SQL has a little known benefit of the fact that if you put extra column in the index - those that you are "actually" selecting on - then the index is used for not only finding the rows (the WHERE part) part but also supplying the data (the SELECT list part).

    But again - these concepts really only become important when you are talking 100's of thousands of rows.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:     [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008
    Reply With Quote Reply With Quote
  7. May 13th, 2007, 12:00 PM #6
    the182guy
    the182guy is offline

    Thread Starter
    Frenzied Member the182guy's Avatar
    Join Date
    Nov 2005
    Location
    Cheshire, UK
    Posts
    1,473

    Re: Applying an index to a field

    Quote Originally Posted by szlamany
    10,000 rows is not a lot.

    If it was a huge figure I was going suggest clustered primary index - which would require a different table layout.

    Also - MS SQL has a little known benefit of the fact that if you put extra column in the index - those that you are "actually" selecting on - then the index is used for not only finding the rows (the WHERE part) part but also supplying the data (the SELECT list part).

    But again - these concepts really only become important when you are talking 100's of thousands of rows.
    Sounds good, I haven't come across stuff as advanced as that.

    Thanks all, rated.
    Chris
    Reply With Quote Reply With Quote
  8. May 14th, 2007, 01:43 AM #7
    brucevde
    brucevde is offline
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: [RESOLVED] Applying an index to a field

    If the majority of your queries include Where StoreId = ?, then the StoreId field is a good candidate for a Clustered Index.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width