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

Откуда:
Сообщений: 30
Здравствуйте,
дело вот в чём:
есть несколько таблиц: FirstTable - таблица с 2 миллионами записей, SecondTable и ThirdTable - 2 небольшие таблицы;
так же табличная функция (для определения доступных пользователю записей)

CREATE TABLE [dbo].[FirstTable](
	[FirstTableID] [uniqueidentifier] NOT NULL,
	[SecondTableID] [uniqueidentifier] NOT NULL,
	
 CONSTRAINT [PK_FirstTable] PRIMARY KEY CLUSTERED 
(
	[FirstTableID] ASC
)


CREATE TABLE [dbo].[SecondTable](
	[SecondTableID] [uniqueidentifier] NOT NULL,
	[ThirdTableID] [uniqueidentifier] NOT NULL,
	
 CONSTRAINT [PK_SecondTable] PRIMARY KEY CLUSTERED 
(
	[SecondTableID] ASC
)


CREATE TABLE [dbo].[ThirdTable](
	[ThirdTableID] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	
 CONSTRAINT [PK_ThirdTable] PRIMARY KEY CLUSTERED 
(
	[ThirdTableID] ASC
)


CREATE FUNCTION [dbo].[FuncID] (@Param1 nvarchar(256))
RETURNS  @ReturnedTable TABLE 
(
	RecordID uniqueidentifier PRIMARY KEY NOT NULL

)
AS
BEGIN
    ...
    RETURN 
END




никак не могу понять, почему долго выполняется запрос:

select * 
From (select top 10000 * 
        From FirstTable) inner join 
        SecondTable on FirstTable.SecondTableID = SecondTable.SecondTableID inner join 
        FuncID(@Param1) as Func on SecondTable.SecondTableID = Func.RecordID inner join 
        ThirdTable on SecondTable.ThirdTableID = ThirdTable.ThirdTableID


хотя если не делать join ThirdTable всё выполняется моментально
25 дек 12, 19:11    [13685191]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31978
Корниенко Василий
никак не могу понять, почему долго выполняется запрос:

select * 
From (select top 10000 * 
        From FirstTable) inner join 
        SecondTable on FirstTable.SecondTableID = SecondTable.SecondTableID inner join 
        FuncID(@Param1) as Func on SecondTable.SecondTableID = Func.RecordID inner join 
        ThirdTable on SecondTable.ThirdTableID = ThirdTable.ThirdTableID



хотя если не делать join ThirdTable всё выполняется моментально
Действительно странно, если ThirdTable небольшая...

Надо бы хоть планы сравнить.
25 дек 12, 19:25    [13685230]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30
ещё моментик,
пробовал создать временную таблицу, в которую insert'ил результат выполнения функции и подставлял вместо этой функции в join,
тоже моментально выполняется всё.
25 дек 12, 19:33    [13685251]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
Корниенко Василий,

статистику соберите
25 дек 12, 19:58    [13685310]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Crimean
Member

Откуда:
Сообщений: 13147
не поможет там статистика. ибо табличная функция возвращает табличную переменную, для которой статистика в принципе не собирается. как следствие оптимизатор не очень знает, что делать с вашим запросом
вы бы примерчик дополнили генерацией данных, относительно отражающих ваше распределение данных - возможно чего и придумалось бы..
25 дек 12, 20:17    [13685359]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30
alexeyvg
Корниенко Василий
никак не могу понять, почему долго выполняется запрос:

select * 
From (select top 10000 * 
        From FirstTable) inner join 
        SecondTable on FirstTable.SecondTableID = SecondTable.SecondTableID inner join 
        FuncID(@Param1) as Func on SecondTable.SecondTableID = Func.RecordID inner join 
        ThirdTable on SecondTable.ThirdTableID = ThirdTable.ThirdTableID



хотя если не делать join ThirdTable всё выполняется моментально
Действительно странно, если ThirdTable небольшая...

Надо бы хоть планы сравнить.


К сообщению приложен файл. Размер - 125Kb
26 дек 12, 08:50    [13686395]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30


К сообщению приложен файл. Размер - 96Kb
26 дек 12, 08:54    [13686401]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
kalimba
Member

Откуда:
Сообщений: 297
Табличные переменные не поддерживают статистику, 99% что проблема именно в этом.
26 дек 12, 10:14    [13686755]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Crimean
Member

Откуда:
Сообщений: 13147
еще раз прошу. дайте пример с заполнением данных с примерно вашими селективностями. есть обходные пути
26 дек 12, 11:35    [13687325]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
попробовать переписать функцию как Inline Table-Valued Function. что-то мне подсказывает, что там подзапрос какой то, не более того. Ну и вариант с использовать вместо функции хранимку и результат во временную таблицу тоже поможет.
26 дек 12, 12:42    [13687972]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Корниенко Василий
почему долго выполняется запрос:
...
хотя если не делать join ThirdTable всё выполняется моментально


Корниенко Василий
К сообщению приложен файл. Размер - 125Kb
<Здесь картинка, где присутствуют только FuncID и FirstTable>

К сообщению приложен файл. Размер - 96Kb
<Здесь картинка, где присутствуют FuncID, FirstTable, SecondTable, ThirdTable>

Вы как-нибудь определитесь, сколько и каких таблиц у вас участвует в моментально выполняющемся запросе, а сколько — в длительном запросе.
26 дек 12, 12:55    [13688085]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30
Crimean
еще раз прошу. дайте пример с заполнением данных с примерно вашими селективностями. есть обходные пути


CREATE TABLE [dbo].[FirstTable](
	[FirstTableID] [uniqueidentifier] NOT NULL,
	[SecondTableID] [uniqueidentifier] NOT NULL,
	[FourthTableID] [uniqueidentifier] NULL,
	[FifthTableID] [uniqueidentifier] NULL,
	[SixthTableID] [uniqueidentifier] NOT NULL,
	[TextColumn1] [nvarchar](350) NULL,
	[TextColumn2] [nvarchar](350) NULL,
	[SeventhTableID] [uniqueidentifier] NULL,
	[EighthTableID] [uniqueidentifier] NULL,
	[NinthTableID] [uniqueidentifier] NULL,
	[TenthTableID] [uniqueidentifier] NULL,
	[IntColumn1] [int] NOT NULL,
	[IntColumn2] [int] NOT NULL,
	[IntColumn3] [int] NOT NULL,
	[CreatedOn] [datetime] NOT NULL,
	[CreatedBy] [uniqueidentifier] NOT NULL,
	[ModifiedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[DateTimeColumn1] [datetime] NULL,
	[ParentFirstTableID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_FirstTable] PRIMARY KEY CLUSTERED 
(
	[FirstTableID] ASC
)


CREATE NONCLUSTERED INDEX [CreatedOnIndex] ON [dbo].[FirstTable]
(
	[CreatedOn] ASC
)
WHERE ([IntColumn3]=(0))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]


INSERT INTO [dbo].[FirstTable]
           ([FirstTableID],
	[SecondTableID],
	[FourthTableID],
	[FifthTableID],
	[SixthTableID],
	[TextColumn1],
	[TextColumn2],
	[SeventhTableID],
	[EighthTableID],
	[NinthTableID],
	[TenthTableID],
	[IntColumn1],
	[IntColumn2],
	[IntColumn3],
	[CreatedOn],
	[CreatedBy],
	[ModifiedOn],
	[ModifiedBy],
	[DateTimeColumn1],
	[ParentFirstTableID])
     VALUES
           (NewID()
           ,NewID()
           ,NewID()
           ,NewID()
           ,NewID()
           ,'123|43234|26.12.2012T13:10:00.000|1|1с**а23154|' -- в среднем около 70 символов
           ,null
           ,NewID()
           ,NewID()
           ,NewID()
           ,NewID()
           ,4
           ,1
           ,0
           ,GETUTCDATE()
           ,NewID()
           ,GETUTCDATE()
           ,NewID()
           ,GETUTCDATE()
           ,NewID())

Структуру 2х других таблиц думаю нет смысла полностью писать, в select'е только join по тем полям которые писал ранее.
Более точный запрос будет выглядеть так:

select LastRecords.*
From	(SELECT top 10000	[FirstTableID],
	                                [SecondTableID],
	                                [FourthTableID],
	                                [FifthTableID],
	                                [SixthTableID],
	                                [TextColumn1],
	                                [NinthTableID],
	                                [TenthTableID],
	                                [IntColumn1],
	                                [IntColumn2],
	                                CreatedOn,
	                                CreatedBy,
	                                [DateTimeColumn1],
	                                [ParentFirstTableID]
		FROM          dbo.FirstTable
		WHERE	IntColumn3 = 0
		Order by CreatedOn desc) as LastRecords inner join 
		SecondTable on LastRecords.SecondTableID= SecondTable.SecondTableID inner join 
		FuncID(@Param1) as func on SecondTable.SecondTableID = func.RecordID inner join 
		ThirdTable on SecondTable.ThirdTableID = ThirdTable.ThirdTableID
26 дек 12, 15:17    [13689609]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30
Мистер Хенки
попробовать переписать функцию как Inline Table-Valued Function. что-то мне подсказывает, что там подзапрос какой то, не более того. Ну и вариант с использовать вместо функции хранимку и результат во временную таблицу тоже поможет.


Функция довольно-таки большая, так что переписать не получится. И переделать на хранимку тоже никак, я эту функцию во вьюхах использую.
26 дек 12, 15:28    [13689689]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30
Гость333
Корниенко Василий
почему долго выполняется запрос:
...
хотя если не делать join ThirdTable всё выполняется моментально


Корниенко Василий
К сообщению приложен файл. Размер - 125Kb
<Здесь картинка, где присутствуют только FuncID и FirstTable>

К сообщению приложен файл. Размер - 96Kb
<Здесь картинка, где присутствуют FuncID, FirstTable, SecondTable, ThirdTable>

Вы как-нибудь определитесь, сколько и каких таблиц у вас участвует в моментально выполняющемся запросе, а сколько — в длительном запросе.


В моментальном запросе используется 2 таблицы (FirstTable и SecondTable) и функция, но SecondTable в план выполнения не попадает
26 дек 12, 15:48    [13689846]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Корниенко Василий
Crimean
еще раз прошу. дайте пример с заполнением данных с примерно вашими селективностями. есть обходные пути


INSERT INTO [dbo].[FirstTable]
<здесь одна запись>

Вы полагаете, что таким образом дали нам селективный пример данных?!

По тем вводным, что есть сейчас — можно попробовать выгружать FuncID(@Param1) во временную таблицу и использовать эту временную таблицу в запросе.
26 дек 12, 15:49    [13689862]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Корниенко Василий
В моментальном запросе используется 2 таблицы (FirstTable и SecondTable) и функция, но SecondTable в план выполнения не попадает

Дайте угадаю — у вас FirstTable и SecondTable связаны через foreign key?
26 дек 12, 15:56    [13689907]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гость333
По тем вводным, что есть сейчас — можно попробовать выгружать FuncID(@Param1) во временную таблицу и использовать эту временную таблицу в запросе.


А, так вы же так и делали:
Корниенко Василий
пробовал создать временную таблицу, в которую insert'ил результат выполнения функции и подставлял вместо этой функции в join, тоже моментально выполняется всё.


Ну нормальное решение, т.к. для временной таблицы оптимизатор знает и статистику, и cardinality, а для функции пытается играть в угадайку.
26 дек 12, 16:02    [13689943]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Crimean
Member

Откуда:
Сообщений: 13147
функция, небось, нужна, чтобы представление данных построить?
26 дек 12, 16:05    [13689970]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30
Гость333,

Как можно заметить в этой таблице из 20 полей - 12 это IDшники, 3 - дата/время.
TextColumn1 - наборы символов в различной последовательности,
IntColumn1 и IntColumn2 - целые (в промежутке от 0 до 5),
IntColumn3 - в 95% случаев = 0.
И таких записей около 2х миллионов.
26 дек 12, 16:06    [13689977]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Корниенко Василий,

а нам надо соотношение числа записей в таблицах aka "распределения данных"
чтобы собрать тестик и погонять варианты выборок на предмет таки обмануть оптимизатор
26 дек 12, 16:14    [13690054]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Корниенко Василий
Member

Откуда:
Сообщений: 30
Гость333

Ну нормальное решение, т.к. для временной таблицы оптимизатор знает и статистику, и cardinality, а для функции пытается играть в угадайку.


Не могу я использовать временную таблицу, т.к. функцию эту я использую в представлении.



Crimean
функция, небось, нужна, чтобы представление данных построить?


Функция нужна для разграничения доступа к данным. Одни юзеры видят одно, другие другое, третьи и то и то, ну и тд.
26 дек 12, 16:15    [13690060]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Корниенко Василий,

ок, с другой стороны спрошу, 2 - уровневка?
ибо если 3, то вариант с временной таблицей реализуем, а вот для 2 - уже с шевелением ушами
или, традиционно, нельзя влиять на "клиентский" код? только на "серверный"?
26 дек 12, 16:19    [13690090]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31978
Корниенко Василий
Не могу я использовать временную таблицу, т.к. функцию эту я использую в представлении.
Может быть, попробовать поменять запрос?
select LastRecords.*
From(
	SELECT	top 10000	
            [FirstTableID],
            [SecondTableID],
            [FourthTableID],
            [FifthTableID],
            [SixthTableID],
            [TextColumn1],
            [NinthTableID],
            [TenthTableID],
            [IntColumn1],
            [IntColumn2],
            CreatedOn,
            CreatedBy,
            [DateTimeColumn1],
            [ParentFirstTableID]
	FROM dbo.FirstTable
	WHERE IntColumn3 = 0
	Order by CreatedOn desc
) as LastRecords 
	inner join SecondTable on LastRecords.SecondTableID = SecondTable.SecondTableID 
	inner join (SELECT TOP 100 PERCENT * FROM FuncID(@Param1)) as func on SecondTable.SecondTableID = func.RecordID 
	inner join ThirdTable on SecondTable.ThirdTableID = ThirdTable.ThirdTableID
26 дек 12, 16:29    [13690165]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Корниенко Василий
Гость333
Ну нормальное решение, т.к. для временной таблицы оптимизатор знает и статистику, и cardinality, а для функции пытается играть в угадайку.


Не могу я использовать временную таблицу, т.к. функцию эту я использую в представлении.

Пока не вижу связи. Мы же оптимизируем запрос? В нём можно использовать временную таблицу? А в представлении пусть и дальше функция используется.
26 дек 12, 16:46    [13690301]     Ответить | Цитировать Сообщить модератору
 Re: join с табличной функицией  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Гость333,

еслисовсем просто, как я понял, это 2 - уровневка и нужно представление, через которое все будут видеть доступные данные

а самое прикольное, что даже решив эту задачу тс не сдвинется с места нифига
ибо вторым вопросом будет соотношение эффективностей "прикладного" запроса и "фильтра безопасности"
ибо если запросившему доступно 10% данных - эффективнее сначала обработать безопасность
а если запросившему доступно 90% данных - эффективнее сначала обработать прикладные фильтры
а из-за сложностей условий безопасности обычно это оценить нельзя нифига в смысле оценка будет дороже выборки
ну и вернемся к тому, с чего начали - к определению стратегии выбора данных, которая упрется в бизнес-требования и допущения / упрощения, на которые можно будет пойти для их реализации
26 дек 12, 16:52    [13690370]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить