I need some advise about selecting a database system.
Currently, I have a client that has an existing system for keeping track of, among other things, county land records. The bulk of the data in this database is static data that is reloaded once a day early in the morning. It is used throughout the day for users to search for property records, subdivision covenants, etc. Right now, there is a one-to-one relationship between the tables in the database and the files in the county’s AS/400. The data is in need of normalizing, but that will require a considerable time investment because the existing search systems would have to be rewritten to account for the new database structure.
The system is currently using PostgreSQL.
My question is, what would be the best database system to use for the system. The client is looking for suggestions about how to speed up the site.
As an example I have tables:
CREATE TABLE ilgf ( "type" character(2), book integer, page integer, subpage character(2), o_or_e character varying(13), seq integer, name character varying(32), "month" integer, "day" integer, "year" integer, index_type integer );
CREATE TABLE ilf ( "type" character(2), book integer, page integer, subpage character(1), "month" integer, "day" integer, "year" integer, "time" integer, nature character varying(20), description character varying(30), receipt_number character varying(9), amount numeric(30,2), "operator" character(3), last_file_main bigint, grantor_count integer, grantee_count integer, subdivision_lots integer, metes_bounds integer, daily_flag character(1), docket_number integer, parcel_number character varying(13), parcel_description character varying(30), acreage numeric(30,3), ending_page integer, ending_subpage character(1), ucc_number integer, instrument_number bigint, doc_type character(2), data_1 character varying(30), data_2 character varying(30) );
Table ilgf has ~1.7 million records, table ilf has ~600,000 records. The tables are related to each other on the ‘book’ and ‘page’ fields.
One of the queries that is run for the subdivision covenant search is:
SELECT DISTINCT ilgf.name FROM ilgf, ilf WHERE ilgf.book = ilf.book AND ilgf.page = ilf.page AND (ilf.doc_type = 14 OR ilf.doc_type = 15 OR ilf.doc_type = 28 OR ilf.doc_type = 29 OR ilf.doc_type = 93 OR ilf.doc_type = 98) ORDER BY ilgf.name;
This query is used to retrieve unique property names to use as the source for an auto complete textbox. This query takes ~30 seconds to execute in the current system.
Also, to give some scope to the size of the database, when the data is reloaded everynight, each of the ~15 involved tables are truncated and their data reloaded from the AS/400 files to pick up changes and new entries. This process takes ~6 hours to complete.
So, what I need/would like is any input, or tips about either a database system that would be better suited to serve this static data, or some performance tuning types for the current database system.
I know that this is a vague question, and what constitutes the “best” DBMS is very subjective. I’m really just looking for opinions and suggestions that I can use as a jumping off point for more research. It may be that PostgreSQL is the best option, but the server needs to be performance tuned. I need some discussion about how to approach this problem to get me headed in the right direction.
Thanks for the help, and thanks for reading my long winded post