主页 / dba / 问题 / 95478
Accepted
VansFannel
Asked: 2015-03-18 08:09:59 +0800 CST

我该如何改进此更新？

  • 772

我正在使用 SQL Server 2014 Express 版本，我想上传一些 OpenStreetMaps 数据。

数据是一个 172 Mb 的 XML，它加载正常。问题出在这个脚本上：

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,'') +')'

我在 SQL Server Management Studio 上得到一个内存不足的 .NET 框架，并且还从 SQLCMD 运行它。 ways表有 101,393 行和waynodes1,018,575 行。

你知道我该如何改进这个脚本吗？

顺便说一句，我从本教程中获得了这个脚本。

我没有索引，也没有执行计划。我只是按照教程进行操作，但出现内存不足错误。

sql-server performance

1 个回答

  1. Best Answer

    正如 Ypercube 在他的评论中所说，你似乎有一个不相关的子查询。这意味着对于每种方式，您都试图构建一条包含 1,000,000 个奇数节点的线。

    此外，最好在子查询的结果周围放置一个 Geography 构造函数。

    您引用的教程的更新语句有一个相关的子查询，因为WHERE子句是wn.wayid = ways.wayid Changing your update query to this 应该可以解决这个问题。

    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)

    我个人更喜欢构建类似于以下内容的更新语句。

    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)

    我已经在一些虚构的数据上测试了我更新的 SELECT 版本。 SQLFiddle

     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

相关问题