 | |
design Index problem
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
design Index problem -
10-22-2007
, 03:38 AM
#2
| |||
| |||
|
|
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!!! |
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 (AT) sommarskog (DOT) 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
#3
| |||
| |||
|
|
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 |
#4
| |||
| |||
|
|
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
#5
| |||
| |||
|
|
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! :-) |
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 (AT) sommarskog (DOT) 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
#6
| |||
| |||
|
|
Enorme Vigenti (LSimon5 (AT) libero (DOT) 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 |
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)
#7
| |||
| |||
|
|
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) |
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 (AT) sommarskog (DOT) 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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.