数据按月分表

当数据量过大，从数据层面可以按月分表，报表查询时可以根据，查询时间来计算查询的年月，查询对应的表
1、按月分表：

存储过程SP_BRANCH_TABLE_TEST
以下存储过程分表，加了索引可以方便后续查询

USE [DASHBOARD]
GO
/****** Object:  StoredProcedure [dbo].[SP_BRANCH_TABLE_TEST]    Script Date: 2024/7/5 10:01:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*********************************************************************************************************
--建立者: DQY  日期﹕2024-04-08
--調用的程序﹕
--說明﹕SP_BRANCH_TABLE_TEST
--更新記錄﹕
--		日期			    更改人				  更新說明
------------------	     -------------			--------------------------------------------
--  2024-04-08              dqy                新增 测试数据水平分割表数据 【月/表】
--  2024-04-17              dqy                 新增 测试项  水平分割表数据 【月/表】

----*********************************************************************************************************/
--调用 EXECUTE [dbo].[SP_BRANCH_TABLE_TEST]

ALTER PROCEDURE [dbo].[SP_BRANCH_TABLE_TEST]
AS
BEGIN
	DECLARE @testTableName NVARCHAR(50); --测试
	DECLARE @sql NVARCHAR(MAX);

	SET @testTableName = N'PROD_TEST_DET_' + FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyyMM');

	SET @sql = N'
	/*创建PROD_TEST_DET分表*/
	IF OBJECT_ID(''' + @testTableName + ''') IS NOT NULL
		DROP TABLE ' + @testTableName + ';
	SELECT TOP 0 * 
	INTO ' + @testTableName + ' 
	FROM PROD_TEST_DET WITH (NOLOCK);
	--分表索引
	CREATE CLUSTERED INDEX [IX_PSTP] ON ' + @testTableName + ' ([PROJ_CODE],[PROCESS_ID],[LOT_NO],[MODULE_SN],[PART_NO],[START_TIME]) ON [PRIMARY];
	CREATE NONCLUSTERED INDEX [IX_PROJ_CODE] ON ' + @testTableName + ' ([PROJ_CODE]) ON [PRIMARY];
	CREATE NONCLUSTERED INDEX [IX_PROCESS_ID] ON ' + @testTableName + ' ([PROCESS_ID]) ON [PRIMARY];
	CREATE NONCLUSTERED INDEX [IX_LOT_NO] ON ' + @testTableName + ' ([LOT_NO]) ON [PRIMARY];
	CREATE NONCLUSTERED INDEX [IX_MODULE_SN] ON ' + @testTableName + ' ([MODULE_SN]) ON [PRIMARY];
	CREATE NONCLUSTERED INDEX [IX_PART_NO] ON ' + @testTableName + ' ([PART_NO]) ON [PRIMARY];
	--添加数据
	INSERT ' + @testTableName + ' 
	SELECT *
	FROM PROD_TEST_DET WITH (NOLOCK)
	WHERE 1 = 1
		  AND TEST_TIME >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
		  AND TEST_TIME < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
	--删除数据
	DELETE
	FROM PROD_TEST_DET
	WHERE 1 = 1
		  AND TEST_TIME >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
		  AND TEST_TIME < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

	';
	--PRINT @sql;
	EXEC SP_EXECUTESQL @sql;

END;

2、根据上面创建的存储过程 SP_BRANCH_TABLE_TEST 添加到job 中每月1号执行一次
在这里插入图片描述

添加到job 每月1号执行

在这里插入图片描述
在这里插入图片描述

3、 以上分表已经完成，下面分表后，查询时匹配到对应的表和跨月查询

如果跨月可以使用UNION ALL
注意开始和结束时间是必选项（可以确定匹配的分表）

/// <summary>
/// 合表 QueryBranchSql
/// </summary>
/// <param name="tablename">表</param>
/// <param name="where">条件</param>
/// <param name="start_time">开始</param>
/// <param name="end_time">结束</param>
/// <returns></returns>
public static string QueryBranchTableSql(string tablename, string where, string start_time, string end_time)
{
	DateTime startDate = Convert.ToDateTime(start_time);
	DateTime endDate = Convert.ToDateTime(end_time);

	string temptable = string.Empty;
	string sql = string.Empty;
	for (DateTime currentDate = startDate; currentDate <= endDate; currentDate = currentDate.AddMonths(1))
	{
		DateTime startsyn =  new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 7, 00, 00).AddDays(-1);
		if (startDate >= startsyn)
		{
			// 当天或昨天
			sql += $"SELECT * FROM {tablename}_DAY WITH (NOLOCK) WHERE 1=1 {where} UNION ALL ";
		}
		else
		{
			if (currentDate.Year == DateTime.Today.Year && currentDate.Month == DateTime.Today.Month)
			{
				// 当月
				sql += $"SELECT * FROM {tablename}  WITH (NOLOCK) WHERE 1=1 {where} UNION ALL ";
			}
			else
			{
				if (currentDate.Year == DateTime.Today.Year && currentDate.Month < DateTime.Today.Month)
				{
					// BranchTable
					sql += $"SELECT * FROM {tablename}_{currentDate.ToString("yyyyMM")} WHERE 1=1 {where} UNION ALL ";
				}
				else
				{
					sql += $"SELECT TOP 0 * FROM {tablename}  WITH (NOLOCK) WHERE 1=1 {where} UNION ALL ";
				}
			}
		}
		
	}

	if (!string.IsNullOrEmpty(sql))
	{
		sql = sql.TrimEnd(" UNION ALL ".ToCharArray());
		temptable = string.Format(@"
		IF OBJECT_ID('tempdb..#T_BRANCHTABLE') IS NOT NULL
			DROP TABLE #T_BRANCHTABLE;
		SELECT * 
		INTO #T_BRANCHTABLE
		FROM ({0}) T
		WHERE TEST_TIME >= '{1}'
			AND TEST_TIME < '{2}';", sql, start_time, end_time);
	}
	return temptable;
}

4、优化-释放数据库内存

SP_DBCC_SQLSERVE 最好在数据库资源闲置的时候调用

USE [DASHBOARD]
GO
/****** Object:  StoredProcedure [dbo].[SP_DBCC_SQLSERVE]    Script Date: 2024/7/5 10:15:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*********************************************************************************************************
--建立者: DQY  日期﹕2024-04-09
--調用的程序﹕
--說明﹕强制释放数据库内存
--更新記錄﹕
--		日期			    更改人				  更新說明
------------------	     -------------			--------------------------------------------
--  2024-04-09              dqy                新增：强制释放数据库内存 【谨用】，在数据吞吐量少的时间段使用，中午12点，凌晨 12点

----*********************************************************************************************************/
--调用 EXECUTE [dbo].[SP_DBCC_SQLSERVE]
ALTER PROCEDURE [dbo].[SP_DBCC_SQLSERVE]
AS
BEGIN
	----自动强制释放内存的SQL脚本
	DECLARE @TargetMemory DECIMAL(19, 2),
			@TotalMemory DECIMAL(19, 2),
			@UseMemoryPecent DECIMAL(19, 2);

	SELECT @TargetMemory = cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Target Server Memory (KB)';

	SELECT @TotalMemory = cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Total Server Memory (KB)';

	SET @UseMemoryPecent = @TotalMemory / @TargetMemory;

	SELECT @UseMemoryPecent;

	IF @UseMemoryPecent > 0.1
	BEGIN

		--清除存储过程缓存
		DBCC FREEPROCCACHE;
		--清除会话缓存  
		DBCC FREESESSIONCACHE;
		--清除系统缓存  
		DBCC FREESYSTEMCACHE('All');
		--清除所有缓存  
		DBCC DROPCLEANBUFFERS;
		--打开高级配置  
		EXEC sp_configure 'show advanced options', 1;
		--设置最大内存值，清除现有缓存空间  20G  （根据实际情况设置，具体思路是最大值先调小，然后再设回合适的值。）
		EXEC sp_configure 'max server memory', 20480;
		EXEC ('RECONFIGURE');
		--设置等待时间，强制释放内存需等待一些时间
		WAITFOR DELAY '00:01:30';
		--重新设置最大内存值  50G   根据实际情况设置，具体思路是最大值先调小，然后再设回合适的值。）
		EXEC sp_configure 'max server memory', 51200;
		EXEC ('RECONFIGURE');
		--关闭高级配置  
		EXEC sp_configure 'show advanced options', 0;

	END;

END

5、每月分表后，视图表跟着更新

更新或创建新的视图

USE [DASHBOARD]
GO
/****** Object:  StoredProcedure [dbo].[SP_CREATE_VIEW_PROD_TEST_DET]    Script Date: 2024/7/5 10:20:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*********************************************************************************************************
--建立者: DQY  日期﹕2024-06-04
--調用的程序﹕
--說明﹕SP_CREATE_VIEW_PROD_TEST_DET
--更新記錄﹕
--		日期			    更改人				  更新說明
------------------	     -------------			--------------------------------------------
--  2024-06-04              dqy                每月分表后更新 视图 VIEW_PROD_TEST_DET

----*********************************************************************************************************/
--调用 EXECUTE [dbo].[SP_CREATE_VIEW_PROD_TEST_DET]

ALTER PROCEDURE [dbo].[SP_CREATE_VIEW_PROD_TEST_DET]
AS
BEGIN
	DECLARE @sql NVARCHAR(MAX);
	DECLARE @viewExists BIT;
	-- 检查视图是否存在
	IF EXISTS (SELECT * FROM sys.views WHERE name = 'V_PROD_TEST_DET')
		SET @viewExists = 1;
	ELSE
		SET @viewExists = 0;

	-- 生成视图定义的SQL脚本
	SET @sql = CASE 
				  WHEN @viewExists = 1 THEN N'ALTER VIEW V_PROD_TEST_DET AS ' 
				  ELSE N'CREATE VIEW V_PROD_TEST_DET AS ' 
			   END + 
			   N'SELECT PROJ_CODE, PROCESS_ID, FFAIL, FAILUREMODE, RESULT, MODULE_SN, START_TIME, TEST_TIME FROM PROD_TEST_DET ' +
			   N'UNION ALL ';

	-- 动态生成每个分表的查询部分
	SELECT @sql = @sql + 
				  N'SELECT PROJ_CODE, PROCESS_ID, FFAIL, FAILUREMODE, RESULT, MODULE_SN, START_TIME, TEST_TIME FROM ' + 
				  name + N' UNION ALL '
	FROM sys.tables
	WHERE name LIKE 'PROD_TEST_DET_%'
		AND name <>'PROD_TEST_DET_DAY';

	-- 移除最后一个 'UNION ALL'
	SET @sql = LEFT(@sql, LEN(@sql) - 10);
	--PRINT @sql
	-- 执行生成的SQL语句以创建或更新视图
	EXEC sp_executesql @sql;

END;

最后和第二步一样 ，把SP_CREATE_VIEW_PROD_TEST_DET 添加到新的job 中，每月一号执行一次，这个视图也更新了

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/778561.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

三分钟内了解卷轴模式

三分钟内了解卷轴模式

在数字化时代的浪潮中&#xff0c;卷轴商业模式巧妙地将积分体系、互动任务、社交裂变、虚拟经济体系以及个性化成长路径等多元要素融为一体。 积分体系&#xff1a;激发参与动力的源泉 卷轴商业模式的核心在于其精心构建的积分系统。新用户踏入平台&#xff0c;即获赠一笔启…
阅读更多...
Windows上Docker的安装与初体验

Windows上Docker的安装与初体验

Docker Desktop下载地址 国内下载地址 一、基本使用 1. 运行官方体验镜像 docker run -d -p 80:80 docker/getting-started执行成功 停止体验服务 docker stop docker/getting-started删除体验镜像 docker rmi docker/getting-started2. 修改docker镜像的存储位置 3. …
阅读更多...
vofa+：一款超级好用的可视化串口调试软件

vofa+：一款超级好用的可视化串口调试软件

目录 一、软件配置 1、先配置好usart1串口 2、重定向printf: 3&#xff0c;勾选魔术棒中的LIB 二、vofa的使用 1、RawData模式 2、FireWater 一、软件配置 1、先配置好usart1串口 2、重定向printf: 在 stm32f4xx_hal.c中添加&#xff1a; #include <stdio.h> e…
阅读更多...
【nvm管理nodejs版本，切换node指定版本】

【nvm管理nodejs版本，切换node指定版本】

nvm管理nodejs版本 nvm管理nodejs版本主要功能使用 nvm nvm管理nodejs版本 nvm&#xff08;Node Version Manager&#xff09;顾名思义node版本管理器&#xff0c;无须去node管网下载很多node安装程序;用于管理多个 Node.js 版本的工具。它允许你在同一台机器上同时安装和管理…
阅读更多...
文件上传（本地、OSS）

文件上传（本地、OSS）

什么是文件上传&#xff1a;将文件上传到服务器。 文件上传-本地存储 前端 <template> <div><!-- 上传文件需要设置表单的提交方式为post&#xff0c;并设置enctype属性、表单项的type属性设置为file --><form action"http://localhost:8080/wedu/…
阅读更多...
使用Python绘制和弦图

使用Python绘制和弦图

使用Python绘制和弦图 和弦图效果代码 和弦图 和弦图用于展示数据的多对多关系&#xff0c;适合用于社交网络、交通流量等领域的分析。 效果 代码 import pandas as pd import holoviews as hv from holoviews import opts hv.extension(bokeh)# 示例数据 data [(A, B, 2),…
阅读更多...
价格预言机的使用总结（一）：Chainlink篇

价格预言机的使用总结（一）：Chainlink篇

文章首发于公众号&#xff1a;Keegan小钢 前言 价格预言机已经成为了 DeFi 中不可获取的基础设施&#xff0c;很多 DeFi 应用都需要从价格预言机来获取稳定可信的价格数据&#xff0c;包括借贷协议 Compound、AAVE、Liquity &#xff0c;也包括衍生品交易所 dYdX、PERP 等等。…
阅读更多...
vb.netcad二开自学笔记1：万里长征第一步Hello CAD!

vb.netcad二开自学笔记1：万里长征第一步Hello CAD!

已入门的朋友请绕行&#xff01; 今天开启自学vb.net 开发autocad&#xff0c;网上相关资料太少了、太老了。花钱买课吧&#xff0c;穷&#xff01;又舍不得&#xff0c;咬牙从小白开始摸索自学吧&#xff0c;虽然注定是踏上了一条艰苦之路&#xff0c;顺便作个自学笔记备忘!积…
阅读更多...
网络安全领域国标分类汇总大全V1.0版：共计425份标准文档，全部可免费下载

网络安全领域国标分类汇总大全V1.0版：共计425份标准文档，全部可免费下载

《网络安全法》、《数据安全法》、《个人信息保护法》落地实施需要大量国家标准的支撑&#xff0c;博主耗时三周时间&#xff0c;吐血整理了从1999年至今相关的所有涉及安全的国家标准&#xff0c;梳理出《网络安全领域国标分类汇总大全V1.0版》&#xff0c;共计 425 项现行标准…
阅读更多...
深度解析 Raft 分布式一致性协议

深度解析 Raft 分布式一致性协议

本文参考转载至&#xff1a;浅谈 Raft 分布式一致性协议&#xff5c;图解 Raft - 白泽来了 - 博客园 (cnblogs.com) 深度解析 Raft 分布式一致性协议 - 掘金 (juejin.cn) raft-zh_cn/raft-zh_cn.md at master maemual/raft-zh_cn (github.com) 本篇文章将模拟一个KV数据读写服…
阅读更多...
ShardingSphere实战

ShardingSphere实战

ShardingSphere实战 文章目录 ShardingSphere实战分库分表实战建表建表sql利用存储过程建表Sharding-jdbc分库分表配置 基于业务的Sharding-key考虑订单id用户id分片策略订单id的设计与实现**设计思想**&#xff1a;设计思路&#xff1a; 具体分片策略实现测试数据插入商户商品…
阅读更多...
【pyqt-实训训练】串口助手

【pyqt-实训训练】串口助手

串口助手 前言一、ui设计二、ui的控件命名三、ui转py使用类的方法【扩展】使用ui文件导入&#xff01;P7的小错误解决办法 总结 前言 我的惯例就是万物之始&#xff0c;拜见吾师&#x1f970;⇨pyqt串口合集 最开始的时候我想的是&#xff0c;学了那么久的pyqt&#xff0c;我…
阅读更多...
进程的控制-孤儿进程和僵尸进程

进程的控制-孤儿进程和僵尸进程

孤儿进程 &#xff1a; 一个父进程退出&#xff0c;而它的一个或多个子进程还在运行&#xff0c;那么那些子进程将成为孤儿进程。孤儿进程将被 init 进程( 进程号为 1) 所收养&#xff0c;并由 init 进程对它们完成状态收集工作 为了释放子进程的占用的系统资源&#xff1a; …
阅读更多...
VS code修改底部的行号的状态栏颜色

VS code修改底部的行号的状态栏颜色

VSCode截图 相信很多小伙伴被底部的蓝色状态栏困扰很久了 处理的方式有两种&#xff1a; 1、隐藏状态栏 2、修改其背景颜色 第一种方法大伙都会&#xff0c;今天就使用第二种方法。 1、点击齿轮进入setting 2、我现在用的新版本&#xff0c;设置不是以前那种json格式展示&…
阅读更多...
ubuntu系统盘扩容

ubuntu系统盘扩容

目录 1 介绍 2 步骤 2.1 关闭虚拟机 2.2 编辑虚拟机设置 2.3 设置扩展大小 2.4 打开虚拟机 2.5 找到磁盘管理 2.6 扩展 1 介绍 本部分主要记述怎么给ubuntu系统盘扩展存储容量&#xff0c;整个过程相对简单&#xff0c;扩容方式轻松、容易。 2 步骤 2.1 关闭虚拟机 2…
阅读更多...
尚庭公寓——数据库设计

尚庭公寓——数据库设计

1. 数据的关系 一对一&#xff0c;一对多&#xff08;多对一&#xff09;&#xff0c;多对多 2. 实体关系模型 实体关系模型常用ER图&#xff08;enity relationship graph&#xff09;表示&#xff1b; 矩形表示实体&#xff08;类似Java中的对象&#xff0c;如学生就是一…
阅读更多...
C++基础（八）：类和对象 （下）

C++基础（八）：类和对象 （下）

经过前面的学习&#xff0c;我们已经翻过了两座大山&#xff0c;类和对象入门知识就剩下这一讲了&#xff0c;加油吧&#xff0c;少年&#xff01; 目录 一、再谈构造函数 1.1 构造函数体赋值 1.2 初始化列表&#xff08;理解&#xff09; 1.3 explicit关键字&#xff08;C…
阅读更多...
51单片机STC89C52RC——15.1 AD/DA(模数数模)

51单片机STC89C52RC——15.1 AD/DA(模数数模)

目的/效果 1 LCD1602 显示 可调电阻、光敏电阻、热敏电阻值&#xff08;AD&#xff09; 2 模拟信号控制LED明暗&#xff08;DA&#xff09; 一&#xff0c;STC单片机模块 二&#xff0c;AD/DA 2.1 AD/DA 介绍 AD&#xff08;Analog to Digital&#xff09;&#xff1a;模拟…
阅读更多...
anaconda中下载压缩包并用conda安装包

anaconda中下载压缩包并用conda安装包

有时直接conda安装包时会出错&#xff1b;报错PackagesNotFoundError: The following packages are not available from current channels 比如 conda install -y bioconda::ucsc-gtftogenepred #直接安装报错 #直接下载压缩包安装https://blog.csdn.net/weixin_45552562/ar…
阅读更多...
Apache Seata 高可用部署实践

Apache Seata 高可用部署实践

本文来自 Apache Seata官方文档&#xff0c;欢迎访问官网&#xff0c;查看更多深度文章。 本文来自 Apache Seata官方文档&#xff0c;欢迎访问官网&#xff0c;查看更多深度文章。 Apache Seata 高可用部署实践 Seata 高可用部署实践 使用配置中心和数据库来实现 Seata 的高…
阅读更多...
最新文章

Copyright @ 2022~2023