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 188.8.131.5226 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,
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.
An index on lists_id would help.
Execute query just builds the query. The first next() is when it goes through the entire table and does the count. The first next on your top x query just has to get to the first row, which may or may not be real easy to find depending on your data.
You can use dbisql to play with your queries and view the resulting plans. Index scans are good - full table scans not good.