Hi all, I have a theoretical proble

[JamesNicholls]

Hi all, I have a theoretical problem which I am looking to draw on peoples experience to solve.

I am designing a solution which needs to pick prices based on a number of complex factors, including UK postcode. The database held postcode : price (table pricearea) relationship defines geographical boundaries and is fluid. Due to the complex nature of price selection I have major concerns over performance, and am looking for a low overhead solution.

I can either do a direct look up (i.e. select area from pricearea where postcode = x) or drill down bit by bit, so with a postcode in the format "ab1c 2de" I can build a temporary recordset based on, say, taking the first 2 characters of a customers postcode and pulling all data based on this and then querying that list at the next level, using the 3rd character, etc.

What do people think about this as a more efficient way to do it, or does anyone have a better idea or have done this before?

 

[Turkbear]

Hi,
Some theoretical answers:

I would suspect that , if the postcode were an indexed field, the table were regularly analyzed ( at least after any large inserts) and the query used the full text of the value, the response would be much faster than any substring-based set of queries..
To further speed things up, if you have enough memory on the server, you could pin the table in memory so no disk I/O would be needed..
Depending on how many fields are in the table, you may want to consider building it as an Index-organized table ( IOT) see the docs about this..

Have fun testing ..