Re: sorting problem
From: Steve Kass (skass_at_drew.edu)
Date: 07/17/04
- Next message: Carol: "Text file import fails because of quotation mark"
- Previous message: Andrew John: "Re: SQL2000 and Yukon on same computer"
- In reply to: Girish: "sorting problem"
- Next in thread: gbb: "Re: sorting problem"
- Reply: gbb: "Re: sorting problem"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 16 Jul 2004 22:55:32 -0400
Girish,
A query result set sorted by SortIndex can often benefit from a
covering index whose first column is SortIndex. A clustered index is
always a covering index, and a nonclustered index is a covering index
for a query when the indexed columns include all columns of the [Data]
table that participate in the query, whether in the select list or in
join or filter conditions. The exact query and data are also relevant,
and this isn't a blanket statement - for instance, if another column of
the Data table occurs in a join or a restrictive WHERE clause of the
query, an index where that other column precedes SortIndex might be optimal.
If you have millions of rows, or even thousands, you might want to
reconsider giving users only the up and down arrows as ways to move the
sort order, and unless you have only one user per table, you need to
consider whether you plan to maintain separate preferred sort orders for
each user, or live with users constantly undoing each others' changes to
the table ordering.
Finding MAX(SortIndex) will be very fast if there is any index with
SortIndex as its first indexed column, though you should be careful with
transactions and their isolation level if items can be added by
concurrent processes. Two processes could both read the MAX value
before either one adds and commits a new value.
You probably need to do a little more thinking about the user
interface and what users will need or use before being able to answer
too many specific questions about the design of the database. Keep in
mind that if your users are viewing data on a monitor, they are unlikely
to be looking at more than a few dozen or hundreds of rows, and users
may not need nearly as much flexibility as you are considering providing..
Steve Kass
Drew University
Girish wrote:
>I have a question about how to implement a sortindex on my rows and I'm not
>sure I'm doing sort the right way.
>
>I want to allow a user to sort the entries manually, I am using a separate
>field (SortIndex) in an existing table. So every record has a # in its
>SortIndex field, and when I display them I sort by SortIndex, and then give
>the user ^ and v to move up and down, in which case I will manually swap
>those topics' Sort Indices using update statements.
>
>Is this the best way to do this sortindex?
>
>Table 1 [Data]
>ID [1] Topic [Cytoskeleton] SortIndex [3]
>ID [2] Topic [Immune Response] SortIndex [2]
>ID [3] Topic [WBC] SortIndex [4]
>ID [4] Topic [Exoskeleton] SortIndex [1]
>
>Im thinking about indexes, physical database layout etc problems.
>
>Can i place a non clustered index on SortIndex?
>Will i use the same table design for millions of rows of data in a table vs
>one that only has say a thousand?
>How can I easily get the MAX sortIndex number for new records that will be
>inserted? Select MAX(sortIndex) + 1 seems really bad!
>
>Thanks for anyones help!
>Girish
>
>
>
>
- Next message: Carol: "Text file import fails because of quotation mark"
- Previous message: Andrew John: "Re: SQL2000 and Yukon on same computer"
- In reply to: Girish: "sorting problem"
- Next in thread: gbb: "Re: sorting problem"
- Reply: gbb: "Re: sorting problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
