关于SQL查询的优化

[maoxiang123]

有一存储过程需要从某一视图中筛选出大量数据并做分组聚合，过滤条件写在存储过程里好还是用过滤条件创建一新的视图给存储过程用好？

比如

SELECT     RTRIM(JC_DEPT_PROPERTY.NAME) AS 科室, RTRIM(VI_ZY_FEE_SPECI.ITEM_NAME) AS 项目, SUM(VI_ZY_FEE_SPECI.NUM) AS 数量, 
                      RTRIM(VI_ZY_FEE_SPECI.UNIT) AS 单位, JC_HSITEM.xishu AS 系数, ROUND(SUM(VI_ZY_FEE_SPECI.NUM)*JC_HSITEM.xishu,2) AS 小计
FROM         JC_DEPT_PROPERTY INNER JOIN
                      VI_ZY_FEE_SPECI ON JC_DEPT_PROPERTY.DEPT_ID = VI_ZY_FEE_SPECI.EXECDEPT_ID INNER JOIN
                      JC_HSITEM ON VI_ZY_FEE_SPECI.XMID = JC_HSITEM.ITEM_ID
WHERE     (VI_ZY_FEE_SPECI.XMLY = 2) AND (VI_ZY_FEE_SPECI.CHARGE_DATE BETWEEN @RQ1 AND @RQ2) AND 
                      (VI_ZY_FEE_SPECI.EXECDEPT_ID = @deptid) AND (VI_ZY_FEE_SPECI.XMID IN (1680, 449, 1351, 1709, 454, 456, 457, 1355, 914, 447, 3111, 527, 
                      1677, 1678, 1342, 1365, 982, 1341, 538, 529, 536, 1339, 1338, 532, 774, 712, 105, 1143, 1066, 3362, 1335, 1334, 980, 981, 450, 832, 453, 831, 560, 
                      562, 1063, 1061, 1062, 1074, 898, 899, 900, 1332, 3226, 452, 1193, 644, 524, 1065, 647, 403, 130, 3111, 3112, 528, 985, 983))
GROUP BY VI_ZY_FEE_SPECI.XMID, VI_ZY_FEE_SPECI.ITEM_NAME, JC_DEPT_PROPERTY.NAME, VI_ZY_FEE_SPECI.UNIT, JC_HSITEM.xishu

里面的

WHERE     (VI_ZY_FEE_SPECI.XMLY = 2) AND (VI_ZY_FEE_SPECI.XMID IN (1680, 449, 1351, 1709, 454, 456, 457, 1355, 914, 447, 3111, 527, 1677, 1678, 1342, 1365, 982, 1341, 538, 529, 536, 1339, 1338, 532, 774, 712, 105, 1143, 1066, 3362, 1335, 1334, 980, 981, 450, 832, 453, 831, 560, 562, 1063, 1061, 1062, 1074, 898, 899, 900, 1332, 3226, 452, 1193, 644, 524, 1065, 647, 403, 130, 3111, 3112, 528, 985, 983))

是新建视图处理还是就放在存储过程里？

[IT-Style]

在表中填充海量数据,试一试两者的查询速度,哪一个查询快就用哪一个.

[JayPan2008]

用过滤条件创建一新的视图给存储过程用好！