You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Bocete <ko...@gmail.com> on 2008/07/23 14:58:59 UTC
Prepared Statement extremely slower that Statement - query
optimizing?
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:
  TypeID INTEGER PRIMARY KEY
  SmallTableColumn VARCHAR
Number of rows: 5366
HugeTable:
  SomeColumn VARCHAR REFERENCES....
  SomeOtherColumn VARCHAR REFERENCES...
  TypeID INTEGER REFERENCES SmallTable
  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
   FROM AMSSTipovi T,
   (SELECT HugeTable.TypeID, MIN (HugeTable.NumberColumn) MinNumber,
MAX (HugeTable.NumberColumn) AS MaxNumber
     FROM HugeTable
     WHERE HugeTable.SomeColumn = ?
       AND HugeTable.someOtherColumn = 'Value'
     GROUP BY HugeTable.TypeID) VirtualTable
   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.