Slow performance calling ResultSet.next() for a count

[Former Member]

Hi,

I have a table with over 500k rows and when I execute a query that supposed to return me a count for a subset of the data (28k) the invocation of ResultSet.next() takes over 5 seconds to perform. I don't see any delay when calling executeQuery(), only when calling ResultSet.next() to get the actual count value. What's more interesting is that when I actually need to fetch the values and use

select top x start at y ...

the performance of ResultSet.next is much better though I'm fetching multiple columns.

I'm running UltraliteJ 12.0.1.3726 on Android OS using Galaxy Nexus device. The schema for the table in question is as follows:

CREATE TABLE "dba"."LIST_DATA_FLAT" ( 
"LIST_DATA_ID" integer NOT NULL 
, "LANGUAGE_ID" integer NOT NULL 
, "VALUE" varchar(512) NOT NULL 
, "DESCRIPTION" varchar(1024) NULL 
, "ATTR_0" varchar(512) NULL 
, "ATTR_1" varchar(512) NULL 
, "ATTR_2" varchar(512) NULL 
, "ATTR_3" varchar(512) NULL 
, "ATTR_4" varchar(512) NULL 
, "ATTR_5" varchar(512) NULL 
, "ATTR_6" varchar(512) NULL 
, "ATTR_7" varchar(512) NULL 
, "ATTR_8" varchar(512) NULL 
, "ATTR_9" varchar(512) NULL 
, "ATTR_OBJECT" long binary NULL 
, "TIMESTAMP_CREATED" timestamp DEFAULT CURRENT UTC TIMESTAMP NOT NULL 
, "TIMESTAMP_UPDATED" timestamp NOT NULL 
, "LISTS_ID" integer NOT NULL 
, "BACKEND_ID" varchar(128) NULL 
, "PARENT_ID" integer NOT NULL 
, "SORT_ORDER" integer NOT NULL 
, "LEVEL" integer NOT NULL 
, PRIMARY KEY ( "LIST_DATA_ID" asc
, "LANGUAGE_ID" asc ) 
)

FYI LISTS_ID is a foreign key.

The query that I'm running is this:

select count(distinct(ld.list_data_id))
from list_data_flat ld
where ld.lists_id = 384 AND ld.level = 0

Am I reaching the boundaries of Ultralite/UltraliteJ performance with that much data or is there something wrong with my schema?

Any hints/advice is much appreciated,

Ross

[philippefbertrand · Accepted solution]

Please confirm you posted the correct plan.

I'm guessing it is because of the distinct it has to build a temp table which, given the volume of rows, just beyond what the device can do.

If you wish to investigate this further, I suggest opening a support case.