Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Optimierung einer Procedure

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von rebelhig, 24 Juni 2015.

  1. rebelhig

    rebelhig Benutzer

    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
     
  2. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  3. Chuky666

    Chuky666 Datenbank-Guru

    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
     
  4. rebelhig

    rebelhig Benutzer

    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
     
  5. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  6. ukulele

    ukulele Datenbank-Guru

    Also im Prinzip MySQL Join mit GROUP_CONCAT oder wie das unter MySQL hieß.
     
  7. akretschmer

    akretschmer Datenbank-Guru

    keiner versteht mich, außer @ukulele ;-)
     
  8. ukulele

    ukulele Datenbank-Guru

    Ja nun, Schleifen können Sinn machen aber meistens nerven sie nur.
     
  9. rebelhig

    rebelhig Benutzer

    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.
     
  10. akretschmer

    akretschmer Datenbank-Guru

  11. BerndB

    BerndB Datenbank-Guru

    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
     
  12. rebelhig

    rebelhig Benutzer

    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.
     
  13. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  14. rebelhig

    rebelhig Benutzer

    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
     
  15. BerndB

    BerndB Datenbank-Guru

    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
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden