利用狐表调用存储过程实现复杂查询

从2011年底开始开始慢慢搭建公司管理系统，到现在已有7个年头了，随着使用的深入，需求在不断变化，数据量也不断攀升，到现在，很多以前处理数据的方法已不能满足需求，不得不寻求新的办法。

查阅大量资料，就记住两句：1、再牛B的硬件，也得让烂代码拖死；2、代码再怎么优化也没有硬件升级来的快。说的都好有道理，对于现状来说，没升级硬件前，还得从代码和数据库上想办法。对复杂的查询，基本上就两个办法：1、数据冗余;2、不要怕麻烦，别总想省事，写个大的视图，需要啥时用条件去查，而是应在表链接时就把条件加进去，直接减少数据检索量，用少的数据去组合最终结果。方法1有两种实现途径：1、客户端程序实现，比如产品入库后，在保存报表时回写到订单表哪里，确认出订单是否完成和入库量是多少；2、在数据库中直接用触发器实现。方法2也有两种实现办法：1.写视图，会用啥条件查找数据，直接按这个条件写视图，这个办法就是写SQL语句，条件是固定时，可以直接写到数据库的视图里，不固定时，就需要在狐表端写视图，或是用狐表的SQLJoinTableBuilder等工具去写视图，不过复杂的视图，我发现狐表工具实现不了，还得自己写视图；2、用存储过程，写临时表，来实现复杂的查询，这个临时表可以很好的简化SQL的查询语句，使SQL查询写时更好理解，我的体会目前只有这么多，可能很不准确。

现在要做的工作是给订单计划表中增加分单转单功能，实现效果如下：

这是个综合表，参与计算的表及数据量如下：

说真的，现在看，原有数据表设计的很不合理，可没办法，咱不是学编程的，大学时学的是机械，参加工作后做了很多年产品设计（主要用机械方面的知识），这系统是在做产品时，为了方便公司管理，自己摸索着乱整，早期用的是myexcel.net平台，这工具不需要知道数据库，简单功能不需要写代码，想要些特殊功能时，就写点VBA。就是现在业务的客户订单，到生管的合同评审，这个转化，用的还是myexcel在实现的，这个平台中，想实现快速综合查询，有时比较困难，当初我用这个工具做系统时，为了方便查询，就用了很多数据冗余，实现方法就是在客户端回写更新数据。但是随着需要的增多，改动涉汲到数据回写的地方是越来越多，比如这个订单为例，入库需要回写，出货需要回写，订单数量变更需要回写，程序维护困难是其次，在网络中多人使用时，不知道怎么整的（不知道是网络问题，还是确实有些逻辑没有考虑到），过段时间就会出现部分回写不到位的问题，虽然做了几个自动更正数据的表去每天定时维护数据，但还是发现不能很好的保证所有的数据都正确，特别是有些数据自动维护的不及时，常常需要手动去处理一下。

学习狐表后，接触数据库的机会就多了很多，慢慢的学会了自己写视图，开始时，喜欢写大视图，然后在狐表中像普通表一样，直接引用视图，数据量少时，这个方法还是很不错的，但是随着数据量的增多，我发现速度是越来越慢了，为了速度，为断优化视图，这视图是写的越来越复杂了，很多时候都多到自己看的眼花的地步。

因为我是一边上线使用，一边开发，狐表客户端与原myexcel.net平台同时使用，没办法，咱是一边学习狐表一边开发系统，旧功能还没有从myexcel中全搬出来，新功能就急着要上，只能通过不断的增加新关联表实现（不知道这是不是在给自己挖坑），而关联表越多，大视图查询就越慢。为了改善这个问题，通过多日来了资料查询，现在初步学会了使用存储过程，临时表来完成查询。实现方法如下：

数据库中写存储过程

USE [excel]

GO

/****** Object: StoredProcedure [dbo].[未完成订单] Script Date: 12/24/2018 17:25:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[未完成订单]

@是否完成 nvarchar(8)

AS

BEGIN

set nocount on

/***创建出货未完成临时表****/

create table #ckw(

下单日期 datetime, 销售单号 varchar(100), 业务员 varchar(50), 品名规格 varchar(100), P数 Float, 数量 Float, 总线数 Float,

出货日期 datetime, 业务备注 Nvarchar(500), 备注 Nvarchar(500), 客户 varchar(100), 组别 Nvarchar(50), 塑件材料状况 Nvarchar(100),

计划生产日 datetime, 完成日期 datetime, 五金材料状况 Nvarchar(100), 其它备注 Nvarchar(500), 延迟信息 Nvarchar(100),

合同评审单号 varchar(100), 原序号 int, 判断是否已回交期 varchar(50), 生管交期 datetime, 生管备注 Nvarchar(500),

操作记录 Nvarchar(2000), ID int, Identify int, 出货合计 float

)

/****查询并保存出货临时表******/

insert into #ckw

SELECT dbo.tabDIYTable372.F2591 AS 下单日期, dbo.tabDIYTable372.F2592 AS 销售单号, dbo.tabDIYTable372.F2593 AS 业务员,

dbo.tabDIYTable372.F2406 AS 品名规格, dbo.订单计划生管信息.P数, dbo.tabDIYTable372.F2407 AS 数量, dbo.订单计划生管信息.总线数,

CASE WHEN dbo.订单计划生管信息.生管交期 IS NULL OR

dbo.订单计划生管信息.生管交期 = '' THEN (CASE WHEN F3337 = '' THEN F2408 ELSE F3337 END)

ELSE dbo.订单计划生管信息.生管交期 END AS 出货日期, dbo.tabDIYTable372.F2412 AS 业务备注,

CASE WHEN dbo.订单计划生管信息.备注 > '' THEN dbo.订单计划生管信息.备注 WHEN dbo.订单计划生管信息.生管备注 > '' THEN dbo.订单计划生管信息.生管备注

WHEN F3330 > '' THEN F3330 ELSE '' END AS 备注, dbo.tabDIYTable372.F2411 AS 客户, dbo.订单计划生管信息.组别,

dbo.订单计划生管信息.塑件材料状况, dbo.订单计划生管信息.计划生产日, dbo.订单计划生管信息.完成日期, dbo.订单计划生管信息.五金材料状况,

dbo.订单计划生管信息.其它备注, dbo.订单计划生管信息.延迟信息, dbo.tabDIYTable371.F2401 AS 合同评审单号, dbo.tabDIYTable372.F2405 AS 原序号,

CASE WHEN F3425 '' THEN 2 WHEN F2646 = '已结束' THEN 2 ELSE 1 END AS 判断是否已回交期, dbo.订单计划生管信息.生管交期,

dbo.订单计划生管信息.生管备注, dbo.订单计划生管信息.操作记录, dbo.tabDIYTable371.ID, dbo.订单计划生管信息._Identify AS Identify, ch.出货合计

FROM dbo.tabDIYTable372 INNER JOIN

dbo.tabDIYTable371 ON dbo.tabDIYTable371.ID = dbo.tabDIYTable372.ID INNER JOIN

dbo.订单计划生管信息 ON dbo.订单计划生管信息.合同评审单号 = dbo.tabDIYTable371.F2401 AND

dbo.订单计划生管信息.原序号 = dbo.tabDIYTable372.F2405 LEFT OUTER JOIN

(SELECT a380.F2673 AS 制令单号, SUM(a380.F2468) AS 出货合计

FROM dbo.tabDIYTable379 AS a379 INNER JOIN

dbo.tabDIYTable380 AS a380 ON a379.ID = a380.ID

WHERE (a379.F2681 > '1/1/2000')

GROUP BY a380.F2673

UNION ALL

SELECT jc.制令单号, SUM(jc.数量) AS 出库合计

FROM dbo.进出库与制令单号关联表 AS jc INNER JOIN

dbo.FT出入库 AS ftcr ON jc.进出库单号 = ftcr.单号

WHERE (ftcr.类型 = '出货清单')

GROUP BY jc.制令单号) AS ch ON ch.制令单号 = dbo.tabDIYTable372.F2592

WHERE (ISNULL(dbo.订单计划生管信息.完成日期, '1/1/1990') ISNULL(ch.出货合计, 0))

/*****创建入库未完成订单临时表********/

create table #rkw(

销售单号 varchar(100),总入库合计 float

)

/******查询并保存总入库合计********/

insert into #rkw

SELECT a405.F2650 AS 销售单号, SUM(a405.F2655) AS 总入库合计

FROM #ckw INNER JOIN dbo.tabDIYTable405 AS a405 on a405.F2650 = #ckw.销售单号 and a405.F2660 = #ckw.品名规格 INNER JOIN

dbo.tabDIYTable404 AS a404 ON a405.ID = a404.ID

WHERE (a404.bIsFinish = 1)

GROUP BY a405.F2650

UNION ALL

SELECT jc.制令单号 as 销售单号, SUM(jc.数量) AS 总入库合计

FROM #ckw INNER JOIN dbo.进出库与制令单号关联表 AS jc on jc.制令单号 = #ckw.销售单号 and jc.产品料号 = #ckw.品名规格

INNER JOIN dbo.FT出入库 AS ftcr ON jc.进出库单号 = ftcr.单号

WHERE (ftcr.类型 = '成品订单入库')

GROUP BY jc.制令单号

/*****创建分单入库临时表********/

create table #fkw(

制令单号 varchar(100), 分单序号 int, 分单量 float,转单序号 int, 转单量 float, 转单分单号 int, 转单分单量 float, 转单料号 varchar(100),

P数 Float, 总线数 Float,生管交期 datetime, 备注 Nvarchar(500), 组别 Nvarchar(50), 塑件材料状况 Nvarchar(100),

计划生产日 datetime, 完成日期 datetime, 五金材料状况 Nvarchar(100), 其它备注 Nvarchar(500), 延迟信息 Nvarchar(100),

操作记录 Nvarchar(2000), 入库合计 float

)

/******查询并保存分单入库********/

insert into #fkw

SELECT dd.制令单号, dd.分单序号, dd.分单量, dd.转单序号, dd.转单量, dd.转单分单号, dd.转单分单量, dd.转单料号, dd.P数, dd.总线数,

dd.生管交期, dd.备注, dd.组别, dd.塑件材料状况, dd.计划生产日, dd.完成日期, dd.五金材料状况, dd.其它备注, dd.延迟信息, dd.操作记录,

rk.入库合计

FROM dbo.生产制令打印及分单记录 AS dd LEFT OUTER JOIN

(SELECT jc.制令单号, jc.产品料号, SUM(jc.数量) AS 入库合计, jc.分单序号, jc.转单序号, jc.转单分单号

FROM #ckw INNER JOIN dbo.进出库与制令单号关联表 AS jc on #ckw.销售单号 = jc.制令单号 INNER JOIN

dbo.FT出入库 AS ftcr ON jc.进出库单号 = ftcr.单号

WHERE (ftcr.类型 = '成品订单入库')

GROUP BY jc.制令单号, jc.产品料号, jc.分单序号, jc.转单序号, jc.转单分单号) AS rk ON dd.制令单号 + 'F' + CONVERT(nvarchar(10), ISNULL(dd.分单序号,

0)) + 'C' + CONVERT(nvarchar(10), ISNULL(dd.转单序号, 0)) + 'CF' + CONVERT(nvarchar(10), ISNULL(dd.转单分单号, 0))

= rk.制令单号 + 'F' + CONVERT(nvarchar(10), ISNULL(rk.分单序号, 0)) + 'C' + CONVERT(nvarchar(10), ISNULL(rk.转单序号, 0)) + 'CF' + CONVERT(nvarchar(10),

ISNULL(rk.转单分单号, 0))

where dd.制令单号 in (select 销售单号 from #ckw) and (isnull(dd.完成日期,'1/1/1990')

/********出总结果*************/

select * from

(select tk.下单日期,tk.销售单号, #fkw.分单序号,#fkw.转单序号,#fkw.转单分单号,tk.业务员,case when #fkw.转单序号 > 0 then

#fkw.转单料号 else tk.品名规格 end as 品名规格,case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.P数 else tk.P数

end as P数,tk.数量 as 订单量,case when #fkw.转单序号 > 0 and #fkw.转单分单号 > 0 then #fkw.转单分单量 when #fkw.转单序号 > 0

then #fkw.转单量 when #fkw.分单序号 > 0 then #fkw.分单量 else tk.数量 end as 计划量,case when #fkw.分单序号 > 0 OR

#fkw.转单序号 > 0 then #fkw.总线数 else tk.总线数 end as 总线数,tk.出货日期,tk.业务备注,case when #fkw.分单序号 > 0

OR #fkw.转单序号 > 0 then #fkw.备注 else tk.备注 end as 备注,tk.客户,case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then

#fkw.组别 else tk.组别 end as 组别,case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.塑件材料状况 else

tk.塑件材料状况 end as 塑件材料状况,case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.计划生产日 else

tk.计划生产日 end as 计划生产日,case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.完成日期 else

tk.完成日期 end as 完成日期,case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.五金材料状况 else tk.五金材料状况

end as 五金材料状况, case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.其它备注 else tk.其它备注 end as 其它备注,

case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.延迟信息 else tk.延迟信息 end as 延迟信息, case when

#fkw.转单序号 > 0 and #fkw.转单分单号 > 0 and #fkw.转单分单量 0 and

#fkw.转单量 0 and #fkw.分单量

tk.合同评审单号,tk.原序号,case when #fkw.转单序号 > 0 and #fkw.转单分单号 > 0 and #fkw.转单分单量

when #fkw.转单序号 > 0 and #fkw.转单量 0 and #fkw.分单量

then '已入库' when tk.数量 '1/1/2000' then '生产中' else '交期确认中' end as 订单状态,

tk.判断是否已回交期,case when #fkw.转单序号 > 0 and #fkw.转单分单号 > 0 and #fkw.转单分单量

when #fkw.转单序号 > 0 and #fkw.转单量 0 and #fkw.分单量

then '已入库' when tk.数量 0 then '入库中' else '未入库' end as 入库状态,

case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.生管交期 else tk.生管交期 end as 生管交期,tk.生管备注,

case when #fkw.分单序号 > 0 OR #fkw.转单序号 > 0 then #fkw.操作记录 else tk.操作记录 end as 操作记录,tk.ID,tk.Identify,

tk.销售单号 + '-F' + CONVERT(nvarchar(10),isnull(#fkw.分单序号,0)) + '-C'+ CONVERT(nvarchar(10),isnull(#fkw.转单序号,0))

+ '-CF' + CONVERT(nvarchar(10),isnull(#fkw.转单分单号,0)) as 区分码

from (select #ckw.下单日期,#ckw.销售单号,#ckw.业务员,#ckw.品名规格,#ckw.P数, #ckw.数量, #ckw.总线数,

#ckw.出货日期, #ckw.业务备注, #ckw.备注, #ckw.客户, #ckw.组别, #ckw.塑件材料状况,

#ckw.计划生产日, #ckw.完成日期, #ckw.五金材料状况, #ckw.其它备注, #ckw.延迟信息,

#ckw.合同评审单号, #ckw.原序号, #ckw.判断是否已回交期, #ckw.生管交期, #ckw.生管备注,

#ckw.操作记录, #ckw.ID, #ckw.Identify, #ckw.出货合计,#rkw.总入库合计 from #ckw left join #rkw on #ckw.销售单号 = #rkw.销售单号

where #ckw.数量 > isnull(#rkw.总入库合计,0)) as tk left join #fkw on tk.销售单号 = #fkw.制令单号) as zzjg

where 是否完成=@是否完成

set nocount off

drop table #ckw

drop table #fkw

drop table #rkw

END

狐表中调用存储过程代码如下：

'生成数据表

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.ConnectionName = "wjexcel"

cmd.CommandText = "未完成订单" '指定存储过程名

cmd.StoredProcedure = True '表示CommandText内容不是标准的SQL语句,而是存储过程名

cmd.Parameters.Add("@是否完成","否")

dt = cmd.ExecuteReader()

Tables(e.Form.Name & "_Table1").DataSource = dt

用这种方法后，发现比没加功能前的大视图查询还要快0.6秒以上。

修改前的大视图