|
안녕하세요. select 문에서 function 사용시 속도가 너무 느립니다.
일반적으로 code 명 가져 오는 함수를 만들어서 사용하는데
아래 query 를 보시면 function 사용시에는 17초 정도 걸리고
function 을 subquery 로 바꾸면 1초만에 나옵니다.
mssql2000 을 사용하고 있으며 고수님들의 도움 부탁드립니다.
아래 query 실행시 17 초 소요. getcodetext2(), getaddrattr() 함수가 있음.
select
isnull(u.DEL_FLAG,'N') AS del_flag,
u.COMPANY_CODE AS COMPANY_CODE,
(case when (u.USER_TYPE = 'S') then a.SELLER_NAME_LOC else c.COMPANY_NAME_LOC end) AS COMPANY_NAME,
u.USER_TYPE AS USER_TYPE,
(case when (u.USER_TYPE = 'S') then getcodetext2('M103',u.USER_TYPE) else c.COMPANY_NAME_LOC end) AS TEXT_USER_TYPE,
u.WORK_TYPE AS WORK_TYPE,
getcodetext2('M104',u.WORK_TYPE) AS TEXT_WORK_TYPE,
u.USER_NAME_LOC AS USER_NAME_LOC,
u.USER_ID AS USER_ID,
(case when (CNV_NULL(u.USER_TYPE,'NULL') = 'S') then getcodetext2('M106',u.POSITION) else getcodetext2('M106',u.POSITION) end) AS POSITION,
(case when (u.USER_TYPE = 'S') then getcodetext2('M107',u.MANAGER_POSITION) else getcodetext2('M107',u.MANAGER_POSITION) end) AS MANAGER_POSITION,
u.DEPT AS DEPT,
(case when (u.USER_TYPE in ('S','P')) then getcodetext2('M105',u.DEPT) else d.DEPT_NAME_LOC end) AS DEPT_NAME,
getaddrattr(u.USER_ID,'3','PHONE_NO1') AS PHONE_NO,
u.MENU_PROFILE_CODE AS MENU_PROFILE_CODE,
u.SIGN_STATUS AS SIGN_STATUS,
(select max(smupd.MENU_NAME) AS max(smupd.MENU_NAME) from smupd where (smupd.MENU_PROFILE_CODE = u.MENU_PROFILE_CODE)) AS MENU_NAME
from (((susmt u left join ssugl a on((u.COMPANY_CODE = a.SELLER_CODE)))
left join scmgl c on((u.COMPANY_CODE = c.COMPANY_CODE)))
left join sdept d on(((u.COMPANY_CODE = d.COMPANY_CODE) and (u.DEPT = d.DEPT)))) where (isnull(u.DEL_FLAG,'N') = 'N')
이것을 아래와 같이 subquery 로 수정했을 경우 1초 걸림.
select
isnull(u.DEL_FLAG,'N') AS del_flag,
u.COMPANY_CODE AS COMPANY_CODE,
(case when (u.USER_TYPE = 'S') then a.SELLER_NAME_LOC else c.COMPANY_NAME_LOC end) AS COMPANY_NAME,
u.USER_TYPE AS USER_TYPE,
(case when (u.USER_TYPE = 'S') then
(SELECT text2
FROM scode
WHERE type = 'M103'
and code = u.user_type
AND ISNULL(del_flag, 'N') = 'N')
else c.COMPANY_NAME_LOC end) AS TEXT_USER_TYPE,
u.WORK_TYPE AS WORK_TYPE,
(SELECT text2
FROM scode
WHERE type = 'M104'
and code = u.WORK_TYPE
AND ISNULL(del_flag, 'N') = 'N') AS TEXT_WORK_TYPE,
u.USER_NAME_LOC AS USER_NAME_LOC,
u.USER_ID AS USER_ID,
(case when (dbo.CNV_NULL(u.USER_TYPE,'NULL') = 'S') then
(SELECT text2
FROM scode
WHERE type = 'M106'
and code = u.POSITION
AND ISNULL(del_flag, 'N') = 'N')
else
(SELECT text2
FROM scode
WHERE type = 'M106'
and code = u.POSITION
AND ISNULL(del_flag, 'N') = 'N')
end) AS POSITION,
(case when (u.USER_TYPE = 'S') then
(SELECT text2
FROM scode
WHERE type = 'M107'
and code = u.MANAGER_POSITION
AND ISNULL(del_flag, 'N') = 'N')
else
(SELECT text2
FROM scode
WHERE type = 'M107'
and code = u.MANAGER_POSITION
AND ISNULL(del_flag, 'N') = 'N')
end) AS MANAGER_POSITION,
u.DEPT AS DEPT,
(case when (u.USER_TYPE in ('S','P')) then
(SELECT text2
FROM scode
WHERE type = 'M105'
and code = u.DEPT
AND ISNULL(del_flag, 'N') = 'N')
else d.DEPT_NAME_LOC end) AS DEPT_NAME,
(select phone_no1 from saddr where code_type = '3' and code_no = u.user_id) AS PHONE_NO,
u.MENU_PROFILE_CODE AS MENU_PROFILE_CODE,
u.SIGN_STATUS AS SIGN_STATUS
from susmt u left join ssugl a on u.COMPANY_CODE = a.SELLER_CODE
left join scmgl c on u.COMPANY_CODE = c.COMPANY_CODE
left join sdept d on u.COMPANY_CODE = d.COMPANY_CODE and u.DEPT = d.DEPT
where (isnull(u.DEL_FLAG,'N') = 'N')
|
