db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bocete <kogachegajagnjet...@gmail.com>
Subject Prepared Statement extremely slower that Statement - query optimizing?
Date Wed, 23 Jul 2008 12:58:59 GMT

Hi

First, excuse my English..

I've got a query that needs to be executed many times. It depends on one
parameter. As for the performance, a simple 

Statement takes ~80ms; PreparedStatement with the parameter provided takes
~280ms. I have tired running the same 

PreparedStatement with the parameter coded in the sql query (just to see how
fast would it go) and it's about ~10ms. So, 

that's what I strive for.

Here are the tables used:

SmallTable:
&nbsp TypeID INTEGER PRIMARY KEY
&nbsp SmallTableColumn VARCHAR
Number of rows: 5366

HugeTable:
&nbsp SomeColumn VARCHAR REFERENCES....
&nbsp SomeOtherColumn VARCHAR REFERENCES...
&nbsp TypeID INTEGER REFERENCES SmallTable
&nbsp NumberColumn INTEGER
Number of rows: 47800
Primary key (SomeColumn, SomeOtherColumn, TypeID, IrrelevantColumn)

And here is the query:
SELECT VirtualTable.TypeID, SmallTable.SmallTableColumn,
VirtualTable.MinNumber, VirtualTable.MaxNumber
&nbsp&nbsp FROM AMSSTipovi T, 
&nbsp&nbsp (SELECT HugeTable.TypeID, MIN (HugeTable.NumberColumn) MinNumber,
MAX (HugeTable.NumberColumn) AS MaxNumber 
&nbsp&nbsp&nbsp&nbsp FROM HugeTable 
&nbsp&nbsp&nbsp&nbsp WHERE HugeTable.SomeColumn = ? 
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp AND HugeTable.someOtherColumn = 'Value'
&nbsp&nbsp&nbsp&nbsp GROUP BY HugeTable.TypeID) VirtualTable 
&nbsp&nbsp WHERE SmallTable.TypeID = VirtualTable.TypeID


So, basically, I need to:
Filter the HugeTable for those following the criteria in the 5th and the 6th
line of the query,
Group those that remain by the TypeID column, getting the min(NumberColumn)
and max(NumberColumn),
Get some additional data relevant to the TypeID from the SmallTable.


You might wonder why I used the inner select.. It seems impossible to use
the GROUP BY column as the join criterium. I 

wouldn't know why, using GROUP BY on a column is the same as DISTINCT as far
as that column is concerned.

The problem: It seems Derby sees the "?" as a threat in this query, so it
doesn't optimize it as much as it would without 

the "?". If I replace the "?" with some fixed value the query is executed
extremely fast. What could I do to this query in 

order to make it optimizable even in parametrised form?
Or, could I optimize it by hand? Statement preparing takes ~70ms in this
case, therefore, Derby is doing a hell of a job 

optimizing it.
Thanks,
Bocete
PS. My connection got broken while sending this message, so I'm repeating
the send. I'll delete it if it turns out to be a double, if I can - but
sorry anyway.
-- 
View this message in context: http://www.nabble.com/Prepared-Statement-extremely-slower-that-Statement---query-optimizing--tp18610195p18610195.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Mime
View raw message