I know I been away for a while but... any hint appreciated.


CREATE TABLE "IT"
(
"ID" INTEGER NOT NULL,
"Description" VARCHAR(250) COLLATE "UNI" NOT NULL,
"HasNote" BOOLEAN DEFAULT FALSE NOT NULL,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID")
)
VERSION 1.00
READWRITE
UNENCRYPTED
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

CREATE TABLE "IV"
(
"TestID" INTEGER NOT NULL,
"ItemID" INTEGER NOT NULL,
"ItemValue" INTEGER NOT NULL,
"ItemColor" INTEGER NOT NULL,
"LastValue" INTEGER,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ItemID", "TestID")
)
VERSION 1.00
READWRITE
UNENCRYPTED
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

================================================================================
SQL Query (Executed by ElevateDB 2.18 Build 1)

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
"it"."ID" AS "No",
"iv"."LastValue" AS "LastValue"
FROM "it", "iv"
WHERE "iv"."TestID" = 100 AND "it"."ID" = "iv"."ItemID"

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

it: 9295 rows
iv: 9295 rows

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

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

Filtering
---------

The following filter condition was applied to the iv table:

"iv"."TestID" = 100

Row scan (IV): 9295 rows, 435.7KB estimated cost

Hint: Create index(es) (IV) on column(s) "iv"."TestID" for possible better
performance

The following filter condition was applied to the result set rows as they were
generated:

"it"."ID" = "iv"."ItemID"

Joins
-----

The driver table was the it table

The optimizer attempted to re-order the joins to a more optimal order

The joins were already in the most optimal order


Result set I/O statistics
-------------------------

Total rows visited: 86397025

Row buffer manager

Max buffer size: 1MB Buffer size: 290.47KB

Hits: 9295   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 0   written: 0B

Index Page buffer manager

Max buffer size: 2MB Buffer size: 280KB

Hits: 18078   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 0   written: 0B

================================================================================
9295 row(s) returned in 165.907 secs
================================================================================

On 10/25/2014 6:13 PM, Fred Schetterer wrote:
> SELECT ALL
> "it"."ID" AS "No",
> "iv"."LastValue" AS "LastValue"
> FROM "it", "iv"
> WHERE "iv"."TestID" = 100 AND "it"."ID" = "iv"."ItemID"

I would definitely suggest a join clause instead of where to prevent a
cross join:

SELECT ALL
"it"."ID" AS "No",
"iv"."LastValue" AS "LastValue"
FROM "it", "iv"
ON "it"."ID" = "iv"."ItemID"
WHERE "iv"."TestID" = 100


> Hint: Create index(es) (IV) on column(s) "iv"."TestID" for possible better
> performance

This might be useful to have.

In general not having indexes can be a culprit so you might also need to
see if having indexes on iv.ItemID and it.ID helps with speed


Raul

Fred


And since you've been away a while to extend what Raul said you can use a JOIN

SELECT
"it"."ID" AS "No",
"iv"."LastValue" AS "LastValue"
FROM "iv"
JOIN IT ON "it"."ID" = "iv"."ItemID"
WHERE "iv"."TestID" = 100

but unless I'm mistaking something or you've only posted a bit of a query then

SELECT
"iv"."ItemID" AS "No",
"iv"."LastValue" AS "LastValue"
FROM "iv"
WHERE "iv"."TestID" = 100

would give you the same result with no join and you could ask for a sensitive result set which would be even faster.

Roy Lambert

>>Roy Lambert wrote:
>>but unless I'm mistaking something or you've only posted a bit of a query then

Yes only a bit of the query, I attempted to see what was causing the slow speed by removing parts.

BTW: The 'Join' statement works in Zero seconds.

>Raul wrote:
>In general not having indexes can be a culprit so you might also need to
>see if having indexes on iv.ItemID and it.ID helps with speed

I thought a Primary key is used as an index? Does one need PKs and Indexes for the same fields?

thanks
Fred

Fred

>>In general not having indexes can be a culprit so you might also need to
>>see if having indexes on iv.ItemID and it.ID helps with speed
>
>I thought a Primary key is used as an index? Does one need PKs and Indexes for the same fields?

The primary key is there to make sure that each record can be accessed in a unique way. Unique indices are there to make sure you don't duplicate that particular bit of data, other indices are there for sorting or to speed up access.

In genera terms anything that is used in the WHERE clause of a query should have an index for speed. Anything used in the ORDER BY clause should have and index so that you can have a sensitive result set, and if the ORDER BY is a compound one you need a compound index. At this point in its development ASC and DESC indices are different in ElevateDB so you may need one of each.

Roy Lambert

>Roy Lambert wrote:
>In genera terms anything that is used in the WHERE clause of a query should have an index for speed. Anything >used in the ORDER BY clause should have and index so that you can have a sensitive result set, and if the >ORDER BY is a compound one you need a compound index. At this point in its development ASC and DESC >indices are different in ElevateDB so you may need one of each.

Thanks Roy, I am going to assume WHERE includes JOIN ON..

Fred

>>In genera terms anything that is used in the WHERE clause of a query should have an index for speed. Anything >used in the ORDER BY clause should have and index so that you can have a sensitive result set, and if the >ORDER BY is a compound one you need a compound index. At this point in its development ASC and DESC >indices are different in ElevateDB so you may need one of each.
>
>Thanks Roy, I am going to assume WHERE includes JOIN ON..


Good catch - yes both sides of the ON

Roy Lambert