Вот допустим есть запрос на 200-300 строк с джинами, групиировками и тд.Поступайте так же, как и с исходными текстами программ: разбивайте на меньшие куски используя представления (view) и вычисляемые поля в таблицах. Например, сложный поиск можно разделить на 2 представления: для поиска и результата (сам запрос будет коротким):
SELECT DISTINCT
SearchName.ID,
SearchName.[Year],
SearchName.[Name],
SearchName.WatchDisplay,
SearchName.Icon,
SearchName.AttributeIcon,
SearchName.[Range],
LEFT(SearchName.[Key], 1) AS Kind,
SearchName.UnitID
FROM SearchName INNER JOIN SearchLike ON SearchName.[Key] = SearchLike.[Key]
WHERE ((NOT @P1 IS NULL) AND (SearchLike.Search LIKE '%' + @P1 + '%'))
OR ((NOT @P2 IS NULL) AND (SearchLike.Search LIKE '%' + @P2 + '%'))
OR ((NOT @P3 IS NULL) AND (SearchLike.Search LIKE '%' + @P3 + '%'))
ORDER BY SearchName.[Range]CREATE VIEW SearchLike
AS
SELECT Film.ID,
ISNULL(CAST(Film.Year AS VARCHAR) + ' ', '') + ISNULL(Head.Name + ' ' + Film.HeadingNumber + ' ', '')
+ ISNULL(Film.Article + ' ', '') + ISNULL(Film.Name + ' ', '') + ISNULL(Film.NameTranslat + ' ', '')
+ ISNULL(Film.Addition + ' ', '') + ISNULL(Film.Country + ' ', '')
+ ISNULL(FilmAttributeGroup.Name + ' ', '') + ISNULL(FilmAttributeValue.Value + ' ', '') AS Search,
Film.[Key]
FROM FilmAttributeValue
INNER JOIN FilmAttribute ON FilmAttributeValue.ID = FilmAttribute.Attribute
INNER JOIN FilmAttributeGroup ON FilmAttributeValue.[Group] = FilmAttributeGroup.ID
RIGHT OUTER JOIN Film ON FilmAttribute.Film = Film.ID
LEFT OUTER JOIN Film AS Head ON Film.Heading = Head.ID
--
UNION
--
SELECT Person,
[Name],
'P' + CAST(Person AS VARCHAR)
FROM PersonNameFormat
WHERE ([Format] > 10) OR ([Format] = 0)
--
GOCREATE VIEW SearchName
AS
-- Фильмы:
SELECT Film.ID,
FilmNamePower.[Year] AS [Year],
FilmNamePower.NameCommon AS [Name],
Film.WatchDisplay,
Film.Icon,
Film.AttributeIcon,
'0' + Film.[Range] AS [Range],
Film.[Key],
Film.ID AS UnitID
FROM Film INNER JOIN FilmNamePower ON Film.ID = FilmNamePower.ID
--
UNION
-- Персоны:
SELECT ID,
ISNULL(YEAR(BirthDay), 1900),
NameFull,
'',
NoteIcon,
NULL,
'1' + CONVERT(VARCHAR, ISNULL(BirthDay, '19000101'), 112) + [Range],
[Key],
-1
FROM PersonCREATE VIEW FilmNamePower
AS
-- Фильмы:
SELECT Film.ID,
Film.[Range],
Film.[Range] AS [RangeSort],
ISNULL(Film.[Year], 1895) AS [Year],
Film.TitleCountry AS NameCommon,
Film.TitleYearCountry AS NameYearCountry,
Film.TitleBase AS NameBase,
Film.SearchValue,
Film.Icon,
Film.AttributeIcon,
Film.LastWatch,
Film.WatchDisplay,
Film.ID AS IDCover
FROM Film
WHERE (Heading IS NULL)
-- Многосерийные фильмы и сериалы:
UNION
--
SELECT Film.ID,
Head.[Range] + Film.[Range],
Film.[Range],
ISNULL( ISNULL(Film.[Year], Head.[Year]), 1895),
ISNULL(CAST(Head.HeadingName AS VARCHAR(200)), Head.Name) + IIF(Film.HeadingNumber = '','',', ' + Film.HeadingNumber)
+ IIF((Film.TitleBase IS NULL),'' + Film.PartDisplay, ': ' + Film.TitleBase )
+ ISNULL( ' · ' + ISNULL( Film.Country, Head.Country), ''),
ISNULL(CAST(Head.HeadingName AS VARCHAR(200)), Head.Name)
+ ', ' + Film.HeadingNumber +
+ IIF( (Film.TitleBase IS NULL),
Film.PartDisplay + ' · ' + CAST(ISNULL( ISNULL(Film.[Year], Head.[Year]), 1895) AS VARCHAR),
': ' + Film.[TitleYear] )
+ ISNULL( ' · ' + ISNULL( Film.Country, Head.Country), ''),
ISNULL(CAST(Head.HeadingName AS VARCHAR(200)), Head.Name) + IIF(Film.HeadingNumber = '','',', ' + Film.HeadingNumber)
+ IIF((Film.TitleBase IS NULL),'' + Film.PartDisplay, ': ' + Film.TitleBase ),
Head.SearchValue + ISNULL('+s' + RIGHT('00' + CONVERT([varchar], Film.Season), 2), ''),
Film.Icon,
Film.AttributeIcon,
Film.LastWatch,
Film.WatchDisplay,
Head.ID
FROM Film
INNER JOIN Film AS Head ON Film.Heading = Head.ID
WHERE NOT (Film.Heading IS NULL)
--
GO
Простой
