Sistema SQL Server 2014 sin sugerencias de memoria

Tengo una base de datos de SQL Server 2014 con dos tablas muy grandes, ARTICLES_NEWque tiene 17 000 000 filas, y otra tabla dbo.[203]con 705 000 000 filas.

Quiero unir esas tablas e insertar el resultado en otra tabla, pero cada vez que intento ejecutar la consulta, después de 2 horas de ejecución, aparece un error.

'System.OutOfMemoryException' was thrown

o SSMS se cerrará solo. Creé todos los índices faltantes y no puedo encontrar la manera de resolver esto.

Las otras tablas dbo.[100]son dbo.[001]tablas pequeñas con alrededor de 5000 filas cada una.

La función dbo.CLEAN_NUMBERsimplemente elimina los caracteres no alfabéticos.

¿Alguna sugerencia?

Gracias de antemano...

Aquí está mi consulta:

SELECT DISTINCT
    art.ART_ID,
    dbo.CLEAN_NUMBER(al.REFNO) AS [ARL_SEARCH_NUMBER],
    al.REFNO AS [ARL_DISPLAY_NR],
    IIF(mf.COMPARE = 1, 4, 3) AS ARL_KIND,
    al.MANNO AS [ARL_BRA_ID],
    ISNULL(sup.BRANDNAME, mf.MANCODE) AS [ARL_BRA_BRAND]
FROM
    dbo.[203] al
INNER JOIN 
    dbo.ARTICLES_NEW art ON art.ART_ARTICLE_NR = al.ARTNO
                         AND CAST(art.ART_SUP_ID AS SMALLINT) = al.BRANDNO
INNER JOIN 
    dbo.[100] mf ON mf.MANNO = al.MANNO
LEFT OUTER JOIN 
    dbo.[001] sup ON sup.MANNO = mf.MANNO
WHERE 
    al.REFNO IS NOT NULL;

Estas son las estructuras de mi tabla:

CREATE TABLE [dbo].[ARTICLES_NEW]
(
    [ART_ID] [int] IDENTITY(1,1) NOT NULL,
    [ART_ARTICLE_NR] [nvarchar](22) NOT NULL,
    [ART_SEARCH_NR] [nvarchar](22) NOT NULL,
    [ART_SUP_ID] [int] NOT NULL,
    [ART_SUP_BRAND] [nvarchar](100) NOT NULL,
    [ART_SUP_MANNO] [int] NOT NULL,
    [DESIG_GENARTNO] [int] NOT NULL,
    [ARL_KIND] [int] NOT NULL,

    CONSTRAINT [PK_ARTICLES_NEW] 
        PRIMARY KEY CLUSTERED ([ART_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 [dbo].[203]
(
    [ARTNO] [VARCHAR](22) NULL,
    [BRANDNO] [SMALLINT] NULL,
    [TABLENO] [SMALLINT] NULL,
    [MANNO] [INT] NULL,
    [COUNTRYCODE] [VARCHAR](3) NULL,
    [REFNO] [VARCHAR](22) NULL,
    [EXCLUDE] [SMALLINT] NULL,
    [SORTNO] [INT] NULL,
    [ADDITIVE] [SMALLINT] NULL,
    [REFERENCEINFO] [VARCHAR](3) NULL,
    [DELETEFLAG] [SMALLINT] NULL
) ON [PRIMARY]

Este es el código de la clean_numberfunción:

CREATE FUNCTION [dbo].[CLEAN_NUMBER]
    (@STR VARCHAR(105))
RETURNS VARCHAR(105)
AS
BEGIN
    DECLARE @LEN INT = LEN(@STR);
    DECLARE @I INT = 1;
    DECLARE @NEWSTR VARCHAR(105) = '';
    DECLARE @C CHAR;

    WHILE (@I <= @LEN)
    BEGIN
        SET @C = SUBSTRING(@STR, @I, 1);

        IF @C >= 'a' 
           AND @C <= 'z'
           OR @C >= 'A'
              AND @C <= 'Z'
           OR @C >= '0'
              AND @C <= '9'
        BEGIN
            SET @NEWSTR = CONCAT(@NEWSTR, @C);
        END;

        SET @I = @I + 1;
    END;

    RETURN @NEWSTR;
END;

Además, probé esta función para eliminar caracteres no alfanuméricos:

CREATE FUNCTION [dbo].[RemoveNonAlphaNumericCharacters] 
    (@Temp NVARCHAR(50))
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @KeepValues AS VARCHAR(50)
    SET @KeepValues = '%[^a-zA-Z0-9]%'

    WHILE PATINDEX(@KeepValues, @Temp) > 0
        SET @Temp = STUFF(@Temp, PATINDEX(@KeepValues, @Temp), 1, '')

    RETURN @Temp
END

Solo como referencia, ejecuto esta consulta en una máquina con 128 GB de memoria

Answer