Допустим (допустим чисто гипотетичевки ибо таблицы у нас на самом деле не было) у нас была таблица Foo:

ID, Param1, Param2, Param3

Для такой таблицы нам нужны запросы вида
Select ID FROM Foo WHERE Param1=1 AND Param2=2
При этом схема таблицы Foo динамическая, и количество столбцов Param1...ParamN определяется в рантайме, при создании таблицы и работе с ней мы НЕ знаем сколько будет Param-ов в WHERE CLAUSE

Смотрели мы на это безоброазие и решили транспонировать таблицу. Т.е. поменять её динамическую структуру на статическую а вместо динамического количества столбцов сделать динамическое количество строк. И получили что то типа такого:

ID, ParamName, ParamValue

При этом получается что если в изначальной таблице было 3 столбца и 1 запись то в новой, транспанированной будет 3 записи. По одной на каждый столбец.

Внимание вопрос

Как теперь к этой транспанированной таблице писать запросы которые по смыслу возвращают тоже самое что и запрос к первой не транспанированой таблице. Т.е. Select ID FROM Foo WHERE Param1=1 AND Param2=2

То есть как это варианты конечно есть, но вот как бы сделать это оптимальнее.

Количество записей в транспанированной таблице может достигать пары миллиардов.

Ниже структура реальной таблицы и 2 запроса больных которые родились у меня:

CREATE TABLE [dbo].[SurveyRouter](
    [CompanyID] [int] NOT NULL,
    [RouterId] [int] NOT NULL,
    [ProjectId] [int] NOT NULL,
    [Question] [nvarchar](255) NOT NULL,
    [Precode] [nvarchar](255) NOT NULL,
    [QuotaCellId] [int] NOT NULL,
    [IsOpened] [bit] NOT NULL,
 CONSTRAINT [PK_SurveyRouter_CompanyId_RouterId_ProjectId_Question_QuotaCellId] PRIMARY KEY CLUSTERED 
(
    [CompanyID] ASC,
    [RouterId] ASC,
    [ProjectId] ASC,
    [QuotaCellId] ASC,
    [Question] ASC
)

SELECT TOP(1) bigQ.ProjectId FROM
(
    SELECT DISTINCT(sub1.ProjectId) FROM
    (
    (SELECT DISTINCT(QuotaCellId), ProjectId
     FROM SurveyRouter 
     WHERE CompanyId = 4 AND RouterId = 0 AND IsOpened = 1 AND Question = N'Company4Router0Question0' AND Precode = N'Company4Router0Question0_Precode4') sub1
    INNER JOIN
    (SELECT DISTINCT(QuotaCellId), ProjectId
     FROM SurveyRouter
     WHERE CompanyId = 4 AND RouterId = 0 AND IsOpened = 1 AND Question = N'Company4Router0Question1' AND Precode = N'Company4Router0Question1_Precode3') sub2
    ON
    sub1.ProjectId =  sub2.ProjectId AND sub1.QuotaCellId = sub2.QuotaCellId
    INNER JOIN
    (SELECT DISTINCT(QuotaCellId), ProjectId
     FROM SurveyRouter
     WHERE CompanyId = 4 AND RouterId = 0 AND IsOpened = 1 AND Question = N'Company4Router0Question2' AND Precode = N'Company4Router0Question2_Precode7') sub3
    ON
    sub1.ProjectId =  sub3.ProjectId AND sub1.QuotaCellId = sub3.QuotaCellId
    )
) bigQ
INNER JOIN
  Projects pr
ON
  bigQ.ProjectId = pr.ProjectId
ORDER BY
  pr.Priority, NEWID()

SELECT TOP(1) pr.ProjectID
FROM
  Projects pr
INNER JOIN
    SurveyRouter sr0
ON
    pr.ProjectID = sr0.ProjectId
INNER JOIN
    SurveyRouter sr1
ON
    sr1.CompanyId = 4 AND sr1.RouterId = 0 AND sr1.IsOpened = 1 AND sr0.QuotaCellId = sr1.QuotaCellId
INNER JOIN
    SurveyRouter sr2
ON
    sr2.CompanyId = 4 AND sr2.RouterId = 0 AND sr2.IsOpened = 1 AND sr0.QuotaCellId = sr2.QuotaCellId
INNER JOIN
    SurveyRouter sr3
ON
    sr3.CompanyId = 4 AND sr3.RouterId = 0 AND sr3.IsOpened = 1 AND sr0.QuotaCellId = sr3.QuotaCellId
WHERE
    1 = 1 AND
    sr1.CompanyId = 4 AND sr1.RouterId = 0 AND sr1.ProjectId = sr0.ProjectId AND sr1.IsOpened = 1 AND sr1.Question = @Question1 AND sr1.Precode = @Precode1 AND
    sr2.CompanyId = 4 AND sr2.RouterId = 0 AND sr2.ProjectId = sr0.ProjectId AND sr2.IsOpened = 1 AND sr2.Question = @Question2 AND sr2.Precode = @Precode2 AND
    sr3.CompanyId = 4 AND sr3.RouterId = 0 AND sr3.ProjectId = sr0.ProjectId AND sr3.IsOpened = 1 AND sr3.Question = @Question3 AND sr3.Precode = @Precode3
ORDER BY
    pr.Priority, NEWID()

При этом оба типа запросов динамические, т.е. зависят от того сколько реальных условий будет в них послано, сейчас их послано по 3 в каждом запросе но может быть до 20.

Предлагать оставить НЕ транспонированную таблицу не предлагать. Мы это решение так же рассматриваем и будем тестировать но позже, когда закончим с транспанированным вариантом.

On 10.12.2010 3:27, Tom wrote:

> Смотрели мы на это безоброазие и решили транспонировать таблицу. Т.е. поменять
> её динамическую структуру на статическую а вместо динамического количества
> столбцов сделать динамическое количество строк. И получили что то типа такого:

Это было у вас очень грубая ошибка проектирования реляционных БД, так что вы
очень правильно сделали.



> ID, ParamName, ParamValue

Пусть будет таблица ATTR

тогда типа того :

select * -- ...
from theTable t
left join ATTR a1 on a1.id = t.id and a1.ParamName = 'atr1'
left join ATTR a2 on a2.id = t.id and a1.ParamName = 'atr2'
left join ATTR a3 on a3.id = t.id and a3.ParamName = 'atr3'
where t.id = @id

или

select * -- ...
from theTable t
left join ATTR a1 on a1.id = t.id and a1.ParamName = 'atr1'
join ATTR a2 on a2.id = t.id and a1.ParamName = 'atr2'
left join ATTR a3 on a3.id = t.id and a3.ParamName = 'atr3'
where a2.ParamValue = 'aaaaa'



> При этом оба типа запросов динамические, т.е. зависят от того сколько реальных
> условий будет в них послано, сейчас их послано по 3 в каждом запросе но может
> быть до 20.


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

В реляционной алгебре это называется "реляционным делением". Можно погуглить по теоретическим основам, и даже можно найти запрос реализующий деление через объедение, пересечение и вычитание.
Я возился с подобными вопросами и нашел что достаточно эффективно следующие:

Если есть unique key (id, ParamName), то следующий запрос будет эквивалентен исходному:

SELECT id
  FROM table 
 WHERE (ParamName = 'Param1' AND ParamValue = 1)
    OR (ParamName = 'Param2' AND ParamValue = 2)
GROUP BY id
 HAVING COUNT(*) = 2

Пока получается, что запрос все равно остается динамический, чтобы убрать это создаем временную QUERY:
query_id, ParamName, ParamValue
таблица может быть временной, вместо таблицы может использоваться динамический массив (не знаю, можно ли в MSSQL Server работать в SQL с массивами), но это уже детали реализации.
Далее делаем следующее:

-- подготовка
INSERT INTO query(query_id, ParamName, ParamValue) VALUES ('mynewquery17', 'Param1', 1);
INSERT INTO query(query_id, ParamName, ParamValue) VALUES ('mynewquery17', 'Param2', 2);

-- запрос
SELECT id
  FROM table, query 
 WHERE table.ParamName = query.ParamName  
   AND table.ParamValue = query.ParamValue 
   AND query = 'mynewquery17'
GROUP BY id
 HAVING COUNT(*) = (SELECT COUNT(*) FROM query WHERE query = 'mynewquery17') 
-- очистка
DELETE FROM query WHERE query = 'mynewquery17';удем тестировать но позже, когда закончим с транспанированным вариантом.

Здравствуйте, MasterZiv, Вы писали:

MZ>On 10.12.2010 3:27, Tom wrote:

>> Смотрели мы на это безоброазие и решили транспонировать таблицу. Т.е. поменять
>> её динамическую структуру на статическую а вместо динамического количества
>> столбцов сделать динамическое количество строк. И получили что то типа такого:

MZ>Это было у вас очень грубая ошибка проектирования реляционных БД, так что вы
MZ>очень правильно сделали.



>> ID, ParamName, ParamValue

MZ>Пусть будет таблица ATTR

MZ>тогда типа того :

MZ>select * -- ...
MZ>from theTable t
MZ>left join ATTR a1 on a1.id = t.id and a1.ParamName = 'atr1'
MZ>left join ATTR a2 on a2.id = t.id and a1.ParamName = 'atr2'
MZ>left join ATTR a3 on a3.id = t.id and a3.ParamName = 'atr3'
MZ>where t.id = @id

А почему LEFT а не INNER JOIN? Ну и у нас пары, параметр=значение, а не просто атрибут...

MZ>или

MZ>select * -- ...
MZ>from theTable t
MZ>left join ATTR a1 on a1.id = t.id and a1.ParamName = 'atr1'
MZ>join ATTR a2 on a2.id = t.id and a1.ParamName = 'atr2'
MZ>left join ATTR a3 on a3.id = t.id and a3.ParamName = 'atr3'
MZ>where a2.ParamValue = 'aaaaa'



MZ>Вообще такие запросы лучше писать не руками, а программно создавать.
MZ>Или использовать что-то типа Hibernate для их генерации и выполнения.
Ессно мы эти запросы динамичсески генерим только без ORM.

F>Пока получается, что запрос все равно остается динамический, чтобы убрать это создаем временную QUERY:
На самом деле проблемы с созданием динамического запроса никаких, главное что бы он максимально быстро выполнялся

MZ>Или использовать что-то типа Hibernate для их генерации и выполнения.

В Хибернэйте ты мэппишь "объект" на базу. Объект должен иметь заранее созданную структуру. А в транспонированном варианте не будет "объекта", будет просто список свойств.

MZ>Это было у вас очень грубая ошибка проектирования реляционных БД, так что вы
MZ>очень правильно сделали.

Уже спорили, поспорим ещё раз

Что они правильно сделали? Послали ссылочную целостность на йух? Или что типизированные данные теперь будут хранить в строковых полях и везде мучаться с конвертацией? Для строковых полей "between ... and ... " (да и вообще любые сравнения кроме =) работают без учёта типа данных. Конвертировать при фильтрации к нужному виду и послать индексы вслед за целостностью? Про эту убийственную работу по обратному транспонтрованию так и быть промолчу, потому что топискстартер утверждает, что им 7 вёрст не крюк

Здравствуйте, avpavlov, Вы писали:

MZ>>Это было у вас очень грубая ошибка проектирования реляционных БД, так что вы
MZ>>очень правильно сделали.

A>Уже спорили, поспорим ещё раз

A>Что они правильно сделали? Послали ссылочную целостность на йух?
Или что типизированные данные теперь будут хранить в строковых полях и везде мучаться с конвертацией? Для строковых полей "between ... and ... " (да и вообще любые сравнения кроме =) работают без учёта типа данных. Конвертировать при фильтрации к нужному виду и послать индексы вслед за целостностью? Про эту убийственную работу по обратному транспонтрованию так и быть промолчу, потому что топискстартер утверждает, что им 7 вёрст не крюк

не совсем понятно зачем вы отвечаете если сказать нечего. Замечу что про 7 вёрст я не писал. Просто отмечу что если мы выберем вариант с "разбитыми таблицами", то их количество будет примерно 500, а количество индексов при этом 10.000.

Здравствуйте, Tom, Вы писали:

Tom>На самом деле проблемы с созданием динамического запроса никаких, главное что бы он максимально быстро выполнялся
Тоже проходил через эти грабли: При большом наборе Params-Values, "вдруг" становится заметно, что запросы не только исполняются, но и компилируются....

Tom>не совсем понятно зачем вы отвечаете если сказать нечего.

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

Tom>Замечу что про 7 вёрст я не писал.

Это была производная метафора от твоей цитаты "На самом деле проблемы с созданием динамического запроса никаких".

Tom>Просто отмечу что если мы выберем вариант с "разбитыми таблицами", то их количество будет примерно 500,

И чего?

Tom>а количество индексов при этом 10.000.

Если ты собираешься хранить и числа и даты, то в твоём варианте будет один индекс, но во многих случаях бесполезный

Опять же я не понимаю страха перед большим числом индексов. Ну будет десять тыщ, и чего? Меньше локов будет да и всё. Опять же проще будет раскидать таблицы на несколько дисков, чем партицировать одну большую (ну это, конечно, если хардваре позволяет).

В МС СКЛ есть прикольная фишка — он собирает статистику, каких индексов ему не хватает. Так что можно запустить базу как есть, а потом постепенно добавлять те, которые наиболее часто требуются. А редко используемые колонки останутся без индексов.

Ну и в завершение — я, собственно, не против такого подхода Просто часть людей на данном форуме считает его венцом эволюции отказываясь просто напросто даже слушать об альтернативах. То, что ты собираешься попробовать оба подхода — это гуд. Хорошо бы отписаться сюда о результатах анализа и принятом решении.

Здравствуйте, avpavlov, Вы писали:



A>Если ты собираешься хранить и числа и даты, то в твоём варианте будет один индекс, но во многих случаях бесполезный :)
Да что ты с типизацией привязался, если как следует подумать, то можно хранить и типизированные параметры, самый простой способ:

id, ParamName, Type, StrValue, DateValue, NumericValue

его, естественно, тоже можно развить.

F>

F>id, ParamName, Type, StrValue, DateValue, NumericValue  
F>

F>его, естественно, тоже можно развить.

Я правильно понимаю, что приложение в момент генерации СКЛ не будет знать про тип, который хранится в Type?

Для разминки, перепиши свои запросы под учёт поля Type на лету (чтобы понять на что ты их обрекаешь своим советом)

Тут, кстати, ещё придётся мозг наморщить, чтобы индекс всё-таки не пошёл на йух

Здравствуйте, avpavlov, Вы писали:

F>>

F>>id, ParamName, Type, StrValue, DateValue, NumericValue  
F>>

F>>его, естественно, тоже можно развить.

A>Я правильно понимаю, что приложение в момент генерации СКЛ не будет знать про тип, который хранится в Type?
Тип переменной, которая биндится на Value известен? значит и Type известен.

A>Для разминки, перепиши свои запросы под учёт поля Type на лету (чтобы понять на что ты их обрекаешь своим советом) ;)
A>Тут, кстати, ещё придётся мозг наморщить, чтобы индекс всё-таки не пошёл на йух ;)
Вот эти два пункта, зря. Это элементарно, тем более если запрос динамический.

Здравствуйте, Formidable, Вы писали:

F>Здравствуйте, Tom, Вы писали:

Tom>>На самом деле проблемы с созданием динамического запроса никаких, главное что бы он максимально быстро выполнялся
F>Тоже проходил через эти грабли: При большом наборе Params-Values, "вдруг" становится заметно, что запросы не только исполняются, но и компилируются....
У меня их много не будет, 5 — ну максимум 10 а реальнее всего 2-4

On 10.12.2010 12:00, Tom wrote:

> А почему LEFT а не INNER JOIN? Ну и у нас пары, параметр=значение, а не просто
> атрибут...

Предполагается, что все атрибуты NULL-able. По факту так оно и есть по схеме.
Но если атрибут уже бизнес-логикой гарантируется быть обязательным, то можно
писать INNER JOIN.

Ну и у нас пары, параметр=значение, а не просто
> атрибут...

Эт я не понял.

>> > ID, ParamName, ParamValue
— такая таблица ?

On 10.12.2010 12:53, avpavlov wrote:

> В Хибернэйте ты мэппишь "объект" на базу. Объект должен иметь заранее созданную
> структуру. А в транспонированном варианте не будет "объекта", будет просто
> список свойств.

Будет объект, и коллекция его атрибутов. Что не ясно-то ?
Ты хочешь сказать, что эту структуру нельзя смапить через Hiber ?
Можно.

On 10.12.2010 12:59, avpavlov wrote:

> Что они правильно сделали?

Они не стали нарушать 1-ую нормальную форму.

> Послали ссылочную целостность на йух?

Ссылочную целостность можно сделать и в этом варианте.

Или что
> типизированные данные теперь будут хранить в строковых полях

Кто тебе сказал, что в строковых ?

и везде мучаться с
> конвертацией?

Не, мучится не надо. Надо хранить атрибуты разных типов отдельно в разных
таблицах. Это всё решаемо, если надо. Обсуждать нечего.

> к нужному виду и послать индексы вслед за целостностью? Про эту убийственную
> работу по обратному транспонтрованию так и быть промолчу, потому что
> топискстартер утверждает, что им 7 вёрст не крюк

Это не убийственная работа. Это необходимая работа. По-другому нельзя.
Невозможно.

avpavlov, ты пойми одно. Реляционных таблиц с переменным числом полей не бывает.
Это мрак, это край. Это ехать дальше некуда. Таблицу обработать невозможно.

On 10.12.2010 13:34, avpavlov wrote:

> Ну и в завершение — я, собственно, не против такого подхода Просто часть людей
> на данном форуме считает его венцом эволюции отказываясь просто напросто даже
> слушать об альтернативах.

Если это намёк на меня, то мимо. EAV я отнють не считаю "серебряной пулей".
Он должен применяться собственно только в одном случае -- когда кол-во атрибутов
сущности переменное.

On 10.12.2010 13:23, Tom wrote:

> не совсем понятно зачем вы отвечаете если сказать нечего. Замечу что про 7 вёрст
> я не писал. Просто отмечу что если мы выберем вариант с "разбитыми таблицами",

Tom, ты может быть, если не трудно, опубликовал бы структуры этой мегатаблицы ДО
и ПОСЛЕ. Ну чтобы более предметный разговор был.

A>>Я правильно понимаю, что приложение в момент генерации СКЛ не будет знать про тип, который хранится в Type?
F>Тип переменной, которая биндится на Value известен? значит и Type известен.

А откуда он известен?

F>Вот эти два пункта, зря. Это элементарно, тем более если запрос динамический.

Если в приложении известны все типы всех 10000 переменных — то элементарно. Но факт "известности" пока ниоткуда не следует