|
|
You're in the
sql server programming
group:cluster index advice - advice needed
sql server programming:
on a group_id and another on a customer_id column. the group_id will be somewhat volatile, lots of updates and and deletes on this table based on this column. this is the design i inherited and i need a little advice. table: orderqueue (id int identity primary key, group_id int, customer_id int not null, queuedate datetime not null default getdate()) clustered index on queuedate (new index i was going to add) non clustered index on customer_id non clustered index on group_id rows are inserted for customers and an outside process reads them and stamps them with a group_id. several customer records can share a group_id (anywhere from 1 to 50 per group_id). once processing is completed for customers in the group the rows are deleted queries being issued select * from orderqueue where customer_id is null order by queuedate insert into orderqueue (group_id, customer_id) values (null, @customer_id) update orderqueue set group_id = @group_id where group_id is null delete from orderqueue where group_id = @group_id the updates/deletes will be about 2:1 to selects. i didn't want to cluster on group_id/queuedate because i was afraid of blocking on the updates to the cluster index page. questions what is the effect on a cluster index when a non-clustered index column is updated (e.g. group_id is set to a value)? looking at query plan for the update, the majority of the time is being spent by an update in actual updating of the cluster index data page.... is that "good"? one test i ran showed about 35% of the time seeking rows and 65% of the time doing a "Clustered Index Update/Update". it was an update of 5 rows out of 15,000. any help is appreciated
hi erland.... thank you and happy holidays to you
the pk is not indexed at all at the moment. i'm not strong on database since i am actually a java developer. you are right select * from orderqueue where customer_id is null order by queuedate is a mistake. it is actually select * from orderqueue where group_id is null order by queuedate. it is used to find all the queue records that need to be marked with a group_id. if i perform queries using order by queuedate should i make the pk clustered and the queuedate non clustered? or just leave out the queuedate altogether as an index? the results per retrieval would be small (between 1 - 100) for each select on the table. is it better to leave out the index and let the database do the sort? i am going to experiment a bit but i was looking for some advice on it. [quoted text, click to view] Erland Sommarskog wrote:
> Derek (gepetto_2000@yahoo.com) writes: > > i have a table that has no index on a date column and a regular index > > on a group_id and another on a customer_id column. the group_id will > > be somewhat volatile, lots > > of updates and and deletes on this table based on this column. > > this is the design i inherited and i need a little advice. > > > > table: > > orderqueue (id int identity primary key, group_id int, customer_id int > > not null, queuedate > > datetime not null default getdate()) > > > > clustered index on queuedate (new index i was going to add) > > non clustered index on customer_id > > non clustered index on group_id > > You did not say whether PK is clustered or not. But both id and queuedate > are good choices for clustered indexes, since they are monotonically > growing. But maybe it's better to use the id, and leave out queuedate > as an un-indexed to keep down the number of indexes. One advantage of > this is that id is four bytes, and an index on queuedate is would be > twelve bytes (datetime + uniqifier), so this keeps down the size of > the NC indexes. (Recall that the clustered index key is the row locator > for the NC indexes.) > > > queries being issued > > > > select * from orderqueue where customer_id is null order by queuedate > > I assume that the WHERE condition is "group_id IS NULL"? Else the > query does not make much sense, since customer_id is not nullable. > > It seems to me that the query would be more efficient, if the process > querying this table, tracks the last id. When you could add > > AND id > @last_id > > > update orderqueue set group_id = @group_id where group_id is null > > As well as for this query. (Which would bring a change, since in the > current process, it appears that rows inserted after the SELECT would > also be assigned a group_id.) > > > the updates/deletes will be about 2:1 to selects. i didn't want to > > cluster on group_id/queuedate because i was afraid of blocking on > > the updates to the cluster index page. > > Yes, you would get a lot of page moves this way, since you also would > have to update the index on customer_id and id. So keeping group_id > out from the clustered index is a good idea. > > > what is the effect on a cluster index when a non-clustered index column > > is updated (e.g. group_id is set to a value)? > > None. Of course, the data page is updated, but the index as such is not > affected. > > > looking at query plan for the update, the majority of the time is being > > spent by an update in actual updating of the cluster index data page.... > > is that "good"? one test i ran showed about 35% of the time seeking > > rows and 65% of the time doing a "Clustered Index Update/Update". it > > was an update of 5 rows out of 15,000. > > It's probably a good thing if the update takes more time than seeking. > That indicates that the indexes are good for locating the rows. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Derek (gepetto_2000@yahoo.com) writes:
[quoted text, click to view] > i have a table that has no index on a date column and a regular index > on a group_id and another on a customer_id column. the group_id will > be somewhat volatile, lots > of updates and and deletes on this table based on this column. > this is the design i inherited and i need a little advice. > > table: > orderqueue (id int identity primary key, group_id int, customer_id int > not null, queuedate > datetime not null default getdate()) > > clustered index on queuedate (new index i was going to add) > non clustered index on customer_id > non clustered index on group_id You did not say whether PK is clustered or not. But both id and queuedate are good choices for clustered indexes, since they are monotonically growing. But maybe it's better to use the id, and leave out queuedate as an un-indexed to keep down the number of indexes. One advantage of this is that id is four bytes, and an index on queuedate is would be twelve bytes (datetime + uniqifier), so this keeps down the size of the NC indexes. (Recall that the clustered index key is the row locator for the NC indexes.) [quoted text, click to view] > queries being issued > > select * from orderqueue where customer_id is null order by queuedate I assume that the WHERE condition is "group_id IS NULL"? Else the query does not make much sense, since customer_id is not nullable. It seems to me that the query would be more efficient, if the process querying this table, tracks the last id. When you could add AND id > @last_id [quoted text, click to view] > update orderqueue set group_id = @group_id where group_id is null As well as for this query. (Which would bring a change, since in the current process, it appears that rows inserted after the SELECT would also be assigned a group_id.) [quoted text, click to view] > the updates/deletes will be about 2:1 to selects. i didn't want to > cluster on group_id/queuedate because i was afraid of blocking on > the updates to the cluster index page. Yes, you would get a lot of page moves this way, since you also would have to update the index on customer_id and id. So keeping group_id out from the clustered index is a good idea. [quoted text, click to view] > what is the effect on a cluster index when a non-clustered index column > is updated (e.g. group_id is set to a value)? None. Of course, the data page is updated, but the index as such is not affected. [quoted text, click to view] > looking at query plan for the update, the majority of the time is being > spent by an update in actual updating of the cluster index data page.... > is that "good"? one test i ran showed about 35% of the time seeking > rows and 65% of the time doing a "Clustered Index Update/Update". it > was an update of 5 rows out of 15,000. It's probably a good thing if the update takes more time than seeking. That indicates that the indexes are good for locating the rows. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Derek (gepetto_2000@yahoo.com) writes:
[quoted text, click to view] > the pk is not indexed at all at the moment. i'm not strong on database > since i am actually a java developer. If you have create the table with id as a primary, then there is an index on id, because that is how PK constraints are implemented in SQL Server. And that index is clustered, since PKs are created as clustered by default (unless there is already is a clustered index at the time.) You can use sp_helpindex on the table to check this. [quoted text, click to view] > if i perform queries using order by queuedate should i make the pk > clustered and the queuedate non clustered? or just leave out the > queuedate altogether as an index? You could just as well order by id, which should be correlated with queuedate. Did you consider the alternative of saving the id for each fetch and adding it to the WHERE condition. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
hi erland yes it looks like the pk is a cluster index. i didn't know it was. i can't check for last id because there will be multiple java threads that can execute the queries and they won't know what the last id is. my new question for you, if my query is select * from orderqueue where group_id is null order by queuedate i was thinking of this scheme based on your recommendation id - (pk of table) - clustered customer_id - non clustered group_id - non clustered queuedate - non clustered assumptions select will be WHERE group_id is null ORDER BY queuedate updates/deletes will be WHERE group_id = @group_id in your professional opinion is this a good index design? or does it make sense to cluster to the queuedate and make the pk a non-cluster or will a cluster pk be sufficient enough to make the sorting happen fast? i understand the queuedate would make the non clusters bigger because of the datetime type. [quoted text, click to view] Erland Sommarskog wrote:
> Derek (gepetto_2000@yahoo.com) writes: > > the pk is not indexed at all at the moment. i'm not strong on database > > since i am actually a java developer. > > If you have create the table with id as a primary, then there is an > index on id, because that is how PK constraints are implemented in SQL > Server. And that index is clustered, since PKs are created as clustered > by default (unless there is already is a clustered index at the time.) > > You can use sp_helpindex on the table to check this. > > > if i perform queries using order by queuedate should i make the pk > > clustered and the queuedate non clustered? or just leave out the > > queuedate altogether as an index? > > You could just as well order by id, which should be correlated with > queuedate. Did you consider the alternative of saving the id for each > fetch and adding it to the WHERE condition. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
hi erland thank you for your replies. the java threads periodically check this queue table for new entries that have been inserted. the group_id is null on the insert. when the threads check the table it stamps them with a group_id so they are processed together by another program. when they are done being processed they are deleted (delete from orderqueue where group_id = @group_id) i did have a follow up question (i'm learning a lot - thank you!) if i do a lot of deletes, updates, and inserts on the table would the cluster on id (or queuedate) be ok since the data pages won't have to move as dramatically it is because the index is growing sequentially anyway and new rows are added to the end. ? do i understand that correctly? [quoted text, click to view] Erland Sommarskog wrote:
> Derek (gepetto_2000@yahoo.com) writes: > > yes it looks like the pk is a cluster index. i didn't know it was. > > > > i can't check for last id because there will be multiple java threads > > that can execute the queries and they won't know what the last id > > is. > > Each thread should know what was the last id it got. > > (It does a bit funny to me. So multiple threads reads data from this > queue and assign group ids? As far as I can understand multiple threads > can read the same unassigned entries, even if only one of them will > actually set a group id for it? By the way, are you on SQL 2000 or > SQL 2005? If you are on SQL 2005, did you consider using Service > Broker?) > > > my new question for you, if my query is > > > > select * from orderqueue where group_id is null order by queuedate > > I still think you should do order by id. If I understand this > correctly, when you add new rows, queuedate is always assigned getdate(). > This means that id and queuedate are fully correlated. But id would be > better to sort on, because it's a smaller column. > > > i was thinking of this scheme based on your recommendation > > > > id - (pk of table) - clustered > > customer_id - non clustered > > group_id - non clustered > > queuedate - non clustered > > Thinking of it, the index on group_id should probably be (group_id, id). > Then this index can be used to retrieve the rows in the desired order. > (This assumes that you order by id and not queuedate.) Note that since id > is the clustered index, it is in the index of group_id anyway. > > Then again, if you only retrieve 50-100 rows at a time, I would not expect > sorting to be very expensive. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Derek (gepetto_2000@yahoo.com) writes:
[quoted text, click to view] > yes it looks like the pk is a cluster index. i didn't know it was. > > i can't check for last id because there will be multiple java threads > that can execute the queries and they won't know what the last id > is. Each thread should know what was the last id it got. (It does a bit funny to me. So multiple threads reads data from this queue and assign group ids? As far as I can understand multiple threads can read the same unassigned entries, even if only one of them will actually set a group id for it? By the way, are you on SQL 2000 or SQL 2005? If you are on SQL 2005, did you consider using Service Broker?) [quoted text, click to view] > my new question for you, if my query is > > select * from orderqueue where group_id is null order by queuedate I still think you should do order by id. If I understand this correctly, when you add new rows, queuedate is always assigned getdate(). This means that id and queuedate are fully correlated. But id would be better to sort on, because it's a smaller column. [quoted text, click to view] > i was thinking of this scheme based on your recommendation > > id - (pk of table) - clustered > customer_id - non clustered > group_id - non clustered > queuedate - non clustered Thinking of it, the index on group_id should probably be (group_id, id). Then this index can be used to retrieve the rows in the desired order. (This assumes that you order by id and not queuedate.) Note that since id is the clustered index, it is in the index of group_id anyway. Then again, if you only retrieve 50-100 rows at a time, I would not expect sorting to be very expensive. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Derek (gepetto_2000@yahoo.com) writes:
[quoted text, click to view] > if i do a lot of deletes, updates, and inserts on the table would the > cluster on id (or queuedate) be ok since the data pages won't have to > move as dramatically it is because the index is growing sequentially > anyway and new rows are added to the end. ? do i understand that > correctly? Yes. You can still experience problems with fragmentation, is some old rows are left around and never deleted, but that fragmenation would only appear at the tail of the table. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Derek (gepetto_2000@yahoo.com) writes:
[quoted text, click to view] > thank you. i can not express enough how appreciative i am for your > advice. i have > really learned a lot! > > is there a good book you can recommend on sql server internals? i need > something > that can explain to a non database person how data paging works, > fragmentation, and so on. i am working on sql server 2005. The obvious recommendation would be "Inside SQL Server 2005: The Storage Engine" by Kalen Delaney. This book is certainly not for a beginner. Then again, you asked for an internals book... The book is actually the third in a series of four. The first two are about T-SQL Querying and T-SQL Programming and written by Itizk Ben-Gan. (The fourth is about tuning and optimization, but has not been published yet, I believe.) Itzik touches topics like indexing but does not go into the gory details. (He does go into the gory details of querying and programming, though!) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
thank you. i can not express enough how appreciative i am for your advice. i have really learned a lot! is there a good book you can recommend on sql server internals? i need something that can explain to a non database person how data paging works, fragmentation, and so on. i am working on sql server 2005. [quoted text, click to view] Erland Sommarskog wrote:
> Derek (gepetto_2000@yahoo.com) writes: > > if i do a lot of deletes, updates, and inserts on the table would the > > cluster on id (or queuedate) be ok since the data pages won't have to > > move as dramatically it is because the index is growing sequentially > > anyway and new rows are added to the end. ? do i understand that > > correctly? > > Yes. > > You can still experience problems with fragmentation, is some old rows > are left around and never deleted, but that fragmenation would only appear > at the tail of the table. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |