Re: Contents of file changed while application is reading it

Subject	Re: Contents of file changed while application is reading it
Newsgroups	comp.os.linux.misc
References	(12 earlier) <2GqdnVP81dcMgZ35nZ2dnZfqnPednZ2d@earthlink.com> <ttpr01$279gm$50@dont-email.me> <dYudnfNen9mG55z5nZ2dnZfqn_ednZ2d@earthlink.com> <ttsejp$279gm$64@dont-email.me> <op.108p94wxa3w0dxdave@hodgins.homeip.net>
From	"28A.I873" <28A.I873@noabzba.net>
Organization	mitotic resell
Date	2023-03-04 01:36 -0500
Message-ID	<CYqcncOUTcOWep_5nZ2dnZfqn_SdnZ2d@earthlink.com> (permalink)

Show all headers | View raw

On 3/3/23 12:16 PM, David W. Hodgins wrote:
> On Fri, 03 Mar 2023 04:28:24 -0500, The Natural Philosopher 
> <tnp@invalid.invalid> wrote:
>> Exactly so.  What I discovered is that building a database that is fully
>> normalised is not hard, and it works really well, but one must eschew
>> complex SQL queries on performance grounds.at a
>>
>> Conceptually use of a high level language to do a 'for each in this
>> table, select from that table' worked far better than some sort of
>> complex join.
> 
> When designing a database, full normalization is one step. Once you have
> the normalized design, you have to look at real life contents, and how 
> it will
> be accessed. Then denormalize parts where that will improve performance.


   What ? DE-NORM ??? HERESY !!!!!!!  :-)

   Alas the Real World is MESSY MESSY MESSY and user
   data will always include "non-normal" stuff.

   Only solution is to add a bit more IQ to yer code
   to recognize/deal.




> For example, in an employee benefits system I worked on one small part of
> it required knowing the ages of their dependent children, so it stored
> the birth dates of those children. Examining how the data was used showed
> it did not matter how many children they had if it was 4 or more.
> 
> That data that doesn't change often, and in the real data, none of the
> employees had more than 4 children under the age of 18, so instead of
> having a separate table for the children, 4 fields were used to store
> the birth dates of the youngest children, up to a max of 4 children.
> It eliminated one table and a join when accessing the database.
> 
> It's not accessed often, and changed even less often, and is low volume
> per record. Having that data de-normalized is better for that application.
> 
> The above applies no matter how the data is physically stored. The data can
> be in a relational database (i.e. sql tables), a hierarchical database, 
> such
> as IMS, or in flat files.
> 
> Two more steps come into play when dealing with sql tables and programming.
> The "prepare" step and the "commit" statement both can have major impacts.
> 
> When the prepare step is processed, the database engine will select how to
> access the data. It examines what indexes are available, how many rows 
> there
> are in the table, how "unique" the index entries are, etc.
> 
> With compiled programs, the prepare step is normally processed in a 
> pre-processor
> at compile time.
> 
> It's critical to ensure the data in the tables when the prepare 
> statement is
> processed, is similar to what it will be at execution time.
> 
> If the prepare step is processed when the tables are empty, then sql will
> correctly ignore the presence of any index and do a full table scan for 
> each
> select statement, as it's less i/o. If at execution time those tables have
> lot's of data, it will still do a full table scan for each select. The
> prepare statement is not automatically re-evaluated.
> 
> One case I heard about from a friend. An application was started on 
> Friday evening,
> expecting to run for about 6 hours. On Monday the application was 
> terminated as
> it was preventing the online part of the system from working. Three days 
> later,
> it actually finished.
> 
> There were two problems. First the tables were all empty when the 
> prepare statements
> had been processed, resulting in every select doing full table scans for 
> every table
> joined in the select statements. Second, the program relied on an 
> automatic commit
> that's done when a program terminates successfully.


   SQL kinda *has* to take the worst-cases into account.
   Alas that means that the MOST COMMON, like 99.99%, of
   cases get the hyper-anal treatment whether they need
   it or not. That means SLOW, CLUNKY.

   SQL has a *rationale* for existing - but, in real life,
   it's probably best to go AROUND it as much as possible.

   I just don't do SQL anymore beyond the initial SELECT's
   to narrow things down. After that, custom-process.

   Yea, kinda "defeats the purpose", but .....

   IMHO, SQL should just go away. An 80s solution that
   never QUITE cuts it. Waiting for the "MyNoSQL" DB ! :-)

   Yea, they exist ... but they're not super-pop and
   mostly kinda "light" and/or are JUST as screwed-up
   in their own ways.

   Maybe there ARE NO "perfect solutions" ???


> By not having representative data when the prepare statements were 
> processed, the
> indexes were ignored leading to massive performance hits.
> 
> By not issuing commits at appropriate intervals, terminating the 
> application meant
> that all of the i/o done up to that point had to be backed out.
> 
> After fixing both of the above, the run time of the program was reduced 
> to three
> hours.
> 
> There are other performance optimizing steps such as buffer allocations 
> that can
> be fine tuned, and should be for applications dealing with lots of data. 
> When
> done properly, a database can have surprisingly good performance. When done
> improperly, it can be a nightmare.
> 
> Regards, Dave Hodgins

Back to comp.os.linux.misc | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Contents of file changed while application is reading it Clark Smith <noaddress@nowhere.net> - 2023-02-26 18:52 +0000
  Re: Contents of file changed while application is reading it Rich <rich@example.invalid> - 2023-02-26 19:20 +0000
  Re: Contents of file changed while application is reading it Robert Heller <heller@deepsoft.com> - 2023-02-26 21:45 +0000
    Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-26 23:05 +0100
      Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-26 23:38 +0000
    Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-26 23:32 +0000
      Re: Contents of file changed while application is reading it "David W. Hodgins" <dwhodgins@nomail.afraid.org> - 2023-02-26 19:06 -0500
        Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-27 00:26 +0000
          Re: Contents of file changed while application is reading it "David W. Hodgins" <dwhodgins@nomail.afraid.org> - 2023-02-26 20:48 -0500
          Re: Contents of file changed while application is reading it Richard Kettlewell <invalid@invalid.invalid> - 2023-02-27 09:03 +0000
            Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-27 09:58 +0000
              Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-27 11:16 +0100
              Re: Contents of file changed while application is reading it Richard Kettlewell <invalid@invalid.invalid> - 2023-02-27 15:20 +0000
        Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-27 10:34 +0100
          Re: Contents of file changed while application is reading it Richard Kettlewell <invalid@invalid.invalid> - 2023-02-27 09:49 +0000
            Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-27 11:13 +0100
          Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-27 10:02 +0000
            Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-27 11:13 +0100
              Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-27 10:17 +0000
                Re: Contents of file changed while application is reading it "David W. Hodgins" <dwhodgins@nomail.afraid.org> - 2023-02-27 12:27 -0500
                Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-27 19:19 +0100
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-02-27 21:46 -0500
                Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-28 13:33 +0100
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-02-28 10:49 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-28 16:21 +0000
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-01 01:05 -0500
                Re: Contents of file changed while application is reading it Charlie Gibbs <cgibbs@kltpzyxm.invalid> - 2023-03-01 21:57 +0000
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-01 22:53 -0500
                Re: Contents of file changed while application is reading it Charlie Gibbs <cgibbs@kltpzyxm.invalid> - 2023-03-02 19:06 +0000
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-02 23:08 -0500
                Re: Contents of file changed while application is reading it Charlie Gibbs <cgibbs@kltpzyxm.invalid> - 2023-03-03 05:23 +0000
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-03 09:36 +0000
                Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-03-03 11:19 +0100
                Re: Contents of file changed while application is reading it Charlie Gibbs <cgibbs@kltpzyxm.invalid> - 2023-03-03 15:58 +0000
                Re: Contents of file changed while application is reading it "Carlos E. R." <robin_listas@es.invalid> - 2023-03-03 18:47 +0100
                Re: Contents of file changed while application is reading it Rich <rich@example.invalid> - 2023-03-04 16:24 +0000
                Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-03-04 18:40 +0100
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-03 18:44 +0000
                Re: Contents of file changed while application is reading it Charlie Gibbs <cgibbs@kltpzyxm.invalid> - 2023-03-04 01:25 +0000
                Re: Contents of file changed while application is reading it Rich <rich@example.invalid> - 2023-03-04 16:21 +0000
                Re: Contents of file changed while application is reading it "28B.I874" <28B.I874@noabzba.net> - 2023-03-04 23:55 -0500
                Re: Contents of file changed while application is reading it Charlie Gibbs <cgibbs@kltpzyxm.invalid> - 2023-03-05 05:35 +0000
                Re: Contents of file changed while application is reading it Allodoxaphobia <trepidation@example.net> - 2023-03-05 13:48 +0000
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-05 14:01 +0000
                Re: Contents of file changed while application is reading it Charlie Gibbs <cgibbs@kltpzyxm.invalid> - 2023-03-05 19:16 +0000
                Re: Contents of file changed while application is reading it "28B.I874" <28B.I874@noabzba.net> - 2023-03-06 00:43 -0500
                Re: Contents of file changed while application is reading it "28B.I874" <28B.I874@noabzba.net> - 2023-03-06 00:42 -0500
                Re: Contents of file changed while application is reading it "David W. Hodgins" <dwhodgins@nomail.afraid.org> - 2023-02-28 12:01 -0500
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-01 01:17 -0500
                Re: Contents of file changed while application is reading it Robert Riches <spamtrap42@jacob21819.net> - 2023-02-28 04:13 +0000
                Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-28 13:16 +0100
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-01 01:32 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-01 10:30 +0000
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-01 22:48 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-02 09:41 +0000
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-02 23:39 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-03 09:28 +0000
                Re: Contents of file changed while application is reading it "David W. Hodgins" <dwhodgins@nomail.afraid.org> - 2023-03-03 12:16 -0500
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-04 01:36 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-04 07:15 +0000
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-04 02:47 -0500
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-04 01:12 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-04 06:51 +0000
                Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-03-04 03:32 -0500
                Re: Contents of file changed while application is reading it Richard Kettlewell <invalid@invalid.invalid> - 2023-03-04 09:54 +0000
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-04 10:09 +0000
                Re: Contents of file changed while application is reading it Richard Kettlewell <invalid@invalid.invalid> - 2023-03-04 11:22 +0000
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-04 11:58 +0000
                Re: Contents of file changed while application is reading it Richard Kettlewell <invalid@invalid.invalid> - 2023-03-05 16:43 +0000
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-06 09:20 +0000
                Re: Contents of file changed while application is reading it "28B.I874" <28B.I874@noabzba.net> - 2023-03-04 20:24 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-05 13:40 +0000
                Re: Contents of file changed while application is reading it Dan Espen <dan1espen@gmail.com> - 2023-03-05 11:29 -0500
                Re: Contents of file changed while application is reading it "28B.I874" <28B.I874@noabzba.net> - 2023-03-06 01:21 -0500
                Re: Contents of file changed while application is reading it "28B.I874" <28B.I874@noabzba.net> - 2023-03-06 01:06 -0500
                Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-03-06 09:28 +0000
                Re: Contents of file changed while application is reading it "28B.I874" <28B.I874@noabzba.net> - 2023-03-06 08:54 -0500
  Re: Contents of file changed while application is reading it Dan Espen <dan1espen@gmail.com> - 2023-02-26 17:35 -0500
    Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-26 23:39 +0000
      Re: Contents of file changed while application is reading it "28A.I873" <28A.I873@noabzba.net> - 2023-02-26 22:42 -0500
    Re: Contents of file changed while application is reading it "Carlos E.R." <robin_listas@es.invalid> - 2023-02-27 10:45 +0100
  Re: Contents of file changed while application is reading it The Natural Philosopher <tnp@invalid.invalid> - 2023-02-26 23:31 +0000

csiph-web