large table help needed

large table help needed

Post by Larry Schenava » Sat, 02 May 1998 04:00:00



I have a 4 column table with 8 million rows.  The key is 3 of the
columns(one of them is a date).  The table is very slow to query.  Will
creating an index on those three columns help?  any other thoughts.

--
==============================
Larry Schenavar
Infrastructure Specialist /DB
Electronic Data Systems, Inc


==============================

 
 
 

large table help needed

Post by Patrick Flaha » Tue, 05 May 1998 04:00:00


It would depend on the uniqueness of the three columns.  If they are unique
or there is very little duplication then an index would probably be a good
idea.  A good rule of thumb (but not necessarily written in stone) is if you
will have to hit 30% or more of the rows, then you should probably do a full
table scan.

You might consider just creating the index and testing to see if it improves
performance.  You could always drop the index later.

Hope this helps.

Patrick Flahan

----------------------------------------------------------------------------
------------------------


>I have a 4 column table with 8 million rows.  The key is 3 of the
>columns(one of them is a date).  The table is very slow to query.  Will
>creating an index on those three columns help?  any other thoughts.

>--
>==============================
>Larry Schenavar
>Infrastructure Specialist /DB
>Electronic Data Systems, Inc


>==============================


 
 
 

large table help needed

Post by Dave McRa » Fri, 08 May 1998 04:00:00


I think it will, Larry.  It won't hurt to try anyway, as you can always drop
the index.  The reason that I'm a little unsure is because I've not indexed
a date field before.  I have also a 3-field (all numerics) concatenated
primary key on one large table (~300K records) and it ran like a dog without
indexes.  I quickly added 6 indexes and it goes like a dream (6 being, 1 for
each field, 1 for the first 2 of 3 fields (a common query), another for the
1st and 3rd of the 3 (another very common join) and 1 for all 3.  - I don't
have 2nd and 3rd as it's not a valid (for business reasons) join.  Also, I
should have more indexes being for the 2nd and 1st, and for 3rd and 1st
(note the order)  - but I'm careful I get the joins right (I check using
explain table).

--

Canberra                            02 6239 4247


>I have a 4 column table with 8 million rows.  The key is 3 of the
>columns(one of them is a date).  The table is very slow to query.  Will
>creating an index on those three columns help?  any other thoughts.

>--
>==============================
>Larry Schenavar
>Infrastructure Specialist /DB
>Electronic Data Systems, Inc


>==============================

 
 
 

large table help needed

Post by Merle Martel » Sat, 09 May 1998 04:00:00



> I think it will, Larry.  It won't hurt to try anyway, as you can always drop
> the index.  The reason that I'm a little unsure is because I've not indexed
> a date field before.  I have also a 3-field (all numerics) concatenated
> primary key on one large table (~300K records) and it ran like a dog without
> indexes.  I quickly added 6 indexes and it goes like a dream (6 being, 1 for
> each field, 1 for the first 2 of 3 fields (a common query), another for the
> 1st and 3rd of the 3 (another very common join) and 1 for all 3.  - I don't
> have 2nd and 3rd as it's not a valid (for business reasons) join.  Also, I
> should have more indexes being for the 2nd and 1st, and for 3rd and 1st
> (note the order)  - but I'm careful I get the joins right (I check using
> explain table).

> --

> Canberra                            02 6239 4247

Dave, please take this a constructive critism. I think you have about 3
indexes that aren't helping at all.
These are the column combinations (and order) that would make the most
sense of what you have installed (assuming there is no PK constraint,
which would also create an index):

1,2,3
1,3
2
3

The index on just column 1 can be satisfied by 1,3 or 1,2,3 efficiently.
The index on 1,2 can be satisfied efficiently by 1,2,3 index.

Any index with exact duplication of a column list, starting at the left,
is redundant, almost always unnecessary, and causes needless overhead on
inserts, updates, deletes, or loads.

I also wouldn't set up indexes on both 1,2 and 2,1 (as you were
initially considering).  2,1 would only be of value if you had a query
with a where condition for 2 (but not 1), but selected column 1 in the
SELECT list.  This would allow for an index-only read scenerio.  If this
were true, I'd replace the index on 2 with an index on 2,1


 
 
 

1. Large Table,Virtual editable GRID control need help

Hi,
I am trying to find a way to deal with large table and GRID control

The grid is editable,means that user can click on a cell and a combo
box with different options will appear.

i dont want to popultae the entire table.

what i have in mind is to use a virtual GRID control
each time i will add 200 rows to the grid.

how exactly can i update changes...?
should i execute UPDATE query on each cell change..?
should i collect all the updates and send it to the server(how...?)

i want to use client side cursor,disconnected recordset.

any idea...?

thanks in advance

tomix

2. adDecimal and Shape

3. Help:Need to dedupe a large table

4. U.C. Berkeley Short Courses on Software

5. Urgent Help Need: CReate Large Table

6. Oracle DBA - n. Va.,USA

7. Help needed: Large table browsing algorithms

8. Pdox 4.0 App Printing Problem

9. Help Help Need to copy a field from one table into another table

10. Reference needed - Current Large (Largest) SQL 7.0 Database?

11. Large Table Query - need more speed

12. How much memory is needed in Sql server 7.0 for a large table

13. need to import large tables