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

Откуда:
Сообщений: 163
Здравствуйте
Есть две таблицы: в одной T1 10000 записей, а другой T2 11129. Связей нет. Не мной проектирована. Гипотетически таблицы связаны T2 к T1 как один ко многим. T1 урезанная таблица, изначальная около 54000000 записей, поэтому не все записи из Т2 связаны с T1(перичных ключей тоже нет, может конечно в этом дело)

Есть запрос:

begin
CREATE TABLE #TempTableFile
(
id_cdrfile INT NOT NULL,
)

CREATE UNIQUE CLUSTERED INDEX I2 ON #TempTableFile (id_cdrfile ASC)

SET rowcount 8856

INSERT INTO
#TempTableFile
SELECT
id_cdrfile
FROM
cdr.cdr_files
WHERE
profilestatus = 0



SELECT --TOP (10000)
(
SELECT
TOP (1) CASE WHEN c.direction = c2.code THEN c2.id_classif END AS direction
FROM
nsi.inf_classif AS c1
INNER JOIN nsi.inf_classif AS c2
ON c1.id_classif = c2.id_parent
WHERE
c1.obo='PROFILDIRECTION'
AND GETDATE() BETWEEN c1.fd AND c1.td
AND GETDATE() BETWEEN c2.fd AND c2.td
ORDER BY direction DESC
) AS cl_direction,
(
SELECT
TOP (1) CASE WHEN (CASE DATEPART(dw, startdate) WHEN 6 THEN 1 WHEN 7 THEN 1 ELSE 2 END) = c2.code THEN c2.id_classif END AS typeday
FROM
nsi.inf_classif AS c1
INNER JOIN nsi.inf_classif AS c2
ON c1.id_classif = c2.id_parent
WHERE
c1.obo='PROFILTYPEDAY'
AND GETDATE() BETWEEN c1.fd AND c1.td
AND GETDATE() BETWEEN c2.fd AND c2.td
ORDER BY typeday DESC
) AS cl_typeday,
(
SELECT
TOP (1) CASE WHEN c.duration BETWEEN CAST(value_first AS int) AND CAST(value_last AS int) THEN c2.id_classif END AS codes
FROM
nsi.inf_classif AS c1 INNER JOIN
nsi.inf_classif AS c2 ON c1.id_classif = c2.id_parent INNER JOIN
nsi.inf_classif_value AS cv ON c2.id_classif = cv.id_classif
WHERE
cv.id_type_value=29--(SELECT id_classif FROM nsi.inf_classif AS cc WHERE obo='TYPEVALUE_SEC' AND GETDATE() BETWEEN fd AND td)
AND c1.obo='PROFILDURATION'
AND GETDATE() BETWEEN c1.fd AND c1.td
AND GETDATE() BETWEEN c2.fd AND c2.td
AND GETDATE() BETWEEN cv.fd AND cv.td
ORDER BY codes DESC
) AS cl_typeduration,
(
SELECT
TOP (1) CASE WHEN c.classnumber_outer BETWEEN CAST(value_first AS int) AND CAST(value_last AS int) THEN c2.id_classif END AS codes
FROM
nsi.inf_classif AS c1 INNER JOIN
nsi.inf_classif AS c2 ON c1.id_classif = c2.id_parent INNER JOIN
nsi.inf_classif_value AS cv ON c2.id_classif = cv.id_classif
WHERE
cv.id_type_value=35--(SELECT id_classif FROM nsi.inf_classif AS cc WHERE obo='TYPEVALUE_NUMBER' AND GETDATE() BETWEEN fd AND td)
AND c1.obo='PROFILTYPENUMBER'
AND GETDATE() BETWEEN c1.fd AND c1.td
AND GETDATE() BETWEEN c2.fd AND c2.td
AND GETDATE() BETWEEN cv.fd AND cv.td
ORDER BY codes DESC
) AS cl_typecalls,
(
SELECT
TOP (1) CASE WHEN c.partday = c2.code THEN c2.id_classif END AS partday
FROM
nsi.inf_classif AS c1
INNER JOIN nsi.inf_classif AS c2
ON c1.id_classif = c2.id_parent
WHERE
c1.obo='PROFILPARTDAY'
AND GETDATE() BETWEEN c1.fd AND c1.td
AND GETDATE() BETWEEN c2.fd AND c2.td
ORDER BY partday DESC
) AS cl_partday,
(
SELECT
TOP (1) CASE WHEN DATEPART(dw, startdate) = c2.code THEN c2.id_classif END AS dayofweek
FROM
nsi.inf_classif AS c1
INNER JOIN nsi.inf_classif AS c2
ON c1.id_classif = c2.id_parent
WHERE
c1.obo='PROFILDAYOFWEEK'
AND GETDATE() BETWEEN c1.fd AND c1.td
AND GETDATE() BETWEEN c2.fd AND c2.td
ORDER BY dayofweek DESC
) AS cl_dayofweek,
(
SELECT
TOP (1) CASE WHEN (ISNULL((SELECT a.slujba_type FROM DW.nsi.info_abonents AS a WHERE a.phonenumber=c.unifiednumber_outer AND GETDATE() BETWEEN a.fd AND a.td),5)) = c2.code THEN c2.id_classif END AS servicetype
FROM
nsi.inf_classif AS c1
INNER JOIN nsi.inf_classif AS c2
ON c1.id_classif = c2.id_parent
WHERE
c1.obo='PROFILTYPESERVICE'
AND GETDATE() BETWEEN c1.fd AND c1.td
AND GETDATE() BETWEEN c2.fd AND c2.td
ORDER BY servicetype DESC
) AS cl_servicetype,
c.unifiednumber_inner,
c.unifiednumber_outer,
c.duration
FROM
#TempTableFile TT
INNER JOIN DW.cdr.calls c
ON TT.id_cdrfile = c.id_cdrfile
WHERE
c.recordduplicflag in (1,2) --and
TRUNCATE TABLE #TempTableFile
DROP TABLE #TempTableFile


end

так вот select для временной таблицы вытягивает записи из T2. Всё записи из Т1 ссылаются всего на 9 из T2. Так вот если во временную таблицу попадает 8855 записей то запрос выполняется 1-2 сек, а если 8856 и до 11129 запрос выполняется более 3 минут ,я его выключал, ждать надоедало.
В чём может быть дело?
Спасибо.
22 сен 05, 16:11    [1902406]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом в SQL Server 2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
В чём может быть дело?
Разные планы выполнения.
22 сен 05, 16:16    [1902443]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить