--我要执行下面语句,传递加班数据到#k19zjk,xz503是不需要传递的加班记录的日期
各位大侠帮我看看下面SQL语句能如何优化,K19有几百万记录declare @datebegin datetime,@dateend datetime
select @datebegin ='2009-06-01',@dateend ='2009-06-30'delete from dbo.k19 where dbo.k19.K1906>=@datebegin and dbo.k19.K1906<=@dateend
if object_id('tempdb..#k19zjk')>0
drop table #k19zjk
select ldsyd.dbo.K19.* into #k19zjk from ldsyd.dbo.K19 where ldsyd.dbo.K19.k1906>=@datebegin and ldsyd.dbo.K19.K1906<=@dateendand ldsyd.dbo.K19.k1906 not in (select xz503 from xz5 where xz503>=@datebegin and xz503<=@dateend )--以下是表结构--加班记录表
CREATE TABLE [K19] (
[K1900] [char] (38) COLLATE Chinese_PRC_CI_AS NOT NULL , --newid()
[A0188] [int] NOT NULL , --人员ID-
[K1905] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --加班名称
[K1906] [datetime] NULL , --加班日期
[K1907] [datetime] NULL , --开始时间
[K1908] [datetime] NULL --结束时间
CONSTRAINT [pk_K19] PRIMARY KEY CLUSTERED
(
[K1900]
) ON [PRIMARY]
) ON [PRIMARY]
GO--计划表
CREATE TABLE [XZ5] (
[KGUID] [varchar] (38) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[XZ503] [datetime] NOT NULL ,
CONSTRAINT [PK_XZ5] PRIMARY KEY CLUSTERED
(
[KGUID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
各位大侠帮我看看下面SQL语句能如何优化,K19有几百万记录declare @datebegin datetime,@dateend datetime
select @datebegin ='2009-06-01',@dateend ='2009-06-30'delete from dbo.k19 where dbo.k19.K1906>=@datebegin and dbo.k19.K1906<=@dateend
if object_id('tempdb..#k19zjk')>0
drop table #k19zjk
select ldsyd.dbo.K19.* into #k19zjk from ldsyd.dbo.K19 where ldsyd.dbo.K19.k1906>=@datebegin and ldsyd.dbo.K19.K1906<=@dateendand ldsyd.dbo.K19.k1906 not in (select xz503 from xz5 where xz503>=@datebegin and xz503<=@dateend )--以下是表结构--加班记录表
CREATE TABLE [K19] (
[K1900] [char] (38) COLLATE Chinese_PRC_CI_AS NOT NULL , --newid()
[A0188] [int] NOT NULL , --人员ID-
[K1905] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , --加班名称
[K1906] [datetime] NULL , --加班日期
[K1907] [datetime] NULL , --开始时间
[K1908] [datetime] NULL --结束时间
CONSTRAINT [pk_K19] PRIMARY KEY CLUSTERED
(
[K1900]
) ON [PRIMARY]
) ON [PRIMARY]
GO--计划表
CREATE TABLE [XZ5] (
[KGUID] [varchar] (38) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[XZ503] [datetime] NOT NULL ,
CONSTRAINT [PK_XZ5] PRIMARY KEY CLUSTERED
(
[KGUID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
declare @datebegin datetime,@dateend datetime
select @datebegin ='2009-06-01',@dateend ='2009-06-30' delete from dbo.k19 where dbo.k19.K1906>=@datebegin and dbo.k19.K1906 <=@dateend
if object_id('tempdb..#k19zjk')>0
drop table #k19zjk
select a.*
into #k19zjk
from ldsyd.dbo.K19 a
where a.k1906>=@datebegin
and a.K1906 <=@dateend
and not exists(
select 1
from zx5
where xz503>=@datebegin
and xz503 <=@dateend and xz503=a.k1906)
他就会停止使用索引转而执行全表扫描.
CREATE
INDEX [INX_K19] ON [dbo].[K19] ([K1900], [A0188], [K1906])
WITH
DROP_EXISTING
ON [PRIMARY]
CREATE
INDEX [INX_K19_1] ON [dbo].[K19] ([A0188], [K1906])
WITH
DROP_EXISTING
ON [PRIMARY]需要在k1906上单独加一个吗?
2.select * from k19 where k1905<>'平时加班' and k1905<>'周末加班'执行效率一样否?汗,SQL的执行计划我看不懂