I can't find the other thread about this so I'm starting a new one

I have a query

INSERT INTO EMSearchResults
SELECT 79, _MsgNo
FROM EMails
WHERE
AttachSearch(_aList, _Encoded, ' CONTAINS ', 'chief operating officer', 'Y')

for which the progress event fires 3 times. Once at the start and twice at what I presume is the end. I thought it might be the fact that its an INSERT with a subselect so I tried out just the SELECT with the same result.

AttachSearch is an external function of my own which is checking email attachments (.docs only) for contained words so I knew it would require a row scan. It looks as though the progress event isn't firing at all during the row scan so there is no indication whatsoever of anything happening.

Without knowing just what your code does, or the logic you're using for firing a progress event I don't know if this is true of any row scan operation or just in these cases where an external function is called or even if your assumption was that a row scan should be fast enough to ignore in comparison to the other activity going on.

The problem I have is that the progress event isn't giving progress information in the case I need it most ie with a long query. Is there anything you can do or is my only option an "oh look somethings moving display" like the old Windows copy AVI - in which case maybe you ought to supply a revolving ElevateDB  avi for us <vbg>

================================================================================
SQL Query (Executed by ElevateDB 2.02 Build 11)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL 79 AS "Expression", "_MsgNo" AS "_MsgNo" FROM "EMails" WHERE
AttachSearch("_aList", "_Encoded", ' CONTAINS ', 'chief operating officer', 'Y') = TRUE

Source Tables
-------------

EMails: 11063 rows

Result Set
----------

The result set was sensitive
The result set consisted of zero or more rows
The result set was ordered using the index PK

Filtering
---------

The following filter condition was applied to the Query table:

AttachSearch("_aList", "_Encoded", ' CONTAINS ', 'chief operating officer', 'Y')
= TRUE [Row scan (EMails): 11063 rows, 7257328 bytes estimated cost]

================================================================================
54 row(s) returned in 63.102 secs
================================================================================



Roy Lambert

ps

I LOVE this line

The result set consisted of zero or more rows

What do we do if it ever says "zero or less rows"?

Quick update  - I can't even use an animation to demonstrate that things are still live. When the query gets underway it fully occupies one of the cores of my PC - all timers and animations in my app freeze. Because of the other core I can still use the PC but its not very good for a user.

Roy Lambert

Roy,

<< AttachSearch is an external function of my own which is checking email
attachments (.docs only) for contained words so I knew it would require a
row scan. It looks as though the progress event isn't firing at all during
the row scan so there is no indication whatsoever of anything happening.

Without knowing just what your code does, or the logic you're using for
firing a progress event I don't know if this is true of any row scan
operation or just in these cases where an external function is called or
even if your assumption was that a row scan should be fast enough to ignore
in comparison to the other activity going on. >>

INSERT queries do not fire progress for the sub-select query at all.  Again,
as noted in another post on this subject - the amount of effort to come up
with, in advance, a "total" for the work that needs to be done in such cases
is very difficult, if not impossible.  Hence we stick to only calculating
progress for those actions that we can determine a valid "total" amount of
work in advance.  I'll be seeing about trying to improve upon this at some
point, but there may still be situations where it is impossible to calculate
the progress accurately.

<< I LOVE this line

The result set consisted of zero or more rows

What do we do if it ever says "zero or less rows"? >>

It is as opposed to "a single row", which is what a single-row result set
consists of.   The query optimizer is telling you what the result set should
look like, in terms of its composition.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>INSERT queries do not fire progress for the sub-select query at all. Again,
>as noted in another post on this subject

Just read it - and it explains why I couldn't spot it to add my post as a reply - I was only looking in the ElevateDB section

>- the amount of effort to come up
>with, in advance, a "total" for the work that needs to be done in such cases
>is very difficult, if not impossible. Hence we stick to only calculating
>progress for those actions that we can determine a valid "total" amount of
>work in advance. I'll be seeing about trying to improve upon this at some
>point, but there may still be situations where it is impossible to calculate
>the progress accurately.

The app "freezing" for, in this case, about 15 secs is not particularly palatable. I'm wary of stuffing it into a background thread because the only safe way I know of terminating such is to wait for the OnProgress event to fire and set Continue to False (if you know another way I'll be happy to hear it).

I appreciate your difficulties but I'd still like some solution. Here's a few ideas - I hope one of them will be workable and acceptable:

1. fire the on progress event at the timed interval anyway just don't update the progress (I guess it will mean it will be 0). If you do this I'm pretty certain you'll get some complaints about progress not incrementing.

2. Have a ProgressPhase in the OnProgress event, fire the OnProgress event as above but the ProgressPhase goes from 0 to whatever (eg unable to work out progress or something)

3. Have a ProgressEventsCounter, again fire the progress event at the timer interval - Progress may stay at 0 but ProgressEventsCounter would tick on. That allows us developers to do something.

4. A separate ProgressPhase event - fires on starting the query, when the query moves from selecting (ie can be killed) to writing (can't be killed) and when the progress event kicks in

My favourite would be number 3, especially if setting Continue to False would bomb the query even before the progress starts incrementing.

><< I LOVE this line
>
>The result set consisted of zero or more rows
>
>What do we do if it ever says "zero or less rows"? >>
>
>It is as opposed to "a single row", which is what a single-row result set
>consists of. The query optimizer is telling you what the result set should
>look like, in terms of its composition.

There are days I despair of your sense of humour. However, to treat your reply totally seriously as you did the post - a single row result set would appear to my mathematical training to be incorporated within zero or more rows, unless this is another area where the infamous SQL has rewritten the laws of mathematics.

Roy Lambert

Roy,


<< The app "freezing" for, in this case, about 15 secs is not particularly
palatable. I'm wary of stuffing it into a background thread because the only
safe way I know of terminating such is to wait for the OnProgress event to
fire and set Continue to False (if you know another way I'll be happy to
hear it).

I appreciate your difficulties but I'd still like some solution. Here's a
few ideas - I hope one of them will be workable and acceptable: >>

I'll see what I can do, but I've explained the reasoning behind the
situation.

<< There are days I despair of your sense of humour. However, to treat your
reply totally seriously as you did the post - a single row result set would
appear to my mathematical training to be incorporated within zero or more
rows, unless this is another area where the infamous SQL has rewritten the
laws of mathematics. >>

My reply was completely serious, so I'm not quite sure why you feel the need
to be a jerk about it.  A single-row result set is a special thing in SQL,
and it is not necessarily obvious to the casual eye that an SQL statement
will always, and only, generate a single row.  *That* is what the query
execution plan is telling the developer or user.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'll see what I can do, but I've explained the reasoning behind the
>situation.

That's why No. 3 was my favourite option - it gets me what I need without compromising your position.

Roy Lambert

Roy,

<< That's why No. 3 was my favourite option - it gets me what I need without
compromising your position. >>

The problem is that EDB was designed in such a compartmentalized fashion
that it's very difficult in certain cases to get one area to "talk" to
another area without causing bad dependencies in the code.  Filter progress
updates are one of these areas, and the row scan in question is using a
filter in the sub-query to filter out the rows.  The progress updating is
also reference-counted to ensure that only one subsystem is issuing progress
at any given time.  This is why I need to take my time with this and figure
out the a decent solution.   Just popping in a ShowProgress(0) in the code
won't work.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>My reply was completely serious, so I'm not quite sure why you feel the need
>to be a jerk about it. A single-row result set is a special thing in SQL,
>and it is not necessarily obvious to the casual eye that an SQL statement
>will always, and only, generate a single row. *That* is what the query
>execution plan is telling the developer or user.

This latest explanation does clarify the difference you're seeking to illustrate. I don't know if your terminology:

The result set consisted of zero or more rows

and

The result set consisted of one row

is mainstream sql language or not but without the additional explanation you've given above I, reading the execution plan would have been totally in the dark as to the meaning. I now appreciate "The result set consisted of zero or more rows" means this is a bog standard type of result set and "The result set consisted of one row" means this is some sort of special case rather than simply commenting on the number of rows produced.


Roy Lambert

Tim


>The problem is that EDB was designed in such a compartmentalized fashion
>that it's very difficult in certain cases to get one area to "talk" to
>another area without causing bad dependencies in the code. Filter progress
>updates are one of these areas, and the row scan in question is using a
>filter in the sub-query to filter out the rows. The progress updating is
>also reference-counted to ensure that only one subsystem is issuing progress
>at any given time. This is why I need to take my time with this and figure
>out the a decent solution. Just popping in a ShowProgress(0) in the code
>won't work.

Damn & blast - but I know where you're coming from - fortunately in this specific case I was able to come up with a kludgy solution - background thread do an insensitive select and then use tables to do the inserts looping around the selected query. That way I can get a check for Terminated in between each post and I don't care if Windows terminates the query part way through.

Roy Lambert

Roy,

<< is mainstream sql language or not but without the additional explanation
you've given above I, reading the execution plan would have been totally in
the dark as to the meaning. I now appreciate "The result set consisted of
zero or more rows" means this is a bog standard type of result set and "The
result set consisted of one row" means this is some sort of special case
rather than simply commenting on the number of rows produced. >>

Yes, and in fact, this part of the plan is generated *before* the result set
row count is known.  It's the query optimizer saying "Hey, this result set
should only have one row" vs. "Hey, this result set may generate any number
of rows".

--
Tim Young
Elevate Software
www.elevatesoft.com