Mark
D5 pro will do everthing you need, Ent only goves you midas and some SQL
tools (you can live withouT)
Midas (ala dbExpress) comes in D6 Pro
> I think my next step is to test a minimal conversion to Interbase (because
> it's there)
As long as this is your olny reason - its a critical decision
> use triggers and stored procedures
These are server side, so unrelated to Pro or Ent
HTH
Neven
----- Original Message -----
From: "Mark Howard" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Sunday, June 10, 2001 12:15 AM
Subject: Re: [DUG]: Tuning SQL
> 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"
>
---------------------------------------------------------------------------
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"
