Server 2008 R2中几个select和insert操作的存储过程性能问题

Question

我的应用程序和数据库设计可以简化为一个简单的报价系统。

以下是详细说明：

数据库中的三个表：

表:项目：

• itemId(唯一的，主键，自动增加)
• 名称varchar不为空
• 范畴varchar不为空
• 描述varchar不为空

表:收件人：

• recipientId(唯一的，主键，自动增加)
• 名varchar不为空
• 姓varchar不为空
• 地址varchar不为空
• 电子邮件varchar不为空

表:顺序：

• id(唯一，主键，自动增加)
• 价格双倍非空
• 日期时间戳
• itemId(不是外键)
• recipientId(不是外键)

我有以下存储过程来处理select/业务逻辑：

CREATE procedere spInsert
   --list of parameters
   set @tmpItemId = (select itemId from item 
                     where name = @name 
                       and category = @category 
                       and description = @description)

   if @tmpItemId is NULL
   begin 
       insert to item values(@name, @category, @description)
       set @tmpItemId = @@IDENTITY
   end

   set @tmpRecipentId = (select recipentId from recipient 
                         where firstname = @firstname 
                           and lastname = @lastname 
                           and address = @address and email = @email )

   if @tmpRecipentId is NULL
   begin 
       insert to recipient values(@firstname, @lastname, @address, @email)
       set @tmpRecipentId = @@IDENTITY
   end

   insert into order values(@price, @date, @tmpItemId, @tmpRecipientId )

因此，基本上，逻辑是检查Item和Recipient的存在，然后插入。

在实际数据库中，Item和Recipient的列数分别为20、20。我试图通过在文本文件中读取大量原始信息(即将项目信息、收件人信息和订单信息混合在一起)来进行压力测试，然后执行spInsert

JdbcTemplate jt = getJdbcTemplate();
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jt);
simpleJdbcCall.withProcedureName("spInsert").execute(paraMap);

但速度并不令人满意。根据我的粗略计算，每100行原始信息插入需要3-5秒。

我想知道还有什么改进的余地吗？包括改变数据库的设计？

PS，我正在考虑将Item和Recipient的自动增加索引Id改为UUID，以便让程序知道新的Id Item和Recipient，并避免存储过程中的select。这是否可行的方法？

以下是执行计划：

​

​

Answer 1

我怀疑您需要以下性能指标：

item(name, category, description)
recipient(firstname, lastname, address, email)

您可能也应该创建这些唯一的索引，这样就避免了“子查询返回太多行”的问题。

Answer 2

这只是一个简化的版本，您实际上是在比较~20列，以确定在每个insert Item表之前是否有匹配，而对于收件人表则是一样的吗？有可能重新考虑设计吗？也许可以从收货人和订单中分别插入物品？

要检查实际情况，请检查执行计划和SET统计信息IO的输出。我猜搜索部分是问题所在。

我相信，这些表聚集在标识列上。在一个或两个最具选择性的列上创建非聚集索引，以加快搜索速度。不要把指数弄得太宽，因为你会减慢它的速度。您将无法使其唯一，但使用SELECT TOP 1保护子查询。

不要使用@标识，而是使用scope_identity()。