Mark
Paradox is an ISAM with a 'local' SQL like engine and you are really seeing
how bandwidth intesive
this type of DB is
600 secs to 27 (22 times) is not bad considering you are going from 10Mb to
? (it would indicate 256Kb)
The problem is no matter how hard you tune it you are still pulling all the
index pages over the WAN (an SQL
'Server' would only return the result set, also you are fetching the whole
record (even though your select statement
only refernces one field)
two options
1/ Port your app to ProstgreSQL (or MS SQL, or IB et al) Will give you
comparable performance on a single query
the only latency it that which is introduced by the data transferred over
the LAN
2/ Multitier it
I'd do 1 first, then look to 2
HTH
Neven
----- Original Message -----
From: Mark Howard <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Sent: Friday, 8 June 2001 17:02
Subject: [DUG]: Tuning SQL
Hi
Re: Paradox SQL
I have a user that is using my app over a WAN, with (not unexpected)
performance problems and I am attempting to relieve the worst of these as an
interim solution. The main problem is the speed of reporting (TQuery
through QuickReports).
The reports are virually instantaneous on a local machine, take about 27
seconds on my LAN and over 10 mins on the WAN.
I had started with some pretty lazy SQL select statements and have pared
those down so they return just the basics, I've used the DBD on the remote
machine to add indexes on all fields that are joined or sorted, removed all
"where field like <user input>" statements.
With all these changes I have not been able to detect any change in
performance. Am I missing something?
Is there a way that I can restructure the following example to be more
efficient?
I thank you in advance for any suggestions.
Mark
SQL.Clear;
SetTime;
SQL.Add('Select l.LogName,');
SQL.Add('f.ForName, s.SpeciesName,');
SQL.Add('d.Comp, d.Stand, d.Grade, d.LogLength,');
SQL.Add('d.TruckLoad PayLoad,');
SQL.Add('d.LogPrice PayPrice,');
SQL.Add('d.TruckUnit PayUnit,');
SQL.Add('d.GangCode, d.DockType, d.DocketDate, d.DocketNo');
SQL.Add('from Dockets d, Forest f, Logger l, Species s');
SQL.Add('where d.LogCode = l.LogCode');
SQL.Add('and d.ForCode = f.ForCode');
SQL.Add('and d.SpeciesCode = s.SpeciesCode');
if not (SelectStr1 = '%') then ;
SQL.Add('and (LogCode like "' + SelectStr1 + '")');
if not (SelectStr2 = '%') then ;
SQL.Add('and (ForCode like "' + SelectStr2 + '")');
if not (SelectStr3 = '%') then ;
SQL.Add('and (Comp like "' + SelectStr3 + '")');
if not (SelectStr4 = '%') then ;
SQL.Add('and (SpeciesCode like "' + SelectStr4 + '")');
SQL.Add('and DocketDate between "' + Date1 + '" and "' + Date2 +
'"');
if TypeMode = 'Detail' then
begin
SQL.Add('Order by LogName, DockType desc, ForName, Comp,
Stand,');
SQL.Add('DocketDate, DocketNo');
Open;
ShowTime;
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
