请教一下，我在文本框的回车事件中，针对产品码，对产品的相关信息进行查询，但有时运行会有查询卡顿的情况发生，若直接将下述查询语句在SQLserver中查询，就能迅速查询出结果，请问怎样优化，能提升性能呢，有建议用存储过程或视图，也有建议用索引或缓冲，请大家给一下建议，谢谢；
private void txtCode20_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
string connectionString = "server=192.168.4.200;database=BCM30_DrugAdmin;user=sa;password=sskyadmin1!";
string query = @"SELECT productName,spec,packageSpec, code20, batchNo, parentCode, orderCode, customerName, orderType, createTime, updateTime
FROM (
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo, dr.parentCode2 as parentCode, wo.orderCode as orderCode ,wd.customerName as customerName,wd.orderType as orderType,wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_193815500501 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2 )
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20 --心可舒96片（注意,这个的大箱码是parentCode2）
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo, dr.parentCode3 as parentCode, wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType ,wo.createTime as createTime ,wo.updateTime as updateTime FROM dbo.da_codeRelation_193815500202 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2, dr.parentCode3)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20 --心可舒48片
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo, dr.parentCode3 as parentCode, wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType ,wo.createTime as createTime ,wo.updateTime as updateTime FROM dbo.da_codeRelation_193815500402 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2, dr.parentCode3)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20 --心可舒72片
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo , dr.parentCode2 as parentCode, wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType , wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_191925600201 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20 --心可舒胶囊24粒
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo , dr.parentCode3 as parentCode , wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType ,wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_191925600102 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2,dr.parentCode3)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20 ---心可舒胶囊72粒
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo , dr.parentCode3 as parentCode , wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType ,wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_193815500302 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2,dr.parentCode3)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20-----心可舒异形片0.62
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo , dr.parentCode2 as parentCode , wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType ,wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_922026600101 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20-----脑血疏10*10ML
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo , dr.parentCode2 as parentCode , wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType ,wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_921996000101 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20---牛黄益金片1:150
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo , dr.parentCode2 as parentCode , wo.orderCode as orderCode,wd.customerName as customerName,wd.orderType as orderType ,wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_921996000102 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20---牛黄益金片1:200
union
SELECT pro.productName as productName,pro.spec as spec,pro.packageSpec as packageSpec,
dr.code20 as code20 ,dr.batchNo as batchNo, dr.parentCode2 as parentCode2, wo.orderCode as orderCode ,wd.customerName as customerName,wd.orderType as orderType,wo.createTime as createTime,wo.updateTime as updateTime FROM dbo.da_codeRelation_922026300101 dr
JOIN dbo.da_warehouseOutCode wo ON wo.code20 IN (dr.code20, dr.parentCode2)
JOIN dbo.bcm_warehouseOrder wd on wo.orderCode = wd.orderCode
JOIN dbo.da_product pro on pro.subTypeNo = dr.subTypeNo
WHERE dr.code20 = @Code20 ) as subquery ---抗病毒口服液";
try
{
using (SqlConnection Conn = new SqlConnection(connectionString))
{
DataTable sumdt = new DataTable();
string Code20 = txtCode20.Text;
using (SqlCommand Cmd = new SqlCommand(query, Conn))
{
Cmd.Parameters.AddWithValue("@Code20", Code20);
SqlDataAdapter sda = new SqlDataAdapter(Cmd);
dataGridView1.DataSource = null;
sda.Fill(sumdt);
}
dataGridView1.DataSource = sumdt;
dataGridView1.Columns[0].HeaderText = "产品名称";
dataGridView1.Columns[1].HeaderText = "规格";
dataGridView1.Columns[2].HeaderText = "包装规格";
dataGridView1.Columns[3].HeaderText = "小盒码";
dataGridView1.Columns[4].HeaderText = "批号";
dataGridView1.Columns[5].HeaderText = "大箱码";
dataGridView1.Columns[6].HeaderText = "销售单号";
dataGridView1.Columns[7].HeaderText = "客户名称";
dataGridView1.Columns[8].HeaderText = "类型";
dataGridView1.Columns[9].HeaderText = "出库日期";
dataGridView1.Columns[10].HeaderText = "出库时间";
foreach (DataRow row in sumdt.Rows) //加上出库单的类型
{
string type = Convert.ToString(row["orderType"]);
if (type == "OA") { row["orderType"] = "销售出库"; }
if (type == "IB") { row["orderType"] = "退货入库"; }
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
