빠른 페이징 쿼리문

xxx = pagesize * (pageno-1) + 1
 
sql = "SELECT TOP "&pagesize&" * from [테이블] WHERE idx <= (SELECT MIN(idx) FROM (SELECT
TOP "&xxx&" idx FROM [테이블] order by idx desc) AS A) order by idx desc"
 
rs.open sql, db,3
 
제가 쓰는 방법인데... 대충 고쳐서 쓰세요..ㅡ,.ㅡ;;
 
not in 보다는 3배정도 빠르더군요... 50만개정도 디비에서 돌리는데 .. 인덱스도 같이 걸어주면 별무리는 없어요...

예제코드

 page = ParseInt(request("page"), 1)
 int_nv = 10
 int_ps = 15
 int_st = int(page / int_nv) + 1
 if page mod int_nv = 0 then  int_st = int_st - 1
 int_st = (int_st - 1) * int_nv + 1
' int_minus = IIF(page = 1, page - 1, page - 2)
 int_dp = (page - 1) * int_ps + 1

 srch_opt = NullToString(ReplaceTo(request("srch_opt"), "toTXT"), "")
 srch_val = NullToString(ReplaceTo(request("srch_val"), "toTXT"), "")

' nDate = Date
 s_year = NullToString(request("s_year"), "")
 s_month = NullToString(request("s_month"), "")
 s_day = NullToString(request("s_day"), "")
 if s_year <> "" and s_month <> "" and s_day <> "" then _
  s_date = dateserial(s_year, s_month, s_day)
 e_year = NullToString(request("e_year"), "")
 e_month = NullToString(request("e_month"), "")
 e_day = NullToString(request("e_day"), "")
 if e_year <> "" and e_month <> "" and e_day <> "" then _
  e_date = dateserial(e_year, e_month, e_day)

 return_value = "&m=bbs&s="& s &"&srch_opt="& srch_opt &"&srch_val="& srch_val &"&s_year="& s_year &"&s_month="& s_month &"&s_day="& s_day &"&e_year="& e_year &"&e_month="& e_month &"&e_day="& e_day

 table_name = "gallery_tbl"

 ' ordey by를 하는 값에 주의하라!!
 sql = "select top "& int_ps &" seq, guid, event_type, title, event_date, visit from "& table_name &" "
 sql = sql &"where seq <= (select min(seq) from (select top "& int_dp &" seq from "& table_name &" where seq > 0 "
 if srch_opt <> "" then  sql = sql &"and "& srch_opt &" like '%"& srch_val &"%' "
 if s_date <> "" and e_date <> "" then sql = sql &"and event_date between '"& s_date &"' and '"& e_date &"' "
 sql = sql &"order by event_date desc) as a "
 sql = sql &") "& vbNewLine
 if srch_opt <> "" then  sql = sql &"and "& srch_opt &" like '%"& srch_val &"%' "
 if s_date <> "" and e_date <> "" then sql = sql &"and event_date between '"& s_date &"' and '"& e_date &"' "
 sql = sql &"order by event_date desc"