On 2/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > SQLite supports the syntax above, FWIW. > > Your theory is that SQLite does well because it doesn't need to > send data back and forth between the client and server? You're > probably right. On the other hand, what good is the data if > the client never sees it? I don't think that can be it. pgAdmin is also just a client AFAIK and it got all results back in just 5 seconds. I'd blame psql for poor results here. psql's output is 500KB in that test, while SQLite's is 72KB. That is the difference far far smaller then the one measured in time. FWIW, psql.exe, cmd.exe and System are using up all CPU for the duration of that test. So perhaps psql is not really fast on windows?
> You'll notice that SQLite seems to do particularly well on the > tests that involve a lot of SQL. For example, test 2 with > 25000 separate INSERT statements. SQLite ran in 0.7 seconds > versus 16.5 seconds for PostgreSQL. Probably a big fraction of > the 16.5 seconds PostgreSQL used were in transmitting all of > that SQL over a socket to the server. I'm wondering if the > use of prepared statements might reduce the performance gap > somewhat? Notice that when doing an equally large insert in > Test 12, but an insert that involves much less SQL and parsing, > that PostgreSQL is actually a little faster than SQLite. > > Any volunteers to run the experiment? Jim? > > Another explanation for the poor performance by PostgreSQL in > test 2 might be the PostgreSQL parser is less efficient. Or > perhaps the PostgreSQL spends a lot more time trying to > optimize - which can pay off on a big query but is a drag for > lots of silly little inserts. A test using prepared statements > would help clearify the issue. I'd bet on Postgres' optimizer taking up too much time. Since we have 25000 separate statements there, I wouldn't be surprised if postgres is trying to optimize each of them individually. And that just has to take some time. Prepared statements should really help postgres here since that would be only one statement to analyze/optimize and there would be less data to send through socket. But, keep in mind that postgres completed the test in 5 seconds, not 200 as stated in test results. Results will be updated once we come up with better way to run them. I'm referring to psql problem here. Originally, I wanted to run these tests on PHP 5.1 since PDO has support for all databases involved and would enable us to use prepared statements and skip cmd line client all together. And db specific drivers should be very thin layer on top of PDO it self. Or did I get this thinness wrong? That might still happen in the future though. -- Nemanja Corlija <[EMAIL PROTECTED]>
