详尽的SQL存储PROC验证使插入慢 -- stored-procedures 领域 和 optimization 领域 dba 相关 的问题

Exhaustive SQL stored proc validation make insert slow


0
vote

问题

中文
所以基本上我们的团队正在研究一个模块来从Excel文件中检索行值并在批处理作业期间插入它。

要求如下:
- 每个列应完成详尽验证,并且验证错误消息应根据要求规范描述 例如:由于日期格式无效/超过最大长度,列[x]中的错误。
- 每个列中的错误应插入数据库(不是日志文件) - 插入应按行排列,没有散装。任何故障插入描述(由于无效数据)都应插入数据库中。

所提出的解决方案如下:
所以,首先,我们将excel数据行插入行进到Excel文件列表中。如果它通过所有验证,它将包括除Errordesc之外的所有值。如果它失败,它将包括错误DESC和除行之外的其他Excel行值为null。

作为我们在此观察到的内容,验证(轻松转到30到40,根据列数和验证要求),以及检查成员名称和MemberCountryname是否在数据库中具有相应的ID,使插入速度慢,范围ExcelContentList中每次插入0.2至0.5s。我们目前正在为每个插入执行SELECT语句,以检查是否存在成员单独的名称和MemberCountryname并具有相应的ID。

我们很欣赏如果有任何建议/改进。

输入图像描述

英文原文

So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job.

The requirement are as follow:
- Exhaustive validation should be done for each column, and the validation error message should be descriptive according to requirement specification
eg: Error in Column [x] due to invalid date format/exceed max length.
- Error in each column should be inserted into database (not log file)
- Insertion should be done ROW by ROW, no bulk insert. Any fail insertion description (due to invalid data) should be inserted into database.

The proposed solution is as follow:
So, first we will insert excel data row by row into excel file list. If it pass all validation, it will include all value except ErrorDesc. If it is failed it will include Error Desc and other excel row value except line will be null.

As what we observed over here, the validation (easily go to 30 to 40 depending on column number and validation requirement), as well as checking whether the memberclassname and membercountryname has corresponding ID in the database makes the insertion slow, ranging from 0.2 to 0.5s per insertion in ExcelContentList. We are currently performing select statement for each insertion to check whether memberclassname and membercountryname exist and has corresponding ID.

We appreciate if there is any suggestion/improvements.

enter image description here

     
   
   

回答列表

0
 
vote
vote
最佳答案
 

将所有数据插入暂存表而不验证任何内容。这应该很快,但你的里程可能会有所不同。这也认为,您只会一次才能完成一个插入。多个插入可能需要多个分段表,但您可能能够使用临时表中的TEMP表,或者在临时表中具有会话标识符,以指示离散插入操作。

在任何情况下,一旦将数据加载到暂存表中,执行验证检查。这样的东西(我不知道你的数据库结构,所以这只是一个例子)。

  UPDATE StagingTable SET ErrorDesc = ISNULL(ErrorDesc,'') + 'MemberClassName mismatch' WHERE NOT(MemberClassName IN (SELECT MemberClassName FROM MemberClass))   

为所有验证规则重复此操作。这应该比行检查快得多。

一旦完成,将在最终表中移动任何没有验证错误的行,然后为您想要与它们执行的任何内容输出所有错误。

 

Insert all the data into a staging table without verifying anything. This should go quickly, but your mileage may vary. This also assumes that you are only ever going to be doing one insert at a time. Multiple inserts may require multiple staging tables, but you might be able to use a temp table for it or have a session identifier in the staging table to indicate discrete insert operations.

In any case, once the data is loaded into the staging table, perform your validation checks. Something like this (I don't know your database structure so this is just an example).

UPDATE StagingTable SET ErrorDesc = ISNULL(ErrorDesc,'') + 'MemberClassName mismatch' WHERE NOT(MemberClassName IN (SELECT MemberClassName FROM MemberClass)) 

Repeat this for all your validation rules. This should go much faster than your row by row check.

Once done, move all rows that had no validation errors into the final tables, then output all the errors for whatever you want to do with them.

 
 
   
   

相关问题

3  如何在程序上使用“Select Benchmark”命令  ( How to use the select benchmark command on a procedure ) 
我试图检查我的哪个程序是最慢的,因此需要优化。 我目前有大约160个程序和大约30个功能。 我正在使用 select benchmark() 来检查过程速度,但是我尝试结束的所有命令"检查你的语法" 。 select BENCHMARK(1000, CALL testProc()); 给了我一个错误, ps...

3  为所有CRUD操作创建一个存储过程  ( Creating one stored procedure for all crud operations ) 
我在用条件逻辑写入存储过程时非常擅长,我可以真正使用一些帮助。我有一个带有7个表的数据模型,我正在尝试为允许四个动作的每个表编写存储过程。每个存储过程应具有4个参数,以允许用户插入,选择,更新和删除表中的记录。我想拥有一个可以接受这些参数的存储过程,因此我只需要每台表拥有一个存储的过程,而不是为7个表的那些4动作拥有...

-4  3表内联[关闭]  ( 3 tables inner join ) 
关闭。这个问题需要详细信息或清晰度。它目前不接受答案。 想要改进这个问题?添加详细信息并阐明编辑此帖的问题。 关闭 1年前。 ...

7  查找尚未在<n>天中调用的过程  ( Find procedures that havent been called in n days ) 
我们正在删除旧的存储过程和表。 如何知道最近没有被称为哪些程序? <代码> dm_exec_procedure_stats 和 dm_exec_query_stats 不可靠,因为它们只返回计划缓存中的过程。 ...

2  声明处理程序过程时的逻辑条件  ( Logical condition when declaring handler procedure ) 
我在mysql中有一个过程,它为 SQLEXCEPTION 执行一个处理程序来读取errno代码并将其插入表。 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTA...

3  MySQL Workbench改变程序  ( Mysql workbench alter procedure ) 
我的工作台程序有问题。我使用Mac Lion OS X和MySQL Workbench程序。当我想做任何程序来改变它时不起作用。它显示到我右键单击它的过程代码,在"更改程序" 单击之后,但不会发生任何操作! 我已更新到MySQL Workbench程序"5.2.45修订10251" 到"5.2.47修订版10398"...

1  如何从DB2中的存储过程访问另一个数据库  ( How to access another db from a stored procedure in db2 ) 
我想在DB2中的存储过程中查询另一个dB中的表。例如,我连接到DB_A1,并从其中调用存储过程,其中我需要连接到DB_A2中的表。 在db2中是可能的吗? ...

0  列名称或提供的值数不匹配表定义  ( Column name or number of supplied values doesn not match table definition ) 
我使用创建了三个表 +----------------------------------------------------------------------------------------------------------------------------------------------+ | ...

2  在没有数据工作室编译存储过程时保留缩进格式  ( Retain indent formatting when compiling a stored procedure without data studio ) 
当我使用CLPPLUS控制台编译存储过程时(甚至在DBEAVER中),删除所有缩进。只有在数据工作室中编译时才保留了格式,但我想用脚本部署项目。 我希望有一些简单的东西我缺少,但没有能够找到一个解决方案甚至与同一问题的任何其他帖子。 使用clpplus i运行这个脚本文件 @c: empproc1.spsql ...

-1  在链接服务器2008上运行sp [已关闭]  ( Run sp on linked server 2008 ) 
关闭。这个问题需要详细信息或清晰度。它目前不接受答案。 想要改进这个问题?添加详细信息并阐明编辑此帖的问题。 closed 5年前。 ...

0  在存储过程中创建和使用FullText索引  ( Create and use fulltext index in a stored procedure ) 
我想在单个过程中创建和使用全文索引。例如: CREATE PROC [dbo].[dbp_PCL_to_Demo_Matching_Mason] AS --IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(...

1  有人在DB2 LUW中使用模块吗?  ( Does anyone use modules in db2 luw ) 
我们的环境目前是DB2 9.7在AIX上修复包4。 我在处理设计和编码某些SQL PL存储过程中,负责将数据存档到归档架构中,然后从主架构中清除旧/不需要的数据。 我创建了一些用户定义的类型(UDT),特别是行类型和游标类型。我正在查看将我的逻辑分为三个存储的程序。 Proc A 将用于清除与我的主事件表相关的特定...

7  如何创建数据类型并使其在所有数据库中可用?  ( How to create a data type and make it available in all databases ) 
如果我在主数据库中创建存储过程,我希望从我的任何数据库中执行它,我只是遵循此链接: 在所有数据库中提供一个可用的过程 < / p> 给我这个代码示例: 只需遵循上面的示例,我可以从任何数据库调用我的过程。 如果我在master中创建表数据类型,那么呢? 如何在我的任何数据库中使用它? use master...

20  在功能/程序中的DML操作后是必要的吗?  ( Is commit necessary after dml operation in function procedure ) 
我想知道是否有必要在功能/过程中插入/删除/更新后写入提交? 示例: create or replace function test_fun return number is begin delete from a; return 0; end; 或过程 create or replace...

0  MySQL作为具有参数的表可重用结果  ( Mysql reusable results as table with parameters ) 
我曾经使用mysql但没有比 JOIN s更复杂,然后我在一个使用mssql的公司工作了一年,我学会了如何编程存储过程。我当前的公司使用MySQL。 在MSSQL中,您可以使用存储过程的结果作为查询中的表,例如,。 CREATE TABLE table_name ( id serial, org...




© 2021 it.wenda123.org All Rights Reserved. 问答之家 版权所有


Licensed under cc by-sa 3.0 with attribution required.