Yeah, Thanks James,
I had just found it and was in the process of reading it when your post came
through.
Looks like you can try it first, and pay later. Pretty cool!
Mark
----- Original Message -----
From: "James Low" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Monday, June 11, 2001 10:04 AM
Subject: RE: [DUG]: Tuning SQL
> Your biggest advantage will be a painless migration and pretty good
support.
> Check out http://www.ibobjects.com/ - theres a speel on all the advantages
> and I am sure its completely objective :).
>
> -----Original Message-----
> From: Mark Howard [mailto:[EMAIL PROTECTED]]
> Sent: 11 June 2001 09:27
> To: Multiple recipients of list delphi
> Subject: Re: [DUG]: Tuning SQL
>
>
> 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"
> --------------------------------------------------------------------------
-
> 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"
