|
|
Posted by DickChristoph on 06/14/06 01:07
> Is there any good reference explaining in Layman's terms why you need
> basic (or advanced) indexing ?
I assume you need to explain this to the people who spend the money.
Do you know how a binary search works? I usually explain the benefits of
indexing by saying. Suppose you are searching the phonebook. You aren't
going to search every entry on every page until you find the name you are
looking for. (full table scan).
You could split the book in half and determine if the name you are looking
for is in the left or the right half. Then keep doing that until you get to
the right page. You will have at worst Log base 2 of N comparisons. N being
the total number of entries. You can search 1,000,000 entries with 20
comparisons versus an average of 500,000 without an index.
Or I just say its like those little tabs in some dictionaries. (which it
isn't really) but it gets across the point that you are making less work for
SQL Server to do.
--
-Dick Christoph
"Ryan" <ryanofford@hotmail.com> wrote in message
news:1150119580.643378.235860@f6g2000cwb.googlegroups.com...
>I have a bit of a problem with regards an indexing strategy. Well,
> basically there is no indexing strategy on a set of data I have at
> work. Now, I didn't create the design as I would have allowed for this.
>
> OK, so there is primary key (clustered) indexes (mainly composite
> keys), but no other indexes on the tables. As you would expect, the
> performance leaves a lot to be desired. A hell of a lot. We have
> several million rows in a lot of the tables. None of the queries seem
> to be overly complex so we can work through the applications at a later
> stage.
>
> We have multiple copies (one per client per country) of the same
> structure (I may have considered combining these to allow better
> performance). One specific SP that I need to run takes 40+ hours
> without indexes and 5 hours with some (130) basic indexes to get us
> started on a better design. These 130 indexes are the minimum I suspect
> we need and from there, we can start to work out which ones we need.
>
> Now the test database (our worst performer) doubles in size to 20Gb,
> but the performance is much much better (as expected). The original
> thinking behind the design was for storage concerns (server space
> recently upgraded) and for performance with bulk inserts.
>
> We have a lot of bulk inserts, but I suspect that these are not too
> bad, and the time taken for indexing these is negligable due to the
> performance gains. I strongly suspect that we have a considerable
> amount of table scans going on, but the problem is that people here
> don't understand indexing (yet) or have the time (probably because it's
> all taken up using the poorly designed system). That's a whole seperate
> issue for me to address.
>
> So, finally getting round to my questions...
>
> Is there any good reference explaining in Layman's terms why you need
> basic (or advanced) indexing ? Any links would be appreciated. I need
> this to help explain to colleagues why a disk space increase and
> indexing will be far better than spending thousands on a new box and
> doing the same (a common problem I suspect).
>
> How can I accurately estimate the amount of time taken to update an
> index once data is bulk inserted. I don't want to re-index from scratch
> as this may take too long. Indexing my database first time round takes
> about 1 hour 30 minutes.
>
> It's all part of an ongoing bit of digging into the system and re-doing
> it to make it work properly. I'm sure most of you will have been there
> at some point or another.
>
> Thanks
>
>
> Ryan
>
Navigation:
[Reply to this message]
|