[firebird-support] Performance diff between insert...select and for select ... do?

[Kjell Rilbe kjell.ri...@datadia.se [firebird-support]]

Hi,

I'm writing a utility that will need to do two things for each record in 
an external table and for this purpose I use a for select ... do 
construct in an execute block. I do it this way because external tables 
can't be indexed and I will scan the entire external table anyway.

The two operations are:
1. Update one existing record in the target table.
2. Insert new record in the same target table.

In "steady state" the target table will contain about 20 million records 
and the external table will contain about 10 thousand records.

But the first time I run this, the target table will be empty and the 
external table will contain about 18 million records. The update will 
never find a record to update during this first execution.

Would I lose a lot of hours if I use the same execute block/for select 
construct the first time? The alternative would be to do a regular 
insert into target table select from externaltable the first time. My 
guess is that the unecessary update will take increasingly long to 
execute as the target table grows towards 18 milliion records, wasting a 
lot of time for me, even if a suitable index is present.

Just to give a measure of the system's performance as it is, a simple 
update on one column for 18 million records in the target table 
currently seems to take about 6 hours. But I could drop a couple of 
indices and perhaps I should bump up the page size from 4k to 8k or 16k.

Regards,
Kjell
-- 

Kjell Rilbe
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

DataDIA AB
Ulvsundavägen 106
168 67 Bromma
www.datadia.se 
08-514 905 90

Företagskontakt.se  - urval av företag 
och kontaktinformation
Personkontakt.se  - urval av hushållsadresser

Re: [firebird-support] Re: Grouping without sorting alphabetically

[Venus Software Operations venussof...@gmail.com [firebird-support]]

[firebird-support] Determine total pre-allocated space

[Bryan Cole bryan.c...@teraview.com [firebird-support]]

Hi,

I need to be able to detect when the total available space for a firebird 
database is running low. When this happens I need to warn users to purge old 
data. However, deleting data does not free up disk space. This in itself is 
fine, but how can I measure the resulting free space in the database after 
deleting many records from a table?

What I want is total space available being the sum of available (unallocated) 
disk space + free pre-allocated space. Measuring unallocated disk space is easy 
but I've no idea how to measure the pre-allocated space.

Thanks,

Bryan Cole

RE: [firebird-support] Re: Grouping without sorting alphabetically

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

Dataset:
1Haemogram Report  1Routine 
Haemogram
1Haemogram Report  5Diff. Count 
(Mature Cells)
1Haemogram Report  10   Special 
Investigations
2Haemogram Report  1Diff. Count 
(Mature Cells)
3C-Reactive Protine1
4Urine Analysis1Physical 
Examination
4Urine Analysis8Chemical 
Examination
4Urine Analysis10   Microscopic 
Examination of Centrifugalised Deposit
5Haemogram Report  1E.S.R.
 
Bhavbhutis attempt:
WITH cteTestGroups AS (SELECT MIN(A.iSrNo) AS iGroupSrNo
, B.cTitle
, MIN(C.iSrNo) AS iSubGroupSrNo
, C.cSubGroup
FROM sReqSlipTestGroup A
JOIN mTestGroups B
ON  B.iID = A.iTestGroupID
JOIN lTestGroupsTest C
ON C.iPID = B.iID
JOIN mTests D
ON D.iID = C.iTestID
WHERE A.iPID = 1
GROUP BY B.lNewPage
, A.iSrNo
, B.cTitle
, C.cSubGroup
ORDER BY 1, 2, 3, 4)

SELECT cteTG.*
FROM cteTestGroups cteTG

>My final output I want something like this:
>1    Haemogram Report      1    Routine 
>Haemogram
>1    Haemogram Report      5    Diff. Count 
>(Mature Cells)
>1    Haemogram Report      10   Special 
>Investigations
>5    Haemogram Report      1    E.S.R.
>3    C-Reactive Protine        1
>4    Urine Analysis        1    Physical 
>Examination
>4    Urine Analysis        8    Chemical 
>Examination
>4    Urine Analysis        10   Microscopic 
>Examination of Centrifugalised Deposit
>
>ie.  The Row 4 is merged with row 2 and row 9 becomes row 4
>
>The idea is that all the cTitle are clubbed together but in the order they 
>were originally selected, thus MIN(A.iSrNo).
>The second grouping is cSubGroup, together but in the order they were defined, 
>thus MIN(C.iSrNo)

I think an additional CTE may be what you want:

WITH cteTestGroups (iGroupSrNo, cTitle, iSubGroupSrNo, cSubGroup AS
(SELECT MIN(A.iSrNo), B.cTitle, MIN(C.iSrNo), C.cSubGroup
 FROM sReqSlipTestGroup A
 JOIN mTestGroups B ON B.iID = A.iTestGroupID
 JOIN lTestGroupsTest C ON C.iPID = B.iID
 JOIN mTests D ON D.iID = C.iTestID
 WHERE A.iPID = 1
 GROUP BY B.lNewPage, A.iSrNo, B.cTitle, C.cSubGroup),
 cteOrderGroups(cTitle, cOrder) AS
 (select cTitle, min(iGroupSrNo)
  from cteTestGroups
  group by 1)
        
SELECT cteTG.*
FROM cteTestGroups cteTG
JOIN cteOrderGroups cteOG on cteTG.cTitle = cteOG.cTitle
ORDER BY cteOG.cOrder, cteTG.iSubGroupSrNo

Maybe things could be simplified further, but this at least ought to work.
Set