Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Unanswered: temporary indexes?

    are there such things as "temporary indexes"?

    DBA noo13 alert: i've never heard of them, but it won't surprise me to learn that they exist

    on the other hand, the guy who posted this could just be dreaming...
    in addtiton to Oracle, Sybase and MS SQLServer employ temporary indexing when a needed index has not been implemented.
    that would be a lovely trick, wouldn't it? a join query is particularly slow because todd forgot to declare indexes, and the dbms picks up on this and creates them on the fly...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - but it ain't usually beautiful. Indexes are at their most beautiful the greater the ratio of selects vs modifications. Temporary indexes by definition usually have a pretty ugly ratio.

    Todd stills needs to know good design, Todd.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rarely would the cost of creating a one-time index exceed the cost of not using it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are you talking about these perhaps...
    Code:
    select [name] from sysindexes where [name] like '_WA_Sys_%'
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I forget the total deal with those but I think they are statistics.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not sure i know what the guy (on another forum) was blathering about concerning temporary indexes

    so i thought i'd ask the SQL Server Gurus

    he has a mysql app (not his choice of dbms, apparently), he did not create indexes, got frustrated when his join query ran slow, and then said that mysql's "temporary indexing algorith" was defective

    i have strong doubts that sql server will "employ temporary indexing when a needed index has not been implemented"

    can anyone categorically confirm or deny, given only this small context?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    so i thought i'd ask the SQL Server Gurus
    I do not know any Gurus. Pat said he met one once and he smoked a lot.

    If this guy is not talking about the statistics that get automatically created and stuck in the sysindexes system table when that database option is on, then he is blowing smoke up someone's rear as far as I know.

    But what do I know. I have not written any SQL Server stuff in a month or 2 now. Sigh.....
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Rudy ... Sean is spot on. If a query uses a column(s) that is not indexed, SQL Server will build statistics for the referenced column(s). The optimizer will use the statistics in formulating the execution plan, but they are not "temporary indexes".

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't know of any way to prove that something that could exist does not exist, so I can't give a definitive statement on the non-existance of anything that could theoretically exist.

    SQL Server will automagically generate, maintain, and dispose of statistical indexes. These are what Thrasymachus was referring to in his post. Statistical indexes are not indexes that can be used like an index created via CREATE INDEX, but they serve some of the same purposes and are a strong indicator that you should consider creating an index with the same structure.

    There are (or at least were) third party products that will automagically analyze and build ordinary indexes based on usage, but they are not well received or very popular. This actually isn't very hard to do given the tools provided in DMO or SMO, but it is a real nusiance in many ways, especially where performance is concerned. Some worked via analyzing the statistical indexes, others worked via monitoring activity (ala SQL Profiler) and tuning queries that were either frequent or noxious in terms of resource usage.

    I've never seen or heard of a version of SQL Server that will build indexes "out of the box" and I would be very skeptical if someone claimed to have used such a thing.

    -PatP

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think what is being referenced (or at least my narrow view upon skimming about half the details in this thread) is the ability of the user/programmer to put an index on a temporary table. The cases where this comes in handy seem to be pretty rare, and I think only Blindman has had any need to use them.


    EDIT: Only Blindman among the SQL Server (ab)users I know of

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    there is a similar concept known as "what if" indexes. these are what DTA uses to figure out what indexes might help your system. they are a way for DTA to ask the optimizer "if there was such-and-such index on these columns, would you use it in the plan for this query?" If the optimizer says yes, DTA recommends you build that index.

    I don't think mere mortals can put them to use in any way though.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I use constraints in temp tables which are enforced by indexes so I guess I do too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by MCrowley
    The cases where this comes in handy seem to be pretty rare, and I think only Blindman has had any need to use them.
    Surely not. Other people must be doing this too, as it really improves the performance of some complex procedures.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    sure, i use indexes on temp tables.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Uh-oh. Time to set up a twelve step process.

    My name is mcrowley, and I use indexes on temp tables.

    ;-)

Posting Permissions

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