Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.os.linux.advocacy > #413164

Re: Snit hitting the glue bag early today

Show all headers | View raw

owl <owl@rooftop.invalid> wrote:
> Sandman <mr@sandman.net> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>> 
>> In article <czcvz8993.ahu@rooftop.invalid>, owl wrote:
>> 
>>> > owl:
>>> > anon@lowtide:~/code/pg$ cat names.csv
>>> > "norton","436"
>>> > "trixie","376"
>>> > "alice","409"
>>> > "ralph","380"
>>> > "moe","399"
>>> > anon@lowtide:~/code/pg$
>>> 
>>> Oops.  Had not dropped the table before running that.  Here's
>>> some correct values:
>> 
>>> anon@lowtide:~/code/pg$ time ./blah
>>> "2017-01-01","23"
>>> "2017-01-13","40"
>>> "2017-01-26","20"
>>> "2017-01-04","28"
>>> "2017-01-03","35"
>>> "2017-01-30","26"
>>> "2017-01-27","29"
>>> "2017-01-19","33"
>>> "2017-01-10","37"
>>> "2017-01-15","37"
>>> "2017-01-28","39"
>>> "2017-01-22","32"
>>> "2017-01-18","26"
>>> "2017-01-20","31"
>>> "2017-01-05","18"
>>> "2017-01-29","40"
>>> "2017-01-06","30"
>>> "2017-01-25","32"
>>> "2017-01-17","40"
>>> "2017-01-24","24"
>>> "2017-01-31","37"
>>> "2017-01-09","24"
>>> "2017-01-07","37"
>>> "2017-01-21","38"
>>> "2017-01-12","46"
>>> "2017-01-16","34"
>>> "2017-01-02","29"
>>> "2017-01-14","26"
>>> "2017-01-08","40"
>>> "2017-01-11","37"
>>> "2017-01-23","32"
>>> "trixie","214"
>>> "norton","186"
>>> "ralph","211"
>>> "alice","178"
>>> "moe","211"
>> 
>>> real    0m8.387s
>>> user    0m0.032s
>>> sys     0m0.032s
>>> anon@lowtide:~/code/pg$
>> 
>> That's weirdly slow...
>> 
>>  dfs> time ./populate2.php 
>> 2017-03-05: 9
>> 2017-03-06: 12
>> 2017-03-07: 15
>> 2017-03-08: 14
>> 2017-03-09: 14
>> 2017-03-10: 19
>> 2017-03-11: 15
>> .. <snip> ...
>> 2017-04-28: 11
>> 2017-04-29: 13
>> 2017-04-30: 18
>> 2017-05-01: 25
>> 2017-05-02: 15
>> 2017-05-03: 16
>> 2017-05-04: 12
>> 2017-05-05: 4
>> alice     : 194
>> moe       : 197
>> norton    : 210
>> ralph     : 209
>> trixie    : 190
>> 
>> real    0m0.732s
>> user    0m0.191s
>> sys     0m0.382s
>> 
>> I thought this was slow, but I'm using SQLite:
>> 
>>  dfs> cat populate2.php 
>> #!/usr/bin/php
>> <?
>> $sql = new SQLite3("names.sqlite");
>> $sql->exec("create table if not exists names (name VARCHAR (20), date DATE)");
>> $sql->exec("delete from names");
>> $names = ["ralph", "alice", "norton", "trixie", "moe"];
>> for ($i = 0; $i < 1000; $i++) {
>>   $date = $sql->escapeString(date("Y-m-d", rand(strtotime("-2 months"), time())));
>>   $name = $sql->escapeString($names[array_rand($names)]);
>>   $sql->query("insert into NAMES values ('$name','$date')");
>> }
>> if ($results = $sql->query("select date, COUNT(name) AS nr from NAMES GROUP BY date")) {
>>   while ($row = $results->fetchArray()) printf("%s: %d\n", $row["date"], $row["nr"]);
>> }
>> if ($results = $sql->query("select name, COUNT(date) AS nr from names GROUP BY name")) {
>>   while ($row = $results->fetchArray()) printf("%-10s: %d\n", $row["name"], $row["nr"]);
>> }
>> 
> 
> I agree it seems really slow at 8+ seconds.  That could be from my code.
> I have very little experience with database stuff.  But from what I
> see online, sqlite is much faster with inserts that postgresql.  So,
> I guess it could be either or both.
> 

OK, I found that I can use the COPY FROM command to load a csv file
into the table rather than using multiple inserts, so I have the code
writing to a csv, then doing that.  That brings the run time down to
about 0.05 seconds.  The problem is that the thing fails with a fatal
error more often than it works, and when it "works" it only brings in
about 960 some odd records.  The exact same query on the exact same
file works perfectly 100% of the time when done from a psql terminal,
whether done as 'copy' or '\copy'.  Debugging it is driving me crazy.

But anyway, the "almost" doing it (with 960 records) gives some idea
of the how fast it should be if I ever get it to work right.  I'm
leaning toward it being a UID issue, since the 'server' version of
the command (the one I'm using in the code, since '\copy' won't work
that way) runs as the UID of the postgres user.  The file is already
world-readable though...

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

Thread

Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-03 09:22 -0400
  Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-03 14:07 +0000
    Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-04 09:29 -0400
      Re: Snit hitting the glue bag early today Sandman <mr@sandman.net> - 2017-05-04 16:42 +0000
        Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-04 20:06 +0000
          Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-04 20:12 +0000
            Re: Snit hitting the glue bag early today chrisv <chrisv@nospam.invalid> - 2017-05-05 06:52 -0500
              Re: Snit hitting the glue bag early today Marek Novotny <marek.novotny@marspolar.com> - 2017-05-05 10:32 -0500
                Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-05 13:22 -0400
              Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-05 13:01 -0400
          Re: Snit hitting the glue bag early today Steve Carroll <fretwizzer@gmail.com> - 2017-05-04 13:16 -0700
          Re: Snit hitting the glue bag early today Sandman <mr@sandman.net> - 2017-05-04 21:36 +0000
        Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-06 09:44 -0400
      Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-04 19:49 +0000
        Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-04 19:28 -0400
          Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-05 02:56 +0000
            Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-04 23:40 -0400
              Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-05 06:18 +0000
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-05 06:34 +0000
                Re: Snit hitting the glue bag early today Sandman <mr@sandman.net> - 2017-05-05 07:07 +0000
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-05 07:29 +0000
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-05 16:48 +0000
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-05 20:49 +0000
                Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-05 13:02 -0400
                Re: Snit hitting the glue bag early today Sandman <mr@sandman.net> - 2017-05-05 19:12 +0000
                Re: Snit hitting the glue bag early today Snit <usenet@gallopinginsanity.com> - 2017-05-05 13:45 -0700
                Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-05 17:09 -0400
                Re: Snit hitting the glue bag early today Sandman <mr@sandman.net> - 2017-05-05 21:41 +0000
                Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-05 13:00 -0400
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-05 20:14 +0000
                Re: Snit hitting the glue bag early today Snit <usenet@gallopinginsanity.com> - 2017-05-05 13:44 -0700
                Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-05 23:51 -0400
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-06 04:48 +0000
                Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-06 10:06 -0400
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-06 15:50 +0000
                Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-07 13:09 -0400
                Re: Snit hitting the glue bag early today owl <owl@rooftop.invalid> - 2017-05-07 18:39 +0000
            Re: Snit hitting the glue bag early today Chris Ahlstrom <OFeem1987@teleworm.us> - 2017-05-05 05:28 -0400
              Re: Snit hitting the glue bag early today DFS <nospam@dfs.com> - 2017-05-05 13:02 -0400

csiph-web