I want to update the values of a joined table which takes about 13
seconds with this statement:

Update Leistungentest set year = year + 1 where AnforderungenID in
(
  select AnforderungenID from Anforderungen where Datum >= date '2013-8-20'
)
From the execution plan:
Row scan (LeistungenTest): 244386 rows, 57,8MB estimated cost


Similar result with this statement:

Update Leistungentest L set year = year + 1 where
EXISTS
(
SELECT AnforderungenID FROM Anforderungen A Where
L.AnforderungenID = A.AnforderungenID and Datum >= date '2013-8-20'
)

I know that I can't use a join with an update statement, but just to
compare:

select * from Leistungentest L
join Anforderungen A
on L.AnforderungenID = A.AnforderungenID
where Datum >= date '2013-8-20'

takes about 0.03 seconds and the execution plan says:
Index scan (Anforderungen.Datum_INX): 727 keys, 12KB estimated cost

How can I speed up the update statement?

P.S. Forgot to say that all indexes are set properly for sure.

Uli

Uli,

Can you post the structure of the tables as well as the indexes ?

--
Fernando Dias
[Team Elevate]

Fernando,

> Can you post the structure of the tables as well as the indexes ?

Sure - here they are:

CREATE TABLE "Anforderungen"
(
"AnforderungenID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH
71096, INCREMENT BY 1) NOT NULL,
"ScheineID" INTEGER,
"AnforderungsID" INTEGER,
"PatientenID" INTEGER,
"PatientenIDDatamed" INTEGER DEFAULT -1  NOT NULL,
"Patientennummer" VARCHAR(10) COLLATE "DEU_CI",
"Datum" DATE,
"Gruppe" VARCHAR(10) COLLATE "DEU_CI",
"Station" VARCHAR(10) COLLATE "DEU_CI",
"Ambulant" BOOLEAN,
"Parameter" CLOB COLLATE "DEU_CI",
"Year" INTEGER,
"LDTFile" CLOB COLLATE "DEU_CI",
"NoInvoice" BOOLEAN DEFAULT false  NOT NULL,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("AnforderungenID")
)
.... (not important indexes)
CREATE INDEX "Datum_INX" ON "Anforderungen" ("Datum" DESC)


CREATE TABLE "LeistungenTest"
(
"LeistungenID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH
1332146, INCREMENT BY 1) NOT NULL,
"AnforderungenID" INTEGER,
"LeistungPrivat" VARCHAR(50) COLLATE "DEU_CI",
"ZifferPrivat" VARCHAR(10) COLLATE "DEU_CI",
"LeistungKasseAmb" VARCHAR(50) COLLATE "DEU_CI",
"ZifferKasseAmb" VARCHAR(10) COLLATE "DEU_CI",
"LeistungKasseStat" VARCHAR(50) COLLATE "DEU_CI",
"ZifferKasseStat" VARCHAR(10) COLLATE "DEU_CI",
"Year" INTEGER,
"Abgerechnet" BOOLEAN DEFAULT false  NOT NULL,
"OrderID" INTEGER DEFAULT 1  NOT NULL,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("LeistungenID")
)
.... (not important indexes)
CREATE INDEX "AnforderungenID_INX" ON "LeistungenTest" ("AnforderungenID")

Thanks Uli

Uli,

Try this one please:

UPDATE
  Leistungentest L
SET
  Year = Year + 1
WHERE
 (SELECT Datum FROM Anforderungen A WHERE A.AnforderungenID = L.AnforderungenID)  >= DATE '2013-8-20'


--
Fernando Dias
[Team Elevate]

Fernando,

> UPDATE
>    Leistungentest L
> SET
>    Year = Year + 1
> WHERE
>   (SELECT Datum FROM Anforderungen A WHERE A.AnforderungenID =
> L.AnforderungenID)  >= DATE '2013-8-20'

The result is the same:

Target Table
------------

Leistungentest: 244386 rows

Filtering
---------

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

(SELECT ALL "Datum" AS "Datum" FROM "Anforderungen" AS "A" WHERE
"A"."AnforderungenID" = "L"."AnforderungenID") >= DATE '2013-8-20'

================================================================================
9436 row(s) updated in 12,481 secs
================================================================================

Regards Uli

Uli,

The issue seems to be that EDB can't use an index on the Anforderungen table to narrow up the selection of the rows to update in the Leistungentest table - in this case an index on Datum column, and I can't think of another way to do this...
How often do you have to run this update statement ?

--
Fernando Dias
[Team Elevate]

Fernando,
> The issue seems to be that EDB can't use an index on the Anforderungen
> table to narrow up the selection of the rows to update in the
> Leistungentest table - in this case an index on Datum column, and I
> can't think of another way to do this...

Yes, I think so, too.
Actually I posted a reduced version of the real query just to make the
issue clear. In my application also two ID's are included and "Year =
Year + 1" was just a sample.

> How often do you have to run this update statement ?

I have to run it quite often. As a workaround I can create a temporary
table with the filtered records of "Anforderungen" and use this table -
but I'm sure there must be a better way.

Thanks anyway...

Uli

> > How often do you have to run this update statement ?
>
> I have to run it quite often. As a workaround I can create a
> temporary table with the filtered records of "Anforderungen" and use
> this table - but I'm sure there must be a better way.

Uli and Fernando,

This has been a very interesting thread for me to watch. I'm still
learning how to translate my Microsoft SQL habits into ElevateDB
habits. Since ElevateDB does not allow inner join updates, would you
please provide an example of how you solve this problem using a
temporary table?

Thank you in advance.

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael,

> This has been a very interesting thread for me to watch. I'm still
> learning how to translate my Microsoft SQL habits into ElevateDB
> habits. Since ElevateDB does not allow inner join updates, would you
> please provide an example of how you solve this problem using a
> temporary table?

Actually using a temporary table doesn't seem to be a real workaround:

SCRIPT
BEGIN

BEGIN
Execute Immediate 'Drop Table "TempAnforderungen"';
EXCEPTION
END;

Execute Immediate 'Create Temporary Table "TempAnforderungen" as
  select AnforderungenID, Datum from Anforderungen where datum >= ?
with data' using date '2013-8-20';

EXECUTE IMMEDIATE 'CREATE INDEX "Anforderungen_INX" ON
"TempAnforderungen" ("AnforderungenID")';

Execute Immediate 'Update Leistungentest set Year = Year + 1 where
AnforderungeniD in
(select AnforderungenID from TempAnforderungen)';

END

though it speeds up the execution time to about 4 seconds. But that's
still way too slow:

Target Table
------------

Leistungentest: 244386 rows

Filtering
---------

The following filter condition was applied to the Leistungentest table:

"AnforderungeniD" IN (SELECT ALL "AnforderungenID" AS "AnforderungenID" FROM
"TempAnforderungen" ORDER BY "AnforderungenID")

Row scan (LeistungenTest): 244386 rows, 57,8MB estimated cost

================================================================================
193 row(s) updated in 3,744 secs
================================================================================

Uli

Michael, Uli

Yeah, I don't think temp tables alone would be a solution...
Perhaps a stored procedure or script using cursors... but I'm just thinking loud...
I'd have to think of it.

--
Fernando Dias
[Team Elevate]