记录表：
CREATE TABLE [dbo].[wsd_count](
[id] [int] IDENTITY(1,1) NOT NULL,
[uid] [varchar](64) NULL,
[line] [varchar](64) NULL,
[num] [varchar](64) NULL,
[result] [varchar](64) NULL,
[data] [datetime] NULL,
[count_reserve1] [nvarchar](255) NULL,
[count_reserve2] [nvarchar](255) NULL,
[count_reserve3] [nvarchar](255) NULL,
[count_reserve4] [nvarchar](255) NULL,
[count_reserve5] [datetime] NULL,
CONSTRAINT [PK_wsd_count] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'uid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产线' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'line'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'num'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结果' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'result'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'data'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve4'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve5'
GO
产品表：
CREATE TABLE [dbo].[wsd_product](
[id] [int] IDENTITY(1,1) NOT NULL,
[num] [varchar](64) NULL,
[pmname] [varchar](64) NULL,
[prname] [varchar](64) NULL,
[scdata] [datetime] NULL,
[zsdata] [datetime] NULL,
[type] [varchar](64) NULL,
[color] [varchar](64) NULL,
[ptlot] [varchar](64) NULL,
[car] [varchar](64) NULL,
[remake] [varchar](64) NULL,
[product_reserve1] [nvarchar](255) NULL,
[product_reserve2] [nvarchar](255) NULL,
[product_reserve3] [nvarchar](255) NULL,
[product_reserve4] [nvarchar](255) NULL,
[product_reserve5] [nvarchar](255) NULL,
[product_reserve6] [nvarchar](255) NULL,
[product_reserve7] [nvarchar](255) NULL,
[product_reserve8] [datetime] NULL,
[product_reserve9] [datetime] NULL,
[product_reserve0] [datetime] NULL,
CONSTRAINT [PK_wsd_product] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'件号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'num'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'pmname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'prname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'scdata'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注塑日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'zsdata'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品分类' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'颜色' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'color'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'喷涂批次' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'ptlot'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'车号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'car'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'remake'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve4'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve5'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve6'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段7' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve7'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段8' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve8'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段9' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve9'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve0'
GO
人员表：
CREATE TABLE [dbo].[sys_user](
[id] [varchar](64) NOT NULL,
[company_id] [varchar](64) NOT NULL,
[office_id] [varchar](64) NOT NULL,
[login_name] [varchar](100) NOT NULL,
[password] [varchar](100) NOT NULL,
[no] [varchar](100) NULL,
[name] [varchar](100) NOT NULL,
[email] [varchar](200) NULL,
[phone] [varchar](200) NULL,
[mobile] [varchar](200) NULL,
[user_type] [char](1) NULL,
[photo] [varchar](1000) NULL,
[login_ip] [varchar](100) NULL,
[login_date] [datetime] NULL,
[login_flag] [varchar](64) NULL,
[create_by] [varchar](64) NOT NULL,
[create_date] [datetime] NOT NULL,
[update_by] [varchar](64) NOT NULL,
[update_date] [datetime] NOT NULL,
[remarks] [varchar](255) NULL,
[del_flag] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[sys_user] ADD DEFAULT ('0') FOR [del_flag]
GO
背景：某零件加工厂有几条产线检测产品是否合格，每检测一件产品在记录表中添加一条记录，检测结果放到result字段中，检测结果有四个大类，三十多小类，详情如下：
使用sqlsever2008，查询百万数据速度在三秒内可增加悬赏。
用临时表代替游标，你试试。
不过3秒不行，你这判断条件基本都是走全表的。
你可以逐条看看哪一块慢，进行针对优化 。
而且你可以看看是否有哪些是无用的。
你给各个关键点加索引。
你需要实时查询还是一段时间查一次？
百万数据3秒查询，这有点费劲吧，而且还需要关联表，给表建索引是必不可少的，调用存储过程
存储过程实现：
```ALTER PROCEDURE [dbo].[wsdmansum]
-- Add the parameters for the stored procedure here
@gh nvarchar(255), @xm nvarchar(255), @cx varchar(64),
@cp varchar(64), @jg varchar(64), @sdata datetime, @edata datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @moth varchar(64),@weekday varchar(64),@scdata datetime, @zsdata datetime, @jcdata datetime;
declare @num varchar(64), @pmname varchar(64), @prname varchar(64);
declare @prtype varchar(64), @color varchar(64), @ptpc varchar(64);
declare @car varchar(64), @remakes varchar(64), @ychgl float, @echgl float;
declare @hg int, @pg int, @dm int, @bf int;
declare @id varchar(64), @uid varchar(64), @value VARCHAR(10);
-- Insert statements for procedure here
--声明变量，存放当前已开启的事务数
declare @exist_trancount int
select @exist_trancount = @@trancount
if @exist_trancount > 0
--创建事务保存点
save transaction tran_proc
else
--开启新事务
begin transaction tran_proc
-- 定义临时表
if object_id('tempdb..#mypsum') is not null drop table #mypsum
create table #mypsum(
id int,pid varchar(64),moth varchar(64),weekday varchar(64),scdata datetime,zsdata datetime,jcdata datetime,
num varchar(64),pmname varchar(64),prname varchar(64),prtype varchar(64),color varchar(64),ptpc varchar(64),car varchar(64),remakes varchar(64),
ychgl float,echgl float,hg int,pg int,dm int,bf int
)
-- 定义游标.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT distinct uid FROM wsd_count a where
a.count_reserve1 like case when @gh is not null and @gh!='' then @gh+'%' else a.count_reserve1 end
and a.count_reserve2 like case when @xm is not null and @xm!='' then @xm+'%' else a.count_reserve2 end
and a.line like case when @cx is not null and @cx!='' then @cx+'%' else a.line end
and a.num like case when @cp is not null and @cp!='' then @cp+'%' else a.num end
and a.data >= case when @sdata is not null and @sdata!='' then @sdata else a.data end
and a.data <= case when @edata is not null and @edata!='' then @edata else a.data end;
-- 打开游标.
OPEN c_test_main;
WHILE 1=1
BEGIN
-- 填充数据.
FETCH NEXT FROM c_test_main INTO @uid;
-- 假如未检索到数据，退出循环.
IF @@fetch_status!= 0 BREAK;
-- 定义游标.
DECLARE c_cp_main CURSOR FAST_FORWARD FOR
SELECT distinct num FROM wsd_count a where a.uid=@uid
and a.line like case when @cx is not null and @cx!='' then @cx+'%' else a.line end
and a.num like case when @cp is not null and @cp!='' then @cp+'%' else a.num end
and a.data >= case when @sdata is not null and @sdata!='' then @sdata else a.data end
and a.data <= case when @edata is not null and @edata!='' then @edata else a.data end;
-- 打开游标.
OPEN c_cp_main;
WHILE 1=1
BEGIN
-- 填充数据.
FETCH NEXT FROM c_cp_main INTO @id;
-- 假如未检索到数据，退出循环.
IF @@fetch_status!= 0 BREAK;
SELECT
@moth = (select distinct a.count_reserve1 from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@weekday = (select distinct a.count_reserve2 from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@scdata = (select distinct b.scdata from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@zsdata = (select distinct b.zsdata from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@num = (select distinct b.num from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@pmname = (select distinct b.pmname from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@prname = (select distinct b.prname from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@prtype = (select distinct b.type from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@color = (select distinct b.color from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@ptpc = (select distinct b.ptlot from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@car = (select distinct b.car from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@remakes = (select distinct b.remake from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
@hg = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '合格%'),
@pg = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '抛光%'),
@dm = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '打磨%'),
@bf = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '报废%');
if (@hg+@pg+@dm+@bf)>0
begin
select @echgl = (@hg+@pg*0.85)/(@hg+@pg+@dm+@bf) , @ychgl = cast(@hg as float)/(@hg+@pg+@dm+@bf) ,
@ychgl = Convert(decimal(20,4),@ychgl) , @echgl = Convert(decimal(20,4),@echgl);
end
else
begin
select @ychgl = 0,@echgl = 0;
end
INSERT INTO #mypsum ([pid],[moth],[weekday],[scdata],[zsdata],[jcdata],[num],[pmname],[prname],[prtype],[color],[ptpc],[car],[remakes],[ychgl],[echgl],[hg],[pg],[dm],[bf])
VALUES
(@id,@moth,@weekday ,@scdata,@zsdata,@jcdata,@num,@pmname,@prname,@prtype,@color,@ptpc,@car,@remakes,@ychgl,@echgl,@hg,@pg,@dm,@bf)
END;
-- 关闭游标
CLOSE c_cp_main;
-- 释放游标.
DEALLOCATE c_cp_main;
END;
-- 关闭游标
CLOSE c_test_main;
-- 释放游标.
DEALLOCATE c_test_main;
select * from #mypsum;
drop table #mypsum;
if @@error<>0
goto error
if @exist_trancount = 0
--提交事务
commit tran tran_proc
return 1
error:
--回滚事务或者事务保存点
rollback transaction tran_proc
return -1
END
这么说吧，数据量如果达到千万级以上的，实时查询的速度就会降下来，因此你要求的快速查询，就不能进行实时查询，数据体量过大的情况下
建议你使用spark进行计算，然后再去取数据吧。
分表，建热点数据。 或者查询结果放缓存。