Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAUAclm80053 for ; Fri, 30 Nov 2001 05:38:47 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fAUAQuv15782 for pgsql-sql@postgresql.org; Fri, 30 Nov 2001 05:26:56 -0500 (EST) (envelope-from news) From: "Steve Brett" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: persistent connections, AOLserver (Was: maybe Offtopic : PostgreSQL & PHP ?) Date: Fri, 30 Nov 2001 10:23:01 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 62 Message-ID: <9u7mtf$fd2$1@news.tht.net> References: <20011119083547.A22031@cc.usu.edu> X-Complaints-To: scrappy@hub.org X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-sql@postgresql.org X-Archive-Number: 200112/17 X-Sequence-Number: 5840 is there a limit/guide to the number of inserts you should use in a transaction block ? i have an app at the minute written indelphi that moves data from a cache database and a sql server databse and produces one definitive customer management database for the company i work for. the transfer is quite slow but i do use single inserts (not through a lack of knowledge for what a database is for though :-) and maybe wrapping in a transaction block is the answer. i'm moving (parsing and matching) pretty small recorsets (10,000 in one and approx 60,000 in the other). Steve "Roberto Mello" wrote in message news:20011119083547.A22031@cc.usu.edu... > On Wed, Apr 18, 2001 at 08:28:14PM +0200, Mathijs Brands wrote: > > > > I've seen both MySQL and PostgreSQL give up (MySQL just hung, pgsql > > cored) when I tried simple selects from a couple of hundred concurrent > > connections; no transactions or other fancy stuff there. I think I was > > using MySQL 3.22.?? and pgsql 6.5.3, so more modern versions may well be > > able to cope with these numbers of connections. It's been more than a > > year since I last tried it. > > PG 7.0 was released almost 2 years ago, so you had a very old version when > you tried. > > > I don't know what the current state of affairs is, but it is my > > understanding that, while pgsql performs admirably on tasks which mostly > > read data, pgsql isn't really able to cope (performance wise) with an > > application that has a very high insert to select ratio, such as OLTP. > > This is simply not true. It may have been true in the past, but not since > PG 7.0 came out. > > A mistake that I see MySQL users do frequently is that when they have a > bulk of inserts to do, they don't use the COPY command to bulk load the > data and/or they don't wrap the inserts into one (or several) transaction > blocks, leaving each INSERT in its own transaction, which obviously will > hurt performance. > > They do that because they usually have no idea of what a database is for, > besides being an SQL interface to their file systems. Exactly what MySQL > was until very recently (and thanks to the work of third-parties, because > the MySQL team itself couldn't care less about real features). > > -Roberto > > -- > +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ > Roberto Mello - Computer Science, USU - http://www.brasileiro.net > http://www.sdl.usu.edu - Space Dynamics Lab, Developer > TAFB -> Text Above Fullquote Below > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org