Hi all,
I have a very large table with many columns: dateTime type, nvarchar
type and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present
too. Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in
every type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!
Some ideas?
thnx

Enorme Vigenti (LSimon5@libero.it) writes:
> I have a very large table with many columns: dateTime type, nvarchar
> type and integer field type.
> A program exec many type of query with where clause.
> Data field is always in where clause, but some other field is present
> too. Sometimes integer field, sometimes nvarchar field.
> Now I must create index for the query!
> For choose index field what i can do?
> I must create only index on a datetime field or every combination in
> every type of query?
> In the second case I must create very much index! But this is very
> dispendious for update/insert/delete operation on the table!!!

How selective is the datetime column? If all queries are for a single
day, maybe an index on that column is sufficient, preferrably a clustered
index.

But if queries can be for longer periods of time, that may address too many
rows, and in such case you will need to add more indexes. How these indexes
should be designed depends on the queries. If a query can be on account
number and a date interval, it's probably better to have the account number
first in that index.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

If you could post an example Query ?

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"Enorme Vigenti" <LSimon5@libero.it> wrote in message
news:0kZSi.151009$U01.1108206@twister1.libero.it.. .
> Hi all,
> I have a very large table with many columns: dateTime type, nvarchar type
> and integer field type.
> A program exec many type of query with where clause.
> Data field is always in where clause, but some other field is present too.
> Sometimes integer field, sometimes nvarchar field.
> Now I must create index for the query!
> For choose index field what i can do?
> I must create only index on a datetime field or every combination in every
> type of query?
> In the second case I must create very much index! But this is very
> dispendious for update/insert/delete operation on the table!!!
> Some ideas?
> thnx

Erland Sommarskog ha scritto:

> How selective is the datetime column? If all queries are for a single
> day, maybe an index on that column is sufficient, preferrably a clustered
> index.
>
> But if queries can be for longer periods of time, that may address too many
> rows, and in such case you will need to add more indexes. How these indexes
> should be designed depends on the queries. If a query can be on account
> number and a date interval, it's probably better to have the account number
> first in that index.


tnx 4 answer :-)
datetime column is much selective because every night I must exec
day-statistical report. I must make hour statistical report too (but it
is another type of report).
The queryes is always on datetime field and then for other some fileds.
but it is for day after only! (I must keep one month)
It is more better to migrate at sqlserver2005 for the partitionet tables
and then use a single day table to create index? In that case I could
have only one day index! :-)
bye

Enorme Vigenti (LSimon5@libero.it) writes:
> It is more better to migrate at sqlserver2005 for the partitionet tables
> and then use a single day table to create index? In that case I could
> have only one day index! :-)

The main advantage of partitioned tables is that it makes it easy to
age out old data very quickly. I don't see any particular gain for
querying in your case. For the queries a clustered index on the datetime
column would be a good start.

If there are many rows per day (say > 50000), you may need to add non-
clustered indexes as well for the most important queries. Possibly with the
datetime value as the first column.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog ha scritto:
> Enorme Vigenti (LSimon5@libero.it) writes:
>> It is more better to migrate at sqlserver2005 for the partitionet tables
>> and then use a single day table to create index? In that case I could
>> have only one day index! :-)
>
> The main advantage of partitioned tables is that it makes it easy to
> age out old data very quickly. I don't see any particular gain for
> querying in your case. For the queries a clustered index on the datetime
> column would be a good start.
>
> If there are many rows per day (say > 50000), you may need to add non-
> clustered indexes as well for the most important queries. Possibly with the
> datetime value as the first column.
>
Delete aged old data is most important for me: I have about 2 million
rows every day for every customer table (and I have about 50 customers).
For the delete operation for cut off the last day I need 4-5 hours!!!
If I could to use truncate table (for delete last day) could be
wonderfull :-)
My problem is in calculate statistical day report too
I must query only yesterday data table with where clause on 3-4 coloumn
and sum/max function on decimal field, and group by clause on 6 coloums.
This operation require 7/8 hours!!!
My tables have 8 non clustered index (with data coloums like first
field) and cluster primary key of course (only one coloumn integer type)

Enorme Vigenti (LSimon5@libero.it) writes:
> Delete aged old data is most important for me: I have about 2 million
> rows every day for every customer table (and I have about 50 customers).
> For the delete operation for cut off the last day I need 4-5 hours!!!
> If I could to use truncate table (for delete last day) could be
> wonderfull :-)
> My problem is in calculate statistical day report too
> I must query only yesterday data table with where clause on 3-4 coloumn
> and sum/max function on decimal field, and group by clause on 6 coloums.
> This operation require 7/8 hours!!!
> My tables have 8 non clustered index (with data coloums like first
> field) and cluster primary key of course (only one coloumn integer type)

Boy, it seems that databasee keeps you busy a great part of the day.

It sounds that SQL 2005 and partitioned tables could help you at least
with the DELETE part. And once that is set up, you can add more indexes
to speed up the queries without getting problems with the updates. Well,
you still have the load part to consider, but I think you should be
able to load into an empty table, index it, and then switch it into the
partitioned table.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx