The following bug has been logged online:
Bug reference: 4954
Logged by: Axel Fix
Email address: axel.fix@sitqsystems.com
PostgreSQL version: 8.3 and 8.4
Operating system: Windows XP
Description: very slow query with 2 statements
Details:
Hello,
I have a simple er-model with 3 tables for 2D objects and use the following
statements for query data:
s1 = "select t1.id,t1.type... from t0,t1
where t1.id_t0 = t0.id and t0.name='x1000'"
s2 = "select X,Y from t2 where id_t1=?"
The table t1 returns 1000 elements which I iterate and use the returned id
for the prepared statement s2. This way the server executes s2 1000 times.
This seems to be very slow (>10sec!!! - the time grows with the whole number
of datasets in the tables).
Table t2 contains about 2.500 elements for the 1000 elements of t1.
If I use a single statement, where I get an all in one result set, it will
be finished after <100ms!
The collected statement (with right table and column names) is:
sc = "select t_graphobj.ID,type,fill,color,x,y\n" +
" from t_graphdata,t_graphobj,t_names\n" +
" where ID_GRAPHOBJ=t_graphobj.ID\n" +
" and t_graphobj.ID_NAMES=t_names.ID\n" +
" and t_names.NAME='x1000'\n" +
" order by t_graphobj.id"
If you need the java source code and SQL statements for the table, let me
know.
Thanks in advance,
Axel Fix
