Subject Re: Increase database respond, make it faster
Author Svein Erling Tysvær
No, if all stock needs the same fields, it is unlikely that you want
one table for each type - even though it would surprise me if Firebird
couldn't handle 2000 tables (I don't know and have never used 2000
tables myself, but given that I've never heard of anyone running into
the limit, I would expect it to be considerably higher than that). A
few tables are probably what you want.

ID Integer, Primary Key
Name VarChar (or whatever)
...various things that do not change

ID Integer, Primary Key
Stock_Type_ID Integer, Foreign Key
ChangeTime TimeStamp (or Date)
VolumeChange Integer or similar

and possibly some other tables.

Then, doing

SELECT <whatever>
FROM Stock_Type ST
JOIN Stock_Change SC on ST.ID = SC.Stock_Type_ID
<possibly joining other tables as well>
WHERE ST.Name = 'Bamsemums'

will give you the information you want pretty quickly (provided
Bamsemums is the item you're interested in, but there are people on
this list that think stock items that are neither Bamsemums, Database
WorkBench or LogManager are generally irrelevant).


--- In, "menjit_singh" wrote:
> The stock are not related between one and another. I will not do a
> query across all stock. One stock tells one story while another
> stock tells another story. the only query i will need is to get all
> of the data (open,high,low,close,volume,date) of only one
> particular stock at any one time.
> By the way, how many tables does firebird support?
> > > ok, understand. Lets say i create a table for each stock. So i
> > > may have upto 2000 tables. i will have a master table that keeps
> > > track of all available stock tables.Each table is sorted
> > > according to date. Will this be faster? How many tables does
> > > firebird support? can i access multiple table at the same time?
> >
> > An index on "stock" could help.
> >
> > Is the data related in any way? Would you be needing to query
> > across "stocks"?
> >
> > If so, keep it in a single table.
> >
> > Tables cannot be sorted. But you can create indices to speed up
> > searching when asking for results.
> >
> > What kind of queries do you need?
> >
> > With regards,
> >
> > Martijn Tonies
> > Please send questions and Bamsemums to the support list, not
> > directly to me.