royal mail PAF on SQL

windows 2000 server
SQL 2000
HP ML370 2.8ghz
1 gb ram

im buying the PAF file (UK) from royal mail and im going to stick it onto this SQL server but i dont know what the performance is going to be like having to search through 27 million records. does anyone else use the PAF file? and what is the CPU, memory usage like searching though this many records? - should i expect it to be really slow?

i guess the final goal will be to have a web page search for a Postcode and return the correct address.  ( ADO, iis5, classic ASP ) Intranet, not on web

if anyone thinks this isn't going to work, what minimum spec hardware should i be looking at.

many thanks

hb
LVL 2
hb21l6Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
Remember to apply an index to the fields that you are searching on.

A bit more RAM wouldn't hurt.

The disk subsystem is also very important and will make a big difference to the speed.


I suggest that you import the records and check the performance when issuing a query from Query Analyzer. Then you can test your web response time (which will be a little bit longer)
hb21l6Author Commented:
thanks for the reply nmcdernaid,

Do you think this hardware will be sufficient to search through 27 million records in one table?

I guess my other option is to split down the file into smaller tables and have some smart programming to point to the correct table..

hb
nmcdermaidCommented:
It should be fine to do that, just make sure you index the search fields.

If you have anything else running (ie IIS or Exchange) on the same server you'll probably run into troubles. Don't expect to be able to add IIS to that server later and expect it to perform the same.



>>I guess my other option is to split down the file into smaller tables and have some smart programming to point to the correct table..

I would avoid any smart programming as it always trips you up in the end!!

A 'SQL' way of doing this would be to create a paritioned view

You split the postcodes into seperate tables, then join them all up with a view.

If you have the correct edition of SQL Server, its smart enough to go to just the table that it needs. Basically you can implement your idea without any programming at all, SQL Server knows what to do.


Anyway that won't be necessary from the outset, but just be aware that SQL can do a lot of things to save you writing complicated code.


What kind of disk subsystem does this have? RAID of any kind or just a single disk?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hb21l6Author Commented:
5 x 32gb disk scsi 15000 rpm.
raid 5.

i've stuck to a nice simple layout for the logical drives
C: O.S. ( 10gb)
D: application (10gb)
E: Data  (30gb) est.. usually what evers left on disk space.

Thanks for all your help nmcdermaid,

i'll give it a whirl and see what happens...

hb

nmcdermaidCommented:
If you get the opportunity you should put indexes, log files, tempdb all on seperate drives. But it'll work fine without that arrangement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.