<<
Can you tell me more how efficient by keeping blobs outside of the main table?
>>
With blobs kept in another table, the records get smaller and there
are space for more records on each page. This can lead to fewer
reads for disk. This was discussed in a session at the conference in
Luxembourg this November.
I'll admit that I haven't paid much attention to this myself -
perhaps other users have made interesting observations.
<<
I have never used a generator
>>
In this case a trigger may not be the best solution.
You can use a query with
select gen_id(ID_GEN,1) from rdb$database
Maybe your access components makes this even easier. E.g. with
IBObjects you can use (from Delphi)
ID:=TIB_Connection1.gen_id('ID_GEN',1);
After you fetch the value of a new ID you can store this in new
records in both tables.
<<
SELECT FIRST 1 YMD FROM DAILYBLOB WHERE SYMB = 'S1234' ORDER BY YMD DESC;
>>
You can use
select first 1 YMD
from DAYLYMAIN
where SYMB = 'S1234'
order by YMD desc
If you use "select first ID, YMD ..." you can then easily pick up
blobs from the other table by using the value of ID.
<<
SELECT TB FROM DAILYBLOB WHERE SYMB='S1234' AND YMD='2011-07-25';
>>
select DB.TB
from DAYLYBLOB DB join DAYLYMAIN DM on DB.ID = DM.ID
where DM.SYMB = 'S1234'
and DM.YMD = '25.07.2011' -- or '2011-07-25'
For parameters - and in particular parameters of type date - using a
parameterised query is usually the best:
select DB.TB
from DAYLYBLOB DB join DAYLYMAIN DM on DB.ID = DM.ID
where DM.SYMB = 'S1234'
and DM.YMD = :YMD
and - depending on your components set - something like
TIB_Query1.ParamByName('YMD').AsDate:=YourDateVariable;
<<
How can I change these statements if I use two tables(DAILYMAIN and
DAILYBLOB) following your suggestion?
3) insert was like:
INSERT INTO DAILYBLOB VALUES('S0001', '2011-12-01', <TB>, <MB>, <DB>);
>>
1: get a new ID (from gen_id)
2: use 2 inserts (for each of the old ones)
insert into DAYLYMAIN (ID,SYMB,YMD) values (:ID,:SYMB,:YMD)
and
insert into DAYLYBLOB (ID,TB,MB,DB) values (:ID,:TB,:MB,:DB)
3: commit
(or use a stored procedure to do both inserts)
YMMV : Your Milage May Vary
In essence: Your results with your data may give different results
(but that is more important than theory and speculation!).
--
Aage J.
