Performance Considerations and Multiple databases

[Rick Kelly]

I just finished putting together the DDL to build the initial version of my applications database. I have a postal code table that currently contains rows for USA and Canada (I also optionally have Mexico if it is needed). While the USA zip codes generates about 42,000 rows, the postal codes for Canada are a whopping 828,000 rows and generate a database of about 65mb. I've normalized everything I can think of and did a VACUUM at the end to clean up.

Would it be worthwhile to use a postal code only database (shouldn't be much insert/update/delete activity) and attach it for use along with my main database?

I'm wondering if the bulk of my queries that don't involve postal codes would perform better.

[Rolf Brandt]

Hello Rick,

I do not think that there will be much of a performance problem since the records holding the postal codes will be very small (I guess).

But if they are not used very often you might want to put them into an attached database.

Rolf

[Rick Kelly]

Thanks for you time in responding Rolf. I attached a small sample of the DDL I use to create some of the relevant tables. I think the rows are about as small as I can make them in the POSTAL table. I can try one or separate files and see how it goes. It's not much of an effort to switch back and forth.

Rick

[Rolf Brandt]

Hi Rick,

have to rush out now, but I am going to have a look at it tonight.

Rolf

[Fred Meier]

Rick, I believe performance must be tested.  I would create the database
one way and then test it.  Create it another way and test again.  The
better design should result. 

Some comments about the DDL you posted.  Primary Key and Unique are
redundant.  You are creating you columns with "type affinity".  I would
create your SUBDIVCLASS table as follows:

Code Select Expand

Create Table SubDivClass (SubDivClassCD Primary Key, SubDivClassDesc, DateLastMaint)
 
There is noting wrong with using "type affinity", but I have found it to
be of little or no value.  Integer Primary Key is the exception.  See the
tutorial I posted at http://www.sqlitening.com/support/index.php?topic=3165.0