i have the folowing query SQL (yes i know it's long)

SELECT DISTINCT
if ((((CURRENT_TIMESTAMP - HARDWARE_LOG_TIME) - REP_TIME_DIFF_MILI) /86400000  >= 5 ) then 'Old'
else if (SHUTDOWN then 'Off'
else if (((((HARDWARE_LOG_TIME + REP_TIME_DIFF_MILI) > REP_SYNC_START)) or
  (((ABS(REP_SYNC_START - (HARDWARE_LOG_TIME + REP_TIME_DIFF_MILI)) / 60000 ) < POLL_TIME * 7))) then
if (IDLE then 'UnAttended' else 'On')   else 'CutOff' ))) as "State" ,
Computer_id as "id",
COMPUTERS.REP_ID as "RepID",
[......some more fields like this....... ],
COMPUTER_NAME as "Computer",
LOOKUP_DOMAIN.VALUE as "Domain",
SHUTDOWN as "Shutdown"
FROM  
  COMPUTERS
JOIN COMPUTER_HARDWARE ON (COMPUTER_ID=ID)
JOIN REPOSITORIES ON (REPOSITORIES.REP_ID=COMPUTERS.REP_ID)
LEFT OUTER JOIN COMPUTER_CUSTOM ON (ID=COMPUTER_ID)
LEFT OUTER JOIN LOOKUP_OS ON (OS_ID=LOOKUP_OS.ID)
LEFT OUTER JOIN LOOKUP_UPGRADE ON (UPGRADE_ID=LOOKUP_UPGRADE.ID)
LEFT OUTER JOIN LOOKUP_PC_MAKER ON (PC_MAKER_ID=LOOKUP_PC_MAKER.ID)
LEFT OUTER JOIN LOOKUP_DOMAIN ON (DOMAIN_ID=LOOKUP_DOMAIN.ID)
WHERE (REP_SEL = TRUE) AND (REP_SHOWING = TRUE)

The problem is, onquery progress occures 3 times though it is defined as
DBISAMDatabase1.Session.ProgressSteps    := 100;

The query takes some 7-10 seconds,
An onProgress event is defined and running, but i only get: 33%, 66% and 100% progress events.

This naturally freazes the main application thread,
and i must "process messages" more often.
is there anything i do wrong? or any ideas as to how to free the application during the query?

Amir,

<< This naturally freazes the main application thread,
and i must "process messages" more often.
is there anything i do wrong? or any ideas as to how to free the
application during the query? >>

How many rows are generated in the result set ?  The number of rows
determines how often the progress event is fired.  With some queries, the
query execution may spend a lot of time in areas of execution that don't
involve progress updates, such as filtering source tables or navigating
through rows via the joins trying to find the next candidate row for the
result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:
<<
How many rows are generated in the result set ?  The number of rows
determines how often the progress event is fired.  With some queries, the
query execution may spend a lot of time in areas of execution that don't
involve progress updates, such as filtering source tables or navigating
through rows via the joins trying to find the next candidate row for the
result set.
>>

The dataset ends up with more than 2600 lines.
i have tried using all other progress events, but with no better results.

i also thought of performing the query in another thread, however this brings up a very complex issue of synchronizing the query and a VCL grid
component depending on it, across the main thread and a second thread.
if you have any idea as to how this can be reasonably achieved i would be more than greatful
for the record, i'm using a dev-express grid to show a query dataset on screen (need to refresh the data occasionally when certain events occure).

Thanks
Amir

Amir,

<< The dataset ends up with more than 2600 lines. i have tried using all
other progress events, but with no better results. >>

Could you possibly send me the involved tables via email ?  I'm not sure why
you're only getting 3 progress events, but running the query here with the
tables should shed some light on the situation.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:
Could you possibly send me the involved tables via email ?  I'm not sure why
you're only getting 3 progress events, but running the query here with the
tables should shed some light on the situation.

attached.
the sql shoul be

SELECT DISTINCT
if ((((CURRENT_TIMESTAMP - HARDWARE_LOG_TIME) - REP_TIME_DIFF_MILI) /86400000  >= 5 ) then 'Old'
else if (SHUTDOWN then 'Off'
else if (((((HARDWARE_LOG_TIME + REP_TIME_DIFF_MILI) > REP_SYNC_START)) or
 (((ABS(REP_SYNC_START - (HARDWARE_LOG_TIME + REP_TIME_DIFF_MILI)) / 60000 ) < POLL_TIME * 7))) then
if (IDLE then 'UnAttended' else 'On')   else 'CutOff' ))) as "State" ,
Computer_id as "id",
COMPUTERS.REP_ID as "RepID",
/* [......some more fields like this....... ],*/
COMPUTER_NAME as "Computer",
LOOKUP_DOMAIN.VALUE as "Domain",
SHUTDOWN as "Shutdown"
FROM  
 COMPUTERS
JOIN COMPUTER_HARDWARE ON (COMPUTER_ID=ID)
JOIN REPOSITORIES ON (REPOSITORIES.REP_ID=COMPUTERS.REP_ID)
LEFT OUTER JOIN COMPUTER_CUSTOM ON (ID=COMPUTER_ID)
LEFT OUTER JOIN LOOKUP_OS ON (OS_ID=LOOKUP_OS.ID)
LEFT OUTER JOIN LOOKUP_UPGRADE ON (UPGRADE_ID=LOOKUP_UPGRADE.ID)
LEFT OUTER JOIN LOOKUP_PC_MAKER ON (PC_MAKER_ID=LOOKUP_PC_MAKER.ID)
LEFT OUTER JOIN LOOKUP_DOMAIN ON (DOMAIN_ID=LOOKUP_DOMAIN.ID)
WHERE (REP_SEL = TRUE) AND (REP_SHOWING = TRUE)

Amir Netiv wrote:

"Tim Young [Elevate Software]" wrote:
Could you possibly send me the involved tables via email ?  
[...] running the query here with the tables should shed some light on the situation.

Any news?
Thanks

Amir


In case Tim doesn't make the point - Please don't post large attachments in the standard newsgroups. Either post them in the binaries news group, or as Tim asked email them to him, or as several people do load to a web address and send that to Tim.

Roy Lambert [Team Elevate]

Amir,

<< Any news? >>

I was expecting you to email the tables to me.  As Roy indicated, please
don't post attachments in the normal newsgroups - email them to me or post
them in the Binaries newsgroup.  I have to manually remove these types of
attachments so that they don't bog down other users that are using
newsreaders that are forced to load the entire attachment in order to read
the message.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:
<<
As Roy indicated, please don't post attachments in the normal newsgroups - email them to me or post
them in the Binaries newsgroup.  
>>

Sorry for this,
I was unaware of the guidlines, will do so next time (hope i dont need to)

Please let me know if you want me to send it again (directly), or if you saved a copy before deleting it.
I still dont understand why/how releaseing the main thread is only occuring 3 times.

Thanks
Amir

Amir,

<< Please let me know if you want me to send it again (directly), or if you
saved a copy before deleting it. I still dont understand why/how releaseing
the main thread is only occuring 3 times. >>

Sorry about the delay in getting back to you on this issue.  I looked at the
query, and the reason for only 3 progress updates is the way DBISAM issues
progress in the queries - in the case of a joined query, DBISAM will only
issue a progress update when the record for the driver table (first table in
the FROM clause) changes.  In the case of your query, the INNER JOINs cause
only 3 unique records to be visited in the driver table, so only 3 updates
occur.  The reason for this is that it is impossible for DBISAM to calculate
the realized total count of records for certain joined queries due to the
way the records are filtered, therefore it has to use something with which
it can calculate a known percentage for the progress updates.  The driver
table fits this bill.

--
Tim Young
Elevate Software
www.elevatesoft.com