Great, Neven
Another couple of pieces of the jigsaw drop into place! Thanks :-}
Re: Interbase
Paradox has been acceptable to my clients, so far ( and it's not M$) - only
2 exceptions (both US companies)
ANY SQL is going to give me speed benefits on my LAN / WAN sites.
I understand that I can distribute Interbase for 6 simultaneous users from
my existing D5 Pro license.
(Someone please tell me if I'm wrong).
I will have no need to scale my app beyond this, and it is a pretty self
contained application, with little need for data sharing with other apps.
Interbase and its data access tools (IBX ) are already distributed with
Delphi.
Unless there are actual *problems* with Interbase, it should prove adequate
for my modest needs (although I am always interested in more experienced
points of view!)
Re: IBX
Can someone tell me what I would get by buying IBO that is not available in
IBX?
I appear to be at the foot of yet another learning curve - buggar!
Mark
----- Original Message -----
From: "Neven MacEwan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Sunday, June 10, 2001 5:45 PM
Subject: Re: [DUG]: Tuning SQL
> 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"
>
---------------------------------------------------------------------------
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"
