| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-11-09 : 07:15:28
|
| There is a table called tblMain with several million records and increasing daily...The sql below shows the fields and Primary keys.-There is an index on Field6 which is datetime type.-There are three triggers. Delete, insert, update. For each action on the record, there is an entry into the audit table...--tblMain SCRIPTCREATE TABLE [dbo].[tblMain]( [Field1] [int] NOT NULL, [Field2] [char](3) NOT NULL, [Field3] [varchar](50) NOT NULL, [Field4] [float] NOT NULL, [Field5] [varchar](50) NOT NULL, [Field6] [datetime] NOT NULL, [Field7] [int] NOT NULL, [Field8] [bit] NOT NULL, [Field9] [bit] NOT NULL, CONSTRAINT [PK_tblMain] PRIMARY KEY CLUSTERED ( [Field1] ASC, [Field2] ASC, [Field3] ASC, [Field5] ASC, [Field6] ASC, [Field7] ASC, [Field8] ASC, [Field9] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]The following sql takes a very long time and sometimes up to five minutes...--delete from tblMain where Field5 != 'SUser' and Field6 = @Date and Field9 = 1 --update tblMain set Field5= 'SUser' where Field5 = '' Field6= @Date and Field9 = 1Question:----------How can this process be optimized please?Thanks |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-09 : 09:07:31
|
| I know that one thing to do is to change the datatype of field3 to char(4) because all the records have a value in field3 which is only 4 letters. |
 |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-11-09 : 09:53:52
|
| Do you need to have a compound primary key with all of those columns? Can you add an identity column and use that as the primary key?If you're updating the primary key columns and it's a clustered primary key there's a lot of reordering of data that has to be done. The following is from Books On Line,you may also want to do some reading on appropriate indexingClustered indexes are not a good choice for the following attributes:Columns that undergo frequent changes. This causes in the whole row to move, because the Database Engine must keep the data values of a row in physical order. This is an important consideration in high-volume transaction processing systems in which data is typically volatile.Wide keys, Wide keys are a composite of several columns or several large-size columns. The key values from the clustered index are used by all nonclustered indexes as lookup keys. Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-09 : 10:01:22
|
| The clustered index is included in all other indexes - having a wide PK makes all others inefficient.Have a look at the triggers - see if they are well written - coonsider getting rid of them in favour of cdc maybe.Check to see if the statment is table scanning.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-09 : 10:32:37
|
quote:
Originally posted by nigelrivett
The clustered index is included in all other indexes - having a wide PK makes all others inefficient.Have a look at the triggers - see if they are well written - coonsider getting rid of them in favour of cdc maybe.Check to see if the statment is table scanning.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
The triggers do a simple inset into the audit table. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-09 : 10:57:08
|
quote:
Originally posted by BruceT
Do you need to have a compound primary key with all of those columns? Can you add an identity column and use that as the primary key?
This is a table which has been around for a long time created by previous developers. The records in there are unique. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-09 : 10:59:27
|
quote:
Originally posted by BruceT
Clustered indexes are not a good choice for the following attributes:Columns that undergo frequent changes. This causes in the whole row to move, because the Database Engine must keep the data values of a row in physical order. This is an important consideration in high-volume transaction processing systems in which data is typically volatile.Wide keys, Wide keys are a composite of several columns or several large-size columns. The key values from the clustered index are used by all nonclustered indexes as lookup keys. Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.
If possible, can you please send me the link in books online which says the statement you mentioned above?Thanks |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-11-09 : 11:06:08
|
| Try thishttp://msdn.microsoft.com/en-us/library/ms190639.aspx |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-09 : 11:13:41
|
| Tahnks for the link, but it says the wide columns in the PK are not good for select query.It does not say that having a wide PK hurts the update or delete.Am I right?Thanks |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-11-09 : 11:43:26
|
| This is more the part you're interested in since you're updating PK columns.Clustered indexes are not a good choice for the following attributes:Columns that undergo frequent changes. This causes the whole row to move, because the Database Engine must keep the data values of a row in physical order. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-09 : 12:23:54
|
| I'm not going to go into much detail, but a Clustered index maintains the LOGICAL order of a table. People like to say it is physically ordered, which is a bit of a misnomer. Certainly, a bad index choice can have negative consequences due to page splits and data movement.Here is a link to a series of articles that talk about indexing in SQL Server. Of specific interest to this discussion is Part 2:http://www.sqlservercentral.com/articles/Indexing/68439/ |
|
|
|
|
optimization