Stored Procedures - slower?
От | Mike Clements |
---|---|
Тема | Stored Procedures - slower? |
Дата | |
Msg-id | 06B12D1D68BCCA4CB1F60BB4EA5097682454B3@pq-exch01.actional.com обсуждение исходный текст |
Список | pgsql-jdbc |
I had an app that was doing groups of 14 inserts into some related tables, each as a separate SQL call. I wrote a storedprocedure to do all 14 inserts and now the app makes a single call to the stored procedure, doing the same thing witha single round trip to the database. The end result: it's SLOWER! I've confirmed by analyzing tables & rows that both techniques do the exact same thing, but doing it with a single call toa stored procedure consistently takes 1.47 times as long - that is to say, it has about 68% of the throughput. This is done using a JDBC CallableStatement to invoke the stored procedure. This is so "impossible" I've been testing all day only to verify it. Any ideas? > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements > Sent: Tuesday, November 08, 2005 8:12 AM > To: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Batch with keygen? > > Thanks everyone for all the ideas. I am going the route of > stored procedures. This lets me keep my DB generated keys, > while also greatly reducing the number of SQL round trips, > which will improve performance, all without denormalizing the schema. > > I was hoping to avoid using stored procedures because we > support multiple different DBs so it means writing them on > various different platforms. But it looks like this is the > only real option. > > > > -----Original Message----- > > From: pgsql-jdbc-owner@postgresql.org > > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Nelson Arape > > Sent: Tuesday, November 08, 2005 4:50 AM > > To: pgsql-jdbc@postgresql.org > > Subject: Re: [JDBC] Batch with keygen? > > > > Maybe I am a bit off, but the old friends curval() and > > nextval() don't do the > > trick? I mean > > > > con.setAutoCommit(false); > > Statement stmt = con.createStatement(); > > stmt.addBatch("INSERT INTO TABLEA " + > > "VALUES(nextval('TABLEA_PK_SEQ'), 1, 2, 3)"); > > stmt.addBatch("INSERT INTO TABLEB " + > > "VALUES(curval('TABLEA_PK_SEQ'), 4, 5, 6)"); > > ... > > int [] updateCounts = stmt.executeBatch(); > > > > Bye > > Nelson Arapé > > PS: sorry for my English > > > > El Lun 07 Nov 2005 20:01, Dave Cramer escribió: > > > Additionally you should be aware, that using this mechanism once a > > > connection gets a hundred values, they are consumed even if > > you don't > > > use them. > > > > > > Dave > > > > > > On 7-Nov-05, at 4:18 PM, Kris Jurka wrote: > > > > On Mon, 7 Nov 2005, Mike Clements wrote: > > > >> I've got a bunch of PreparedStatements doing inserts on > > tables with > > > >> primary keys generated by sequences. For example, insert > > on table A, > > > >> take generated primary key, insert on table B assigning > > foreign key > > > >> generated value for A's primary key. > > > >> > > > >> For performance reasons, I need to batch these commands. But I > > > >> don't see > > > >> how it would be possible to continue using this approach with a > > > >> batch of > > > >> commands, because I need the results of the first insert > > to make the > > > >> second insert. Is there some way to do this or am I > > going to have to > > > >> stop using keygen and instead have my application > > generate its own > > > >> keys? > > > > > > > > One option would be to tune your sequence generator to > your batch > > > > size, consider: CREATE SEQUENCE myseq INCREMENT BY 100; Then if > > > > you fetch a nextval you know that you are also free to > > use the next > > > > 99 values as well in your batch statement without touching the > > > > sequence. > > > > > > > > Kris Jurka > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- > > > > TIP 6: explain analyze is your friend > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > TIP 9: In versions below 8.0, the planner will ignore > your desire to > > > choose an index scan if your joining column's > > datatypes do not > > > match > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly > >
В списке pgsql-jdbc по дате отправления: