Sql Server 分区视图查询和原表查询的性能测试

2022-10-04 10:06:05

【前言】

由于公司库存出入库业务数据量比较大，看是否有必要使用分区视图来提高查询性能。

测试前提条件：

原数据表：sw_KCRK_b

数据量：1500万条

分区视图表（将核算年设成分区属性列进行分区）：

得到三张表：

sw_KCRK_b_2018

sw_KCRK_b_2019

sw_KCRK_b_2020

为三张表各自添加约束：

通过union all将三个分区表合成一张视图

SELECT *
FROM sw_KCRK_b_2018
UNION ALL
SELECT *
FROM sw_KCRK_b_2019
UNION ALL
SELECT *
FROM sw_KCRK_b_2020

得到这样一张分区视图表

PS：建立好分区视图表后，可以对这张视图进行增删改查等操作。它会根据核算年字段去对应表操作数据。

【正文】

1、不带分区列属性查询

原表和分区视图都有1500万数据

这边笔者自己写了个winform程序来测试

代码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (TestEntities db=new TestEntities())
            {
                textBox3.Text += "原表查询\r\n";
                for (int i = 0; i < 10; i++)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    List<sw_KCRK_b> kcrk = db.sw_KCRK_b.Where(p => p.vou_no == textBox1.Text).ToList();
                    sw.Stop();
                    string time = sw.Elapsed + "(" + sw.Elapsed.Seconds + "秒" + sw.Elapsed.Milliseconds + "毫秒)";
                    textBox3.Text += time + "\r\n";
                }
                
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (TestEntities db = new TestEntities())
            {
                textBox3.Text += "分区视图查询\r\n";
                for (int i = 0; i < 10; i++)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    List<V_sw_KCRK_b> kcrk = db.V_sw_KCRK_b.Where(p => p.vou_no == textBox2.Text).ToList();
                    sw.Stop();
                    string time = sw.Elapsed + "(" + sw.Elapsed.Seconds + "秒" + sw.Elapsed.Milliseconds + "毫秒)";
                    textBox3.Text += time + "\r\n";
                }

            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            using (TestEntities db = new TestEntities())
            {
                var kcrk_b = db.sw_KCRK_b;
                label3.Text = "原表条数：" + kcrk_b.Count().ToString();
                var V_kcrk_b = db.V_sw_KCRK_b;
                label4.Text = "分区视图条数：" + V_kcrk_b.Count().ToString();
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            textBox3.Text="";
        }
    }
}

可以从代码看出，我分别对原表查询和分区视图查询都进行了10次查询

可以看出，两者查询时间相差并不大。

这边也有数据库缓存的原因

在每次查询前使用这个代码清理下数据库缓存

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE (‘ALL‘);

2、带分区列查询

修改下代码，将分区列带入条件中

可以从测试中看出，带上分区属性之后，查询速度也提升了许多

【结论】

从两个测试可以看出，分区视图对于查询速度还有有帮助的，当查询条件带上分区列后，在1500万行数据时能够提升三分之一的速度。

Sql Server 分区视图查询和原表查询的性能测试

码农公寓

相关文章