Optimierung einer Procedure

rebelhig

Benutzer
Beiträge
16
Hallo,

ich könnte mal eueren Rat zu der folgenden Prozedur gebrauchen. Wenn ich die Prozedur ausführe, benötigt die Abfrage bei ca. 33 Rows Ergebnis aus ca. 120 Rows ca 6 Sekunden. Das finde ich doch sehr lange. Kann man hier noch einiges optimieren? Bin halt noch Anfänger;)
Code:
CREATE DEFINER=`root`@`%` PROCEDURE `Test`(inprojectid bigint)
BEGIN
    declare tmp_uid bigint;
    declare tmp_nameid bigint;
    declare tmp_valid bigint;
    declare tmp_part varchar(255);
    declare tmp_value varchar(2048);
    declare tmp_user varchar(255);
    declare tmp_count int default 0;
   
    declare done int default 0;
    declare cur1 cursor for select sg.fk_user_id, sg.name_id, sv.id, sn.signature_partname, sv.signature_value, (select str_username from tbl_user tu where tu.id=sg.fk_user_id) as user  from tbl_signature_value sv
    join tbl_signature sg on sg.value_id = sv.id
    join tbl_signature_name sn on sn.id=sg.name_id
    where sg.project_id=inprojectid order by sg.fk_user_id;
   
    declare continue handler for not found set done=1;
   
    create temporary table if not exists signatures (
        project_id bigint,
        user_id bigint,
        name_projectid bigint,
        name_userid bigint,
        name_phoneid bigint,
        name_faxid bigint,
        project_valueid bigint,
        user_valueid bigint,
        phone_valueid bigint,
        fax_valueid bigint,
        project_value varchar(2048),
        user_value varchar(512),
        phone_value varchar(255),
        fax_value varchar(255),
        fk_user varchar(255),
        index (project_id),
        index (user_id));
   
    open cur1;
    sign_loop: loop
   
        fetch cur1 into tmp_uid, tmp_nameid, tmp_valid, tmp_part, tmp_value, tmp_user;
       
        if done=1 then
            leave sign_loop;
        end if;
       
        if tmp_part = 'project_signature' then
            select count(user_id) into tmp_count from signatures where user_id=tmp_uid;
            if tmp_count = 0 then
                insert into signatures(project_id,user_id,name_projectid,project_valueid,project_value,fk_user)
                    values(inprojectid,tmp_uid,tmp_nameid,tmp_valid,tmp_value,tmp_user);
            else
                #SET SQL_SAFE_UPDATES = 0;
                update signatures
                    set name_projectid = tmp_nameid,
                        project_valueid = tmp_valid,
                        project_value = tmp_value
                            where user_id=tmp_uid and project_id=inprojectid;
                #SET SQL_SAFE_UPDATES = 1;
            end if;
        elseif tmp_part = 'user_signature' then
            select count(user_id) into tmp_count from signatures where user_id=tmp_uid;
            if tmp_count = 0 then
                insert into signatures(project_id,user_id,name_userid,user_valueid,user_value,fk_user)
                    values(inprojectid,tmp_uid,tmp_nameid,tmp_valid,tmp_value,tmp_user);
            else
                #SET SQL_SAFE_UPDATES = 0;
                update signatures
                    set name_userid = tmp_nameid,
                        user_valueid = tmp_valid,
                        user_value = tmp_value
                            where user_id=tmp_uid and project_id=inprojectid;
                #SET SQL_SAFE_UPDATES = 1;
            end if;
        elseif tmp_part = 'telefon' then
            select count(user_id) into tmp_count from signatures where user_id=tmp_uid;
            if tmp_count = 0 then
                insert into signatures(project_id,user_id,name_phoneid,phone_valueid,phone_value,fk_user)
                    values(inprojectid,tmp_uid,tmp_nameid,tmp_valid,tmp_value,tmp_user);
            else
                #SET SQL_SAFE_UPDATES = 0;
                update signatures
                    set name_phoneid = tmp_nameid,
                        phone_valueid = tmp_valid,
                        phone_value = tmp_value
                            where user_id=tmp_uid and project_id=inprojectid;
                #SET SQL_SAFE_UPDATES = 1;
            end if;
        elseif tmp_part = 'fax' then
            select count(user_id) into tmp_count from signatures where user_id=tmp_uid;
            if tmp_count = 0 then
                insert into signatures(project_id,user_id,name_faxid,fax_valueid,fax_value,fk_user)
                    values(inprojectid,tmp_uid,tmp_nameid,tmp_valid,tmp_value,tmp_user);
            else
                #SET SQL_SAFE_UPDATES = 0;
                update signatures
                    set name_faxid = tmp_nameid,
                        fax_valueid = tmp_valid,
                        fax_value = tmp_value
                            where user_id=tmp_uid and project_id=inprojectid;
                #SET SQL_SAFE_UPDATES = 1;
            end if;
        end if;
   
    end loop sign_loop;
    close cur1;
   
    select * from signatures;
    drop table signatures;
       
END

Reiner
 
Werbung:
Ich bin hier echt zu faul, reverse aus dem Quältext dessen Sinn zu disassemblieren. Aber SQL in Schleifen ist fast immer eine tödliche Entscheidung.
 
Hallo,

also erstmal muss ich anmerken das Kommentare innerhalb solcher Scripte echt helfen können ... ;-)
Dann muss ich dich mal fragen welchen Editor benutzt du....?

Und für die "schlechte" Verarbeitungszeit gibt es meiner Meinung nach in deinem Script relativ viele Faktoren, wie zB der Cursor, If ELSE geschachtelt.... vllt. das Joinen, Indizies usw bla blubb....

Mehr Informationen wären hier(so denke ich) hilfreich....

Grüße
 
Der Hintergrund ist der, dass ich 3 Tabellen habe und durch die Abfrage einige Zeilen in Spalten brauche. In der Tabelle "tbl_signature_name" stehen 4 Werte wie 'project_signature', 'user_signature', 'telefon' und 'fax' und die ID's. In der Tabelle 'tbl_signature_value' stehen die Werte zu den Namen aus der der Tabelle 'tbl_signature_name'. In der Tabelle 'tbl_signature' stehen nur die Schlüsselvon beiden Tabellen, sowie Projektid (project_id) und Mitarbeiterid (fk_user_id).
Zum Schluss soll das so aussehen, dass es dann pro Projekt die Zeilen aus der Tabelle 'tbl_signature_name' als Spalten ausgegeben werden mit den dazugehörigen Werten aus 'tbl_signature_value'.
Als Editor benutze ich MySQL Workbench.
Gruß
Reiner
 
Das klingt erst einmal nach einem ganz normalen Join. Wenn Du Zeilen zu Spalten machen willst: dazu hab ich hier auch schon mal eine kleine FAQ geschrieben. Alles in allem bin ich mir recht sicher, das in einem (in Zahlen: 1) SQL machen zu können.
 
Wie finde ich deine FAQ zu dem Thema? Ich finde für eine SQL Abfrage einfach keinen Ansatz aufgrund der 3 Tabellen. Daher kam mir die Prozedur als gangbare Lösung in den Sinn, jedoch kann ich mit der Performance diese nicht als Lösung für uns nehmen.
 
Hallo,

poste doch mal einen Dump (mit Create Table) von deinen Tabellen und die Aufgabenstellung noch mal, dann wird dir bestimmt jemand helfen.

Gruss

Bernd
 
Hallo,

hier mal die Tabellen, von welchen ich die Abfrage machen muss:
Code:
CREATE TABLE IF NOT EXISTS `tbl_signature` (
  `project_id` bigint(20) NOT NULL,
  `name_id` bigint(20) NOT NULL,
  `value_id` bigint(20) NOT NULL,
  `fk_user_id` bigint(20) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `updated` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DELIMITER $$
CREATE TRIGGER `tbl_signature_BEFORE_INSERT` BEFORE INSERT ON `tbl_signature`
FOR EACH ROW BEGIN
    SET NEW.created = NOW();
END
$$
DELIMITER ;

ALTER TABLE `tbl_signature`
  ADD PRIMARY KEY (`project_id`,`name_id`,`value_id`),
  ADD KEY `project_id` (`project_id`),
  ADD KEY `name_id` (`name_id`),
  ADD KEY `value_id` (`value_id`),
  ADD KEY `fk_user_id` (`fk_user_id`);

CREATE TABLE IF NOT EXISTS `tbl_signature_name` (
  `id` bigint(20) NOT NULL,
  `signature_partname` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl_signature_name` (`id`, `signature_partname`) VALUES
(4, 'fax'),
(1, 'project_signature'),
(3, 'telefon'),
(2, 'user_signature');

ALTER TABLE `tbl_signature_name`
  ADD PRIMARY KEY (`id`),
  ADD KEY `signature_partname` (`signature_partname`);

ALTER TABLE `tbl_signature_name`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5; 

CREATE TABLE IF NOT EXISTS `tbl_signature_value` (
  `id` bigint(20) NOT NULL,
  `signature_value` varchar(2048) NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tbl_signature_value`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tbl_signature_value`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREM

Die Daten aus der Tabelle tbl_signature_value und tbl_signature kann ich nicht mitschicken, da das persönliche Daten der Mitarbeiter beinhaltet. Es soll aber letztendlich so aussehen:
zb. aus tbl_signature_name die Id von 'project_signature' 1 in tbl_signature 'name_id',
tbl_signature 'project_id' 25 (sind ca. 90 Einträge mit 25),
tbl_signature 'fk_user_id' 101 (kann pro Projekt maximal 4 mal vorkommen, also für projec_signature, user_signature, telefon und fax),
tbl_signature_value 'id' in tbl_signature 'value_id',
tbl_signature_value 'signature_value' ist dann ein Wert, z. B. eine Telefonnummer oder Text

Die Namen aus der tbl_signature_name sollen dann die Spalten werden, in jeder Zeile steht dann ein Mitarbeiter mit allen Werten aus tbl_signature_value unter den Spaltennamen project-signature, user_signature usw.
Also pro Datensatz ein Mitarbeiter mit project_id, fk_user_id, 4 x Values.

Ich bekomme es mit einer einfachen SQL Anweisung nicht hin, habe schon ein paar mal angesetzt und wieder aufgegeben.
 
mal so als kleine Fingerübung:

Code:
test=*# \d rebelhig
  Table "public.rebelhig"
 Column |  Type  | Modifiers
--------+---------+-----------
 id  | integer | not null
 name  | text  |
Indexes:
  "rebelhig_pkey" PRIMARY KEY, btree (id)
Referenced by:
  TABLE "rebelhig_values" CONSTRAINT "rebelhig_values_id_fkey" FOREIGN KEY (id) REFERENCES rebelhig(id)

test=*# \d rebelhig_values
 Table "public.rebelhig_values"
  Column  |  Type  | Modifiers
----------+---------+-----------
 id  | integer |
 value_id | integer |
 val  | text  |
Foreign-key constraints:
  "rebelhig_values_id_fkey" FOREIGN KEY (id) REFERENCES rebelhig(id)
  "rebelhig_values_value_id_fkey" FOREIGN KEY (value_id) REFERENCES value_names(id)

test=*# select * from rebelhig;
 id |  name
----+--------
  1 | name 1
  2 | name2
(2 rows)

Time: 0,160 ms
test=*# select * from rebelhig_values ;
 id | value_id |  val
----+----------+-----------
  1 |  1 | telefon 1
  1 |  2 | fax 1
  1 |  3 | email 1
  2 |  2 | fax 2
(4 rows)


test=*# select r.name, t.val as "Telefon", f.val as "Fax" from rebelhig r left join rebelhig_values t on r.id=t.id and t.value_id = 1 left join rebelhig_values f on r.id=f.id and f.value_id=2;
  name  |  Telefon  |  Fax
--------+-----------+-------
 name 1 | telefon 1 | fax 1
 name2  |  | fax 2
(2 rows)

Lesen, verstehen, für Dich anpassen - das überlasse ich Dir.
 
Besten Dank erst einmal für die Hilfe. Ich habe es jetzt so ausprobiert:
Code:
select t1.fk_user_id,
     t2.signature_value as project_signature,
     t3.signature_value as user_signature,
  t4.signature_value as telefon,
  t5.signature_value as fax
  from tbl_signature t1
     left join tbl_signature_value t2 on t1.value_id=t2.id and t1.name_id=1
     left join tbl_signature_value t3 on t1.value_id=t3.id and t1.name_id=2
     left join tbl_signature_value t4 on t1.value_id=t4.id and t1.name_id=3
     left join tbl_signature_value t5 on t1.value_id=t5.id and t1.name_id=4
   where t1.project_id=25 order by t1.fk_user_id
Es kommt dem Gesuchten schon sehr nahe, jedoch stehen dann, sofern es 4 Einträge pro Mitarbeiter gibt 4 Zeilen pro Mitarbeiter in meinem Ergebnis.
z.B. Zeile 1:
12, projekt 1 Signature,null , null,null
12,null, user Signature 1, null, null
12,null,null,012345,null
12,null,null,null,06789
Ich muss es jedoch so hinbekommen:
12,projekt 1 Signature,user Signature 1,012345,06789
 
Werbung:
genau,........

Das ist der Grund warum ich gerne einen Dump haben wollte. Es müssen ja keine Originaldaten sein. Man kann
die ja auch ändern.

Du wolltest doch Hilfe von uns beim Query und nicht um Dummy-Daten zu erzeugen mit denen es dann
funktioniert und mit deinen dann wieder nicht.

Also ran.

Gruss

Bernd
 
Zurück
Oben