Microsoft SQL Server forum - Database Problem related Clusterd data

Author

Database Problem related Clusterd data

vibha vyas

2005-11-18, 3:23 am

Hello sir
We have a very huge database its around 6 lakh records
are being stored in it.
records are not being a sorted order so we checked all
record field through clustering option in Sql server.when we used
clustering records are showing in sorted order but speed of database is
very slow .
i want to know with clustering is there any implecation in database
regading performance od data.Can any one give his views to solve this
problem?and increase the performance of our database.
thanks a lot

2005-11-18, 7:23 am

I dont understand.
Do you mean to say that you put clustered indeks on a number of columns in
the table to make it ordered? Thats a mistake, you must use order by clause
in a query to ensure that it is ordered. Furthemore, indeksing a large table
is not something you should do lightly, clustered indeks is (if it can be)
in memory and its key is in the nonclustered indexes so if the key of the
clustered indeks is large, your performance will suffer. Not to mention the
data changes (insert, update..)

Please post more info about your situation so we can help some more.

MC
"vibha vyas" <vyas.vibha@gmail.com> wrote in message
news:1132297991.742116.21620@g49g2000cwa.googlegroups.com...
> Hello sir
> We have a very huge database its around 6 lakh records
> are being stored in it.
> records are not being a sorted order so we checked all
> record field through clustering option in Sql server.when we used
> clustering records are showing in sorted order but speed of database is
> very slow .
> i want to know with clustering is there any implecation in database
> regading performance od data.Can any one give his views to solve this
> problem?and increase the performance of our database.
> thanks a lot
>

Erland Sommarskog

2005-11-20, 8:23 pm

[posted and mailed, please reply in news]

vibha vyas (vyas.vibha@gmail.com) writes:
> We have a very huge database its around 6 lakh records
> are being stored in it.
> records are not being a sorted order so we checked all
> record field through clustering option in Sql server.when we used
> clustering records are showing in sorted order but speed of database is
> very slow .

If records are not displayed in the order you want, you need to use
an ORDER BY clause in your queries. That's the only way.

> i want to know with clustering is there any implecation in database
> regading performance od data.Can any one give his views to solve this
> problem?and increase the performance of our database.

It's usually considered best practice to have a clustered index on a
table. What you should put clustered index on is a more delicate matter.
For INSERT-itensive tables, the clusrered index may be chosen to
keep fragmentation in check. But normally, the clustered index is picked
to accomdate a common queries. An Orders table for instance is likely
to have its clustered index on OrderDate or CustomerID.

Generally, the clustered index should not be too long as it also appears
as row locator in non-clustered indexes.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links