MySQL存储过程执行很慢

[mengyalizuopeng]

DECLARE cursorIn CURSOR FOR SELECT sopinstanceUId FROM dicomentity a WHERE NOT EXISTS(SELECT sopinstanceUId FROM Instance b WHERE a.sopinstanceUId=b.sopinstanceUId );
DECLARE cursorOut CURSOR FOR SELECT sopinstanceUId FROM dicomentity a WHERE EXISTS(SELECT sopinstanceUId FROM Instance b WHERE a.sopinstanceUId=b.sopinstanceUId );


DECLARE CONTINUE HANDLER FOR NOT FOUND SET _v_done = 1;

DELETE FROM move_file;
SELECT CONCAT(savepath,'/') INTO dest_filepath FROM dicom_filePath WHERE pathtype='default';

OPEN cursorIn;

REPEAT

FETCH cursorIn INTO ins_code;

IF NOT _v_done THEN

INSERT INTO instance(instanceNumber,patient_fk ,study_fk ,series_fk ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId)
SELECT instanceNumber,patientId,studyInstanceUID, seriesInstanceUID ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId FROM dicomEntity a
WHERE NOT EXISTS(SELECT 1 FROM dicomentity WHERE a.sopInstanceUID=sopInstanceUID AND a.id<id) AND sopInstanceUID =ins_code;

INSERT INTO dicom_file_info(sOPInstanceUID,fileName,filePath) VALUES (ins_code,dest_filepath,DATE_FORMAT(NOW(),'%Y/%m/%e'));


END IF;
UNTIL _v_done
END REPEAT;
CLOSE cursorIn;

[mengyalizuopeng]

SET done=0; OPEN cursorOut; REPEAT FETCH cursorOut INTO ins_out_code; IF NOT done THEN DELETE FROM instance WHERE sopInstanceUID =ins_out_code; INSERT INTO instance(instanceNumber,patient_fk ,study_fk ,series_fk ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId) SELECT instanceNumber, patientId,studyInstanceUID, seriesInstanceUID ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId FROM dicomEntity a WHERE NOT EXISTS(SELECT 1 FROM dicomentity WHERE a.sopInstanceUID=sopInstanceUID AND a.id<id) AND sopInstanceUID =ins_out_code; INSERT INTO move_file(sopInstanceUID,filePath,createtime) SELECT sopInstanceUID, CONCAT(fileName,filePath,sopInstanceUID),NOW() FROM dicom_file_info WHERE sopInstanceUID=ins_out_code; UPDATE dicom_file_info SET fileName=dest_filepath, filePath=DATE_FORMAT(NOW()) WHERE sopInstanceUID=ins_out_code; END IF; UNTIL done END REPEAT; CLOSE cursorOut; INSERT INTO patient(patientId ,patientName ,PatientBirthDate ,PatientSex ,updateTime,createTime) SELECT patientId ,patientName ,PatientBirthDate ,PatientSex ,updateTime,createTime FROM dicomEntity a WHERE NOT EXISTS (SELECT patientId FROM patient b WHERE a.patientId=b.patientId) GROUP BY patientId ORDER BY createtime DESC; INSERT INTO study(studyId,patient_fk,studyInstanceUID ,accessionNumber, studyDatetime, refPhysician, studyDesc, availability, modality, checkedTime ,updateTime,createTime) SELECT studyId,patientId,studyInstanceUID ,accessionNumber, studyDatetime, refPhysician, studyDesc, availability, modality, checkedTime ,updateTime,createTime FROM dicomEntity a WHERE NOT EXISTS(SELECT studyInstanceUID FROM study b WHERE a.studyInstanceUID=b.studyInstanceUID) GROUP BY studyInstanceUID ORDER BY createtime DESC; INSERT INTO series(seriesNumber ,patient_fk, study_fk ,seriesInstanceUID ,seriesDatetime , seriesDesc , availability , modality , seriesNo , sourceAET , seriesInStudy ,updateTime,createTime) SELECT seriesNumber,patientId,studyInstanceUID,seriesInstanceUID ,seriesDatetime , seriesDesc , availability , modality , seriesNo , sourceAET , seriesInStudy ,updateTime,createTime FROM dicomEntity a WHERE NOT EXISTS(SELECT seriesInstanceUID FROM series b WHERE a.seriesInstanceUID=b.seriesInstanceUID) GROUP BY seriesInstanceUID ORDER BY createtime DESC; DELETE FROM dicomEntity ; COMMIT; END

[mengyalizuopeng]

论坛还不让我 一次提交，分两次提交，并且有些特殊字符不让提交我删了，大概流程就这样。只有四五条数据也要20几秒，大神帮我看看什么问题

[rick-he]

你可以将每条sql单独explain下，找下那个有问题，或者将explain结果贴下

[ACMAIN_CHM]

这个只能逐句来找，看看问题出在哪一句。