高分求优化一条sql语句

Question

select * from (
select * from (
select * from (
select distinct 
replace(convert(varchar, gamelog.intime, 102),'.','-') as date1,
count(*) as vistCount
from 
gamelog  
where 
intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11
group by  
replace(convert(varchar, gamelog.intime, 102),'.','-')
) as ta right join (
select distinct 
replace(convert(varchar, gamelog.intime, 102),'.','-') as date2,
count(distinct iptvName) as userCount
from 
gamelog  
where 
intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11
group by  
replace(convert(varchar, gamelog.intime, 102),'.','-')
) as tb on date1=date2
)as ab left join (
select distinct 
replace(convert(varchar, gamelog.intime, 102),'.','-') as date3,
cast(sum(datediff(s,outtime,intime)) /3600.00 as decimal(18,2)) as playTime 
from 
gamelog  
where 
intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11
group by  
replace(convert(varchar, gamelog.intime, 102),'.','-')
) as tc  on date2=date3
) as abc left join (
select replace(convert(varchar, Ta.intime, 102),'.','-')as date4,COUNT(Ta.iptvName) as newUserCount 
from (select *,rownum= ROW_NUMBER ()over(partition by iptvName order by intime) 
  from gamelog 
  where intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11 and 
iptvName not in (select distinct iptvName from gamelog where intime<'2011-06-01 00:00:00') )as Ta 
where Ta.rownum =1 group by  replace(convert(varchar, Ta.intime, 102),'.','-')
)as td on date2=date4数据：
logId,factoryId,gameId,intime,outtime
971811,8,23,2011-06-02 15:36:11.210,2011-06-02 15:36:10.420
971812,8,22,2011-06-02 15:39:03.487,2011-06-02 15:39:02.530
971813,8,22,2011-06-02 15:41:17.630,2011-07-02 15:41:17.630
971814,8,-1,2011-06-02 15:41:23.660,2011-07-02 15:41:23.660
971815,8,22,2011-06-02 15:42:04.027,2011-07-02 15:42:04.027
971816,8,21,2011-06-02 15:45:02.083,2011-06-02 15:45:01.203
971817,8,19,2011-06-02 15:49:55.947,2011-07-02 15:49:55.947
971818,8,19,2011-06-02 15:50:21.693,2011-07-02 15:50:21.693
971819,8,19,2011-06-02 15:53:56.373,2011-07-02 15:53:56.373
971820,8,19,2011-06-02 15:54:00.087,2011-07-02 15:54:00.087
971821,8,22,2011-06-02 15:54:04.997,2011-07-02 15:54:04.997
971822,8,11,2011-06-02 15:54:08.520,2011-06-02 15:54:07.580
971823,8,19,2011-06-02 16:01:55.307,2011-06-02 16:01:55.640
971824,8,19,2011-06-02 16:14:57.620,2011-06-02 16:14:57.970
971825,8,19,2011-06-02 16:17:27.730,2011-06-02 16:17:28.060
971826,10,15,2011-06-02 16:19:57.510,2011-07-02 16:19:57.510
971827,8,20,2011-06-02 16:34:10.107,2011-06-02 16:34:10.420
971828,8,19,2011-06-02 16:40:15.387,2011-06-02 16:40:15.720
971829,8,19,2011-06-02 16:47:02.497,2011-06-02 16:47:02.813
971830,8,-1,2011-06-02 17:19:08.980,2011-07-02 17:19:08.980
971831,10,-1,2011-06-02 17:19:39.793,2011-07-02 17:19:39.793这张表里一共有400W条数据，在用这个sql语句速度很慢，小弟对sql知识了解颇少，求指教

Answer 1

补充一下
'2011-06-01 00:00:00' 这个对应存储过程的参数是 @startTime
'2011-06-15 00:00:00'  @endTime
factoryId=11  @factoryId

Answer 2

replace(convert(varchar, gamelog.intime, 102),'.','-')
改为：
convert(varchar, gamelog.intime, 120)

Answer 3

关键是我想group by 在同一天的

Answer 4

IPTV ?1、你的时间如果只是做比较用的话就不要在嵌套查询里面做类似这种处理，
replace(convert(varchar, gamelog.intime, 102),'.','-') as date3, 
比较同一天可以用 where datediff(day, datetime1, datetime2)=0 做条件，
在得出最终结果后将要显示的日期写成2楼说的模式2、嵌套较多，或子查询比较复杂的情况下，建议用用临时表或表变量

Answer 5

楼主试验数据不完整，所以下面如果有误请指正。楼主要group by date 可以使用 CONVERT(VARCHAR(10), GG.INTIME, 102) AS DATE 只获得intime 的前十个字符，即日期。还有query 中的前三个subquery 可以合并在一起，因为都是按照date 来group by的。最后一个query的功能不清楚，不然应该也可以优化一下的。SELECT 
MQ1.*,
MQ2.NEWUSERCOUNT
FROM (SELECT  CONVERT(VARCHAR(10), GG.INTIME, 102) AS DATE,
        COUNT(GG.*) AS VISTCOUNT,
COUNT(DISTINCT GG.IPTVNAME) AS USERCOUNT,
CAST(SUM(DATEDIFF(s,GG.OUTTIME,GG.INTIME)) /3600.00 AS DECIMAL(18,2)) AS PLAYTIME
FROM 
GAMELOG GGWHERE GG.INTIME BETWEEN '2011-06-01 00:00:00' AND '2011-06-15 00:00:00' AND GG.FACTORYID=11
GROUP BY  CONVERT(VARCHAR(10), GG.INTIME, 102)) MQ1
--以下这个query 不知道楼主为了啥？？能说明一下吗？ 如果单纯为了取时间2011-06-01 00:00:00之后的新用户，貌似不用这么复杂
LEFT JOIN (
        SELECT CONVERT(VARCHAR(10), TA.INTIME, 102) AS DATE4,COUNT(TA.IPTVNAME) AS NEWUSERCOUNT 
        FROM (SELECT *,ROWNUM= ROW_NUMBER ()OVER(PARTITION BY IPTVNAME ORDER BY INTIME) 
              FROM GAMELOG 
              WHERE INTIME BETWEEN '2011-06-01 00:00:00' AND '2011-06-15 00:00:00' AND FACTORYID=11 AND 
                    IPTVNAME NOT IN (SELECT DISTINCT IPTVNAME FROM GAMELOG WHERE INTIME<'2011-06-01 00:00:00') )AS TA 
        WHERE TA.ROWNUM =1 GROUP BY  CONVERT(VARCHAR(10), TA.INTIME, 102)MQ2
ON MQ1.DATE=MQ2.DATE4

Answer 6

赞成3楼的做法，你可以按下面的思路去试试
if Object_id('Tmp_gamelog') si not null Drop table Tmp_gamelog;
select  *, replace(convert(varchar, intime, 102),'.','-') as date
into Tmp_gamelog
from gamelog  
where intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11;create Index Tmp_gamelog_data
on Tmp_gamelog(date);--其他你需要建立的索引
--.....--然后对 Tmp_gamelog 查询，利用索引，应该会快很多
另外你说的group by 在同一天，我看你的语句
 replace(convert(varchar, gamelog.intime, 102),'.','-')，
这个不表示同一天的。如果要同一天可以用 convert(varchar(10), gamelog.intime, 120)

Answer 7

上面的3个query分开来会比较快，您说的下面新增用户有什么优化方法吗，我查了下 这个新增用户的query确实比较浪费时间

Answer 8

建议把原始表和想要的结果写出来，要的逻辑描述一下，光看LZ的SQL，太累了

Answer 9

优化原则：
1、集合处理
2、使用SGAG模式如
select distinct 
                replace(convert(varchar, gamelog.intime, 102),'.','-') as date1,
                count(*) as vistCount
            from 
                gamelog  
            where 
                intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11
            group by  
                replace(convert(varchar, gamelog.intime, 102),'.','-')
 这个多处使用，建议使用临时表2、 not in  不要使用，建议使用 not exists
3、 建议组合索引 create index t_index on gamelog(factoryId,intime)
   条件改成 factoryId=11 and   intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' 希望你能理解。

Answer 10

希望楼主能解释一下 最后一个query中 业务逻辑上的意义，还有楼主其中用到了row_number 是为了什么？其中还有一个 not in条件是为了取哪些数据？ 这个讲清楚了，优化query就简单些了。