Re: [Hsqldb-user] simple select needs a long time

[Fred T. <fr...@us...>]

There are indexes on the Pk and FK columns, but not on the column in the
query condition.
You need to create an index on the column NAMEN.

Fred

On Fri, 23 Apr 2010 18:42 +0200, "Gerd Gühne" <ger...@we...>
wrote:
> Hello,
> 
> I have a performance problem by executing a simple select.
> 
> My Table:
> CREATE MEMORY TABLE PUBLIC.NAMEN(ID INTEGER GENERATED BY DEFAULT AS
> IDENTITY(START WITH 579148) NOT NULL PRIMARY KEY,DATENID INTEGER NOT
> NULL,NAMEN VARCHAR(255) NOT NULL,FOREIGN KEY(DATENID) REFERENCES
> PUBLIC.DATEN(ID))
> 
> The simple select:
> connection.prepareStatement("SELECT datenid FROM namen WHERE namen =
> ?;");
> 
> If I execute the statement with a name which is the first entry in the
> table, I get the datenid in same time as I used a name from the end of
> the table.
> In most cases the execution speed is between 180 and 210 ms. But every 3
> or 4 executions of the same query (with same name) the query needs ~2
> seconds!
> 
> Because of this I've put all ~580,000 names into a hash map and than I
> get the datenid by the name. The java hash map needs for one get 0 ms.
> 
> What can I do for increasing the query speed? I used the latest hsqldb
> jar file and the database logging is offline.
> Thank you for your help!
> 
> Best regards
> Gerd Gühne
> http://www.marfir.de
> ___________________________________________________________
> NEU: WEB.DE DSL für 19,99 EUR/mtl. und ohne Mindest-Laufzeit!
> http://produkte.web.de/go/02/
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>