|
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; |
BEGIN:VCARD VERSION:2.1 N:Howard;Mark FN:Mark Howard ORG:Forest Production Systems Ltd TEL;WORK;VOICE:(07) 348 8177 TEL;CELL;VOICE:(021) 477564 TEL;WORK;FAX:(07) 348 8178 ADR;WORK:;;P.O.Box 5155;Rotorua LABEL;WORK;ENCODING=QUOTED-PRINTABLE:P.O.Box 5155=0D=0ARotorua EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20010608T050232Z END:VCARD
