Tim

Whilst trying to sort out something else I've encountered a problem with this.

Firstly

Swap.Database.Session.ProgressTimeInterval := 100;

blows up with an AV (Swap is an EDBQuery). I tried moving it into the OnProgress event itself just to see and it blows up because the session is open - catch 22 I think

Secondly the event is firing a LOT faster than the default of 1000 ms AND its just seesawing between 0 and 100


Roy Lambert

I just tried


Engine.SessionList[Swap.SessionName].ProgressTimeInterval := 100;

and of course got the session open complaint

Roy Lambert

Just for info


for  a tickcount interval of 44641 the onprogress event fired 32078 times

the sql (without substitutions) was


CREATE TABLE "$CallList" AS

SELECT
_CallID,
_fkContacts,
_fkCompanies,
_fkSites,
_Priority,
_LastResult,
RAND (RANGE 999999) AS _Random,
_DateAdded,
_Done,
_IsCandidate,
J._Secretary,

IF(M._MktDesc IS NOT NULL  AND O._Description IS NOT NULL  THEN M._MktDesc +' (' + O._Description + ')'
ELSE M._MktDesc + O._Description) AS _MarketAndType,

IF(C._Name IS NOT NULL THEN  C._Name ELSE   J._UnlistedCompany + ' ??') +
IF(C._Status IS NOT NULL THEN  ' (' + C._Status+')' ELSE '')
AS _CompanyAndStatus,

IF(SG._Description IS NOT NULL  AND SC._Description IS NOT NULL  THEN SG._Description + ': ' + SC._Description
ELSE SG._Description +  SG._Description) AS _SIC,

IF(J._MainPhone IS NOT NULL  THEN J._MainPhone
ELSE  Site._Switchboard) AS _MainPhone,

IF(T._Turnover IS NOT NULL  AND S._Staff IS NOT NULL  THEN T._Turnover +', '+S._Staff+' staff'
ELSE
IF(T._Turnover IS NOT NULL  THEN T._Turnover
ELSE
IF(S._Staff IS NOT NULL  THEN S._Staff+' staff' ELSE ''))) AS _Size,

W._Title+' '+  W._Forename+' '+W._Surname AS _FullName,

IF(W._Status IS NOT NULL  AND J._JobTitle IS NOT NULL  THEN J._JobTitle + ' (' + W._Status+')'
ELSE J._JobTitle + W._Status) As _JobAndStatus,

W._Forename,
W._Surname,
W._HomePhone,
W._Mobile,
W._HomeEMail,
W._Status,
IF(W._LatestCV IS NOT NULL THEN TRUE ELSE FALSE) AS _HasCV,
J._EMail,
J._fkJobCodes,
IF(J._Left IS NOT NULL THEN TRUE ELSE FALSE) AS _StillEmployed,
IF (C._Name IS NOT NULL THEN  C._Name ELSE   J._UnlistedCompany + ' ??') AS _CompanyName,

Site._Town,
Site._County,
Site._Country,
('Site: '+
Site._SiteName +
IF(Site._Town IS NOT NULL THEN ' - ' + Site._Town ELSE '') +
IF(Site._County IS NOT NULL THEN ', ' + Site._County ELSE '') +
IF(Site._Country IS NOT NULL THEN ', ' + Site._Country ELSE '')
)AS _Location,

C._fkMarkets,
C._fkOrgType,
Site._Switchboard,
S._Staff AS _Staffing,
T._Turnover AS _Turnover,
C._fkSICGroup,
C._fkSICCode,
C._Status AS _CompanyStatus,
C._Website,
IF(P._Name IS NOT NULL THEN 'Parent: '+ P._Name ELSE '') AS _Parent

FROM "$From".Calls Calls
LEFT OUTER JOIN "$From".Companies C ON Calls._fkCompanies = C._ID
LEFT OUTER JOIN "$From".Contacts W ON Calls._fkContacts = W._ID
LEFT OUTER JOIN "$From".Career J ON Calls._fkCareer = J._ID
LEFT OUTER JOIN "$From".SandT S ON C._fkSandT_Staffing = S._Band
LEFT OUTER JOIN "$From".SandT T ON C._fkSandT_Turnover = T._Band
LEFT OUTER JOIN "$From".Markets M ON C._fkMarkets = M._ID
LEFT OUTER JOIN "$From".OrgType O ON C._fkOrgType = O._ID
LEFT OUTER JOIN "$From".SICGroup SG ON C._fkSICGroup = SG._ID
LEFT OUTER JOIN "$From".SICCode SC ON C._fkSICCode = SC._ID
LEFT OUTER JOIN "$From".Companies P ON C._fkCompanies_Parent = P._ID
LEFT OUTER JOIN "$From".Sites Site ON  _fkCompanies = Site._fkCompanies AND _fkSites = Site._SiteID
WHERE
_fkProjects = :ProjectID
AND
(_SleepUntil IS NULL OR _SleepUntil <= CURRENT_DATE)
AND
(C._Name IS NOT NULL OR W._Surname IS NOT NULL)

WITH DATA


Roy Lambert

Roy,

<< the sql (without substitutions) was >>

Again, do I have these tables ?  If not, please email me the database
catalog and tables.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Table and catalog will be sent - c25Mb

For educational purposes why are they needed for something like this?

Roy Lambert

Roy,

<< For educational purposes why are they needed for something like this? >>

Because I need to use the actual data in order to replicate the the same
behavior.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Because I need to use the actual data in order to replicate the the same
>behavior.

But surely this behaviour can't be data dependant - can it?

Roy Lambert

Roy,

<<  But surely this behaviour can't be data dependant - can it? >>

I have no idea what the cause is until I run it, and I can't run it without
the tables.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

Okay, the issue has been fixed.  It was a problem with the CREATE TABLE AS
progress updating.  Also, just so you know, you can't use parameters in a
CREATE TABLE AS statement, and the next build will make sure of this by
raising an error if you try.   Right now the parameter is just ignored.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Also, just so you know, you can't use parameters in a
>CREATE TABLE AS statement, and the next build will make sure of this by
>raising an error if you try. Right now the parameter is just ignored.

I'd already given up on that as a bad job anyway since I'm passing the sql code across to a little unit which executes it doing the nice progress display.

Roy Lambert