梦槐浔萱 2021-04-25 11:32 采纳率: 0%
浏览 34
已结题

同事写的数据库存储过程和C#代码,在C#应用中查询要60s,在SSMS中只需1S不到

数据库存储过程:

USE [SEWAssetDepreciation]
GO
/****** Object:  StoredProcedure [dbo].[GetAssetScrapInfo]    Script Date: 2021/4/25 11:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetAssetScrapInfo]
(
@AS_DEPARTMENT  nvarchar(20) = '',
@AS_SCRAP_STATUS  nvarchar(20) = '',
@AS_COMPANY nvarchar(20) = '',
@AS_ID  nvarchar(255) = '',
@Rowcount INT OUTPUT
)
AS
BEGIN
SELECT [AS_DATE]
,[AS_USER]
,[AS_DESPCIPTION]
,[AS_COUNT]
,[AS_DEPARTMENT]
,[AS_COST_CENTER]
,[AS_ID]
,[AS_NOTE]
,[AS_SCRAP_STATUS] FROM dbo.ASSET_COLLECT WHERE dbo.RegexMatch(@AS_ID,AS_ID)>0 
and ((AS_DEPARTMENT LIKE '%'+@AS_DEPARTMENT+'%') or (@AS_DEPARTMENT='全部')) 
and AS_COMPANY = @AS_COMPANY 
and AS_SCRAP_STATUS = @AS_SCRAP_STATUS 
SET @Rowcount = @@ROWCOUNT 
END

C#代码:

using (SqlConnection conn = DbConn.CreateConnection())
            {
                conn.Open();
                //SqlCommand comm = conn.CreateCommand();
                SqlCommand comm = new SqlCommand("GetAssetScrapInfo", conn);
                //SqlTransaction myTran = conn.BeginTransaction();
                //comm.Transaction = myTran;
                DataSet MyDataSet = new DataSet();
                DataTable dt = new DataTable();
                SqlDataAdapter DataAdapter = new SqlDataAdapter();
                try
                {
                    //DataSet ds_EXC = Getexcelds(this.txtFilePath.ToString(), this.cmbtablename.SelectedItem.ToString().Trim());                    
                    comm.CommandType = CommandType.StoredProcedure;
                    //存储过程名字
                    //comm.CommandText = "GetAssetCollectInfo";
                    //添加SqlCommand对象必要的参数
                    comm.Parameters.Add("@AS_DEPARTMENT", SqlDbType.NVarChar);
                    if (label4.Text != "财务处")
                    {
                        comm.Parameters["@AS_DEPARTMENT"].Value = this.label4.Text;
                    }
                    else
                    {
                        comm.Parameters["@AS_DEPARTMENT"].Value = this.comboBox2.SelectedItem.ToString();
                    }      
                    comm.Parameters.Add("@AS_ID", SqlDbType.NVarChar, 100);
                    if (this.AS_ID_ARRAY.Text != "")
                    {
                        string regular = this.AS_ID_ARRAY.Text.ToString().Trim();
                        regular = regular.Replace("\r\n", "|");
                        comm.Parameters["@AS_ID"].Value = regular;
                    }
                    else
                    {
                        comm.Parameters["@AS_ID"].Value = this.AS_ID_ARRAY.Text;
                    }                    
                    comm.Parameters.Add("@AS_SCRAP_STATUS", SqlDbType.NVarChar);
                    comm.Parameters["@AS_SCRAP_STATUS"].Value = this.comboBox1.SelectedItem.ToString();
                    comm.Parameters.Add("@AS_COMPANY", SqlDbType.NVarChar);
                    comm.Parameters["@AS_COMPANY"].Value = Form2.USER_COMPANY;
                    comm.Parameters.Add("@Rowcount", SqlDbType.Int);
                    comm.Parameters["@Rowcount"].Direction = ParameterDirection.Output;
                    comm.CommandTimeout = 100000;
                    comm.ExecuteNonQuery();
                    //myTran.Commit();
                    DataAdapter.SelectCommand = comm;
                    if (MyDataSet != null)
                    {
                        DataAdapter.Fill(MyDataSet, "table");
                    }
                    dt = MyDataSet.Tables[0];
                    this.dataGridView1.DataSource = dt;                    
                    conn.Close();
                }
                catch (Exception error)
                {
                    //myTran.Rollback();
                    MessageBox.Show(error.Message, "错误提示!");
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }

在comm.ExecuteNonQuery();这一句执行需要30+S,求大神帮帮孩子,查询太慢了,数据库中一共也没有1w条数据

  • 写回答

2条回答 默认 最新

  • cologry 2021-04-25 11:35
    关注

    66666,谢谢分享,

     

    评论

报告相同问题?

悬赏问题

  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决