下面这个SQL的查询结果有6万多行, 执行时间有4分钟, 如何优化?
写存储过程能否优化, 高手求思路!
select * from [csm-gz].[dbo].[TestResult]
where 1=1
and agent_id in(0,14,17,18,19,21,25,30,38,40,51) and app_id in
(0,32,33,34,35,36,37,38,39,40,41,44,45,48,50,51,52,53,55,56,58,59,60,61,62,64,65,66,67,69,70,71,72,73,74,75,76,77,78,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,112,113,114,115,116,117,118,120,121,124,126,127,128,129,130,133,134,135,136,137,138,139,140,141,142,143,145,148,150,151,152,153,155,157,158,159,160,161,162,164,165,166,167,168,170,172,173,174,176,177,178,179,180,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,244,245,247,248,249,250,251,252,253,254,262,263,264,265,266,267,268,269,271,272,273,275,278,279,280,281,283,284,285,286,287,288,289,290,291,292,293,294,295,297,298,299,300,302,303,304,305,306,307,308,309,310,311,312,313,314,315,318,319,320,321,322,323,324,325,326,327,329,330,331,332,334,336,338,339,345,346,347,348,350,351,352,353,354,355,356,357,358,359,360,361,362,363,367,368,372,373,374,375,376,377,378,379,380,381,383,384,385,386,388,389,390,391,393,395,402,404,405,406,407,408,409,410) and id>=1417968000000 and id<=1418832000000
------解决思路----------------------
建议，先把这些 in 里面的值写到一个临时表中，然后再和[csm-gz].[dbo].[TestResult] 关联后，起到过滤作用，否则很难快的。
------解决思路----------------------
还有条件 1=1 去掉。
加上后只能保证逻辑等价，不等于不影响性能。
------解决思路----------------------
执行计划，贴来看看
------解决思路----------------------
同意这样,
而且加条件,and id>=1417968000000 and id<=1418832000000两个放最前面去,先过滤掉后,再关联临时表
另,如果数据表字段超20列,而实际用不到,建议把列名明确写出来,不要用*号
------解决思路----------------------
不清楚索引结构是怎样的，按ID聚集，看下效果先
------解决思路----------------------
请谨慎使用in，在in的参数列表有长度的限制，建议在业务的基础上增加where条件范围及关联条件做查询。
------解决思路----------------------
请讲in 替换为exists(select 存在的内容)
------解决思路----------------------
建议:
1.确认字段id,app_id上是否有索引,如没有请添加.
2.不建议用"select * ..", 请写需要返回的字段列表.
3.表名之后可加"(nolock)", 避免锁争.