直接执行的话只要2分钟多一点，当我把这段sql转换成存储过程，如：exec [RUN_Daily_Report_by_Channel_Month_City] '2014-10-21',1却跑了20分钟都没有出结果，我分段传上来，最后又一段展示的对结果印象不大我就不传了。我研究下来应该是卡在了按年处理这块，但是没想通为啥放在外面执行可以很快，放在存储过程里执行就会那么慢，之前在建索引那块代码是分别在createtime和mamaid上建聚集和非聚集索引的，那样的话要跑3-4分钟，现在改成在把2个字段都建在聚集索引上了就出问题了。
DECLARE @Month_income DATETIME,@Year_income DATETIME,@InputDate DATETIME,@param INT--计算当月第一天,计算当年第一天
SET @Month_income=CONVERT(DATETIME,(CONVERT(NVARCHAR,(YEAR(@InputDate)))+'-'+CONVERT(NVARCHAR,(MONTH(@InputDate)))+'-'+'1'))
SET @Year_income =CONVERT(DATETIME,(CONVERT(NVARCHAR,(YEAR(@InputDate)))+'-01-01'))
SET @InputDate='2014-10-21'
SET @param=1
SET @InputDate = CONVERT(DATETIME,@InputDate)
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Daily_Mother'))
BEGIN
DROP TABLE #Daily_Mother
END
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#LogDetail_Clup'))
BEGIN
DROP TABLE #LogDetail_Clup
END
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#LogDetail_ttnmc'))
BEGIN
DROP TABLE #LogDetail_ttnmc
END
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#LogDetail_BarCode'))
BEGIN
DROP TABLE #LogDetail_BarCode
END
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#LogDetail_Team'))
BEGIN
DROP TABLE #LogDetail_Team
END
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#LogDetail_coolpj'))
BEGIN
DROP TABLE #LogDetail_coolpj
END
--SR(商场首次),MA(妈妈首次),PRO(医务首次)取3个渠道的总数据
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#New_Income'))
BEGIN
DROP TABLE #New_Income
END
--CREATE TABLE #New_Income (MARK NVARCHAR(100),MamaID NVARCHAR(50),JoinDate DATETIME)
--SR
--INSERT INTO #New_Income ( MARK ,MamaID ,JoinDate)
SELECT mamaid,CreateTime,ErrorDescription INTO #LogDetail_Clup FROM [10.237.70.205].crm_import.[dbo].[LogDetail_Clup]
WHERE ISNULL(mamaid,'')<>'' AND CreateTime >= '2014-01-01' AND CreateTime<@InputDate+1
SELECT mamaid,CreateTime,ErrorDescription,ChannelName INTO #LogDetail_ttnmc FROM [10.237.70.205].crm_import.[dbo].[LogDetail_ttnmc]
WHERE ISNULL(mamaid,'')<>'' AND ISNULL(mamaid,'')<>'' AND CreateTime >= '2014-01-01' AND CreateTime<@InputDate+1
SELECT mamaid,CreateTime,ErrorDescription,WXOpenID,MA_City,HCPNo,RecommendCode INTO #LogDetail_BarCode FROM
[10.237.70.205].crm_import.dbo.LogDetail_BarCode WHERE CreateTime >= '2014-01-01' AND CreateTime<@InputDate+1
SELECT mamaid,CreateTime,ErrorDescription INTO #LogDetail_Team FROM [10.237.70.205].crm_import.dbo.LogDetail_Team
WHERE ISNULL(Mamaid,'')<>'' AND CreateTime >= '2014-01-01' AND CreateTime<@InputDate+1
SELECT mamaid,CreateTime,ErrorDescription INTO #LogDetail_coolpj FROM [10.237.70.205].crm_import.dbo.LogDetail_coolpj
WHERE ISNULL(mamaid,'')<>'' AND CreateTime >= '2014-01-01' AND CreateTime<@InputDate+1
--INSERT INTO #Details ( MARK ,MamaID ,JoinDate)
SELECT 'SR' AS MARK ,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
into #Details
FROM #LogDetail_Clup WHERE ISNULL(mamaid,'')<>''
UNION ALL
SELECT 'SR' AS MARKK ,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
FROM #LogDetail_ttnmc WHERE ChannelName<>'医生推荐' AND ISNULL(mamaid,'')<>''
UNION ALL
SELECT 'SR' AS MARK ,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
FROM #LogDetail_BarCode WHERE ISNULL(WXOpenID,'')<>'' AND ISNULL(RecommendCode,'')<>''
AND ISNULL(mamaid,'')<>''
--MA
UNION ALL
SELECT 'MA' AS MARK ,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
FROM #LogDetail_Team WHERE ISNULL(Mamaid,'')<>''
UNION ALL
SELECT 'MA' AS MARK ,Mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
FROM #LogDetail_BarCode WHERE ISNULL(MA_City,'')<>'' AND ISNULL(mamaid,'')<>''
--PRO
UNION ALL
SELECT 'PRO' AS MARK ,Mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
FROM #LogDetail_ttnmc WHERE ChannelName='医生推荐'AND ISNULL(mamaid,'')<>''
UNION ALL
SELECT 'PRO' AS MARK ,Mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
FROM #LogDetail_BarCode WHERE ISNULL(WXOpenID,'')<>'' AND ISNULL(HCPNo,'')<>''
AND ISNULL(mamaid,'')<>''
UNION ALL
SELECT 'PRO' AS MARK ,Mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) AS JoinDate
FROM #LogDetail_coolpj WHERE ISNULL(mamaid,'')<>''
SELECT 'SR' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME) as joindate
into #New_Income
FROM #LogDetail_Clup WHERE ISNULL(mamaid,'')<>'' AND ErrorDescription IN ('新增资料','超龄资料','新增不合规资料')
UNION
SELECT 'SR' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME)
FROM #LogDetail_ttnmc WHERE ChannelName<>'医生推荐' AND ISNULL(mamaid,'')<>'' AND ErrorDescription IN ('超龄资料','新增不合规资料','新增资料')
UNION
SELECT 'SR' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME)
FROM #LogDetail_BarCode WHERE ISNULL(WXOpenID,'')<>'' AND ISNULL(RecommendCode,'')<>'' AND ErrorDescription IN ('新增数据')
--MA
union
SELECT 'MA' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME)
FROM #LogDetail_Team WHERE ISNULL(Mamaid,'')<>'' AND LEFT(ErrorDescription,4)='成功导入'
UNION
SELECT 'MA' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME)
FROM #LogDetail_BarCode WHERE ISNULL(MA_City,'')<>'' AND ISNULL(mamaid,'')<>'' AND ErrorDescription IN ('新增数据')
--PRO
union
SELECT 'PRO' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME)
FROM #LogDetail_ttnmc WHERE ChannelName='医生推荐'AND ISNULL(mamaid,'')<>'' AND ErrorDescription IN ('超龄资料','新增不合规资料','新增资料')
UNION
SELECT 'PRO' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME)
FROM #LogDetail_BarCode WHERE ISNULL(WXOpenID,'')<>'' AND ISNULL(HCPNo,'')<>'' AND ErrorDescription IN ('新增数据')
AND ISNULL(mamaid,'')<>''
UNION
SELECT 'PRO' AS MARK,mamaid ,CAST(SUBSTRING(CONVERT(NVARCHAR,CreateTime),1,10) AS DATETIME)
FROM #LogDetail_coolpj WHERE ISNULL(mamaid,'')<>'' AND ErrorDescription ='新增资料'
--根据mother表取宝宝生日，
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Mother'))
BEGIN
DROP TABLE #Mother
END
--CREATE TABLE #Mother (Site1 NVARCHAR(100),Site2 NVARCHAR(100),MARK NVARCHAR(100),MamaID NVARCHAR(50),Monthage INT ,FirstSource NVARCHAR(2) , Frozen NVARCHAR(2),JoinDate DATETIME )
--INSERT INTO #Mother(Site1,Site2,MARK,MamaID,Monthage,FirstSource,Frozen,JoinDate)
SELECT Site1,Site2,'MOTHER' AS MARK,mamaid,
CASE WHEN DATEDIFF(DD,bbirthday,JoinDate)<0 THEN 1 --孕期
WHEN DATEDIFF(DD,bbirthday,JoinDate) BETWEEN 0 AND 365 THEN 2 --宝宝0-12M
ELSE 3 END AS Monthage --宝宝12M+
,FirstSource,Frozen,CAST(SUBSTRING(CONVERT(NVARCHAR,JoinDate),1,10) AS DATETIME) AS JoinDate
into #Mother
FROM CRM.dbo.v_mother WHERE JoinDate >= '2014-01-01' AND JoinDate<@InputDate+1
SELECT a.Site1,a.Site2,MARK,a.MamaID ,a.Monthage,a.FirstSource,a.Frozen,a.JoinDate AS CreateTime INTO #Daily_Mother
FROM #Mother a
SELECT a.MARK,a.MamaID,a.JoinDate AS CreateTime INTO #Daily_Channel
FROM #New_income a
SELECT a.MARK,a.MamaID,a.JoinDate AS CreateTime INTO #Daily_ALL
FROM #Details a
IF EXISTS (SELECT name from tempdb.sys.indexes
WHERE name = N'IX_#Daily_Mother')
DROP INDEX IX_#Daily_Mother ON #Daily_Mother
CREATE CLUSTERED INDEX IX_#Daily_Mother
ON #Daily_Mother(CreateTime,mamaid)
-- IF EXISTS (SELECT name from sys.indexes
-- WHERE name = N'IX1_#Daily_Mother')
-- DROP INDEX IX1_#Daily_Mother ON #Daily_Mother
--CREATE NONCLUSTERED INDEX IX1_#Daily_Mother
-- ON #Daily_Mother(mamaid)
SELECT Site1 ,
Site2 ,
MARK ,
mamaid ,
Monthage ,
FirstSource ,
Frozen ,
CreateTime FROM #Daily_Mother
SELECT * FROM #Daily_Channel
IF EXISTS (SELECT name from tempdb.sys.indexes
WHERE name = N'IX_#Daily_Channel')
DROP INDEX IX_#Daily_Channel ON #Daily_Channel
CREATE CLUSTERED INDEX IX_#Daily_Channel
ON #Daily_Channel(CreateTime,mamaid)
-- IF EXISTS (SELECT name from sys.indexes
-- WHERE name = N'IX1_#Daily_Channel')
-- DROP INDEX IX1_#Daily_Channel ON #Daily_Channel
--CREATE NONCLUSTERED INDEX IX1_#Daily_Channel
-- ON #Daily_Channel(mamaid)
IF EXISTS (SELECT name from tempdb.sys.indexes
WHERE name = N'IX_#Daily_ALL')
DROP INDEX IX_#Daily_ALL ON #Daily_ALL
CREATE CLUSTERED INDEX IX_#Daily_ALL
ON #Daily_ALL(CreateTime,mamaid)
-- IF EXISTS (SELECT name from sys.indexes
-- WHERE name = N'IX1_#Daily_ALL')
-- DROP INDEX IX1_#Daily_ALL ON #Daily_ALL
--CREATE NONCLUSTERED INDEX IX1_#Daily_ALL
-- ON #Daily_ALL(mamaid)
------解决思路----------------------
补充一下，直接执行的话，它会重新编译，而存储过程是先判断有没有已经编译好的执行计划，存在的话，不会再编译，直接按照原先以编译好的计划执行。。