Appel, Sascha wrote:
> Hi.
>
> We are using SapDB version 7.3 Build 18 on a Windows NT
> machine with 512MB
> of RAM and a Raid5 HD-Subsystem. The database instance is set
> up as OLTP.
> Besides some other problems we are facing massive performance
> issues using
> stored procedures.(Called by PHP using ODBC) The database
> itself is quite
> small (a backup takes about 40 Meg) but as soon as we execute
> the below
> dbproc, our CPU usage raises to 100% while executing the
> procedure. Even
> worse, the execution takes about 2-3 minutes to complete. Due
> to the high
> system usage, the database server is unusable during this timeperiod.
> Now the questions are:
> - Is there any "easy to see" big error in the dbproc we
> haven't found yet ?
> - In theory, is the database able to perform other transactions while
> executing a stored procedure ?
> - Is there any kernel parameter that could affect the speed ?
>
> Thanks in advance,
> Sascha
>
> --------------------SNIP--------------------
> CREATE DBPROC "I_CREATE_PROFILES" (IN OBJID INTEGER, IN UID
> INTEGER, IN
> ID_FS_DIRECTORY INTEGER, IN ID_QUARKDOC INTEGER, IN ID_THEME
> INTEGER, IN EDU
> INTEGER ) AS
> VAR
> ID_METAVARIABLE INTEGER;
> ID_INST_METAVARIABLE INTEGER;
> ID_DIALOGPAGE INTEGER;
> ID_NEWVARIABLE INTEGER;
> ID_NEWINSTANCE INTEGER;
> CNT INTEGER;
> STRING CHAR(200);
> ADDSTRING CHAR(200);
> NAME CHAR(64);
>
> CREATE TABLE "TEMP"."I_CREATE_PROFILES" (
> "RESULT" INTEGER
> );
>
> SELECT COUNT("ID") FROM "RUHRGAS"."USEROBJECTS" WHERE "ID" = :OBJID;
> FETCH INTO :CNT;
>
> IF CNT = 0 THEN BEGIN
>
> INSERT INTO "RUHRGAS"."USEROBJECTS"
> ("ID", "UID", "ID_EDU", "ID_FS_DIRECTORY", "C_DATE",
> "C_TIME", "M_DATE",
> "M_TIME", "RELEASED", "ID_LAST_ORDER" )
> VALUES (:OBJID, :UID, :EDU, :ID_FS_DIRECTORY, DATE, TIME,
> DATE, TIME,
> TRUE, 0);
> END
> ELSE BEGIN
> UPDATE "RUHRGAS"."USEROBJECTS"
> ( "UID", "ID_EDU", "ID_FS_DIRECTORY", "RELEASED",
> "ID_LAST_ORDER" )
> VALUES ( :UID, :EDU, :ID_FS_DIRECTORY, FALSE, 0 )
> KEY "ID" = :OBJID;
> END;
>
> /* THESE ARE THE IDS OF ALL METAVARS THAT ARE AFFECTED */
> DECLARE CMETAVARIABLES CURSOR FOR
> SELECT DISTINCT "DIALOG_META"."ID_METAVARIABLE",
> "DIALOG_META"."ID_DIALOGPAGE", "METAVARIABLES"."NAME" FROM
> "RUHRGAS"."DIALOG_META", "RUHRGAS"."METAVARIABLES"
> WHERE "DIALOG_META"."ID_DIALOGPAGE" IN
> (SELECT "ID" FROM "RUHRGAS"."DIALOGPAGE" WHERE "ID_THEME" = :ID_THEME)
> AND "DIALOG_META"."ID_METAVARIABLE" IS NOT NULL
> AND "METAVARIABLES"."NAME" LIKE '%profil%'
> AND "METAVARIABLES"."ID" = "DIALOG_META"."ID_METAVARIABLE";
>
> FETCH CMETAVARIABLES INTO :ID_METAVARIABLE, :ID_DIALOGPAGE, :NAME;
>
> WHILE ($RC = 0) DO BEGIN
> SELECT COUNT ("INST_METAVARIABLES"."ID") INTO :CNT FROM
> "RUHRGAS"."INST_METAVARIABLES", "RUHRGAS"."INSTANCES"
> WHERE "INST_METAVARIABLES"."ID_ORIG" = :ID_METAVARIABLE
> AND "INST_METAVARIABLES"."ID" IN
> (SELECT "ID_INST_METAVARIABLE" FROM "RUHRGAS"."INSTANCES"
> WHERE "OBJID"
> = :OBJID);
> IF (CNT = 0) THEN BEGIN
> /* GET NEXT META INST ID */
> SELECT SEQ_INST_METAVARIABLES.NEXTVAL INTO
> :ID_INST_METAVARIABLE
> FROM SYSDBA.DUAL;
> INSERT INTO "RUHRGAS"."INST_METAVARIABLES" ("ID",
> "UID", "ID_ORIG")
> VALUES (:ID_INST_METAVARIABLE, :UID, :ID_METAVARIABLE);
>
> UPDATE "RUHRGAS"."INST_METAVARIABLES"
> SET ("TOP", "LEFT", "WIDTH", "HEIGHT", "ID_ACTION_TYPE") =
> (SELECT "TOP", "LEFT", "WIDTH", "HEIGHT", "ID_ACTION_TYPE"
> FROM "RUHRGAS"."METAVARIABLES" WHERE "ID" = :ID_METAVARIABLE)
> KEY "ID" = :ID_INST_METAVARIABLE;
> /* PROFIL2 = UNTERNEHMEN, 3 = ABTEILUNG (DERZEIT
> LEER), 4 = STRA�E +
> HAUSNUMMER, 5 = PLZ + STADT */
> SET STRING = '';
> /* GERERATE THE VARIABLE WITH THE DEFAULT */
> IF ( NAME = '_profil2_' ) THEN BEGIN
> SELECT "FIRMA" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil3_' ) THEN BEGIN
> SELECT "ABTEILUNG" INTO :STRING FROM "RUHRGAS"."EDU" WHERE
> "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil4_' ) THEN BEGIN
> SELECT "STRASSE" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil5_' ) THEN BEGIN
> SELECT "CPLZ" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> SELECT "ORT" INTO :ADDSTRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> SET STRING = STRING & ' ' & ADDSTRING;
> END
> ELSE IF ( NAME = '_profil6_' ) THEN BEGIN
> SELECT "CPLZ" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil7_' ) THEN BEGIN
> SELECT "ORT" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil8_' ) THEN BEGIN
> SELECT "CPLZ" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> SELECT "ORT" INTO :ADDSTRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> SET STRING = STRING & ' ' & ADDSTRING;
> END
> ELSE IF ( NAME = '_profil10_' ) THEN BEGIN
> SELECT "NOMINATIV" INTO :STRING FROM "RUHRGAS"."EDU" WHERE
> "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil11_' ) THEN BEGIN
> SELECT "GENITIV" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil12_' ) THEN BEGIN
> SELECT "DATIV" INTO :STRING FROM "RUHRGAS"."EDU" WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil13_' ) THEN BEGIN
> SELECT "AKKUSATIV" INTO :STRING FROM "RUHRGAS"."EDU" WHERE
> "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil14_' ) THEN BEGIN
> SELECT "ART_NOMINATIV" INTO :STRING FROM "RUHRGAS"."EDU"
> WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil15_' ) THEN BEGIN
> SELECT "ART_GENITIV" INTO :STRING FROM "RUHRGAS"."EDU" WHERE
> "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil16_' ) THEN BEGIN
> SELECT "ART_DATIV" INTO :STRING FROM "RUHRGAS"."EDU" WHERE
> "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil17_' ) THEN BEGIN
> SELECT "ART_AKKUSATIV" INTO :STRING FROM "RUHRGAS"."EDU"
> WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil18_' ) THEN BEGIN
> SELECT "POSSESIVPRONOM" INTO :STRING FROM "RUHRGAS"."EDU"
> WHERE "ID" = :EDU;
> END
> ELSE IF ( NAME = '_profil100_' ) THEN BEGIN
> SELECT "VERB_KOOPERIEREN" INTO :STRING FROM "RUHRGAS"."EDU"
> WHERE "ID" =
> :EDU;
> END
> ELSE IF ( NAME = '_profil101_' ) THEN BEGIN
> SELECT "VERB_UNTERSTUETZEN" INTO :STRING FROM "RUHRGAS"."EDU"
> WHERE "ID" =
> :EDU;
> END
> ELSE IF ( NAME = '_profil102_' ) THEN BEGIN
> SELECT "VERB_PRAESENTIEREN" INTO :STRING FROM "RUHRGAS"."EDU"
> WHERE "ID" =
> :EDU;
> END
> ELSE IF ( NAME = '_profil103_' ) THEN BEGIN
> SELECT "VERB_LADEN" INTO :STRING FROM "RUHRGAS"."EDU" WHERE
> "ID" = :EDU;
> END;
>
> SELECT SEQ_INST_VARIABLES.NEXTVAL INTO :ID_NEWVARIABLE FROM
> SYSDBA.DUAL;
>
> INSERT INTO "RUHRGAS"."INST_VARIABLES" ("ID", "UID", "CONTENT")
> VALUES (:ID_NEWVARIABLE, :UID, :STRING);
>
> UPDATE "RUHRGAS"."INST_METAVARIABLES" ("ID_INST_VARIABLE") VALUES
> (:ID_NEWVARIABLE)
> KEY "ID" = :ID_INST_METAVARIABLE;
>
> /* NOW GENERATE THE INSTANCE RECORD */
> SELECT SEQ_INSTANCES.NEXTVAL INTO :ID_NEWINSTANCE FROM SYSDBA.DUAL;
>
> INSERT INTO "RUHRGAS"."INSTANCES" ("ID", "OBJID", "UID",
> "ID_QUARKDOC",
> "ID_THEME", "ID_DIALOGPAGE", "ID_INST_METAVARIABLE")
> VALUES (:ID_NEWINSTANCE, :OBJID, :UID, :ID_QUARKDOC, :ID_THEME,
> :ID_DIALOGPAGE, :ID_INST_METAVARIABLE);
>
> UPDATE "RUHRGAS"."INSTANCES"
> SET ("CHOOSABLE") = (SELECT "CHOOSABLE" FROM "RUHRGAS"."METAVARIABLES"
> WHERE "ID" = :ID_METAVARIABLE)
> KEY "ID" = :ID_NEWINSTANCE;
> END;
> FETCH CMETAVARIABLES INTO :ID_METAVARIABLE, :ID_DIALOGPAGE, :NAME;
> END;
> --------------------------- SNIP ---------------------------
Some comments:
- do not use UPDATE ... KEY if you want to code standard-SQL
- do not code UPDATE .. VALUES if you want to code standard-SQL
The standard is like this:
UPDATE <target table> SET <set clause list> [ WHERE <search condition> ]
- you select the nextval of a sequence and then uses it twice.
why don't you say .NEXTVAL for the first usage and .CURRVAL for the
second?
- did you check these select each by its own and have a look at the
explain-result?
I do not know if there are indexes on some of those columns
(METAVARIABLES.ID
or ID_THEME for example) which could improve speed.
- I do not see the need for the temp table created in the beginning.
- I do not understand, what you really want to do, therefore I cannot tell
you
anything about a way which may be better for receiving the result you
want.
(But, please, do not send more info to allow me to understand. I will not
have time
for this.)
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
