fetch data very slow from large database

Anon

Dear friends,
I have a large database that more than 18million records. I want fetch some records from it. But it very slow. I wait for about 5 minutes before it appear. The sql statement is "select name from billing where time <10". How can I promote the speed. Pls help. Thank you.

Anon

friend,

Which database you are looking for? Can you detail your hardware (memory, CPU, disk (disk array or internal, size))? Do you have index on this field?

For your case, it need further information to come out solution, 1.8m records only shold not be that long.

Mansfield Chen

Anon

Well it depends on how many rows are being returned and whether you have indexed your table properly.

Make sure you have an index on any column in your WHERE clause (in this case, the "time" field).

And try to reduce the number of rows being returned - use a LIMIT/offset statement if you are on mysql or postgres

Pretty basic stuff ?

Anon

Sun E450,512M,2CPUs/4Mcache,54GHDD,A billing database that contain 18000000 records with 16 fields.

Anon

I want return all the records that meet the condition. No more than 1000 records with average 150 records. And the database is real time. It append records continuously and could not create index.

Anon

Xht,

Well, I am working on heavily loaded financial switching center, our data are several folds than yours. And, we are really real-time.

The most efficient way which can speed you up is to create index for the column that you wanna query, it does not cause by the number of records returned, it is a full-scan on database.

Your hardware is ok.

Mansfield

Anon

Creating an index has nothing to do with 'real-time' usage: it's a property of the database, and should be created after you perform the initial data load (if any.)

If, on the other hand, you mean the database couldn't meet performance requirements for inserts if you added an index to this column, then the only solution is faster hardware (enough so that you can meet performance requirements for adding data when the index exists.)

Anon

We have tried use pro/c to solve this problem. the pro/c program fetch so rapidly. Why? Does it mean OCI is not so good than C program?

Anon

OCI might be introducing some overhead, but no matter what a sequential scan through 18 million records is going to be relatively slow. You really do need an index on the column to get adequate retrieval performance.