Início / dba / Perguntas / 95478
Accepted
VansFannel
Asked: 2015-03-18 08:09:59 +0800 CST

Como posso melhorar esta atualização?

  • 772

Estou trabalhando com a edição SQL Server 2014 Express e quero carregar alguns dados do OpenStreetMaps.

Os dados são um XML de 172 Mb e carregam bem. O problema está neste script:

UPDATE ways
SET geog4326 = 'LINESTRING(' + STUFF((
SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
FROM
ways w JOIN waynodes wn ON w.wayid = wn.wayid
JOIN nodes n ON wn.nodeid = n.nodeid
WHERE wn.wayid = w.wayid
ORDER BY w.wayid, orderid
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)'),1,1,'') +')'

Recebo uma estrutura .NET sem memória no SQL Server Management Studio e também o executo no SQLCMD. waystabela tem 101.393 linhas e waynodestem 1.018.575 linhas.

Você sabe como posso melhorar esse script?

By the way, eu recebo este script deste tutorial .

Não tenho índice e nem execução de plano. Estou apenas seguindo o tutorial e recebo um erro de falta de memória.

sql-server performance

1 respostas

  1. Best Answer

    Como Ypercube disse em seu comentário, você parece ter uma subconsulta não correlacionada. Isso significa que, para cada caminho, você está tentando criar uma linha contendo 1.000.000 de nós ímpares.

    Também seria melhor colocar um construtor Geography em torno do resultado da subconsulta.

    A instrução de atualização do tutorial que você mencionou tem uma subconsulta correlacionada, porque a WHEREcláusula é wn.wayid = ways.wayid Alterar sua consulta de atualização para isso deve corrigir o problema.

    UPDATE ways
SET geog4326 = Geography::STGeomFromText('LINESTRING(' + STUFF((
    SELECT ',' + 
        CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + 
        CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
    FROM ways w 
        JOIN waynodes wn ON w.wayid = wn.wayid
        JOIN nodes n ON wn.nodeid = n.nodeid
    WHERE wn.wayid = ways.wayid -- Changed to ways to correlate
    ORDER BY w.wayid, orderid
    FOR XML PATH(''), TYPE
    ).value('/', 'NVARCHAR(MAX)'),1,1,'') +')',4326)
WHERE EXISTS (SELECT 1 FROM waynodes wg WHERE ways.wayid = wg.wayid GROUP BY wg.wayid HAVING COUNT(*) > 1)

    Pessoalmente, prefiro construir minhas declarações de atualização semelhantes às seguintes.

    UPDATE z
SET geog4326 = Geography::STGeomFromText('LINESTRING(' + STUFF(CAST(x.linestring AS VARCHAR(MAX)),1,1,'') +')',4326)
FROM ways z
    CROSS APPLY (
        SELECT ',' + 
            CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + 
            CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
        FROM ways w 
            JOIN waynodes wn ON w.wayid = wn.wayid
            JOIN nodes n ON wn.nodeid = n.nodeid
        WHERE z.wayid = w.wayid 
        ORDER BY w.wayid, orderid
        FOR XML PATH('')
    ) x (linestring)
WHERE EXISTS (SELECT 1 FROM waynodes wg WHERE z.wayid = wg.wayid GROUP BY wg.wayid HAVING COUNT(*) > 1)

    Eu testei uma versão SELECT da minha atualização em alguns dados inventados. SQLFiddleName

     WITH ways AS (
 SELECT *
 FROM (VALUES
    (1),(2),(3)
    ) W(wayid)
 ), 
 waynodes AS (
 SELECT *
 FROM (VALUES
    (1,1,1),
    (1,2,2),
    (1,3,3),
    (2,1,4),
    (2,2,5),
    (2,3,6),
    (3,1,7),
    (3,2,8)
    ) WN(wayid, orderid, nodeid)
 ), 
 nodes AS (
 SELECT *
 FROM (VALUES
    (1, 0, 0),
    (2, .1, 0),
    (3, .1, .1),
    (4, .1, .1),
    (5, .2, 0),
    (6, .3, 0),
    (7, 0, 0),
    (8, 0, .2)
    ) N(nodeid, longitude, latitude)
 )
SELECT Geography::STGeomFromText('LINESTRING(' + STUFF(x.linestring,1,1,'') +')',4326)
FROM ways z
    CROSS APPLY (
        SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
        FROM ways w 
            JOIN waynodes wn ON w.wayid = wn.wayid
            JOIN nodes n ON wn.nodeid = n.nodeid
        WHERE z.wayid = w.wayid 
        ORDER BY w.wayid, orderid
        FOR XML PATH('')
        ) x(linestring)
    • 3

relate perguntas