请协助优化一下要查询的语句；

Question

请教一下，我做了个小软件，通过在文本框输入药品的小盒码，就可直接显示相关信息，相关数据库的查询语句如下，但有时查询会比较慢，因为有的产品表的数据会很多，请大家看看针对查询语句，是否还能再优化一下，提高一下查询性能，谢谢；

 string connectionString = "server=192.168.4.200;database=BCM30_DrugAdmin;user=sa;password=sskyadmin1!";

 string query = @"SELECT code20, batchNo, parentCode, orderCode, customerName, orderType, createTime, updateTime 
             FROM (

             SELECT 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 
             WHERE dr.code20 = @Code20 --心可舒96片（注意,这个的大箱码是parentCode2）

             union 

             SELECT 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 
             WHERE dr.code20 = @Code20 --心可舒48片

             union 

             SELECT 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 
             WHERE dr.code20 = @Code20 --心可舒72片

             union 

             SELECT 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 
             WHERE dr.code20 = @Code20 --心可舒胶囊24粒

              union 
  
             SELECT 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 

             WHERE dr.code20 = @Code20 ---心可舒胶囊72粒

             union 

             SELECT 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 

             WHERE dr.code20 = @Code20-----心可舒异形片0.62                         

             union 
        
             SELECT 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 

             WHERE dr.code20 = @Code20-----脑血疏10*10ML

             union 
  
             SELECT 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 

             WHERE dr.code20 = @Code20---牛黄益金片1:150

             union  
  
             SELECT 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 

             WHERE dr.code20 = @Code20---牛黄益金片1:200
             
              union 

              SELECT 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 
             WHERE dr.code20 = @Code20 ) as subquery ---抗病毒口服液"; 

Answer 1

加索引或者把结果缓存一下