At 09:27 AM 19/02/2003 +1000, you wrote:

>Helen Borrie wrote:
>
> > The better the info you supply by way of problem description, the better
> > the solution is.
>
>We have 2 threads running, one populating a GUI (TFrame) of saw stations, the
>other of Jig Stations. All the fields in the where clauses are indexed. The
>primary key is an autoincrement field.
>
>function TfraSawStationJobs.GetJobListSql: string;
>begin
> result :=
> 'SELECT DISTINCT JOB_NUMBER FROM SAWMEMBERS'
> + ' WHERE STATUS <= ' + S_COMPLETED
> + ' AND STATION_ID = ' + IntToStr(StationID);
>end;// GetJobListSql
>
>procedure TfraSawStationJobs.ReloadTrusses;
>begin
> if lstJobs.Items.Count > 0 then
> begin
> if DMConnection.GetImportType(lstJobs.Items[lstJobs.ItemIndex]) =
> 'BULK' then
> begin
> lstTrusses.Items.Text := GlobalBulkJob;
> end
> else
> begin
> DBUtils.LoadStrings(DMConnection.DbSuite, lstTrusses.Items,
> 'SELECT DISTINCT TRUSS_NUMBER FROM SAWMEMBERS'
> + ' WHERE STATION_ID = ' + IntToStr(StationID)
> + ' AND JOB_NUMBER = ' + SQLString(JobNumber));
> end;
> end;
>end;// ReloadTrusses
>function TfraJigStationJobs.GetJobListSql: string;
>begin
> result :=
> 'SELECT DISTINCT JOB_NUMBER FROM JIGTRUSSDETAILS' +
> ' WHERE STATION_ID = ' + IntToStr(StationID) +
> ' AND STATUS <= ' + S_COMPLETED;
>end;// GetJobListSql
>
>procedure TfraJigStationJobs.ReloadTrusses;
>var SQL: string;
>begin
> SQL := 'SELECT DISTINCT TRUSS_NUMBER FROM JIGTRUSSDETAILS'
> + ' WHERE STATION_ID = ' + IntToStr(StationID)
> + ' AND JOB_NUMBER = ' + SQLString(JobNumber)
> + ' ORDER BY TRUSS_SORT';
> DbUtils.LoadStrings(DMConnection.DbSuite, lstTrusses.Items, SQL);
>end;// ReloadTrusses
>
>
> > Not clear what you mean by "the internal structure of the
> > tables and indexes".
>
>I'm trying to visualise what the database driver must be doing. I don't
>know how
>the data is laid out on the disk.

Mark,
As a general rule, you don't need to know. However, a too-small page-size,
or one that's not an optimal size for your table structure overall, can
affect performance to some degree...another factor will be where you have
created indexes yourself for key fields - this is not recommended as,
often, it will prevent the optimiser from using the auto-created primary
key or foreign key index.

The performance hit I see is on the client side. Performance with this
will have little or nothing to do with how you have tuned the server
through optimising its configuration (although I'm not saying don't work on
a more optimal configuration....)

It's a matter of the approach you are using in Delphi, and it's off-topic
here. But, as a general guideline, wherever possible, design query specs
that are loaded once and only once in the whole application. What your
methods here tell me is that you are loading the query specs for *every*
query *every* time you want a fresh set of data. This means that your
dataset objects are repeatedly going through an unprepare...prepare cycle -
very costly, because they literally reconstruct themselves - including
every field object - every time.

As a lead-in, study the Delphi help on the subject of parameterised
queries. Here's what you do with the first example here:

Use a TQuery or equivalent (according to the data access comps you are
using) which has this as its SQL property:

SELECT DISTINCT JOB_NUMBER FROM SAWMEMBERS
WHERE STATUS <= :STATUS
AND STATION_ID = :STATIONID

After setting the design-time object active, inspect the Params[]
array. With luck, Delphi will have done the right thing, and you will see
each of these params with the right parameter type (ptInput) and the right
field type (both ftInteger, I presume). If not, then set them up manually,
and they'll stay put.

In your run-time code, use the ParamByName and the AsXXXXX casting methods
to apply the values you want to the parameters. The usual place to do that
is in the BeforeOpen event:

...
with Dataset do
begin
if not Prepared then Prepare; // only happens first time
ParamByName('Status').AsInteger := S_COMPLETED;
ParamByName('StationID').AsInteger := IntToStr(StationID);
end;
...

Once you get the hang of how these parameterised queries work, go back to
those stringlists where you are currently storing static SQL statements,
and replace the SQL statements with parameter names, making sure that
sStringlist[n] is properly mapped to Params[n]. There is an order of
magnitude less client work in that, than in your current ReloadTrusses
procedure, for example.

Can't really go further than this in ib-support, as it's not a Delphi list,
but haven't I seen your name in delphi-db@...?

regards,
heLen