안녕하세요~ 무더운 날씨입니다. 궁금한 내용이 있어서요
프로시저를 작성하였고 sqlserver 제공 암호화 한것을 복호화 하여 조인합니다.
프로시져를 직접 실행하면 1초도 안되서 나옵니다. (데이터도 1천건이 안됩니다.)
하지만. asp.net mvc + EnterpriseLibrary 를 통해서 불러오면 타임 아웃 걸립니다.
SqlException (0x80131904)
CommandTimeout = 0 으로 조절하면 불러오긴 하지만 너무 오래 걸립니다.
이것저것 해봐도 모르겠어서 질문 남깁니다.
모니터링 툴로 확인해보면 첨부한 이미지와 같은 내용이 몇 십만건이 발생됩니다.
왜 그런지 힌트좀 주실수 있을까요?
프로시져 내용은 아래와 같습니다.
exec usp_common_openkey;
select * from
(
select top (@nextPage)
ROW_NUMBER() over (order by a.RegDate desc) as RowNumber
, a.RegDate
, e.coName as Ptype
, dbo.uf_DecData(a.GiftNo) as GiftNo
, d.Ptitle
, d.SaleCode
, isnull(a.RemainAmt,0) as RemainAmt
, d.SendMobileNo
, convert(varchar(10) ,a.ExpireDate2, 120) as ExpireDate2
, convert(varchar(10) ,a.ReturnDate, 120) as ReturnDate
, convert(varchar(10) ,(DateAdd(DAY,-7,a.ExpireDate2)), 120) as Daybefore7
, convert(varchar(10) ,(DateAdd(DAY,30,a.ExpireDate2)), 120) as Daylater30
, f.ShopName
, a.GiftState
, b.coName as JongName
, c.coName as GiftStateName
from GiftMst a with (nolock)
left outer join CodeDetail b with (nolock) on (a.Jong = b.coCode and b.coGroup = 12)
join CodeDetail c with (nolock) on (a.GiftState = c.coCode and c.coGroup = 24)
join SaleDtl d with (nolock) on (dbo.uf_DecData(a.GiftNo)= dbo.uf_DecData(d.GiftNo))
left outer join CodeDetail e with (nolock) on (d.Ptype = e.coCode and e.coGroup = 10)
left outer join Shops f with (nolock) on (f.ShopCode = a.ShopCode)
where a.RegDate between convert(datetime, @Sdate + ' 00:00:00', 120) and convert(datetime, @Edate + ' 23:59:59', 120)
and (case when @S_Type = '' then '' else d.Ptype end) = @S_Type
and (case when @JongList = '' then '' else d.Jong end) = @JongList
and (case when @GiftState = '' then '' else a.GiftState end) = @GiftState
and (case @KeyCode
when 'GiftNo' then dbo.uf_DecData(a.GiftNo)
when 'ShopName' then f.ShopName
when 'MobileNo' then d.SendMobileNo
else '' end) like (case when @KeyCode = '' then '' else @Keyword end)
and (
(case when @KeyCode = '' then dbo.uf_DecData(a.GiftNo) else '' end) like (case when @KeyCode = '' then @Keyword else '' end)
or (case when @KeyCode = '' then f.ShopName else '' end) like (case when @KeyCode = '' then @Keyword else '' end)
or (case when @KeyCode = '' then d.SendMobileNo else '' end) like (case when @KeyCode = '' then @Keyword else '' end)
)
) a where RowNumber between @prevPage and @nextPage
Comment 7
-
Hisory
2014.06.09 18:14
-
Hisory
2014.06.09 18:15
sp_executesql 사용해서 쿼리를 만들어보시는것도 좋을듯 합니다. http://technet.microsoft.com/ko-kr/library/ms175170(v=sql.105).aspx
-
jw0130
2014.06.09 18:23
우선 답변 고맙습니다.
sp_executesql은 알고 있지만 아직 해보진 않았습니다.문제는 where절을 모두 제거한 상태로 프로시져를 실행해도 마찬가지라서 문제 입니다.
-
Hisory
2014.06.10 10:56
실행계획을 보실수 있으면 실행계획에서 확인하면
인덱스나 어디서 문제생기는지 알수 있을테고여
만약 실행계획이 힘드시다면.
제일 join 조건절을 전체 제거후
한개씩 추가후 어느테이블 join 할때 문제가 생기는지 살펴보시는것도 방법입니다.
-
jw0130
2014.06.10 13:18
답변 고맙습니다. join table을 변경하면서 성능상의 문제는 해결했습니다.
그리고 또 다른 궁금한건 sqlserver 프로파일러를 실행해보면 프로시저내의 쿼리는 한번만 실행되는거 아닌가요?첨부한 이미지를 보시는거와 같이 프로파일러에 같은 쿼리가 몇 만번에서 혹은 몇 수십만번 실행되는데요
(혹 조인한 데이터 건수만큼 반복하는걸까요?)
-
Hisory
2014.06.10 13:40
한번만 호출하는데 위와같이 여러번 호출되면 분명 문제는 있는거지여
혹시 프로시져 안에서 실수로 재귀 호출을 하고 있는것이 아닌지
혹은 다른 프로그램 혹은 실서버에 배포해서 동시 다발적으로 호출되는건 아닌지 체크해보세요
-
Hisory
2014.06.10 13:41
느낌상 해당 프로시져 위에
exec
usp_common_openkey; 이부분이
재귀 호출을 하고 있는건 아닌가 싶네여..
제가 보기에는 where 조건에 너무많은 조건식및 함수가 들어가 있네여
where 문에 case 문이라도 제거하는 방향으로 쿼리를 만들어보세여...