clustered index

[shaminda]

If I add a clustered index to a table in my SQL Server database will it increase application performance? (VB Application) The database was created sometime back and it has about 40MB of data.

 

[TomSark]

If you create an appropriate index it can dramatically enhance performance.

From books-online, clustered indexes are good for:

Columns that contain a limited number of distinct values, such as a state column that contains only 50 distinct state codes. However, if there are very few distinct values, such as only 1 and 0, no index should be created.

Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.

Columns that are accessed sequentially.

Queries that return large result sets.

Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.

OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.
Clustered indexes are not a good choice for:

Columns that undergo frequent changes because this results in the entire row moving (because SQL Server must keep the row’s data values in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.

Covered queries. The more columns within the search key, the greater the chance for the data in the indexed column to change, resulting in additional I/O.

I strongly suggest you read up on indexes in books online...

Tom

 

[foxdev]

Tom gave a stellar overview of the use of clustered indexes. Shaminda, if you could give us the table structure and an approximation of the selectivity (uniqueness) of the column(s) you have in mind, we might be able to answer more precisely.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Also, if you know of the different types of queries that hit the table(s), please include the queries.

Tom

 

[foxdev]

...and, if available, the number of records in the table and the number of inserts per hour or day.

Robert Bradley

http://www.foxdev.com/

 

[calahans]

For large tables the simple answer is yes, however if your table has less than a few thousand rows the index may slow you down!

Cal

 

[TomSark]

On very small tables, I wouldn't put an index on them either. On a table with a few thousand rows, I would put an index on it. If it had a few hundred rows or less, then I wouldn't place an index on it.

This is a relative measure, so you may have to generalize your policy, such as look-up tables don't get them, but updated tables do, or whatever...

Tom

 

[shaminda]

Here is the table structure

tblRack_PDSN
PDSN_ID--------int 4
866_ID---------int 4
Rack_ID--------int 4
PDSN-----------char 10
Weld_Lot_Date--char 10
cell_Name------char 15
shift_Date-----datetime 8
last_Name------char 18

This is one of the queries
SQL = &quot;SELECT * FROM [tblRack_PDSN] WHERE [PDSN] = '&quot; & Right(rp![Product_ID_JS], 4) & &quot;' AND [866_ID] = '&quot; & int866 - 1 & &quot;';&quot;

and vb code

.addnew 'adds the first 4 columns
----
---
---
.update

I read around 700 rows from one table and Insert around 700 rows every day.

 

[TomSark]

If you put the select in a proc, it would probably help a little...

Tom

 

[calahans]

I'd agree with tom, the problem is more likely your application than the table. Look at ways you could speed the process from the application end. You could use prepared statements, stored procedures, or try to break you code out to different layers.

Cal