这是例子:USE AdventureWorks2012 ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO 单纯限定最大并行度不能解决根本问题
SELECT a.F_id, a.zx, a.sampleid, CASE WHEN a.checkmoney - isnull(b.checkmoney, 0) > 0 THEN a.checkmoney - isnull(b.checkmoney, 0) ELSE 0 END AS checkmoney, CASE WHEN (a.checkmoney - isnull(b.checkmoney, 0)) > 0 THEN (a.checkmoney - isnull(b.checkmoney, 0)) / a.checkmoney * a.hse END AS hse, CASE WHEN (c.checkmoney - isnull(b.checkmoney, 0)) > 0 THEN (c.checkmoney - isnull(b.checkmoney, 0)) / c.checkmoney * c.hse END AS hse_zk, a.qsrq, a.zzrq, a.isty, isnull(c.zk, 1) AS zk, a.sr FROM a_client_forzjs_hs a LEFT OUTER JOIN hs_forshs b ON a.F_id = b.F_id AND a.sampleid = b.sampleid AND a.isty = b.isty AND a.zzrq = b.zzrq LEFT OUTER JOIN a_client_forzjs_hs_foryear c ON a.F_id = c.F_id AND a.zzrq = c.zzrq AND a.zx = c.zx AND a.sampleid = c.sampleid AND a.isty = c.isty UNION ALL SELECT a.F_id, b.zx, a.sampleid, CASE WHEN a.checkmoney >= isnull(b.checkmoney, 0) THEN isnull(b.checkmoney, 0) ELSE a.checkmoney END AS checkmoney, CASE WHEN a.checkmoney >= isnull(b.checkmoney, 0) THEN (isnull(b.checkmoney, 0)) / a.checkmoney * a.hse ELSE a.hse END AS hse, CASE WHEN c.checkmoney >= isnull(b.checkmoney, 0) THEN (isnull(b.checkmoney, 0)) / c.checkmoney * c.hse ELSE c.hse END AS hse_zk, a.qsrq, a.zzrq, a.isty, isnull(c.zk, 1) AS zk, a.sr FROM a_client_forzjs_hs a INNER JOIN hs_forshs b ON a.F_id = b.F_id AND a.sampleid = b.sampleid AND a.isty = b.isty AND a.zzrq = b.zzrq LEFT OUTER JOIN a_client_forzjs_hs_foryear c ON a.F_id = c.F_id AND a.zzrq = c.zzrq AND a.zx = c.zx AND a.sampleid = c.sampleid AND a.isty = c.isty这是一个视图
然后对这个视图进行查询 SELECT F_id, zx, zzrq, isty, SUM(checkmoney) AS checkmoney, SUM(hse) AS hse FROM hs_forzjs GROUP BY F_id, zx, zzrq, isty 这段语句本人电脑用了1秒。但服务器用了6秒呢
sql语句比较长,因为需求比较复杂,真不好意思,让你看着累
从语句上来说问题不大,但是a_client_forzjs_hs LEFT OUTER JOIN a_client_forzjs_hs_foryear c ON a.F_id = c.F_id AND a.zzrq = c.zzrq AND a.zx = c.zx AND a.sampleid = c.sampleid AND a.isty = c.isty 这句在两个地方都出现了,可以考虑把它查询出来放入一个临时表里面然后再做其他join,另外你的索引应该要处理一下。把加了主键之后的执行计划贴出来看看
奇怪了,你的每个表只有一个聚集索引,怎么会出现index scan?有几个问题: 1、3个表有isty这列,但是第一个表是int,其他两个表是nvarchar(2),这个有问题,第一个问题是,通常这种命名的都是标识两个值,一个是是,一个是否,原则上不建议作为索引键。如果我的猜想是对的,把这列从索引定义中去掉。第二个问题,类型不同,如果你这个列不需要存储多语言,也就是说只需要存储数字、英文或者简体中文,就换varchar/char,不要用n开头的类型。另外这三个表的这列数据类型统一起来,也就是用同一个数据类型,避免数据类型隐式转换带来的性能问题。 2. 2000我记得索引定义和where条件的列顺序有严格的关系,所以我建议3个表的join的on条件里面,on xx=xx and xx=xx这种,要按照索引的定义顺序来写,也就是f_id=f_id and sampleid=sampleid and zzrq=zzrq这样写,顺序不能边。 3. sapleid、f_id这两个类型,还是类型问题,确保数据类型及其长度完全一样。并且尽可能避免使用n开头的类型。如果纯粹的数字,那么用int或者更小的数据类型即可。你先做了这些看看效果再做下一步改进吧。另外记得把执行计划贴出来。至于你说的你本机1秒服务器6秒,那应该是数据量的不同导致的,你可以对比一下你本机的执行计划服务器的执行计划
限制并行数,Option (MAX DOP 1) 试下
优化器也不是次次准确,也是根据一些规则确定的
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO
单纯限定最大并行度不能解决根本问题
> 0 THEN a.checkmoney - isnull(b.checkmoney, 0) ELSE 0 END AS checkmoney,
CASE WHEN (a.checkmoney - isnull(b.checkmoney, 0))
> 0 THEN (a.checkmoney - isnull(b.checkmoney, 0))
/ a.checkmoney * a.hse END AS hse,
CASE WHEN (c.checkmoney - isnull(b.checkmoney, 0))
> 0 THEN (c.checkmoney - isnull(b.checkmoney, 0))
/ c.checkmoney * c.hse END AS hse_zk, a.qsrq, a.zzrq, a.isty, isnull(c.zk, 1) AS zk,
a.sr
FROM a_client_forzjs_hs a LEFT OUTER JOIN
hs_forshs b ON a.F_id = b.F_id AND a.sampleid = b.sampleid AND a.isty = b.isty AND
a.zzrq = b.zzrq LEFT OUTER JOIN
a_client_forzjs_hs_foryear c ON a.F_id = c.F_id AND a.zzrq = c.zzrq AND
a.zx = c.zx AND a.sampleid = c.sampleid AND a.isty = c.isty
UNION ALL
SELECT a.F_id, b.zx, a.sampleid, CASE WHEN a.checkmoney >= isnull(b.checkmoney, 0)
THEN isnull(b.checkmoney, 0) ELSE a.checkmoney END AS checkmoney,
CASE WHEN a.checkmoney >= isnull(b.checkmoney, 0) THEN (isnull(b.checkmoney,
0)) / a.checkmoney * a.hse ELSE a.hse END AS hse,
CASE WHEN c.checkmoney >= isnull(b.checkmoney, 0) THEN (isnull(b.checkmoney,
0)) / c.checkmoney * c.hse ELSE c.hse END AS hse_zk, a.qsrq, a.zzrq, a.isty,
isnull(c.zk, 1) AS zk, a.sr
FROM a_client_forzjs_hs a INNER JOIN
hs_forshs b ON a.F_id = b.F_id AND a.sampleid = b.sampleid AND a.isty = b.isty AND
a.zzrq = b.zzrq LEFT OUTER JOIN
a_client_forzjs_hs_foryear c ON a.F_id = c.F_id AND a.zzrq = c.zzrq AND
a.zx = c.zx AND a.sampleid = c.sampleid AND a.isty = c.isty这是一个视图
然后对这个视图进行查询
SELECT F_id, zx, zzrq, isty, SUM(checkmoney) AS checkmoney, SUM(hse) AS hse
FROM hs_forzjs
GROUP BY F_id, zx, zzrq, isty
这段语句本人电脑用了1秒。但服务器用了6秒呢
AND a.zzrq = c.zzrq
AND a.zx = c.zx
AND a.sampleid = c.sampleid
AND a.isty = c.isty
这句在两个地方都出现了,可以考虑把它查询出来放入一个临时表里面然后再做其他join,另外你的索引应该要处理一下。把加了主键之后的执行计划贴出来看看
哦,明白了,是scan呢,昨天还没明白这两啥区别,不好意思哦
别那么凶,对女生温柔点,有点男人风度,学学DBA_Huangzj,又有才又稳重又有风度,ok??
1. sp_help a_client_forzjs_hs2. sp_help hs_forshs3.sp_help a_client_forzjs_hs_foryear
1如下:2如下:3如下:
1、3个表有isty这列,但是第一个表是int,其他两个表是nvarchar(2),这个有问题,第一个问题是,通常这种命名的都是标识两个值,一个是是,一个是否,原则上不建议作为索引键。如果我的猜想是对的,把这列从索引定义中去掉。第二个问题,类型不同,如果你这个列不需要存储多语言,也就是说只需要存储数字、英文或者简体中文,就换varchar/char,不要用n开头的类型。另外这三个表的这列数据类型统一起来,也就是用同一个数据类型,避免数据类型隐式转换带来的性能问题。
2. 2000我记得索引定义和where条件的列顺序有严格的关系,所以我建议3个表的join的on条件里面,on xx=xx and xx=xx这种,要按照索引的定义顺序来写,也就是f_id=f_id and sampleid=sampleid and zzrq=zzrq这样写,顺序不能边。
3. sapleid、f_id这两个类型,还是类型问题,确保数据类型及其长度完全一样。并且尽可能避免使用n开头的类型。如果纯粹的数字,那么用int或者更小的数据类型即可。你先做了这些看看效果再做下一步改进吧。另外记得把执行计划贴出来。至于你说的你本机1秒服务器6秒,那应该是数据量的不同导致的,你可以对比一下你本机的执行计划服务器的执行计划