Neven
Thanks for that very thorough response, which gives me a much better
understanding of some of the issues.
I think my next step is to test a minimal conversion to Interbase (because
it's there) to assess the speed difference, between Paradox and Interbase,
of a few specific functions across my LAN. Someone has sent me a copy of a
DataPump type utility to convert the database.
Something that I am still not sure of is, how far down this track can I go
with D5 Pro? eg can I use triggers and stored procedures etc or does that
require Enterprise?
Are there any new goodies packaged with D6 that would assist this exercise?
I see there's MYSQL, which would make deployment of Demo copies of the
software much more straightforward than having to use the BDE.
----- Original Message -----
From: "Neven MacEwan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Friday, June 08, 2001 11:27 PM
Subject: Re: [DUG]: Tuning SQL
> 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"
>
---------------------------------------------------------------------------
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"
