> 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)
So, there is no point whatsoever in trying
to tune the SQL as long as I'm using Paradox?
>
> 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
Neven, what exactly do you mean here? Say I port to IB, what speed should I expect (given the above times) just from the change of Database?
>
> 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
Neven, what exactly do you mean here? Say I port to IB, what speed should I expect (given the above times) just from the change of Database?
If I wanted to do a "minimal change" port to IB,
do I understand correctly that I am not compelled to replace my TTables with
TQuery's (at least initially).
I understand I would need to change any
AutoIncrement fields.
Is there an easy way to create an IB database
from an existing Paradox one and transfer the data - D5 Pro (so no
Datapump)?
Anyone know of any good resources devoted to
this question of moving from Paradox to IB (or similar)?
Neven, thanks for sharing your DB
expertise.
Mark
> ----- 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"
>
> 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"
>
