저희 사이트는 html 제너레이터를 이용해서 index.html 을 만들고 있음에도

불구하고 메인의 쿼리가 너무 오래 걸려서 여러게시판에서 골라올때

이렇게 한번 만들어 보았습니다.

이걸 스케줄러로 돌려주고 캐싱테이블에서 뽑아 쓰기 입니다. (실제 테이블입니다.)

여러 다른 디비도 이런식으로 연결해서 해보았는데 좋더군요

혹시라도 쓰고 싶다면 한번 써보세요

고수분은 더 좋은 방법있으면 좀 알려주세요

^^;

여기 질답란 인데 죄송 ~

저희는 디비가 여러대라서

create proc usp_make_Ctbl_mainNews
as
-- drop proc usp_make_Ctbl_mainNews
-- -------------------------------------------------------------------------------------------------------------------
-- 시작
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- 참고 테이블
-- create table Ctbl_articleClass
-- (
-- dep varchar(20)
-- ,articleid varchar(20)
-- ,compcode char(2)
-- ,inputtime datetime
-- ,title varchar(150)
-- ,contents varchar(1000)
-- ,wowcode char(4)
-- ,cnt int
-- )

-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- 캐싱테이블의 멕스 값 + 1
declare @thisCount int
select @thisCount = isnull(max(cnt),0) + 1 from Ctbl_articleClass

-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- 결과값이 너무 많아서 일정량을 끊어온다.
declare @tmp_articleclass TABLE
(
idx int,
articleid varchar(20)
)
-- 중복값 제거를 위한 변수
declare @articleidHeader varchar(20)
declare @contents varchar(1000)
declare @compcode char(2)
declare @sql varchar(1000)
insert into @tmp_articleclass select top 1000 idx,articleid from articleclass order by idx desc

-- 1번
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------

insert into Ctbl_articleClass
select top 1 '증권금융메인타이틀' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx
where (b.wowcode='W001') and (b.title not LIKE '_표]%' and b.title not LIKE '%사진]%')
order by b.ArticleDate desc

-- 중복값 제거를 위한 아이디값 찾기 및 컨텐츠 내용 불러오기
select @articleidHeader=articleid, @compcode = compcode from Ctbl_articleClass where dep ='증권금융메인타이틀' and cnt = @thisCount

set @sql = 'update Ctbl_articleClass set contents = (select substring(Contents,1,1000) from articlelist_'+@compcode+' where articleid ='''+ @articleidHeader+''') where dep =''증권금융메인타이틀'''
exec(@sql)

insert into Ctbl_articleClass
select top 5 '증권금융' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx where (wowcode='W001')
and b.articleid <> @articleidHeader
order by b.articledate desc

-- 2번
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
insert into Ctbl_articleClass
select top 1 '주식시황메인타이틀' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx
where (b.wowcode='W002') and (b.title not LIKE '_표]%' and b.title not LIKE '%사진]%')
order by b.ArticleDate desc

-- 중복값 제거를 위한 아이디값 찾기

select @articleidHeader=articleid, @compcode = compcode from Ctbl_articleClass where dep ='주식시황메인타이틀' and cnt = @thisCount
set @sql = 'update Ctbl_articleClass set contents = (select substring(Contents,1,1000) from articlelist_'+@compcode+' where articleid ='''+ @articleidHeader+''') where dep =''주식시황메인타이틀'''
exec(@sql)

insert into Ctbl_articleClass
select top 5 '주식시황' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx where (wowcode='W002')
and b.articleid <> @articleidHeader
order by b.articledate desc

-- 3번
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
insert into Ctbl_articleClass
select top 1 '업종뉴스메인타이틀' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx
where (b.wowcode='W004') and (b.title not LIKE '_표]%' and b.title not LIKE '%사진]%')
order by b.ArticleDate desc

-- 중복값 제거를 위한 아이디값 찾기
select @articleidHeader=articleid, @compcode = compcode from Ctbl_articleClass where dep ='업종뉴스메인타이틀' and cnt = @thisCount

set @sql = 'update Ctbl_articleClass set contents = (select substring(Contents,1,1000) from articlelist_'+@compcode+' where articleid ='''+ @articleidHeader+''') where dep =''업종뉴스메인타이틀'''
exec(@sql)

insert into Ctbl_articleClass
select top 5 '업종뉴스' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx where (wowcode='W004')
and b.articleid <> @articleidHeader
order by b.articledate desc

-- 4번
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
insert into Ctbl_articleClass
select top 1 '해외뉴스메인타이틀' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx
where (b.wowcode='W008') and (b.title not LIKE '_표]%' and b.title not LIKE '%사진]%')
order by b.ArticleDate desc

-- 중복값 제거를 위한 아이디값 찾기
select @articleidHeader=articleid, @compcode = compcode from Ctbl_articleClass where dep ='해외뉴스메인타이틀' and cnt = @thisCount

set @sql = 'update Ctbl_articleClass set contents = (select substring(Contents,1,1000) from articlelist_'+@compcode+' where articleid ='''+ @articleidHeader+''') where dep =''해외뉴스메인타이틀'''
exec(@sql)

insert into Ctbl_articleClass
select top 5 '해외뉴스' dep , b.articleid,b.compcode, b.ArticleDate ,b.title,'' contents,b.wowcode, @thisCount cnt from
articleclass b
inner join
(
select max(idx) idx,articleid from @tmp_articleclass group by articleid
) a
on a.idx = b.idx where (wowcode='W008')
and b.articleid <> @articleidHeader
order by b.articledate desc

-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- 데이터를 입력했으므로 예전 테이터를 지운다.
delete from Ctbl_articleClass where cnt < (select max(cnt) from Ctbl_articleClass)
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------

-- for test
-- select * from Ctbl_articleClass

exec usp_make_Ctbl_mainNews