Can we please have a query is alive and doing something indicator. The progress bar is fine in most cases but with a looong query (1803 secs) churning through c10k rows and returning 900 rows it just doesn't work. Or at least it doesn't when there's an external function checking on the validity of companies web sites for me.

Roy Lambert

Roy,

<< Can we please have a query is alive and doing something indicator. The
progress bar is fine in most cases but with a looong query (1803 secs)
churning through c10k rows and returning 900 rows it just doesn't work. Or
at least it doesn't when there's an external function checking on the
validity of companies web sites for me. >>

If the performance of the external module is such that it prevents EDB from
checking the progress interval regularly, then there isn't much I can do to
fix that.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

<<If the performance of the external module is such that it prevents EDB from
checking the progress interval regularly, then there isn't much I can do to
fix that.>>


I was thinking about the twirly globe thingy that IE used to have but I just realised that EDBManager runs the queries in the foreground so a timer based twirly globe would just sit there staring blankly at me

Its obviously time for me to receive more education The query is

select * from companies where _website is not null and validateurl(_website)

and the execution plan shows

The following filter condition was applied to the companies table:

validateurl("_website") = TRUE AND "_website" IS NOT NULL

Row scan (Companies): 7122 rows, 3361584 bytes estimated cost

Firstly I don't understand why ElevateDB has reversed it but that's its affair secondly I naively thought "Row scan" meant it went down row by row and you would be updating progress in between rows. Obviously I'm wrong.

Is there anything I can do to allow progress to be shown? I've posted the function below so you can see what it does. The problem isn't intermittent or slow progress reporting but none at all so that for all I know EDBManager is dead in the water. This sort of thing is so slow anyway that I'm happy for it to slow down a bit more to get a progress report.

I don't know about anyone else but most of the queries I do in EDBManager are either so quick I don't get a chance to see the progress bar or take a long time and the progress bar doesn't do anything. There are a few that take c15 secs for which its nice.


Roy Lambert

function ValidateURL(const URL: string): boolean;
var
grab: THTTPSend;
sl: TStringList;
Cntr: integer;
Location: string;
AddSlash: string;
begin
if URL = '' then begin
 Result := False;
 Exit;
end;
sl := TStringList.Create;
grab := THTTPSend.Create;
grab.Timeout := 600;
sl.Text := URL;
Result := True;
try
 for Cntr := 0 to sl.Count - 1 do begin
  if IsURL(sl[Cntr]) then begin
   if grab.HTTPMethod('GET', sl[Cntr]) then begin
    if (grab.ResultCode div 100) <> 2 then begin
     if grab.ResultCode = 302 then begin
      Location := Copy(grab.Headers.Text, Pos('location:', LowerCase(grab.Headers.Text)) + 9, MaxInt);
      Location := LowerCase(Copy(Location, 1, Pos(#13, Location) - 1));
      if 0 <> Pos('?', Location) then begin
       if URL[Length(URL)] = '/' then AddSlash := '/' else AddSlash := '';
       if LowerCase(URL) = Copy(Location, Pos('?url=', Location) + 5, MaxInt) + AddSlash then begin
        Result := False;
        Break;
       end;
      end;
      // have to assume its OK cos I can't do much else
     end else begin
      Result := False;
      Break;
     end;
    end;
    grab.Clear;
   end else begin
    Result := False;
    Break;
   end;
  end else begin
   Result := False;
   Break;
  end;
 end;
finally
 grab.Free;
 sl.Free;
end;
end;

Roy,

<< Its obviously time for me to receive more education The query is

select * from companies where _website is not null and
validateurl(_website)

and the execution plan shows

The following filter condition was applied to the companies table:

validateurl("_website") = TRUE AND "_website" IS NOT NULL

Row scan (Companies): 7122 rows, 3361584 bytes estimated cost

Firstly I don't understand why ElevateDB has reversed it but that's its
affair secondly I naively thought "Row scan" meant it went down row by row
and you would be updating progress in between rows. Obviously I'm wrong. >>

Does the query return a live result set ?  If so, then that's the reason for
the lack of progress.  Live query result sets are like filters right now,
they don't show progress.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Does the query return a live result set ? If so, then that's the reason for
>the lack of progress. Live query result sets are like filters right now,
>they don't show progress.

It did, but I've just tried it canned, and nothing after 10 mins when I bombed it.

Roy Lambert

Roy,

<< It did, but I've just tried it canned, and nothing after 10 mins when I
bombed it. >>

Sorry - same issue.  It's the WHERE clause specifically, un-optimized row
scans do not show progress, only the generation of the result set show
progress.  Imagine a WHERE clause with un-optimized conditions on 4
different tables - should EDB show a 0-100% for each row scan, and then
0-100% for the result set generation with JOINs, etc. ?  The whole point of
not showing progress in some cases is to not have a situation where the
progress shows the user "Done", "Wait, not done yet", "Okay, done", "Oops,
not done quite yet".....

The problem is really trying to project out the "total amount of work" ahead
of time and scale the progress accordingly.  This is difficult to do when
you combine all of the various aspects of optimization level, different
tables, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Sorry - same issue. It's the WHERE clause specifically, un-optimized row
>scans do not show progress, only the generation of the result set show
>progress. Imagine a WHERE clause with un-optimized conditions on 4
>different tables - should EDB show a 0-100% for each row scan, and then
>0-100% for the result set generation with JOINs, etc. ? The whole point of
>not showing progress in some cases is to not have a situation where the
>progress shows the user "Done", "Wait, not done yet", "Okay, done", "Oops,
>not done quite yet".....

At least that gave me a chuckle to start the weekend with

>The problem is really trying to project out the "total amount of work" ahead
>of time and scale the progress accordingly. This is difficult to do when
>you combine all of the various aspects of optimization level, different
>tables, etc.

I know that's why, until I realised it would just freeze, I was thinking just some sort of I'm alive indicator driven by a TTimer.

The only thing I can think off is to bung the query execution into a thread which would at least mean an I'm alive indicator could be made to work, but I have a sneaky suspicion that the cost benefit equation would be heavily skewed on the cost side.

Roy Lambert