Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Simple 9k row join over 165secs? |
Sat, Oct 25 2014 6:13 PM | Permanent Link |
Fred Schetterer | 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 ================================================================================ |
Sun, Oct 26 2014 1:07 AM | Permanent Link |
Raul Team Elevate | 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 |
Sun, Oct 26 2014 4:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Oct 26 2014 1:09 PM | Permanent Link |
Fred Schetterer | >>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. |
Sun, Oct 26 2014 1:12 PM | Permanent Link |
Fred Schetterer | >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 |
Mon, Oct 27 2014 3:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Oct 27 2014 1:14 PM | Permanent Link |
Fred Schetterer | >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.. |
Tue, Oct 28 2014 4:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |