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
Reiner
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;
ENDReiner
