In DBISAM the following query takes 4.422 seconds but in EDB it is
taking 41.844 seconds.

   SELECT
     MQ_DocumentTranslations.DisplayName,
     MQ_Documents.Document_GUID,
     MQ_Documents.IncludeHeader,
     MQ_Documents.IncludeFooter,
     MQ_Documents.IncludeSignaturePage,
     MQ_Documents.IncludeTagLine,
     MQ_Documents.IncludePatientNameSection,
     MQ_Documents.IncludeCaregiverSection,
     MQ_Documents.IncludeFollowupSection,
     MQ_Documents.IncludeAdditionalNoteSection,
     MQ_Documents.IsForm,
     MQ_Documents.UseForDemo,
     mq_Documents.ForDemoOnly,
     mq_Documents.datalevel,
     mq_Documents.ref
   FROM MQ_Documents
     INNER JOIN MQ_DocumentTranslations ON (MQ_Documents.Document_GUID
= MQ_DocumentTranslations.Document_GUID)
   WHERE
     (MQ_DocumentTranslations.Language_GUID =
'{F3E5BA5E-87CE-428F-88B9-C705189C07C0}')
     AND (MQ_DocumentTranslations.Active=true)
     AND (MQ_Documents.Active=true)


any ideas?

Bern

--

Bern,

<< In DBISAM the following query takes 4.422 seconds but in EDB it is taking
41.844 seconds. >>

Could you post the query plan for EDB ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Bern,
>
> << In DBISAM the following query takes 4.422 seconds but in EDB it is
> taking 41.844 seconds. >>
>
> Could you post the query plan for EDB ?

Sure here it is. BTW I just did a migrate from DBISAM for the tables,
have not touched them in any other way.

I am also including the SQL to create the 2 tables



========================================================================
========
SQL Query (Executed by ElevateDB 1.00 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
"MQ_DocumentTranslations"."DisplayName" AS "DisplayName",
"MQ_Documents"."Document_GUID" AS "Document_GUID",
"MQ_Documents"."IncludeHeader" AS "IncludeHeader",
"MQ_Documents"."IncludeFooter" AS "IncludeFooter",
"MQ_Documents"."IncludeSignaturePage" AS "IncludeSignaturePage",
"MQ_Documents"."IncludeTagLine" AS "IncludeTagLine",
"MQ_Documents"."IncludePatientNameSection" AS
"IncludePatientNameSection",
"MQ_Documents"."IncludeCaregiverSection" AS "IncludeCaregiverSection",
"MQ_Documents"."IncludeFollowupSection" AS "IncludeFollowupSection",
"MQ_Documents"."IncludeAdditionalNoteSection" AS
"IncludeAdditionalNoteSection"

"MQ_Documents"."IsForm" AS "IsForm",
"MQ_Documents"."UseForDemo" AS "UseForDemo",
"mq_Documents"."ForDemoOnly" AS "ForDemoOnly",
"mq_Documents"."datalevel" AS "datalevel",
"mq_Documents"."ref" AS "ref"
FROM "MQ_Documents" INNER JOIN "MQ_DocumentTranslations" ON
("MQ_Documents"."Document_GUID" =
"MQ_DocumentTranslations"."Document_GUID")
WHERE ("MQ_Documents"."Active" = true)
WHERE ("MQ_DocumentTranslations"."Language_GUID" =
'{F3E5BA5E-87CE-428F-88B9-C705189C07C0}') AND
("MQ_DocumentTranslations"."Active" = true)

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

MQ_Documents: 3244 rows
MQ_DocumentTranslations: 7563 rows

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

The result set was static
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the MQ_Documents table:

("MQ_Documents"."Active" = true [Index scan: 2981 keys, 49152 bytes
estimated
cost])

The following filter condition was applied to the
MQ_DocumentTranslations table:

("MQ_DocumentTranslations"."Language_GUID" =
'{F3E5BA5E-87CE-428F-88B9-C705189C07C0}') AND
("MQ_DocumentTranslations"."Active" = true) [Index scan: 3414 keys,
53248 bytes estimated cost]

Joins
-----

The driver table was the MQ_Documents table

The MQ_Documents table was joined to the MQ_DocumentTranslations table
with the
inner join expression:

("MQ_Documents"."Document_GUID" =
"MQ_DocumentTranslations"."Document_GUID")

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

The joins were already in the most optimal order

The following join condition was applied to the MQ_DocumentTranslations
table:

("MQ_DocumentTranslations"."Document_GUID" =
"MQ_Documents"."Document_GUID"
[Index scan])

========================================================================
========
3244 row(s) returned in 43.75 secs
========================================================================
========



==============================
CREATE TABLE "MQ_Documents"
(
"Document_GUID" CHAR(38) COLLATE "ANSI" DEFAULT CURRENT_GUID,
"Document_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH
13763, INCREMENT BY 1),
"DocumentName" CHAR(255) COLLATE "ANSI_CI",
"State_GUID" CHAR(38) COLLATE "ANSI",
"IncludeHeader" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludeFooter" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludeSignaturePage" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludeTagLine" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludePatientNameSection" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludeCaregiverSection" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludeFollowupSection" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludeAdditionalNoteSection" BOOLEAN DEFAULT TRUE  NOT NULL,
"IncludeDrugSection" BOOLEAN DEFAULT True  NOT NULL,
"IsForm" BOOLEAN DEFAULT FALSE  NOT NULL,
"UseForDemo" BOOLEAN DEFAULT FALSE  NOT NULL,
"ForDemoOnly" BOOLEAN DEFAULT FALSE  NOT NULL,
"DocumentInformationVerified" BOOLEAN DEFAULT FALSE  NOT NULL,
"Active" BOOLEAN DEFAULT TRUE  NOT NULL,
"DateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"AddedBy" CHAR(50) COLLATE "ANSI",
"DateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"ModifiedBy" CHAR(50) COLLATE "ANSI",
"DateDeactivated" TIMESTAMP,
"DeactivatedBy" CHAR(50) COLLATE "ANSI",
"DataLevel" INTEGER DESCRIPTION '0 - MedQuest, 1 - Global, 2 -
Facility, 3 - Dept - Global, 4 - Dept - Facility, 5 - User',
"REF" CHAR(77) COLLATE "ANSI" DESCRIPTION '0 - MedQuest - Null, 1 -
Global - Null, 2 - Facility - Faclility GUID, 3 - Dept - Global -
Departmen',
"SafeDXID" INTEGER,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("Document_GUID"),
CONSTRAINT "DocumentName" UNIQUE ("DocumentName"),
CONSTRAINT "Document_ID" UNIQUE ("Document_ID")
)
VERSION 6.146
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
  
CREATE INDEX "Active" ON "MQ_Documents"
("Active")
===================================
CREATE TABLE "MQ_DocumentTranslations"
(
"DocumentTranslation_GUID" CHAR(38) COLLATE "ANSI" DEFAULT CURRENT_GUID,
"DocumentTranslation_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 26507, INCREMENT BY 1),
"Document_GUID" CHAR(38) COLLATE "ANSI",
"Language_GUID" CHAR(38) COLLATE "ANSI",
"DisplayName" CHAR(255) COLLATE "ANSI",
"DocumentFileName" CHAR(255) COLLATE "ANSI",
"DocumentText" CLOB COLLATE "ANSI" COMPRESSION 9,
"ForYou" CLOB COLLATE "ANSI",
"RevisedDocument" BOOLEAN DEFAULT FALSE  NOT NULL,
"Step" SMALLINT DEFAULT 1  NOT NULL,
"Priority" SMALLINT DEFAULT 2  NOT NULL,
"FKLevel" FLOAT,
"CheckedOut" CHAR(38) COLLATE "ANSI",
"DocumentNotes" CLOB COLLATE "ANSI",
"Active" BOOLEAN DEFAULT TRUE  NOT NULL,
"DateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"AddedBy" CHAR(50) COLLATE "ANSI",
"DateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP  NOT NULL,
"ModifiedBy" CHAR(50) COLLATE "ANSI",
"DateDeactivated" TIMESTAMP,
"DeactivatedBy" CHAR(50) COLLATE "ANSI",
"DeactivationReason" CHAR(255) COLLATE "ANSI",
"DateCreated" DATE,
"DateReleased" DATE,
"DateReReleased" DATE,
"DateReviewed" TIMESTAMP,
CONSTRAINT "Check_Step" CHECK ("Step" >= -1 AND "Step" <= 6),
CONSTRAINT "Check_Priority" CHECK ("Priority" >= 1 AND "Priority" <= 3),
CONSTRAINT "PrimaryKey" PRIMARY KEY ("DocumentTranslation_GUID"),
CONSTRAINT "DocumentTranslation_ID" UNIQUE ("DocumentTranslation_ID")
)
VERSION 6.146
ENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
  
CREATE INDEX "Document_GUID" ON "MQ_DocumentTranslations"
("Document_GUID" COLLATE "ANSI")
  
CREATE INDEX "Language_GUID" ON "MQ_DocumentTranslations"
("Language_GUID" COLLATE "ANSI")
  
CREATE INDEX "DisplayName" ON "MQ_DocumentTranslations"
("DisplayName" COLLATE "ANSI_CI")
  
CREATE INDEX "Active" ON "MQ_DocumentTranslations"
("Active")
  
CREATE INDEX "ModifiedBy" ON "MQ_DocumentTranslations"
("ModifiedBy" COLLATE "ANSI")

--

Bern,

<< Sure here it is. BTW I just did a migrate from DBISAM for the tables,
have not touched them in any other way.

I am also including the SQL to create the 2 tables >>

Very odd.  Could you send me the actual tables ?  There's something
happening that isn't being reflected in the query plan.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Bern,
>
> << Sure here it is. BTW I just did a migrate from DBISAM for the
> tables, have not touched them in any other way.
>
> I am also including the SQL to create the 2 tables >>
>
> Very odd.  Could you send me the actual tables ?  There's something
> happening that isn't being reflected in the query plan.

Posted to binaries, about 46 meg in size, I will email you the password
in an email. The subject is "Files requested by Tim From Bern"

--

Bern


You really need to get a new newsreader. I mean 192 posts.

Roy Lambert

Roy Lambert wrote:

> Bern
>
>
> You really need to get a new newsreader. I mean 192 posts.
>
> Roy Lambert

I only made 1 post, XanaNews split it up, did not now it was going to
do that, sorry.

--

Bern


That's really kind of it isn't it

Roy Lambert