Hi Claudio:

Does this belong here?

IB 6 dialect 3 IBX 4.2 BCB5 WinNT4 sp5

Queries below are run with IBDataSet against IB 6 dialect 3, tcpip to
localhost. It is very slow!!

1000 master records and about 4000 detail records. Results of 1 & 3 are
loaded into temps then 2 & 4 executed against those. Results are loaded
in to stringlist for running report.. The queries are the slow part.

Any suggestions
appreciated.
Best regards

Sequence: 1

Insert into Sum1
(ID, STATUSMEM, RENEWDTE, IDINITPAY, INITFEE, INITDUES,
INITPAID, MEMCANCEL, REVRECVD, REFRECVD, ASSESSLEV, ASSESSAMT,
ASSESSPD, IDPARENT, IDASSOC, STATUSASOC, ASOCACTIVE, ASOCANCEL)
SELECT M.ID AS ID, M.STATUS_MEM AS STATUSMEM,
M.RENEW_DTE AS RENEWDTE, M1.ID AS IDINITPAY,
M1.APPL_FEE AS INITFEE, M1.FIRST_DUES AS INITDUES,
M1.PAID1_MEM AS INITPAID, M1.CANCL_DATE AS MEMCANCEL,
M1.REV_RECVD AS REVRECVD, M1.REF_RECVD AS REFRECVD,
M1.REFAS_LEV AS ASSESSLEV, M1.REFAS_FEE AS ASSESSAMT,
M1.REFAS_PAID AS ASSESSPD, A.ID AS IDPARENT, A.ASSOCID AS IDASSOC,
A.STATUS_MEM AS STATUSASOC, A.INITL_DATE AS ASOCACTIVE,
A.CANCL_DATE AS ASOCANCEL
FROM members M
INNER JOIN mempay1 M1
ON (M.ID = M1.ID)
LEFT OUTER JOIN Assoc_c A
ON (M1.ID = A.ID)


-----------------------------------------------------------------------------------------------------------------
Sequence: 2

SELECT ID,
(SELECT COUNT (DISTINCT S1.ID)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' ) AND
S1.INITPAID >= '01/01/2000' AND S1.INITPAID <= '08/31/2000'
) AS TotNew,
(SELECT COUNT (DISTINCT S1.ID)
FROM Sum1 S1
WHERE ((S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' )
AND
S1.INITPAID < '01/01/2000' ) OR (S1.STATUSMEM = 'CANCELLED'
AND
S1.MEMCANCEL > '08/31/2000' AND S1.INITPAID < '01/01/2000'
)) AS TotExist ,
(SELECT COUNT (DISTINCT S1.ID)
FROM Sum1 S1
WHERE S1.STATUSMEM = 'CANCELLED' AND S1.MEMCANCEL >= '01/01/2000'
AND
S1.MEMCANCEL <= '08/31/2000' ) AS TotCancel,
(SELECT SUM (S1.INITFEE)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' ) AND
S1.INITPAID >= '01/01/2000' AND S1.INITPAID <= '08/31/2000'
) AS NewFees,
(SELECT COUNT (S1.ASSESSLEV)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
S1.STATUSMEM = 'CANCELLED') AND
(S1.ASSESSLEV = '03/01/1997' )) AS AssessCnt,
(SELECT SUM (S1.ASSESSAMT)
FROM Sum1 S1
WHERE ((S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND S1.ASSESSLEV = '03/01/1997' AND
S1.ASSESSPD >= '01/01/2000' AND S1.ASSESSPD <= '08/31/2000'
AND
S1.ASSESSPD <= '12/31/1999' )) AS AssessPaid,
(SELECT SUM (S1.ASSESSAMT)
FROM Sum1 S1
WHERE ((S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND S1.ASSESSLEV = '03/01/1997' AND
S1.ASSESSPD <= '12/31/1999' AND S1.ASSESSPD <=
'08/31/2000')) AS TotAssPaid,
(SELECT SUM (S1.INITDUES)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' ) AND
S1.INITPAID >= '01/01/2000' AND S1.INITPAID <= '08/31/2000'
) AS NewDues,
(SELECT COUNT (S1.REFRECVD)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND S1.REFRECVD IS NOT NULL AND
S1.REFRECVD >= '01/01/2000' AND S1.REFRECVD <= '08/31/2000'
) AS TRGRecvd,
(SELECT COUNT (*)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ASSOCIATE' AND S1.STATUSASOC = 'ACTIVE' AND
S1.ASOCACTIVE <= '08/31/2000' ) OR (S1.STATUSASOC =
'CANCELLED' AND
S1.ASOCANCEL > '08/31/2000' )) AS TotAssoc
FROM Sum1 S;


-----------------------------------------------------------------------------------------------------------------------------
Sequence: 3

Insert into Sum2
(ID, STATUSMEM, RENEWDTE, IDINITPAY, INITFEE, INITDUES,
INITPAID, MEMCANCEL, REVRECVD, REFRECVD, ASSESSAMT,
ASSESSPAID, IDRENEW, PAYYEAR, RENEWDATE, DUESAMT, TRGDUES,
RESTATAMT, DUESPAID, CREDITAMT, CREDITPAID)
SELECT M.ID AS ID, M.STATUS_MEM AS STATUSMEM,
M.RENEW_DTE AS RENEWDTE,M1.ID AS IDINITPAY,
M1.APPL_FEE AS INITFEE, M1.FIRST_DUES AS INITDUES,
M1.PAID1_MEM AS INITPAID, M1.CANCL_DATE AS MEMCANCEL,
M1.REV_RECVD AS REVRECVD, M1.REF_RECVD AS REFRECVD,
M1.REFAS_FEE AS ASSESSAMT, M1.REFAS_PAID AS ASSESSPAID,
M2.ID AS IDRENEW, M2.YEARM2 AS PAYYEAR,
M2.RENEW_DATE AS RENEWDATE, M2. RENEW_DUES AS DUESAMT,
M2.TENREF_FEE AS TRGDUES,
M2.RSTAT_DUE AS RESTATAMT,M2.DATE_PAID AS DUESPAID,
M2.CRDRPT_FEE AS CREDITAMT,M2.CRDRPT_PD AS CREDITPAID
FROM members M
INNER JOIN mempay1 M1
ON (M.ID = M1.ID)
LEFT OUTER JOIN mempay2 M2
ON (M1.ID = M2.ID)
WHERE (( M2.CRDRPT_PD >= '01/01/2000' AND M2.CRDRPT_PD <= '08/31/2000')
OR
( M2.YEARM2 = 2000 AND M2.RENEW_DATE >= '01/01/2000' AND
M2.RENEW_DATE <= '08/31/2000') OR (M2.YEARM2 = 1999 AND
(M2.DATE_PAID IS NULL OR (M2.DATE_PAID >= '01/01/2000' AND M2.DATE_PAID
<= '08/31/2000')))
OR (M2.YEARM2 = 2001 AND M2.DATE_PAID IS NOT NULL AND
M2.DATE_PAID >= '01/01/2000' AND M2.DATE_PAID <= '08/31/2000') )

------------------------------------------------------------------------------
Seuence: 4

SELECT ID,
(SELECT SUM (S2.DUESAMT)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > '08/31/2000')) AND
(((S2.RENEWDATE >= '01/01/2000' AND S2.RENEWDATE <= '08/31/2000')
AND
(S2.DUESPAID >= '01/01/2000' OR S2.DUESPAID IS NULL )) OR
(S2.RENEWDATE < '01/01/2000' AND S2.DUESPAID IS NULL)))) AS
RenewDue,
(SELECT SUM (S2.DUESAMT)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' ) OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > '08/31/2000')) AND
S2.DUESPAID >= '01/01/2000' AND S2.DUESPAID <= '08/31/2000')
AS RenewPaid,
(SELECT SUM (S2.TRGDUES)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' ) AND
S2.DUESPAID >= '01/01/2000' AND S2.DUESPAID <= '08/31/2000')
OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > '08/31/2000' ))
AS TRGPaid,
(SELECT SUM(S2.RESTATAMT)
FROM Sum2 S2
WHERE (S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' ) AND
S2.DUESPAID >= '01/01/2000' AND S2.DUESPAID <= '08/31/2000') AS
RStatFees,
(SELECT COUNT (S2.CREDITPAID)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND
S2.CREDITPAID >= '01/01/2000' AND S2.CREDITPAID <=
'08/31/2000')) AS CreditCnt,
(SELECT SUM (S2.CREDITAMT)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND
S2.CREDITPAID >= '01/01/2000' AND S2.CREDITPAID <=
'08/31/2000')) AS CreditPaid
FROM Sum2 S;

==================================================================================

HI:

Did some testing here by putting code in to write time to stringlist
then at end to file. Any idea what could be going on on the server
during this "missing 2.5 + minutes?

Thanks.

Best
regards

-----------------------------------------------------------------------------

This is log w/o prepare()

Entering Procedure for Testing Summary Queries 1 through 4

Start Clear Tables Sum 1 & Sum 2 :11:34:06 PM
Finish Clear Tables Sum 1 & Sum 2 :11:34:08 PM

Set Up Insert/Join Sum 1 Query:11:34:08 PM
Finish setup and Execute Insert/Join Sum 1 Query:11:34:08 PM
Finish Execute Insert/Join Sum 1 Query:11:34:09 PM

Set Up Count Sum 1 Query:11:34:09 PM
Finish Set Up and Open Count Sum 1 Query:11:34:09 PM
Query Opened update data structure sum 1 Query:11:34:30 PM 21
seconds to open query

Finished updating data structure Count Sum 1 Query-Close the
query:11:34:30 PM
Count Sum 1 Query Closed:11:34:30 PM

2 minutes 44 seconds for what????

Set Up Insert/Join Sum 2 Query:11:37:14 PM
Finish Set Up and Execute Insert/Join Sum 2 Query:11:37:14 PM
Finish Execute Insert/Join Sum 2 Query:11:37:15 PM

Set Up Count Sum 2 Query:11:37:15 PM
Finish Set Up and Open Count Sum 2 Query:11:37:15 PM
Query opened update data structure Sum 2 Query:11:37:20 PM 5
seconds to open query

Finished updating data structure Count Sum 2 Query/close the
queries:11:37:20 PM

Sum Queries 1 & 2 Closed:11:37:28 PM

-------------------------------------------------------------------------------------

This is log w/prepare()

Entering Procedure for Testing Summary Queries 1 through 4

Start Clear Tables Sum 1 & Sum 2 :11:51:21 PM
Finish Clear Tables Sum 1 & Sum 2 :11:51:21 PM

Set Up Insert/Join Sum 1 Query:11:51:21 PM
Finish setup and Execute Insert/Join Sum 1 Query:11:51:21 PM
Finish Execute Insert/Join Sum 1 Query:11:51:22 PM

Set Up Count Sum 1 Query:11:51:22 PM
Finish Set Up and Open Count Sum 1 Query:11:51:22 PM
Query Opened update data structure sum 1 Query:11:51:40 PM 18 seconds
to open uery

Finished updating data structure Count Sum 1 Query-Close the
query:11:51:40 PM
Count Sum 1 Query Closed:11:51:40 PM

2 minutes 33 seconds for what???

Set Up Insert/Join Sum 2 Query:11:54:13 PM
Finish Set Up and Execute Insert/Join Sum 2 Query:11:54:13 PM
Finish Execute Insert/Join Sum 2 Query:11:54:13 PM

Set Up Count Sum 2 Query:11:54:13 PM
Finish Set Up and Open Count Sum 2 Query:11:54:13 PM
Query opened update data structure Sum 2 Query:11:54:18 PM 5
seconds to open query

Finished updating data structure Count Sum 2 Query/close the
queries:11:54:18 PM

Sum Queries 1 & 2 Closed:11:54:26 PM 8 seconds to close queries

-----------------------------------------------------------------------------------


This is block of code which consumes over 2.5 minutes from the "Count
Sum 1 Query Closed:" to the
"Set Up Insert/Join Sum 2 Query:"

IBServer is running 99% cpu for the entire execution of all the queries

Count = Tracker->Count;
DateTime = Time();
Tracker->Insert(Count, "Finished updating data structure Count Sum 1
Query-Close the query:" + TimeToStr(DateTime));

//Close the query and clear SQL
IBReportQuery->Close();

Count = Tracker->Count;
DateTime = Time();
Tracker->Insert(Count, "Count Sum 1 Query Closed:" +
TimeToStr(DateTime));



// IBReportQuery->UnPrepare();
IBReportQuery->SQL->Clear();

Count = Tracker->Count;
DateTime = Time();
Tracker->Insert(Count, "Set Up Insert/Join Sum 2 Query:" +
TimeToStr(DateTime));