I'm working with a client that has a 400 Million record database. The Win2000 Advance Server box consist of 4-Pentuim Z 450 w/2mb of cache, 4 gigs of memory and about 300 gig of disk space on 15 7200rpm SCSI W2 Hard Drive with 3 Adaptec Controllers separating the OS, SQLServer 7.0 and Misc hard disk space for other work need to be done on each channel.

The database is for searching people by fullname, lastname, firstname, address, partial address, city, state, zip code, gender or any combination of these criteria. For example: you can search for anyone named Smith in Los Angeles living on Figouroa regardless whether it's a Street, Avenue, Road or search for Victor Campos born on 01/01/1979 in California.

When we search by specific columns results are returned immediately however, when we search by more than 2 fields performance drops - some search may take up to 3 minutes to complete and return 1 record. Indices are set up on all necessary fields but there are no clustered or concatinated indeces. The database is used only for searching with an occassional bulk insert of about 10,000 records.

I'm thinking of seperating name information from address and separating birthday, gender and other non-searching criteria.

Would this be a proper design for such a large database or can anyone advise of a better way to get better performance of this database?

Any advice would be helpful...TIA!