sql表数据压缩与分区存储那个效率高

[zj_8092]

各位大神好
请问sql表数据压缩与分区存储那个效率高

[OwenZeng_DBA]

这是2个不同的技术，没必要拿来比较，你说下你现在的问题和场景吧

[zj_8092]

我的订单子表中有4000万条数据左右，现在查询很慢，不知道使用表数据压缩还是分区存储来提高查询效率

[OwenZeng_DBA]

引用 2 楼 zj_8092 的回复:

我的订单子表中有4000万条数据左右，现在查询很慢，不知道使用表数据压缩还是分区存储来提高查询效率

2个都能提高一些效率，但是这是2个方向，场景也不一样，分区表也不一定能提高你的查询的效率，，你把你的sql语句发来看看，能不能对sql进行优化

[zj_8092]

SELECT m.pro_id, m.clientname, m.clientid, m.recordtime, m.recorder, m.inter_date, m.Inprice, m.Salesman, m.Iprice, m.GoodPrice, m.yearmonth, m.car_code, m.isRed, m.rtg_code, m.auditname, m.auditdate, m.auditfalag, m.recorddate, m.isfalag, n.pr_code, n.pro_code, n.proname, n.account, n.price, n.sumprice, n.sumaccount, n.falag, n.Referred FROM dbo.Provisional AS m INNER JOIN dbo.Provisionals AS n ON m.pro_id = n.pro_id

[OwenZeng_DBA]

引用 4 楼 zj_8092 的回复:

SELECT m.pro_id, m.clientname, m.clientid, m.recordtime, m.recorder, m.inter_date, m.Inprice, m.Salesman, m.Iprice, m.GoodPrice, m.yearmonth, m.car_code, m.isRed, m.rtg_code, m.auditname, m.auditdate, m.auditfalag, m.recorddate, m.isfalag, n.pr_code, n.pro_code, n.proname, n.account, n.price, n.sumprice, n.sumaccount, n.falag, n.Referred FROM dbo.Provisional AS m INNER JOIN dbo.Provisionals AS n ON m.pro_id = n.pro_id

这是报表吗，这个查询没有筛选条件啊，你一次查询出来多少数据

[zj_8092]

SELECT m.pro_id, m.clientname, m.clientid, m.recordtime, m.recorder, m.inter_date, m.Inprice, m.Salesman, m.Iprice, m.GoodPrice, m.yearmonth, m.car_code, m.isRed, m.rtg_code, m.auditname, m.auditdate, m.auditfalag, m.recorddate, m.isfalag, n.pr_code, n.pro_code, n.proname, n.account, n.price, n.sumprice, n.sumaccount, n.falag, n.Referred FROM dbo.Provisional AS m INNER JOIN dbo.Provisionals AS n ON m.pro_id = n.pro_id where m.clientname like 'XXX%'

[OwenZeng_DBA]

引用 6 楼 zj_8092 的回复:

SELECT m.pro_id, m.clientname, m.clientid, m.recordtime, m.recorder, m.inter_date, m.Inprice, m.Salesman, m.Iprice, m.GoodPrice, m.yearmonth, m.car_code, m.isRed, m.rtg_code, m.auditname, m.auditdate, m.auditfalag, m.recorddate, m.isfalag, n.pr_code, n.pro_code, n.proname, n.account, n.price, n.sumprice, n.sumaccount, n.falag, n.Referred FROM dbo.Provisional AS m INNER JOIN dbo.Provisionals AS n ON m.pro_id = n.pro_id where m.clientname like 'XXX%'

对于这种场景，看看全文索引能否满足你。

[吉普赛的歌]

--1. 先加索引
CREATE INDEX ix_Provisional_clientname ON Provisional(clientname) INCLUDE(
	   pro_id,
       clientname,
       clientid,
       recordtime,
       recorder,
       inter_date,
       Inprice,
       Salesman,
       Iprice,
       GoodPrice,
       yearmonth,
       car_code,
       isRed,
       rtg_code,
       auditname,
       auditdate,
       auditfalag,
       recorddate,
       isfalag
)

--2. 再查询看效果
SELECT m.pro_id,
       m.clientname,
       m.clientid,
       m.recordtime,
       m.recorder,
       m.inter_date,
       m.Inprice,
       m.Salesman,
       m.Iprice,
       m.GoodPrice,
       m.yearmonth,
       m.car_code,
       m.isRed,
       m.rtg_code,
       m.auditname,
       m.auditdate,
       m.auditfalag,
       m.recorddate,
       m.isfalag,
       n.pr_code,
       n.pro_code,
       n.proname,
       n.account,
       n.price,
       n.sumprice,
       n.sumaccount,
       n.falag,
       n.Referred
FROM   dbo.Provisional              AS m
       INNER JOIN dbo.Provisionals  AS n
            ON  m.pro_id = n.pro_id
WHERE  m.clientname LIKE 'XXX%'

[砸死牛顿的苹果]

左匹配应该可以利用索引

[唐诗三百首]

引用 6 楼 zj_8092 的回复:

SELECT m.pro_id, m.clientname, m.clientid, m.recordtime, m.recorder, m.inter_date, m.Inprice, m.Salesman, m.Iprice, m.GoodPrice, m.yearmonth, m.car_code, m.isRed, m.rtg_code, m.auditname, m.auditdate, m.auditfalag, m.recorddate, m.isfalag, n.pr_code, n.pro_code, n.proname, n.account, n.price, n.sumprice, n.sumaccount, n.falag, n.Referred FROM dbo.Provisional AS m INNER JOIN dbo.Provisionals AS n ON m.pro_id = n.pro_id where m.clientname like 'XXX%'

看此查询返回的数据量如何, 如果过大(如大于10000行),是否应重新考虑前端程序设计是否合理,真有必要一次性拿这么多数据到前端分页显示吗.