Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка за период  [new]
bvmblad
Member

Откуда: Минск
Сообщений: 184
Добрый день. server 2000.

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


Столкнулся с проблемой. Есть таблица

CREATE TABLE [Working_Hours] (
	[Rec_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Exec_Date] [datetime] NOT NULL CONSTRAINT [DF_Working_Hours_Exec_Date] DEFAULT (convert(nvarchar(11),getdate())),
	[Start_Hours] [tinyint] NOT NULL CONSTRAINT [DF_Working_Hours_Start_Hours] DEFAULT (0),
	[Start_Minutes] [tinyint] NOT NULL CONSTRAINT [DF_Working_Hours_Start_Minutes] DEFAULT (0),
	[End_Hours] [tinyint] NOT NULL CONSTRAINT [DF_Working_Hours_End_Hours] DEFAULT (0),
	[End_Minutes] [tinyint] NOT NULL CONSTRAINT [DF_Working_Hours_End_Minutes] DEFAULT (0),
	[Order_ID] [int] NOT NULL CONSTRAINT [DF_Working_Hours_Order_ID] DEFAULT (0),
	[ID_PR] [int] NOT NULL CONSTRAINT [DF_Working_Hours_ID_PR] DEFAULT (0),
	[Work_ID] [int] NOT NULL CONSTRAINT [DF_Working_Hours_Work_ID] DEFAULT (0),
	[Customer_ID] [int] NOT NULL ,
	[Author_ID] [int] NULL CONSTRAINT [DF_Working_Hours_Author_ID] DEFAULT ([dbo].[_UserID]()),
	[Input_date] [datetime] NULL CONSTRAINT [DF_Working_Hours_Input_date] DEFAULT (getdate()),
	CONSTRAINT [PK_Working_Hours] PRIMARY KEY  NONCLUSTERED 
	(
		[Rec_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [FK_Working_Hours_Works_Classifier] FOREIGN KEY 
	(
		[Work_ID]
	) REFERENCES [Works_Classifier] (
		[Work_ID]
	) NOT FOR REPLICATION 
) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Working_Hours] ON [dbo].[Working_Hours]([Customer_ID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_Working_Hours_1] ON [dbo].[Working_Hours]([Exec_Date]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_Working_Hours_2] ON [dbo].[Working_Hours]([Order_ID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO


в которой порядка 400000 записей. надо отбирать записи за определенный период по полю Exec_Date . Записей в таблице за 5 лет. Обычно делается выборка за 1 месяц. Критерии отбора периода для каждого пользователя находятся в таблице периодов и значение начальной и конечной даты получается через функцию.


CREATE  FUNCTION [_DateB](@Period_ID int)
RETURNS datetime 

AS  
BEGIN 

declare @date as datetime

Select @date =   dbo.Work_Period.BDate_P
    FROM     dbo.Work_Period INNER JOIN
               dbo.logins ON dbo.Work_Period.User_ID = dbo.logins.User_ID

   WHERE  (dbo.Work_Period.Period_ID = @Period_ID) 
           AND (dbo.logins.SQL_User = SYSTEM_USER)

return @date

END


CREATE  FUNCTION [_DateE](@Period_ID int)
RETURNS datetime 

AS  
BEGIN 

declare @date as datetime

Select @date =   dbo.Work_Period.EDate_P
    FROM     dbo.Work_Period INNER JOIN
               dbo.logins ON dbo.Work_Period.User_ID = dbo.logins.User_ID

   WHERE  (dbo.Work_Period.Period_ID = @Period_ID) 
           AND (dbo.logins.SQL_User = SYSTEM_USER)

return @date

END



если я делаю запрос такого вида

SELECT  Order_ID, Customer_ID
FROM        Working_Hours
where Exec_Date BETWEEN dbo._DateB(53) AND dbo._DateE(53)

происходят тормоза на 1 минуту вот план запроса
  |--Filter(WHERE:([Working_Hours].[Exec_Date]>=[dbo].[_DateB](53) AND [Working_Hours].[Exec_Date]<=[dbo].[_DateE](53)))
       |--Table Scan(OBJECT:([PowerHouse].[dbo].[Working_Hours]))

в хранимке я использую такой вариант


declare @dateB datetime
declare @dateE datetime
set @dateB=dbo._DateB(53)
set @dateE=dbo._DateE(53)
SELECT  Order_ID, Customer_ID
FROM        Working_Hours
where Exec_Date BETWEEN  @dateB AND @dateE


  |--Table Scan(OBJECT:([PowerHouse].[dbo].[Working_Hours]), WHERE:([Working_Hours].[Exec_Date]>=[@dateB] AND [Working_Hours].[Exec_Date]<=[@dateE]))

и проблем никаких.
Как можно построить запрос на выборку в не используя переменные, просто не везде возможно использование хранимых процедур и работа по варианту 2 ? Спасибо.
4 дек 09, 09:47    [8017983]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за период  [new]
aleks2
Guest
bvmblad,

Откройте для себя кластерный индекс. Создайте

CREATE UNIQUE CLUSTERED INDEX [IX_Working_Hours_ExecDate_RecID] ON [dbo].[Working_Hours]([Exec_Date], [Rec_ID])ON [PRIMARY]

И не парьтесь.
4 дек 09, 10:31    [8018275]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за период  [new]
bvmblad
Member

Откуда: Минск
Сообщений: 184
aleks2
bvmblad,

Откройте для себя кластерный индекс. Создайте

CREATE UNIQUE CLUSTERED INDEX [IX_Working_Hours_ExecDate_RecID] ON [dbo].[Working_Hours]([Exec_Date], [Rec_ID])ON [PRIMARY]

И не парьтесь.


спасибо.
мы не ищем легких путей =) , что такое кластерный индекс я знаю, эта таблица частный пример по этой схеме работают многие таблицы где поле даты имеет просто индекс не кластерный, кластерных индексов на всех не хватает, я немного не коректно привел скрипт данной таблицы. В реальной схеме кластерный индекс стоит по полю Order_ID (он чаще надо).
4 дек 09, 10:52    [8018437]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за период  [new]
aleks2
Guest
bvmblad
aleks2
bvmblad,

Откройте для себя кластерный индекс. Создайте

CREATE UNIQUE CLUSTERED INDEX [IX_Working_Hours_ExecDate_RecID] ON [dbo].[Working_Hours]([Exec_Date], [Rec_ID])ON [PRIMARY]

И не парьтесь.


спасибо.
мы не ищем легких путей =) , что такое кластерный индекс я знаю, эта таблица частный пример по этой схеме работают многие таблицы где поле даты имеет просто индекс не кластерный, кластерных индексов на всех не хватает, я немного не коректно привел скрипт данной таблицы. В реальной схеме кластерный индекс стоит по полю Order_ID (он чаще надо).


Тогда у вас есть еще возможность изучить покрывающие индексы.
И тоже не париться.
4 дек 09, 10:59    [8018471]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за период  [new]
aleks2
Guest
Ну и мелкие усовершенствования
1. Функции надо писать короче
CREATE  FUNCTION [_DateE](@Period_ID int)
RETURNS datetime 
AS  
BEGIN 
return (Select TOP 1  dbo.Work_Period.EDate_P
    FROM     dbo.Work_Period INNER JOIN
               dbo.logins ON dbo.Work_Period.User_ID = dbo.logins.User_ID
   WHERE  (dbo.Work_Period.Period_ID = @Period_ID) 
           AND (dbo.logins.SQL_User = SYSTEM_USER)
)
END

2. Функции с результатом DATETIME сервер держит за НЕдетерминистические...
SELECT  Order_ID, Customer_ID
FROM        Working_Hours
inner join
(select dbo._DateB(53) D1, dbo._DateE(53) D2) X
ON Exec_Date BETWEEN D1 AND D2
позволяет направить сервер на детерминистический путь...
4 дек 09, 11:09    [8018517]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за период  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> 2. Функции с результатом DATETIME сервер держит за НЕдетерминистические...

datetime-то чем провинился? вот SYSTEM_USER - это да.

Posted via ActualForum NNTP Server 1.4

4 дек 09, 11:20    [8018597]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за период  [new]
bvmblad
Member

Откуда: Минск
Сообщений: 184
aleks2
Ну и мелкие усовершенствования
1. Функции надо писать короче
CREATE  FUNCTION [_DateE](@Period_ID int)
RETURNS datetime 
AS  
BEGIN 
return (Select TOP 1  dbo.Work_Period.EDate_P
    FROM     dbo.Work_Period INNER JOIN
               dbo.logins ON dbo.Work_Period.User_ID = dbo.logins.User_ID
   WHERE  (dbo.Work_Period.Period_ID = @Period_ID) 
           AND (dbo.logins.SQL_User = SYSTEM_USER)
)
END

2. Функции с результатом DATETIME сервер держит за НЕдетерминистические...
SELECT  Order_ID, Customer_ID
FROM        Working_Hours
inner join
(select dbo._DateB(53) D1, dbo._DateE(53) D2) X
ON Exec_Date BETWEEN D1 AND D2
позволяет направить сервер на детерминистический путь...


Спасибо большое просвятили (2. Функции с результатом DATETIME сервер держит за НЕдетерминистические...) заработало веселее
4 дек 09, 11:31    [8018664]     Ответить | Цитировать Сообщить модератору
 Re: Выборка за период  [new]
bvmblad
Member

Откуда: Минск
Сообщений: 184
daw

> 2. Функции с результатом DATETIME сервер держит за НЕдетерминистические...

datetime-то чем провинился? вот SYSTEM_USER - это да.


есть таблица logins в которой User_ID ключ и имя пользователя SQL_User
все входят под своей учеткой

во всех таблицах прописывается User_ID
4 дек 09, 11:39    [8018724]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить