안녕하세요,,

하류 실력 항해자™ 입니다;;

 

과연 윈도우 애저를 통해 실서비스를 할 수 있을까??

애저로 어디까지 가능할까??

윈도우 호스팅을 받는게 나을까? 애저를 이용하는게 나을까??

성능은 얼마나 좋은가??

그냥 테스트용으로만 사용해야 하는 건가??

 

궁금한 점이 많았습니다.

 

 

일단 요즘 공부하고 있는 페이징에 관련된 테스트를 진행해 보았습니다.

데이터를 생성하는 부분을 먼저 테스트 해 보았습니다.

  

IF db_id('PAGING_DB') IS NOT NULL
BEGIN
    USE MASTER
    ALTER DATABASE PAGING_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE PAGING_DB
END

CREATE DATABASE PAGING_DB ON PRIMARY
(   NAME = N'PAGING_DATA'
,   FILENAME = N'D:\MSSQL\DATA\PAGING.mdf'
,   SIZE = 100MB
,   MAXSIZE = UNLIMITED
,   FILEGROWTH = 100MB
) LOG ON
(   NAME = N'PAGING_LOG'
,   FILENAME = N'D:\MSSQL\LOG\PAGING.ldf'
,   SIZE = 100MB
,   MAXSIZE = UNLIMITED
,   FILEGROWTH = 100MB
)
GO

USE PAGING_DB
GO
WITH TEMP AS (
    SELECT TOP 1000000
            CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS INT) AS idx
        ,   'PNAME_'+ RIGHT(REPLICATE('0',7)+CAST(CAST(ABS(CHECKSUM(NEWID())) % 1000 AS INT) AS VARCHAR(10)),7)
                    + CHAR(ASCII('A') + ABS(CHECKSUM(NEWID()))%10) AS ProductName
    FROM sys.objects AS A1
    CROSS JOIN sys.objects AS A2
    CROSS JOIN sys.objects AS A3
    CROSS JOIN sys.objects AS A4
    CROSS JOIN sys.objects AS A5
)
SELECT  idx
    ,   ProductName
    ,   CAST(SUBSTRING(ProductName,7,7) AS INT) AS ProductID
    ,   CAST(ABS(CHECKSUM(NEWID())) % 100 AS INT) AS ShopID
    ,   CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) AS Price
    ,   DATEADD(minute, CAST(ABS(CHECKSUM(NEWID())) % 20000 AS INT) * -1, GETDATE()) AS InsertTime
INTO    dbo.TBLx
FROM    TEMP
GO
SELECT *
INTO dbo.TBLv
FROM dbo.TBLx
-- 복합 인덱스
CREATE UNIQUE CLUSTERED INDEX UCL_TBLx ON dbo.TBLx (idx)
CREATE UNIQUE NONCLUSTERED INDEX NC_TBLx_01 ON dbo.TBLx (InsertTime, idx)
CREATE UNIQUE NONCLUSTERED INDEX NC_TBLx_02 ON dbo.TBLx (Price, idx)
CREATE UNIQUE NONCLUSTERED INDEX NC_TBLx_03 ON dbo.TBLx (ProductID, idx)
CREATE UNIQUE NONCLUSTERED INDEX NC_TBLx_04 ON dbo.TBLx (ProductName, idx)
-- 개별 인덱스
CREATE UNIQUE CLUSTERED INDEX UCL_TBLv ON dbo.TBLv (idx)
CREATE NONCLUSTERED INDEX NC_TBLv_01 ON dbo.TBLv (InsertTime)
CREATE NONCLUSTERED INDEX NC_TBLv_02 ON dbo.TBLv (Price)
CREATE NONCLUSTERED INDEX NC_TBLv_03 ON dbo.TBLv (ProductID)
CREATE NONCLUSTERED INDEX NC_TBLv_04 ON dbo.TBLv (ProductName)
GO

 

1분 이상;;

 

헐;; 제 노트북에서 테스트 할 때보다 훨씬 많은 시간이 걸리네요..ㅜ,.ㅠ

SSD를 사용하고 있기는 하지만,,, 서버인데;; 이건 좀,,, 아닌거 같네요;;

 

 

DECLARE
 @rows_per_page int = 10
,@jump_page int = 7
,@insertTime datetime = '2012-11-27 12:53:06.090'
,@idx int = 529105
,@st_row_no int
,@en_row_no int
,@st_time numeric(8,7)
,@en_time numeric(8,7)
SELECT
 @st_row_no = (@rows_per_page * @jump_page) + 1
,@en_row_no = (@rows_per_page * @jump_page) + @rows_per_page
DBCC dropCleanBuffers
DBCC freeProcCache
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
SET @st_time = RIGHT(CONVERT(CHAR(16),SYSDATETIME(),14),9)
SELECT  A.*
FROM    dbo.TBLx AS A
        INNER JOIN (
            SELECT  TOP (@en_row_no)
                    idx
                ,   ROW_NUMBER() OVER(ORDER BY insertTime, idx) AS row_no
            FROM    dbo.TBLx
            WHERE   insertTime >= @insertTime
                AND (   (   insertTime  = @insertTime
                        AND idx         > @idx
                        )
                    OR  (   insertTime  > @insertTime)
                )
            ORDER BY
                    insertTime
                ,   idx
        ) AS B
    ON  A.idx   = B.idx
    AND row_no  BETWEEN @st_row_no AND @en_row_no
ORDER BY
        A.insertTime
    ,   A.idx
SET @en_time = RIGHT(CONVERT(CHAR(16),SYSDATETIME(),14),9)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF
PRINT @en_time - @st_time
/*PLAN 10 1 QUERY 10 1 |--Sort(ORDER BY:([B].[InsertTime] ASC, [PAGING_DB].[dbo].[TBLx].[idx] ASC)) 10 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([PAGING_DB].[dbo].[TBLx].[idx]) OPTIMIZED) 10 1 |--Filter(WHERE:([Expr1003]>=CONVERT_IMPLICIT(bigint,[@st_row_no],0) AND [Expr1003]<=CONVERT_IMPLICIT(bigint,[@en_row_no],0))) 80 1 | |--Top(TOP EXPRESSION:(CONVERT_IMPLICIT(bigint,[@en_row_no],0))) 80 1 | |--Sequence Project(DEFINE:([Expr1003]=row_number)) 80 1 | |--Segment 80 1 | |--Index Seek(OBJECT:([PAGING_DB].[dbo].[TBLx].[NC_TBLx_01]), SEEK:([PAGING_DB].[dbo].[TBLx].[InsertTime] >= [@insertTime]), WHERE:([PAGING_DB].[dbo].[TBLx].[InsertTime]=[@insertTime] AND [PAGING_DB].[dbo].[TBLx].[idx]>[@idx] OR [PAGING_DB].[dbo].[TBLx].[InsertTime]>[@insertTime]) ORDERED FORWARD) 10 10 |--Clustered Index Seek(OBJECT:([PAGING_DB].[dbo].[TBLx].[UCL_TBLx] AS [B]), SEEK:([B].[idx]=[PAGING_DB].[dbo].[TBLx].[idx]) ORDERED FORWARD) */
 
페이징 테스트를 해 보았는데,,, 만족할 만한 성능은 나오지 않네요,,
일하면서 띄엄띄엄 하다보니 결과 값은 빠지고 테스트 스크립트만 올렸;; 쿨럭;;
다음번 캠프에 당첨되면 좀더 납득할 만한 자료도 만들어서 올려야 되겠네요..
기간이 너무 짧아요..ㅜ,.ㅠ