Hi,

I have a table with currently 167473 rows. Database is stored local.

When I execute    select * from xxx order by id      I get from the ElevateDB Manager a execution time of approx. 16-17 seconds.     Time is unchanged when I remove the order by part.

The table has 8 indexes and the ID is in one of these indexes.

Is this speed normal or is anything wrong?

Thank you.

Teco


The clue may be in the fact you say "the ID is in one of these indexes." You need to have it as either a stand alone index or (I think) as the first column in an index.

It would also be useful if you posted the execution plan here - there's an option to produce it in EDBManager and you can cut'n'paste


Roy Lambert

Dear Roy,

Please find below some information about the table, the indexes and the execution plan

Thank you.
Teco



Table
EXECUTE IMMEDIATE 'CREATE TABLE "Contenttable"
(
"ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ContenttableRegister" VARCHAR(12) COLLATE "UNI" NOT NULL,
"VersionNo" INTEGER NOT NULL,
"Parts" INTEGER NOT NULL,
"Connectors" INTEGER NOT NULL,
"Variations" INTEGER,
"ProjectMGR" VARCHAR(20) COLLATE "UNI",
"Batch" VARCHAR(20) COLLATE "UNI",
"Factory" VARCHAR(20) COLLATE "UNI",
"Remarks" VARCHAR(1023) COLLATE "UNI",
"Writtenby" VARCHAR(25) COLLATE "UNI" NOT NULL,
"Finalized" BOOLEAN,
"Finalizedby" VARCHAR(1023) COLLATE "UNI",
"DrawingVersion" VARCHAR(10) COLLATE "UNI" NOT NULL,
"Writtendate" DATE NOT NULL,
"WrittenTime" TIME NOT NULL,
"RevisedDate" DATE,
"RevisedTime" TIME,
"DrawingHashID" VARCHAR(260) COLLATE "UNI" NOT NULL
)
VERSION 1.00
READWRITE
ENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';




Indexes
EXECUTE IMMEDIATE 'CREATE INDEX "Index1" ON "Contenttable" ("ContenttableRegister" COLLATE "UNI")';
EXECUTE IMMEDIATE 'CREATE INDEX "Index6" ON "Contenttable" ("Batch" COLLATE "UNI")';
EXECUTE IMMEDIATE 'CREATE INDEX "Index7" ON "Contenttable" ("ProjectMGR" COLLATE "UNI")';
EXECUTE IMMEDIATE 'CREATE INDEX "Index8" ON "Contenttable" ("Factory" COLLATE "UNI")';
EXECUTE IMMEDIATE 'CREATE INDEX "Index2" ON "Contenttable" ("ContenttableRegister" COLLATE "UNI")';
EXECUTE IMMEDIATE 'CREATE INDEX "Index3" ON "Contenttable" ("VersionNo")';
EXECUTE IMMEDIATE 'CREATE INDEX "Index4" ON "Contenttable" ("ContenttableRegister" COLLATE "UNI", "VersionNo")';
EXECUTE IMMEDIATE 'CREATE INDEX "Index5" ON "Contenttable" ("ID")';


SQL Statement
select * from Contenttable

Excecution Plan
SQL Query (Executed by ElevateDB 2.33 Build 2)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"Contenttable"."ID" AS "ID",
"Contenttable"."ContenttableRegister" AS "ContenttableRegister",
"Contenttable"."VersionNo" AS "VersionNo",
"Contenttable"."Parts" AS "Parts",
"Contenttable"."Connectors" AS "Connectors",
"Contenttable"."Variations" AS "Variations",
"Contenttable"."ProjectMGR" AS "ProjectMGR",
"Contenttable"."Batch" AS "Batch",
"Contenttable"."Factory" AS "Factory",
"Contenttable"."Remarks" AS "Remarks",
"Contenttable"."Writtenby" AS "Writtenby",
"Contenttable"."Finalized" AS "Finalized",
"Contenttable"."Finalizedby" AS "Finalizedby",
"Contenttable"."DrawingVersion" AS "DrawingVersion",
"Contenttable"."Writtendate" AS "Writtendate",
"Contenttable"."WrittenTime" AS "WrittenTime",
"Contenttable"."RevisedDate" AS "RevisedDate",
"Contenttable"."RevisedTime" AS "RevisedTime",
"Contenttable"."DrawingHashID" AS "DrawingHashID"
FROM "Contenttable"

Source Tables
-------------

Contenttable: 167473 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

================================================================================
167473 row(s) returned in 16,687 secs
================================================================================

Hi Teco,

> select * from Contenttable order by id

With that query being insensitive, you are effectively making a copy of the entire table.  Depending upon the speed of the system and how fast the drive can save down the results to a temporary table, that time may not be out of line with what I would expect.

Try setting the RequestSensitive property of the TEDBQuery object to true.  Since you are sorting by an indexed field, it should give you a "live" dataset that doesn't have to create a copy of the entire table.

If you don't want the end user to be able to edit the results, set the object ReadOnly property to true.

If I'm correct, you should see the results return almost immediately.  

Teco


What Terry said

Roy Lambert

Thank you to all.

Works now nearly perfect.

The only slow query is "select count(id) from Contenttable"

Teco


Try

"select count(*) from Contenttable"

it may be optimised - I don't know


Roy Lambert

Dear Roy,

works perfectly.

Thank you.
Teco

Teco


I hoped it would be - using count(*) simply has to get the count of all records with anything or nothing in them whereas count(ID) actually has to go through and count something.

Roy Lambert