/*
---------------------------------------------------------------------
  MCPWorld 28ȸ
  "ÀúÀå ÇÁ·Î½ÃÀú ¼º´É ÃÖÀûÈ­" ¼¼¹Ì³ª µ¥¸ð ÀÚ·á


  ¹ßÇ¥ÀÏ: 2006-05-30
  ÀÛ¼ºÀÚ: ±èÁ¤¼±, Çʶó³Ý DB»ç¾÷ºÎ ¼ö¼® ÄÁ¼³ÅÏÆ®
                  »ï¼ºSDS ¸ÖƼķÆÛ½º ÀüÀÓ±³¼ö
---------------------------------------------------------------------
*/
USE Credit


/*
  Data Áغñ¿ë ½ºÅ©¸³Æ®
*/

select member_no, count(*)
from dbo.charge
group by member_no
order by 2 desc


update dbo.charge
	set member_no = 10000
where member_no >= 9960	and member_no < 10000


update statistics charge


DBCC SHOW_STATISTICS (charge, nc_member_no)


-- i/o ºñ±³, 
SET STATISTICS IO ON

  -- ¼±Åõµ ³·Àº Ư¼ö ȸ¿ø
  select * from dbo.charge where member_no = 10000
  
  -- ¼±Åõµ ³ôÀº ÀÏ¹Ý È¸¿ø
  select * from dbo.charge where member_no = 8850

SET STATISTICS IO OFF


/*
Cache View¿ë
*/
SELECT LEFT(sql, 30), objtype, cacheobjtype, usecounts, pagesused, setopts, sqlbytes, *
FROM master.dbo.syscacheobjects
WHERE dbid = db_id('credit')
  AND objid > 1000



/*
---------------------------------------------------------------------
  1Àå 
---------------------------------------------------------------------
*/



/*
	Test Äõ¸®-1
*/
SELECT DB_ID()
DBCC FLUSHPROCINDB(7)

-- 1) Adhoc
SELECT DISTINCT 
  p.*, t.*, c.*, s.*
FROM provider p
  INNER JOIN charge c
    ON p.provider_no = c.provider_no
  INNER JOIN category t
    ON t.category_no = c.category_no
  INNER JOIN statement AS s
    ON c.member_no = s.member_no
WHERE	c.member_no = 10000


SELECT DISTINCT 
   p.*, t.*, c.*, s.*
FROM provider p
	INNER JOIN charge c
		ON p.provider_no = c.provider_no
	INNER JOIN category t
		ON t.category_no = c.category_no
	INNER JOIN statement AS s
   	ON c.member_no = s.member_no
WHERE	c.member_no = 8850


-- 2) Prepared
EXECUTE sp_executesql N'SELECT DISTINCT 
  p.*, t.*, c.*, s.*
FROM provider p
	INNER JOIN charge c
		ON p.provider_no = c.provider_no
	INNER JOIN category t
		ON t.category_no = c.category_no
	INNER JOIN statement AS s
   	ON c.member_no = s.member_no
WHERE	c.member_no = @member_no'
, N'@member_no int'
, @member_no = 8850


EXECUTE sp_executesql N'SELECT DISTINCT 
  p.*, t.*, c.*, s.*
FROM provider p
	INNER JOIN charge c
		ON p.provider_no = c.provider_no
	INNER JOIN category t
		ON t.category_no = c.category_no
	INNER JOIN statement AS s
   	ON c.member_no = s.member_no
WHERE	c.member_no = @member_no'
, N'@member_no int'
, @member_no = 10000


  

-- 3) Proc
DROP PROC dbo.up_GetChargewithMember
GO

CREATE PROC dbo.up_GetChargewithMember
	@member_no int
AS
  SET NOCOUNT ON

  SELECT DISTINCT 
     p.*, t.*, c.*, s.*
  FROM dbo.provider p
  	INNER JOIN dbo.charge c
  		ON p.provider_no = c.provider_no
  	INNER JOIN dbo.category t
  		ON t.category_no = c.category_no
  	INNER JOIN dbo.statement AS s
     	ON c.member_no = s.member_no
  WHERE	c.member_no = @member_no 
GO


-- Cache È®ÀÎ
SELECT db_id('credit'), db_id('northwind')

SELECT * FROM master..syscacheobjects
WHERE dbid IN (7)
	AND objid > 100

DBCC FLUSHPROCINDB(7)
DBCC FREEPROCCACHE


EXEC dbo.up_GetChargewithMember @member_no = 10000 
  WITH RECOMPILE
EXEC dbo.up_GetChargewithMember @member_no = 8850	-- 9850
  WITH RECOMPILE
EXEC dbo.up_GetChargewithMember @member_no = 5514	-- 9850
  WITH RECOMPILE
EXEC dbo.up_GetChargewithMember @member_no = 4887	-- 9850
  WITH RECOMPILE
GO




/*
  ÀúÀå ÇÁ·Î½ÃÀú vs. Proc ºñ±³-Ãß°¡
*/
  -- TOP¿¡ µû¸¥ µ¿Àû Äõ¸®¿¡ ´ëÇؼ­ º°µµ·Î º¸¿©ÁÙ °Í.
  EXECUTE sp_executesql N'SELECT TOP 20 
    p.*, t.*, c.*, s.*
  FROM provider p
  	INNER JOIN charge c
  		ON p.provider_no = c.provider_no
  	INNER JOIN category t
  		ON t.category_no = c.category_no
  	INNER JOIN statement AS s
     	ON c.member_no = s.member_no
  WHERE	c.member_no = @member_no'
  , N'@member_no int'
  , @member_no = 10000


/*
  ¸ð´ÏÅ͸µ
*/
DBCC CACHESTATS

DBCC PROCCACHE

DBCC MEMORYSTATUS







/*
---------------------------------------------------------------------
  2Àå 
---------------------------------------------------------------------
*/

/*
  ÆĶó¹ÌÅÍ °³Ã¼ ÀÚµ¿ »ý¼º
*/
-- Á¢±Ù Å×À̺í: sysobject, syscolumns, systypes, syscomments, spt_provider
exec [credit]..sp_procedure_params_rowset N'up_GetChargewithMember', 1, NULL, NULL



/*
  ¾Ï½ÃÀûÀÎ ÆĶó¹ÌÅÍ Çü½Ä »ç¿ë
*/


/*
  ¾Ï½ÃÀûÀÎ ¼ÒÀ¯ÀÚ ÁöÁ¤, sp_ Á¢µÎ»ç ÇÁ·Î½ÃÀú
*/
DROP PROC dbo.sp_GetChargewithMember
GO

CREATE PROC dbo.sp_GetChargewithMember
	@member_no int
AS
  SET NOCOUNT ON

  SELECT DISTINCT 
     p.*, t.*, c.*, s.*
  FROM dbo.provider p
  	INNER JOIN dbo.charge c
  		ON p.provider_no = c.provider_no
  	INNER JOIN dbo.category t
  		ON t.category_no = c.category_no
  	INNER JOIN dbo.statement AS s
     	ON c.member_no = s.member_no
  WHERE	c.member_no = @member_no 
GO




/*
------------------------------------------------------
  ÀçÄÄÆÄÀÏ
*/

/*
  ÀÚµ¿ ÀçÄÄÆÄÀÏ

  Âü°í. profiler¿Í ÇÔ²² ¸ð´ÏÅÍ 
    - Recompile À̺¥Æ®
    - EventSubClass, ObjectName Ãß°¡
    - CacheHit Á¦¿Ü
*/
DROP TABLE mycharge

SELECT * INTO mycharge
FROM charge
GO

DROP PROC dbo.up_Recompile
GO
CREATE PROC dbo.up_Recompile
  @charge_no int
AS
  SELECT member_no, count(*) FROM dbo.mycharge
  WHERE charge_no <= @charge_no
  GROUP BY member_no
GO

  -- KEEPFIXED PLAN¿ë
  DROP PROC dbo.up_Recompile2
  GO
  CREATE PROC dbo.up_Recompile2
    @charge_no int
  AS
    SELECT member_no, count(*) FROM dbo.mycharge
    WHERE charge_no <= @charge_no
    GROUP BY member_no
    OPTION (KEEPFIXED PLAN)
  GO


EXEC dbo.up_Recompile 100
EXEC dbo.up_Recompile 10
GO
  EXEC dbo.up_Recompile2 10
  EXEC dbo.up_Recompile2 100


-- ½ºÅ°¸¶ º¯°æ ½Ã
ALTER TABLE mycharge
  ADD recomp int
GO
EXEC dbo.up_Recompile 100


-- Á¦¾àÁ¶°Ç, À妽º Ãß°¡ ½Ã
ALTER TABLE mycharge
  ADD PRIMARY KEY NONCLUSTERED (charge_no)
GO

EXEC dbo.up_Recompile 100


-- À妽º Ãß°¡ ½Ã
CREATE CLUSTERED INDEX CL_dt
ON mycharge (charge_dt)
GO

EXEC dbo.up_Recompile 100 

  -- 
  SELECT TOP 1000 charge_no FROM mycharge ORDER BY charge_no DESC

-- Åë°è º¯°æ ½Ã
UPDATE mycharge
  SET charge_dt = charge_dt + 1
WHERE charge_no >= (100000 - 50000) -- 21+%
GO

EXEC dbo.up_Recompile 100 
GO
  EXEC dbo.up_Recompile2 100

  SELECT name, rows, rowmodctr FROM credit..sysindexes
  WHERE id = object_id('credit..mycharge')



-- DRN
DROP PROC dbo.RecompDRN
GO
CREATE PROCEDURE dbo.RecompDRN
AS
	CREATE TABLE #t (a int)
	SELECT * FROM #t
GO

EXEC RecompDRN
GO


-- Session ¿É¼Ç
DROP PROC dbo.SEToption
GO

CREATE PROC dbo.SEToption
AS
	set quoted_identifier off			-- no recompile
	select * from Northwind.dbo.Orders

	SET CONCAT_NULL_YIELDS_NULL OFF	-- recompile
	select * from Northwind.dbo.Orders

	SET NUMERIC_ROUNDABORT ON	-- recompile
	select * from Northwind.dbo.Orders

	SET ANSI_WARNINGS OFF			-- recompile
	select * from Northwind.dbo.Orders
GO

EXEC dbo.SEToption


-- DDL/DML ±³Â÷ ½ÇÇà
DROP PROCEDURE Interleave
go
CREATE PROCEDURE dbo.Interleave
AS
	CREATE TABLE dbo.#t1 (a int , b char(5))
	select a, sum(a) from dbo.#t1 a join Northwind.dbo.Orders b
		on a.a = b.orderid
	group by a
	order by a

	CREATE INDEX nc_#t1_a on #t1(a)
	select a, sum(a) from dbo.#t1 a join Northwind.dbo.Orders b
		on a.a = b.orderid
	group by a
	order by a

	CREATE TABLE dbo.#t2 (a int , b char(5))
	select a, sum(a) from dbo.#t2 a join Northwind.dbo.Orders b
		on a.a = b.orderid
	group by a
	order by a
GO

EXEC dbo.Interleave
GO
EXEC dbo.Interleave








/*
  ¼öµ¿ ÀçÄÄÆÄÀÏ

  - Perfmon
*/
-- Proc
DROP PROC dbo.up_GetChargewithMember_Recompile
GO

CREATE PROC dbo.up_GetChargewithMember_Recompile
	@member_no int
WITH RECOMPILE
AS
SELECT DISTINCT 
   p.*, t.*, c.*, s.*
FROM provider p
	INNER JOIN charge c
		ON p.provider_no = c.provider_no
	INNER JOIN category t
		ON t.category_no = c.category_no
	INNER JOIN statement AS s
   	ON c.member_no = s.member_no
WHERE	c.member_no = @member_no 
GO

EXEC up_GetChargewithMember_Recompile 10000


-- EXEC ... WITH
EXEC up_GetChargewithMember 10000

EXEC up_GetChargewithMember 8850
  WITH RECOMPILE



-- sp_recompile proc , table
EXEC up_GetChargewithMember 8850
EXEC up_GetChargewithMember 10000

-- 1. proc
EXEC sp_recompile up_GetChargewithMember

EXEC up_GetChargewithMember 10000

-- 2. table
EXEC sp_recompile [dbo.Charge]





/*
  SARG
*/
DROP PROC dbo.up_4
GO
CREATE PROC dbo.up_4
  @ds datetime
, @dd datetime
AS
  DECLARE @s datetime
  SET @s = @ds + 7

  SELECT * FROM Northwind.dbo.Orders
  WHERE OrderDate BETWEEN @ds AND @s

  SELECT * FROM Northwind.dbo.Orders
  WHERE OrderDate BETWEEN @ds AND (@ds + 7)
GO

-- ½ÇÇà °èȹ ºñ±³
EXEC dbo.up_4 '19960710', '19960717'



/*
  parameter sniffing
*/
USE Credit

-- ±âº» Á¦°øµÇ´Â ÇÁ·Î½ÃÀú
DROP PROC dbo.SalesByDate
GO
CREATE PROCEDURE dbo.SalesByDate
	@B_Date DateTime, @E_Date DateTime 
AS
SELECT *
FROM Northwind.dbo.Orders
WHERE OrderDate Between @B_Date AND @E_Date
GO

EXEC dbo.SalesByDate '19960710', '19960930'
GO
EXEC dbo.SalesByDate '19960710', '19960711'
  WITH RECOMPILE


/*
  parameter vs. variable
*/
DROP PROC dbo.ParamVsVar
GO

CREATE PROC dbo.ParamVsVar
  @no int
AS
  -- 1. 
  SELECT * FROM dbo.charge 
  WHERE member_no = @no


  -- 2. 
  DECLARE @v_no int
  SET @v_no = @no

  SELECT * FROM dbo.charge 
  WHERE member_no = @v_no
GO

EXEC dbo.ParamVsVar 10000



/*
  parameter ¿¡ µû¶ó ´Ù¸¥ ½ÇÇà °èȹ
*/
DROP PROC dbo.ParamCondi
GO

CREATE PROC dbo.ParamCondi
  @id int
, @date datetime
AS
  IF @id IS NOT NULL
    SELECT * FROM Northwind.dbo.Orders
    WHERE OrderID = @id
  ELSE
    SELECT * FROM Northwind.dbo.Orders
    WHERE OrderDate <= @date
GO

EXEC dbo.ParamCondi 10248, NULL
  WITH RECOMPILE
GO
EXEC dbo.ParamCondi NULL, '19960930'
  WITH RECOMPILE




/*
  µ¿Àû °Ë»ö Á¶°Ç ó¸®
*/
-- sp_executesql ÀÇ °æ¿ì
DROP PROC dbo.up_SearchOrders
GO
CREATE PROCEDURE dbo.up_SearchOrders
  @orderid   int          = NULL
, @fromdate  datetime     = NULL
, @todate    datetime     = NULL
, @prodname  nvarchar(40) = NULL
, @debug     int          = 0
AS                                                                                                
	DECLARE @sql       nvarchar(4000)
      , @paramlist  nvarchar(4000)                                     
                                                                        
	SELECT @sql =                                                          
	 'SELECT *             
	  FROM   Northwind.dbo.Orders o                                                   
	  JOIN   Northwind.dbo.[Order Details] od ON o.OrderID = od.OrderID               
	  JOIN   Northwind.dbo.Products p ON p.ProductID = od.ProductID                   
	  WHERE  1 = 1'                                                     
	                                                                    
	-- °Ë»öÁ¶°Ç Á¶ÇÕ
	IF @orderid IS NOT NULL                                                
		SELECT @sql = @sql + ' AND o.OrderID = @xorderid'
	                                                                    
	IF @fromdate IS NOT NULL                                            
		SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'             
	                                                                    
	IF @todate IS NOT NULL                                              
		SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'               
	                                                                    	                                                                    
	IF @prodname IS NOT NULL                                            
		SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' 
	                                                                    
	SELECT @sql = @sql + ' ORDER BY o.OrderID DESC'                          

	-- µð¹ö±ë
	IF @debug = 1                                                          
		PRINT @sql                                                          
	                                                                    
	-- ÆĶó¹ÌÅÍ Á¤º¸
	SELECT @paramlist = '@xorderid   int,                                  
	                  @xfromdate  datetime,                             
	                  @xtodate    datetime,                             
	                  @xprodname  nvarchar(40)'                         

	-- ½ÇÇà
	EXEC sp_executesql @sql, @paramlist,                                  
	                @orderid, @fromdate, @todate, @prodname  
go


-- ¼­·Î ´Ù¸¥ Äõ¸® Çü½ÄÀÇ °æ¿ì
EXEC dbo.up_SearchOrders @orderid = 10248

EXEC dbo.up_SearchOrders @fromdate = '19960710', @todate = '19960711'
  , @prodname = 'M'

EXEC dbo.up_SearchOrders @prodname = 'M'



-- °°Àº Äõ¸® Çü½Ä¿¡¼­ ¼±Åõµ°¡ ´Ù¸¥ °æ¿ì
-- Àç»ç¿ë ¹®Á¦
EXEC dbo.up_SearchOrders @fromdate = '19960710', @todate = '19961031'
  , @prodname = 'M'

-- recompile ¿É¼ÇÀÌ Àû¿ëµÉ±î?
EXEC dbo.up_SearchOrders @fromdate = '19960710', @todate = '19961031'
  , @prodname = 'M'
  WITH RECOMPILE

-- ÇÁ·Î½ÃÀú¸íÀ¸·Î
EXEC sp_recompile [up_SearchOrders]

-- table·Î
USE Northwind
EXEC sp_recompile [Orders]

USE Credit

  -- ¾Æ·¡´Â Âü°í¿ë
  SELECT *             
  	  FROM   Northwind.dbo.Orders o                                                   
  	  JOIN   Northwind.dbo.[Order Details] od ON o.OrderID = od.OrderID               
  	  JOIN   Northwind.dbo.Products p ON p.ProductID = od.ProductID                   
  	  WHERE  1 = 1 AND o.OrderDate >= '19960710' AND o.OrderDate <= '19961031' 
      ORDER BY o.OrderID DESC