Indexing (1 Viewer)

[FluffyKitten]

Hi

I'm fairly new to working with SQL as a be to my db. The SQL db has several views that need to be accessed through the fe. Someone has advised me to use Indexing to deal with this, however, the small amount of information I am able to find on the internet hints that you would need to update these frequently if any of the underlying views etc change.

Is there anyone there who can give me an overview of what use indexing is and what the benefits would be before I waste too much time trying to learn and change only to find that it's not viable to our system. The be of the system is held on a network and used by around 100 users.

Thank you so much in advance for any advice you can offer.

 

[WayneRyan]

fluffy,

The server will maintain your indexes. Unless you have an unusually
large amount of data, you don't need a real detailed plan.

The server will automatically create a unique index for your primary
key field(s).

Any fields that you use in the Where/Order by clauses for your search
forms should be indexed. This will speed up retrieval of data.

You should be wary of indexing large string values as they will
incur quite a bit of overhead.

Wayne

 

[FluffyKitten]

Are you saying that as long as i have views theres no need to use indexes? that would be a relief.

 

[WayneRyan]

Fluffy,

Not at all.

Views are just select queries and provide a way to look at your data.
To make this more efficient, any criteria referenced in the Where/Order By
clauses should have an index on it.

Once indexes are defined, the server will maintain them. There is no
need to refresh/regenerate them.

You should still be wary about indexing large string fields.

hth,
Wayne

 

[FluffyKitten]

Thanks Wayne i;ll investigate indexing further then and see if thats going to be of any use to us and if not well at last i mite learn something

 

[Endre]

The way I understand indexing to work, is that if as an example you are searching for a record that has an indexed field, the system will immediately jump to the middle record in the data and check if the middle record value is higher or lower than the one you are searching for. If higher, it immediately goes to the middle of the lower set of data discarding the higher set. If lower, it goes to the middle of the higher set of data and discards the lower. Repeat repeat and so on. This halving, halving, and halving finds the record much much quicker than searching one by one.

Well that's my laymans understanding anyway.

 

[SQL_Hell]

Indexing must be implemented in all cases, it is simply not good enough to say "oh we have a small amount of data so we dont need indexes"

All databases need a sensible indexing strategy, its what keeps our database running in an optimised way, and it is what future proofs the database against growth.

Wayne Ryan:

What do you mean when you say sql server maintains indexes? As far as I am aware sql server does not maintain indexes at all but it does maintain statistcs. Indexes become fragmented especially on tables that have many inserts or deletes and maintenance plans (or maintenance jobs) are needed to defrag or drop and re-create the indexes.

 

[WayneRyan]

SQL,

Mine was a very superficial look into indexes. They definitely are
mandatory in any database.

Generally speaking, if you search or sort on any column, it should
have an index.

SQL Server does maintain indexes. That's why you can specify a fill
factor depending on data volatility. The indexes are just data, in a
way, and can become fragmented.

Rereading the initial post, if the back-end data is used by around
100 users, it definitely isn't a trivial database.

However, I'd still INITIALLY index the columns used in the Where
and Order By clauses while I researched the volatility of the data
and developed a long-term plan.

Wayne

 

[Banana]

Not totally familiar with SQL Server's indexing, but wanted to point out that for strings, I would definitely do a prefix length for the indexing in MySQL. Say I have a column that allows 200 characters of text to be entered. I know that first 10 characters are good enough to make the records stand out among other, so the index definition would only store the first 10 characters, making it significantly less intensive to use the index.


(I quickly googled but couldn't verify from a glance if SQL Server supported prefixing outside of full text indexing which isn't always necessary)

 

[WayneRyan]

Banana,

I don't deal with a lot of text data, but I definitely know that you shouldn't
fully index long text strings. It would probably be far worse if the data
had a lot of inserts/updates/deletes. You'd be constantly rebuilding the
indexes (I mean the server would).

I don't recall ever seeing any option for the prefix. It makes sense. I'll
research it tomorrow at work.

Wayne

 

[SQL_Hell]

Wayne

I am not trying undermind what you've said as most is correct, but I want pick you up on this one thing:
To say the server maintains indexes and once they are defined there is no need to regenerate them is misleading. The server does maintain the order of the indexes but that does not constitute a rebuild or dealing with any resulting fragmentation.

Automatic index rebuilding would be a very bad thing because to rebuild an index you must put exclusive locks all over your table (unless you have enterprise edition), which of course stops appplications working, I certainly wouldn't be very happy about this occuring in the middle of the day.

Taken from, BOL

The SQL Server 2005 Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. For more information, see this Microsoft Web site.
In SQL Server 2005 you can remedy index fragmentation by either reorganizing an index or by rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or on a single partition of an index.

 

[FluffyKitten]

That's great, many thanks for this sorry if I stirred up a can of worms.