如何优化大数据的循环查询？

[我一口气能吃十个包子]

现在有一个5556715行的数据，共有user_id，shop_id，time_stamp三列，
我现在写了一个查询语句，但是效率好低，大神能帮我优化一下吗？谢谢！
DROP PROCEDURE if EXISTS pro;
CREATE PROCEDURE `pro`()
BEGIN
DECLARE i INT;
SET i = 1;
WHILE i < 2001 DO
INSERT INTO test (nouse)
SELECT count(*)
FROM userview
WHERE user_id = i AND time_stamp > '2016-10-10 00:00:00' AND time_stamp < '2016-10-31 00:00:00';
SET i = i + 1;
END WHILE;
END;

[Andy-W]

把while中的insert ...select ....from ..语句提取出来，改用update形式。

DROP PROCEDURE if EXISTS pro;
go
CREATE PROCEDURE `pro`()
  BEGIN
    DECLARE i INT;
    SET i = 1;
    WHILE i < 2001 DO
      INSERT INTO test (nouse)
		values(0)
      SET i = i + 1;
    END WHILE;

	update a set a.nouse=b.nouse
		from test a 
			inner join(select user_id,count(*) as nouse
				from userview
				where user_id <2001 and time_stamp > '2016-10-10 00:00:00' and time_stamp < '2016-10-31 00:00:00'
				group by user_id
				) b on b.user_id=a.user_id
  END; 
go

如果test表没有user_id字段对应，只是保持count()的结果，可以这样即可：

DROP PROCEDURE if EXISTS pro;
go
CREATE PROCEDURE `pro`()
  BEGIN
 
      INSERT INTO test (nouse)
			select count(*) as nouse
				from userview
				where user_id <2001 and time_stamp > '2016-10-10 00:00:00' and time_stamp < '2016-10-31 00:00:00'
				group by user_id
  END; 
go

[二月十六]

不写循环了，直接把要存入的2001条数据直接全部存入，或者不是2001，语句就不加top即可。

 INSERT INTO Test
        ( nouse
        )
        SELECT TOP 2001
                COUNT(*)
        FROM    userview
        WHERE   time_stamp > '2016-10-10 00:00:00'
                AND time_stamp < '2016-10-31 00:00:00'
        GROUP BY USER_ID
        ORDER BY user_id;

[卖水果的net]

先构造一个1-2000的序列表，再left 业务表的group by 结果。

[0与1之间]

select count(1) from userview a join master..spt_values b on b.type='p' and a.user_id=b.number
where time_stamp > '2016-10-10 00:00:00' AND time_stamp < '2016-10-31 00:00:00'
group by user_id

[0与1之间]

 INSERT INTO test (nouse)
select count(1) from userview a join master..spt_values b on b.type='p' and a.user_id=b.number
where user_id<2001  time_stamp > '2016-10-10 00:00:00' AND time_stamp < '2016-10-31 00:00:00'
group by user_id

[0与1之间]

少写了一个and INSERT INTO test (nouse) select count(1) from userview a join master..spt_values b on b.type='p' and a.user_id=b.number where user_id<2001 and time_stamp > '2016-10-10 00:00:00' AND time_stamp < '2016-10-31 00:00:00' group by user_id