Max Records

[CaptainPinko]

what would you guys suggest be the maximum number of records in a database that is being searched a lot? i have a database of events, but i wanna break it up into regions so that ppl gfrom vancouver aren't searchign throught stuff in toronto but i wanna know how small to make the groups because it is a bit of a pain w/ each additional table... any suggestions ort guidelines?

[James Stanich]

Have a table of regions . Then in every other table have the same field Region...

So if I want to look up Inventory I :

"Select * From Inventory where REGION = '" & Me.Combo1.Text & "'"

The combo part is if you loaded the regions and allowed user to select (thinking ahead)


Is this what you are after, or am I just rambling...

[Jethro]

I'm with you on this one James .... if running large databases, then they are large by nature no way around it. Which db you using, could generate state tables, and then programmatically generate a new one in the unlikely event of a new state being created......

We run a huge client database, and have fields for country and region. Searches run pretty quickly regardless..... maybe if your frontend is a web page or something could be a problem, other than that l don't really see any other approach. Splitting tables into increasingly smaller units will actually lead you to at some stage having to do joins, which are a) hard to maintain b) don't really allow ad hoc reporting c) making coding a pain in the arse..

As James pointed out, use an SQL on the table to extract just the state records you are interested in.

[James Stanich]

So have you redesigned your tables?

[JHausmann]

Originally posted by Jethro
I'm with you on this one James .... if running large databases, then they are large by nature no way around it. Which db you using, could generate state tables, and then programmatically generate a new one in the unlikely event of a new state being created......

We run a huge client database, and have fields for country and region. Searches run pretty quickly regardless..... maybe if your frontend is a web page or something could be a problem, other than that l don't really see any other approach. Splitting tables into increasingly smaller units will actually lead you to at some stage having to do joins, which are a) hard to maintain b) don't really allow ad hoc reporting c) making coding a pain in the arse..

As James pointed out, use an SQL on the table to extract just the state records you are interested in.

Umm, joins? Why? Just create a temp table (Select * into ##temptable from permanentTable) and then dump all the other tables into it (Insert into ##temptable select * from permanentTable2). Use ## temptable (for those unaware, that's how you define a global temporary table in SQLServer. For Access, just don't use the ##) in any select statements and drop when done.

Joins are best used when combining data from disparate tables, and are 1) easy to maintain, 2) allow for adhoc reporting and 3) do not make coding more difficult (you select from a join the same as a table). The programmer has to be aware that data insertion/modification/deletion needs to be handled outside of the join but it certainly isn't difficult.

[JHausmann]

Originally posted by CaptainPinko
what would you guys suggest be the maximum number of records in a database that is being searched a lot? i have a database of events, but i wanna break it up into regions so that ppl gfrom vancouver aren't searchign throught stuff in toronto but i wanna know how small to make the groups because it is a bit of a pain w/ each additional table... any suggestions ort guidelines?

The answer: there is no answer to your question without analyzing the data. Is your system more geared to data retrieval than input or are the bulk of your queries running against non-indexed fields? Consider modifying your indexing scheme. Do you have millions upon millions of records in an Access database? Consider upgrading to SQL Server.

Ideally, you'll get notification of poor responsiveness from your users when you cross the performance threshold, you'll need to identify your Highwater mark. You'll need to determine how often you want to do split maintenance. A lot of what you'll be able to do is going to depend on your DBMS (it may make more sense to abandon Jet and use SQL Server, for example).