June 7, 2007 at 5:12 pm
I am making a database what stores a tom of appearance data for our product. Currently there are about 2 million data points. The table is shown below. The queries can get very specific, asking for
Select avg(reading_values) from appearance where reading_date between *** and *** and reading_style='300' and reading_mod='1' and reading_colour='12' order by reading_location
The issue is that it is getting slow, I am new to sql and I hope I don't know only enough to get me into trouble but I would like to know what to index, and the best way to set the index up.
Thanks for everyone’s help.
Mike
[ID] [int] IDENTITY(1,1) NOT NULL,
[Acess_id] [int] NULL,
[reading_date] [datetime]
NULL,
[reading_type] [nchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[reading_location] [nchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[reading_style] [nchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[reading_mod] [nchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[reading_colour] [nchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[reading_value] [float]
NOT NULL,
[site] [nchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
June 8, 2007 at 10:48 am
It's hard to say, without knowing what is driving the search criteria, what is typically searched on, etc., but I can hopefully get you started, keeping in mind that these are based on guesses on my part as to how your data is distributed, typical searches, etc.
Reading_date looks like a potential candidate for a clustered index.
Several of your nchar columns appear to be foreign keys into various lookup tables. If this is true, is there a reason they are nchars instead of ints, for example?
Potentially, you could need an index on any of the other columns, but I typically don't index every column even in those cases where a user could potentially set them all as criteria. I index the datetime column, which limits it quite a bit, then often will apply indexes to any additional columns that are almost always included in the criteria, and then test performance. From there, I only add indexes as the need arises, which can come from a user that looks at the data in a completely different way from everyone else, as an example.
I'd likely put an index on that ID column, as I'm guessing that it is either an FK in another table, or that there is something you do with this table that would benefit from that, whether it be the front end ap, or something else.
Also, if any of the lookup tables that I'm assuming are present are large, I'd consider indexing that particular FK in this table.
A lot of this depends on factors such as how frequently inserts are performed, whether they are done in bulk, and many other factors. For instance, having a ton of indexes is going to slow down your inserts, but if you do few or no inserts, and if disk space is abundant, then you can go overboard, if you'd like.
In other words, like much in the SQL Server world, "it depends".
June 11, 2007 at 4:20 am
Thanks for the reply
To be honest I set this table up initially for easy of use doing manual queries, then we started writing an app to do them for us, then that grew to charting and the balloon took off. I am currently working on adding lookup tables and converting those columns to int's. That was leads to another question, will having lookup tables help with performance or just size?
Thanks for the help on the indexing I will play around with that today.
Thanks again
Mike
June 11, 2007 at 9:21 am
The lookup tables should help with both size and with performance, in most cases, but indexing can be important there, especially with very large lookup tables. Using int lookups also allows a lot more index entries to fit on a page, which is another reason it is usually faster.
If you want to keep the ease of use for ad hoc queries, you might want to consider wrapping the relationship in a view. Then you have the advantage of storing it optimally, while still maintaining simplified query creation.
June 13, 2007 at 4:33 am
Thanks again for the help. I will give a go.
Have a good one.
Mike Cooper
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply