Mark
> So, there is no point whatsoever in trying to tune the SQL as long as I'm
using Paradox?
Basically Yes
> 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?
That would depend on several things..
Your Basic ISAM generates traffic like
Open Files -> Fetch Index Page -> Fetch Pages -> Fetch Index..Close Files
For simple single file querys (selecting on an indexed field) this can be
acceptable, The only ineffiency is that the index pages are moved across the
WAN. cf an SQL Server
Submit Query -> Result Set
So you have increased traffic in the process, In my experience, I was using
Dataflex, Btrieve (a server based ISAM) and Scalable SQL
the move from a straight ISAM (Dataflex) to a Server Based ISAM (Btrieve)
resulted in a 80% reduction in traffic. The server based ISAM
is not a lean as an SQL server because it fetches there whole record and not
just the specified columns (which is also why you should never put 'select *
from' in a query - unless you actually need all the fields). For example if
you had a table with a ID char(10) and a Description char(255) columns and
you wanted only the ID then a 'select *' will increase your traffic by
25.5x! with all the redundant Descriptions flying over your WAN!
You can basically estimate the performance over the WAN by calculating the
volume of traffic/ data rate which is
NoRecordsFetched*Sum(FieldSizesFetch)/DataRate
ie if your WAN is 64K = 6.4K Byte/sec, LogName is Char(20) and you fetch
1000 of them then XferTime = 3 seconds
Of course there are other factors (Query Prep Time, Query Size, WAN
Protocol, Packetizing) but there is no reason
a well designed app 2 tier should not perform acceptably on a WAN
> 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).
There are some ineffic with TTables, firstly (and I don't use them so I
don't suggest i'm an expert), check do they generate
a 'Select col1, col2.. ' or a 'Select *' if the later then they are really
turn your SQL server into a server based ISAM and a
5 fold increase is all you'll get 27sec - 120sec
>From the example you have given below you would be using queries anyway,
Tables could be repalced by SELECT * from queries anyway. More importantly
DONT USE FILTERS OR LOCATE, these operations are performed locally and
therefore the advantages are lost, trick it keep your result sets small
(parametiised queries), dont use master detail relationships (as imp by
tdataset) as they are
to anal, and generate zillions of queries (use explicit fetches or I have a
TRelationship Comp that does delayed fetches)
> I understand I would need to change any AutoIncrement fields.
Not necesarily - some DBMS support autoinc fields (PostgreSQL, MS SQL,
Pervasive...)
> Is there an easy way to create an IB database from an existing Paradox one
and transfer the data - D5 Pro (so no Datapump)?
If you are intending to use SQL I'd suggest you look at xcase www.xcase.com
This can port a database to a number of SQL servers via an Access Model ie
import the table into an Access DB and
then use xcase to xfer it to your target (inc IB if you must!). The work is
in transferring the metadata, Moving the
data is trivial (I'll send you a d5 proj that does it if you want)
HTH
Regards Neven
N.K. MacEwan B.E. E&E
Ph 649 574 0027
Fax 649 570 2706
[EMAIL PROTECTED]
"A truth denied the light of action will wither to a promise, propaganda and
then a lied
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"
>
---------------------------------------------------------------------------
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"
