Posted by Jason Lepack on 05/02/07 18:44

Think of IX_4 like a phone book.

City, Last Name, First Name, Phone Number.

That's great, as long as you know what city a person is in. If you
don't know the city then it takes you a lot longer to look up the
perosns name because you have to loop through each city to find if the
person is in there.

That's where IX_1, Ix_2, and IX_3 come in. You could easily get rid of
IX_1, because it will be in the same order as IX_4.

Cheers,
Jason Lepack

On May 2, 1:07 pm, rshivara...@gmail.com wrote:
> On May 2, 10:39 am, Utahduck <Utahd...@hotmail.com> wrote:
>
>
>
>
>
> > On May 1, 6:29 pm, rshivara...@gmail.com wrote:
>
> > > Hi :
>
> > > I have a TableA with around 10 columns with varchar and numeric
> > > datatypes
> > > It has 500 million records and its size is 999999999 KB. i believe it
> > > is kb
> > > i got this data after running sp_spaceused on it. The index_size was
> > > also pretty big in 6 digits.
>
> > > On looking at the tableA
> > > it didnot have any pks and hence no clustered index.
> > > It had other indices
> > > IX_1 on ColA
> > > IX_2 on ColB
> > > IX_3 on ColC
> > > IX_4 on ColA, ColB and ColC put together.
>
> > > Queries performed in this table are very slow. I have been asked to
> > > tune up this table.
> > > I know as much info as you.
>
> > > Data prior to 2004 can be archived into another table. I need to run a
> > > query to find out how many records that is.
>
> > > I am thinking the following, but dont know if i am correct ?
> > > I need to add a new PK column (which will increase the size of the
> > > tableA) which will add a clustered index.
> > > Right now there are no clustered indices
>
> > > 2. I would like help in understanding should i remove IX_1, IX_2, IX_3
> > > as they are all used in IX_4 anyway .
>
> > > 3. I forget what the textbox is called on the index page. it is set to
> > > 0 and can be set from 0 to 100. what would be a good value for it ?
>
> > > thank you.
> > > RS
>
> > I'm afraid I might not be able to offer all the hints you are looking
> > for, but here are some things I've found helpful:
>
> > 1) LOOK AT YOUR QUERIES!! We've obtained huge performance boosts
> > just by looking at the queries and stored procedures and optimizing
> > them. Usually if a query is going extremely slow it is because of
> > poor programming techniques.
> > 2) LOOK AT YOUR QUERIES!! Are they sometimes filtering only on
> > ColB? If so, IX_4 will be useless if you remove IX_2. IX_4 will kick
> > in when you are filtering on several columns. But are there other
> > columns that are being filtered on?
> > 3) Are you dynamically parcing or calculating columns? For instance,
> > if you have a column with EventDateTime of 01-01-2007 12:34PM but you
> > are filtering on dates (ie WHERE CONVERT(varchar, EventDateTime, 101)
> > = '01-01-07') then that is going to slow down your query. Create a
> > new column of EventDate and calculate the event date from the event
> > date and time and then stick an index on it. That will add some
> > boosts as the sampled WHERE clause won't reference the index.
> > 4) Do you look at the query execution plan? That can tell you if you
> > are doing full table scans or hitting an index.
> > 5) Have you used the query index wizard?
> > 6) Breaking the table up will help a lot. At one company I consult
> > for we have a similar table (a half million records added per day)
> > with data going back to 2003. We create two (what we call) _Mini
> > tables. TableName_Mini_Last45Days, TableName_Mini_Last6Months, and
> > TableName_Mini_Last1Year. This does take up space but since 80% of
> > the queries only want data from the last month the first mini table
> > makes sense to hit. If they need to go further back further then they
> > hit the appropriate table. The main table (TableName) is rarely
> > queries except after its morning data load (off hours) to create the
> > _Mini tables so if somebody does need information from it, it isn't
> > bogged down by heavy usage.
> > 7) I've never done this personally so I cannot attest to its success
> > (though I should test it). I have heard you can recreate/rebuild an
> > index and that should get rid of some of the fragmentation. I think
> > I'll give it a test, but you might want to do that as well. But since
> > it is re-indexing half a billion records you may want to do that off-
> > hours.
>
> > I hope one or more of these helps you out!- Hide quoted text -
>
> > - Show quoted text -
>
> Thank you Utahduck, your mail was helpful. Also am reading up on
> indexes and that is helping me.
> You idea of laying out mini tables by the year is a great suggestion i
> hadnt thought of. I will be definitely using that
> Thank you
> RS- Hide quoted text -
>
> - Show quoted text -

[Back to original message]