January 17, 2006 at 10:21 am
I have a question about how to best design and implement a table and its indexes when the columns that will be searched on vary greatly from query to query. Here is the specific example that I am dealing with:
I have a table such as:
CREATE
TABLE [dbo].[MyDocumentInfo](
[DocumentID] [uniqueidentifier] NOT NULL,
[ProductType] [tinyint]
NULL,
[MinimumAmount] [money]
NULL,
[MaximumAmount] [money]
NULL,
[Length] [tinyint]
NULL,
[Brand] [tinyint]
NULL,
[MinimumScore] [int]
NULL,
[TransactionType] [tinyint]
NULL,
[DocumentationType] [tinyint]
NULL
)
My question is, what is the best way (or at least a really good way) to design and index this table if I can have queries of all sorts coming against the table? For instance, one query may be:
select * from MyDocumentInfo where length=5 and minimumscore=2
and another query may be:
select * from MyDocumentInfo where brand=5 and minimumamount>200000
The queries are built dynamically and change every time a search is performed. Also note that I don't do "Select *" in the actual queries, I just put that here to simplify my examples - but I do select every column in the queries. Currently I am getting a clustered index scan on my queries.
I have thought to index each search column individually since I don't have a concern with inserting performance, but will that really buy me anything? Any suggestions? Or can you point me to a thread that deals with this problem? I am looking for general practices and techniques for this problem and not necessarily a solution just for my given table. Thanks ahead of time.
Casey Pettingill
PS. Is there a name for this kind of a pattern or problem? Certainly it must be a common problem, but it is hard to search for solutions without a name.
January 17, 2006 at 10:47 am
You are probably seeing a clustered index scan because you have a primery key on the unique identifier. Will this ever be used in the generated query? I would suggest determining which columns will be searched on the most frequently and placing the clustered index there. Since your table is not very wide, you could index all of the other columns as well.
With any solution that you decide to use, I would use Query Analyzer to do some performance comparisions between your current table design and the new design.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply