Urgent, need Idea to speed up my huge database

[Armin Bahl]

I have a huge database, with nearly 300000 people. I want to search quickly for a one by name or birth. But It lasts much too long. Who can help. Please help. A little advice would be enough.

[Mike Deskevich]

do you have indexes on Name and Birthdate?

mike

[peterchen]

Hi,
Sometimes, it might be necessary to compact the databas e(typically with smaller engines like JET).
Also, Dou you already have an index on the column(s) you're searching for?

Peter

[Armin Bahl]

Hi. I know that I need indexes, but I do not know how it works.

[Armin Bahl]

Compacting is good, but in the future my database will perhaps grow to a million people and than it is still slow with compaction. THe indexes are very good, but I do not know how to use them. Perhaps help again?

[Mike Deskevich]

an index created a column or set of columns that you use to do searches...i believe it sets up a b-tree for that column (or group of columns)...to create one just do this after you create the table

CREATE INDEX MyIndex ON TableName(Column1[,Column2[,...]])

mike

[Armin Bahl]

Thank you for the fast reply. I'll try it. Can I send you an eMail, if I still have any problems?

[Mike Deskevich]

sure, you can post here or email michael.deskevich@smed.com...i'll be at work for another half hour...after that just email me at home: mikedeskevich@yahoo.com

mike

[Armin Bahl]

Thank you. I think that I will have to ask you. Because I use VC 6.0++ and the kind of writing the code you did I have never uses. Basic?. But I'll try first. Bye. Thanks.

[Mike Deskevich]

no, that CREATE INDEX... is SQL DDL code...it belongs in the schema file you are using to actually create the tables in your data base...if you're not using a schema file, then you have to go into the dbms and set up the index...where you do this depents on which dbms you are using.

mike

[peterchen]

You have an good answer from mike.

If compacting is _possible_ with your DB, either you say "DB Admin has to do it", or you change to another engine...

Still need help?

[Armin Bahl]

Wow. it is wonderful these fast replay. Thank you again. I hope I will achieve. Thank you. Perhaps till later.

[Armin Bahl]

I thought of indexes or something like that. But thank you.

[PeterK]

If indexes do not sufficiently speed up your query, you want to keep the indexes but perhaps next try Stored Procedures.

[Armin Bahl]

Could you explain it more exactly, it sounds iteresting?