SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    creating indexes - memory vs speed

    My table strategy involves a main table for registered users.

    This table as it works out has a primary index (auto increment) and approximately 6 foreign key columns. All linked column values require three-way and four-way joins to print their values.

    How can I balance the "memory required for indexed columns vs. process speed"? (Designing with a combination of single and composite indexes, assuming this can be done.)

    Is creating an index really worth it if there will be many inserts?
    What about if not many updates or deletes but many selects?

    Can do these to test, but where do I get actual time figures from?:

    SELECT /*+ FULL (table)*/ col1, col2 FROM table: //full scan
    SELECT /*+ INDEX (table indexname)*/ col1, col2 FROM table: //index
    Last edited by datadriven; Feb 12, 2005 at 10:24.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    not really sure i understand what you're doing

    "All linked column values require three-way and four-way joins to print their values." -- this is a byproduct of using foreign keys instead of redundantly storing the values (e.g. names), and eliminating redundancy is usually much preferred to eliminating joins

    the "memory required for indexed columns vs. process speed" balance should be left up to the database engine -- trust me, it handles input/output disk caching a lot better than we can

    "What about if not many updates or deletes but many selects?" -- then in general you want more indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by datadriven
    Is creating an index really worth it if there will be many inserts?
    What about if not many updates or deletes but many selects?

    Can do these to test, but where do I get actual time figures from?:
    Take a look at this article about indexing in MS SQL, it discusses many of the things you ask. (I wrote it).

    The thing to remember is that there is no in vitra best solution, you miust continually reasses your application's performance with the current indexing strategy to get maximum benefit.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    nice article

    what would be the additional benefit of declaring a clustered index on a GUID column, as opposed to a non-clustered index

    and isn't it redundant to declare an index on the primary key? it gets one anyway, by virtue of being declared the primary key

    by the way, i think datadriven's database is oracle

    see http://www.billmagee.co.uk/oracle/sq...70_single.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Well, I would question the use of GUIDs in general. The only time you really need them is when you are doing replication or federated servers, where you need to ensure that two instances of the same database do not create the same PKs. If you take the standard sql-weenie view, GUIDs are artificial and therefore have no rightfiul place in a relational database anyway...

    But let's suppose you do have a table field which is of the GUID datatype. That is 16 bytes, just as large as for ntext. That's a lot of data to put in an index. Say you have a table with 100 million rows, that's a lot of megabytes just for the clustered index. And since any other index you put on the table is going to be including the clustered index key too, that's a lot of IO and storage space.

    You generally use clustered indexes for locating ranges, using between or < >. None of these operators makes any sense in connection with GUIDs, you can't meaningfully group on a GUID. Then again they are also used for equality lookups, so if your app does a lot of "select f1 from t1 where pk = 'guidxxxxxxxxxxxxxxxx'" then it is probably worth while after all....

    And yes, if you specify a field as being a PK, and the table does not already have a clustered index, SQL Server will create a unique clustered index for you.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by asterix
    None of these operators makes any sense in connection with GUIDs.
    that's what i thought, but i'm not a dba so i tend not to be interested in performance minutiae

    presumably if you're looking up single rows based on a GUID, a non-clustering index would be just as good

    i just had momentary visions of GUIDs being inserted into different disk pages in random sequence and i couldn't imagine why this would be a good idea

    perhaps it has something to do with no "hot spots" like you get with incremental ids

    but like i said, i usually don't care about such stuff, because, you see, i am an sql weenie

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •