[GENERAL] General question about speed of functions
I am starting a project next week that looks like it will involve some massive sql rewrites to gain needed performance, and I am looking at implementing as many functions as possible. I haven't worried that much about specific implementations in the past, but this project can use any performance gains available, so even a five percent difference between a function written in c vs pgsql would be helpful. The apparent choices available for the existing application are to use functions in pgsql, php or c. Any comments about relative performance of using any of those languages would be greatly appreciated. Thanks, Brent Friedman -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Insert vs Update syntax
If you don't like the standard sql implementation, you could use plsql
or any language to make an abstraction layer/wrapper for this
functionality. Just pass everything as a key/value pair, in an array or
hashtable structure, to your abstraction layer/wrapper, and it can cycle
through the data structure to do the insert or update for you.
In very rough psuedo-code, something like
create my_data_structure {
table = employees,
first_name = sally,
last_name = smith,
date_of_birth = 2008-01-01
}
call function mywrapper (my_data_structure)
.
function cycles through array, and creates an sql string
dynamically
This may have problems with constraints, foreign keys, etc.
Clodoaldo wrote:
2008/2/29, Martijn van Oosterhout [EMAIL PROTECTED]:
On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:
When inserting into a table and there are many columns to be inserted
it is hard to synchronize columns to values:
snip
Is there some reason for the insert syntax to be the way it is in
instead of the much easier to get it right Update syntax?:
Because it's what the SQL standard says. If you don't like it I suggest
you take it up with them... But it's a little late to change now I
think.
I know about the standards and I'm not blaming postgresql. I just want
to know if it is worth to bring the the matter to some responsible
group.
Regards, Clodoaldo Pinto Neto
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] JDBC and Postgresql 8.0 - cpu usage question
We are running a Java app with Tomcat 5.0.x and Postgres 8.0.x on a linux machine: 2.6.16 kernel, dual processor, 16gb ram, 2 scsi drives. The machine runs a few other ongoing applications that handle replication, but their impact on performance should be constant over time. The application is using apache/jakarta (jndi) connection pooling with Tomcat. We are using this jdbc driver: postgresql-8.0-315.jdbc3.jar. Some of the queries that we are running must use serial counts, so I know those are not as fast as in other rdbms. With five to ten clients of the jsp/servlet application, I am seeing very high cpu usage associated with what are apparently idle threads. postgres 26999 0.0 0.0 116080 9984 ?S14:51 0:00 \_ postgres: user db ip (62233) idle postgres 2090 20.6 0.5 114844 85876 ?S17:31 43:29 \_ postgres: user db ip (42411) idle postgres 5968 24.1 0.5 114844 84888 ?S18:55 30:19 \_ postgres: user db ip (41929) idle The 20.6 and 24.1 numbers represent cpu usage, as reported from ps -auxf. Based on the time of day, these values can run from very low (2% or so) to 30% or more. The connection pool has a range of 5 to 25, and these conditions reflect a very low usage level, with maybe 5-6 concurrent connections in the pool. We are closing all jndi connections after use, but we are not specifically invoking garbage collection or forcing use of a destroyer method. Any ideas about the jdbc type 3 driver, or tomcat connection pooling with a Sun jvm on linux? The application itself represents a constant load factor, with queries executing every minute on a cron - type schedule (http://www.sauronsoftware.it/projects/cron4j/). Thanks, Brent Friedman ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
