mssql에서 대량의 db 데이터 쿼리 시 사용하는 방법

오늘은 asp 에서 mssql의 db 쿼리 시 데이터의 양이 적을 때는 상관이 없지만, 데이터양이 많은 db 데이터 쿼리 시 페이지징을 해도 그 속도가 매우 느리다는 사실을 아시게 될겁니다.

 

오라클, mysql이야 rownum 이 있어서 이걸 이용하면 해당 레코드만 땡겨올 수 있지만 mssql 은 그렇지 못하다는 사실을 아시는 분은 그리 많지 않습니다~

 

아래의 기본적인 asp에서의 쿼리를 보시면

 

sql = "select * from aaa "

rs.open sql,dbcon,1

i=0

rs.pagesize=f_pgsize

totalpage=rs.pagecount

trcount=rs.recordcount

if trcount > 0 then

rs.absolutepage=int(f_page)

pgcount=totalpage

n=trcount - (int(f_page)-1) * f_pgsize

end if

 

do until rs.eof or i>(int(f_pgsize)-1) 

 

...

 

rs.movenext

loop

rs.close

 

위에서 보듯이 rs.open sql,dbcon,1 에서 ,1 을 해줘서 레코드 크기을 받을 수 있는데 이때 문제가 발생을 하게 됩니다. 즉 mssql은 레코드가 매우 많으면 이 단계에서 멍때리기 시작합니다. 그래서 총 레코드 크기를 받게 되면 rs.absolutepage=int(f_page) 을 통해 현재 표시할 레코드를 정하게 되는데 이 이전에 얘기한 rs.open sql,dbcon,1 에서 부하가 너무 많다는 겁니다.

 

그래서 asp에서 강제로 rownum 을 구현해서 오라클에서 처럼 해당 위치의 정확한 레코드셋만을 가져와야 합니다.

 

아래는 그 방법이 가능한 함수를 구현해 놓은 것입니다.

 

단 중요한 사항은 레코드개수가 몇 백 개 안되는 곳에서 사용하는 것이 아니며. 

최소한 몇만개 이상의 레코드가 있는 db 테이블에서만 구현하시는게 좋습니다.

레코드수가 적은데서는 오히려 역효과가 납니다.

 

* mssql에서 rownunm 구현

 

Function GetSqlTotalCountQuery(parm_sql, parm_where)

s = ""

s = s & "   select  count(*) as count "

s = s & "       from  "

s = s & "         ( " &  parm_sql

s = s & "           " &  parm_where

s = s & "         ) as query_1"

GetSqlTotalCountQuery = s

End Function

 

Function GetSqlTotalPage(trcount, f_pgsize)

if(trcount = 0) then

GetSqlTotalPage = 0

else

GetSqlTotalPage = int( cdbl( trcount / f_pgsize ) + 0.999999999 )

end if

 

End Function

 

Function GetSqlPagingQuery(parm_sql, parm_where, parm_order_by, parm_curr_page, parm_page_size )

 

if trim(parm_order_by) = ""  then 

is_order_by = " over( order by (select 0) ) " 

is_order_by2 = "  " 

else

is_order_by = " over( order by " & parm_order_by & ") "

is_order_by2 = "  order by " & parm_order_by & " " 

end if

 

firstrow = (int(parm_curr_page) -1)*int(parm_page_size) + 1

lastrow = firstrow + parm_page_size - 1

s = ""

s = s & "select * from "

s = s & " ("

s = s & "   select  row_number() "&is_order_by&" as row , query_1.* "

s = s & "       from  "

s = s & "         ( " & parm_sql

s = s & "           " & parm_where

s = s & "         ) as query_1 "

s = s & "  ) as query_2"

s = s & " where row between " &firstrow& " and " &lastrow

GetSqlPagingQuery = s

End Function

 

 

위 3개의 함수가 미리 만들어놓아야 합니다. 내용에서 핵심은

 

해당 쿼리의 총레코드수를 구해서  (GetSqlTotalCountQuery)  현재 페이지의 위치를 찾아서 (GetSqlTotalPage) rownum 쿼리를 구현한다. (GetSqlPagingQuery)

 

입니다.

 

그래서 구현한 샘플코드는

 

'==================================================================================================

' total record

sql_cnt = GetSqlTotalCountQuery(sql, "")

rs.open sql_cnt,dbcon

if rs.eof<>true Then trcount = rs("count") Else trcount = 0 End If 'total record cnt

rs.close

 

' page count

totalpage=GetSqlTotalPage(trcount,f_pgsize) 'total page cnt

pgcount=totalpage 

 

'리스트(페이징) SQL

sql = GetSqlPagingQuery(sql, "", sql_order, f_page, f_pgsize )

rs.open sql,dbcon

do until rs.eof 

 

....

 

rs.movenext

loop

rs.close

 

'==================================================================================================

 

위와 같이 구현할 수 있습니다.

 

저같은 경우 위 코드를 넣고 해 보았을 때 일반적인 방식보다 훨씬 빠른 결과를 도출할 수 있고., db의 부하도 줄어듭니다.