Я портирую процесс, который создает МАССИВНУЮ CROSS JOIN из двух таблиц. Результирующая таблица содержит 15 млн записей (похоже, процесс выполняет перекрестное соединение длиной 30 млн с таблицей из 2600 строк и таблицей из 12000 строк, а затем выполняет некоторую группировку, которая должна разделить ее пополам). Ряды относительно узкие — всего 6 столбцов. Работает уже 5 часов без признаков завершения. Я только что заметил несоответствие счетчика между известным хорошим и тем, что я ожидал бы от перекрестного соединения, поэтому в моем выводе нет группировки или дедупликации, которая уменьшит итоговую таблицу вдвое, но это все еще кажется, что это не собирается завершать какие-либо время скоро.
Сначала я собираюсь исключить эту таблицу из процесса, если это вообще возможно — очевидно, ее можно заменить путем присоединения к обеим таблицам по отдельности, но сейчас я не вижу, где еще она используется.
Но учитывая, что существующий процесс это делает (за меньшее время, на менее мощной машине, используя язык FOCUS), есть ли какие-то варианты повышения производительности больших CROSS JOINов в SQL Server (2005) (аппаратно не вариант, эта коробка 64-битная 8-полосная с 32-гигабайтной оперативкой)?
Подробности:
В FOCUS это написано так (я пытаюсь получить тот же результат, что и CROSS JOIN в SQL):
JOIN CLEAR *
DEFINE FILE COSTCENT
WBLANK/A1 = ' ';
END
TABLE FILE COSTCENT
BY WBLANK BY CC_COSTCENT
ON TABLE HOLD AS TEMPCC FORMAT FOCUS
END
DEFINE FILE JOINGLAC
WBLANK/A1 = ' ';
END
TABLE FILE JOINGLAC
BY WBLANK BY ACCOUNT_NO BY LI_LNTM
ON TABLE HOLD AS TEMPAC FORMAT FOCUS INDEX WBLANK
JOIN CLEAR *
JOIN WBLANK IN TEMPCC TO ALL WBLANK IN TEMPAC
DEFINE FILE TEMPCC
CA_JCCAC/A16=EDIT(CC_COSTCENT)|EDIT(ACCOUNT_NO);
END
TABLE FILE TEMPCC
BY CA_JCCAC BY CC_COSTCENT AS COST CENTER BY ACCOUNT_NO
BY LI_LNTM
ON TABLE HOLD AS TEMPCCAC
END
Таким образом, требуемый результат действительно является CROSS JOIN (он объединяет пустой столбец с каждой стороны).
В SQL:
CREATE TABLE [COSTCENT](
[COST_CTR_NUM] [int] NOT NULL,
[CC_CNM] [varchar](40) NULL,
[CC_DEPT] [varchar](7) NULL,
[CC_ALSRC] [varchar](6) NULL,
[CC_HIER_CODE] [varchar](20) NULL,
CONSTRAINT [PK_LOOKUP_GL_COST_CTR] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [JOINGLAC](
[ACCOUNT_NO] [int] NULL,
[LI_LNTM] [int] NULL,
[PR_PRODUCT] [varchar](5) NULL,
[PR_GROUP] [varchar](1) NULL,
[AC_NAME_LONG] [varchar](40) NULL,
[LI_NM_LONG] [varchar](30) NULL,
[LI_INC] [int] NULL,
[LI_MULT] [int] NULL,
[LI_ANLZ] [int] NULL,
[LI_TYPE] [varchar](2) NULL,
[PR_SORT] [varchar](2) NULL,
[PR_NM] [varchar](26) NULL,
[PZ_SORT] [varchar](2) NULL,
[PZNAME] [varchar](26) NULL,
[WANLZ] [varchar](3) NULL,
[OPMLNTM] [int] NULL,
[PS_GROUP] [varchar](5) NULL,
[PS_SORT] [varchar](2) NULL,
[PS_NAME] [varchar](26) NULL,
[PT_GROUP] [varchar](5) NULL,
[PT_SORT] [varchar](2) NULL,
[PT_NAME] [varchar](26) NULL
) ON [PRIMARY]
CREATE TABLE [JOINCCAC](
[CA_JCCAC] [varchar](16) NOT NULL,
[CA_COSTCENT] [int] NOT NULL,
[CA_GLACCOUNT] [int] NOT NULL,
[CA_LNTM] [int] NOT NULL,
[CA_UNIT] [varchar](6) NOT NULL,
CONSTRAINT [PK_JOINCCAC_KNOWN_GOOD] PRIMARY KEY CLUSTERED
(
[CA_JCCAC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
С кодом SQL:
INSERT INTO [JOINCCAC]
(
[CA_JCCAC]
,[CA_COSTCENT]
,[CA_GLACCOUNT]
,[CA_LNTM]
,[CA_UNIT]
)
SELECT Util.PADLEFT(CONVERT(varchar, CC.COST_CTR_NUM), '0',
7)
+ Util.PADLEFT(CONVERT(varchar, GL.ACCOUNT_NO), '0',
9) AS CC_JCCAC
,CC.COST_CTR_NUM AS CA_COSTCENT
,GL.ACCOUNT_NO % 900000000 AS CA_GLACCOUNT
,GL.LI_LNTM AS CA_LNTM
,udf_BUPDEF(GL.ACCOUNT_NO, CC.COST_CTR_NUM, GL.LI_LNTM, 'N') AS CA_UNIT
FROM JOINGLAC AS GL
CROSS JOIN COSTCENT AS CC
В зависимости от того, как впоследствии будет использоваться эта таблица, ее можно будет исключить из процесса, просто присоединив к обеим исходным таблицам, использованным для ее построения. Тем не менее, это чрезвычайно большие усилия по переносу, и я мог бы не найти использования таблицы в течение некоторого времени, поэтому мне было интересно, есть ли какие-либо приемы для своевременного CROSS JOINing больших таблиц (особенно с учетом того, что существующий процесс в FOCUS это можно сделать быстрее). Таким образом, я мог бы проверить правильность моего построения запроса на замену, а затем разложить его по представлениям или чему-то еще.
Я также рассматриваю возможность выделения UDF и манипуляций со строками и сначала выполнения CROSS JOIN, чтобы немного разбить процесс.
УДАЧНЫЕ РЕЗУЛЬТАТЫ:
Оказывается, пользовательские функции действительно сильно (отрицательно) влияют на производительность. Но также существует большая разница между поперечным соединением 15-метрового ряда и поперечным соединением 30-метрового ряда. У меня нет прав SHOWPLAN (бу-ху), поэтому я не могу сказать, лучше или хуже план, который он использует, после изменения индексов. Я еще не рефакторил его, но ожидаю, что вся таблица скоро исчезнет.
