再来一个猛点的,优化XML读取,只优化即可,目前是3-4秒左右,能再优化下的答案+200分

Question

再来一个猛点的,优化XML读取,只优化即可,目前是3-4秒左右,能再优化下的答案+200分
如能优化到毫秒级别再发贴+400分
测试的时候可能需要一些权限,读取本地XML文件第一步,
定义表变量,一会儿用来查询DECLARE @BA_WEATHER TABLE (
[CityID] [nvarchar](20) NULL,
[CityName] [nvarchar](20) NULL,
[DateFirst] [nvarchar](20) NULL,
[DateFirstWeatherAbstract] [nvarchar](20) NULL,
[DateFirstHighTemperature] [nvarchar](20) NULL,
[DateFirstLowTemperature] [nvarchar](20) NULL,
[DateFirstWindDirection] [nvarchar](20) NULL,
[DateFirstWindPower] [nvarchar](20) NULL,
[DateFirstBinqilin] [nvarchar](20) NULL,
[DateFirstBinqilinClass] [nvarchar](20) NULL,
[DateFirstBinqilinDescription] [nvarchar](20) NULL,
[DateFirstChuanyi] [nvarchar](20) NULL,
[DateFirstChuanyiClass] [nvarchar](20) NULL,
[DateFirstChuanyiDescription] [nvarchar](20) NULL,
[DateFirstTiganwendu] [nvarchar](20) NULL,
[DateFirstTiganwenduClass] [nvarchar](20) NULL,
[DateFirstTiganwenduDescription] [nvarchar](20) NULL,
[DateFirstWuran] [nvarchar](20) NULL,
[DateFirstWuranClass] [nvarchar](20) NULL,
[DateFirstWuranDescription] [nvarchar](20) NULL,
[DateFirstYinliao] [nvarchar](20) NULL,
[DateFirstYinliaoClass] [nvarchar](20) NULL,
[DateFirstYinliaoDescription] [nvarchar](20) NULL,
[DateFirstZiwaixian] [nvarchar](20) NULL,
[DateFirstZiwaixianClass] [nvarchar](20) NULL,
[DateFirstZiwaixianDescription] [nvarchar](20) NULL,
[DateSecond] [nvarchar](20) NULL,
[DateSecondWeatherAbstract] [nvarchar](20) NULL,
[DateSecondHighTemperature] [nvarchar](20) NULL,
[DateSecondLowTemperature] [nvarchar](20) NULL,
[DateSecondWindDirection] [nvarchar](20) NULL,
[DateSecondWindPower] [nvarchar](20) NULL,
[DateSecondBinqilin] [nvarchar](20) NULL,
[DateSecondBinqilinClass] [nvarchar](20) NULL,
[DateSecondBinqilinDescription] [nvarchar](20) NULL,
[DateSecondChuanyi] [nvarchar](20) NULL,
[DateSecondChuanyiClass] [nvarchar](20) NULL,
[DateSecondChuanyiDescription] [nvarchar](20) NULL,
[DateSecondTiganwendu] [nvarchar](20) NULL,
[DateSecondTiganwenduClass] [nvarchar](20) NULL,
[DateSecondTiganwenduDescription] [nvarchar](20) NULL,
[DateSecondWuran] [nvarchar](20) NULL,
[DateSecondWuranClass] [nvarchar](20) NULL,
[DateSecondWuranDescription] [nvarchar](20) NULL,
[DateSecondYinliao] [nvarchar](20) NULL,
[DateSecondYinliaoClass] [nvarchar](20) NULL,
[DateSecondYinliaoDescription] [nvarchar](20) NULL,
[DateSecondZiwaixian] [nvarchar](20) NULL,
[DateSecondZiwaixianClass] [nvarchar](20) NULL,
[DateSecondZiwaixianDescription] [nvarchar](20) NULL
) 

Answer 1

优化下边的代码,目前执行是3-4秒
下载测试文件
http://tstring.com.cn/i/domesticcityweather.xml
/* 加载本地数据 */
DECLARE @TEMPWEA TABLE(WEATHERXML XML)
INSERT INTO @TEMPWEA
SELECT CAST(A AS XML) FROM OPENROWSET(BULK N'd:\DomesticCityWeather.xml',SINGLE_BLOB) A(A); /* 将符合要求的城市天气预报放入表变量 */
INSERT INTO @BA_WEATHER
SELECT t2.doc.value('(CityID)[1]','nvarchar(20)') AS CityID,
   t2.doc.value('(CityName)[1]','nvarchar(20)') AS CityName,
   
   /* 第一天所有参数 */
   t2.doc.value('(DateFirst)[1]','nvarchar(20)') AS DateFirst,
   t2.doc.value('(DateFirstWeatherAbstract)[1]','nvarchar(20)') AS DateFirstWeatherAbstract,
   t2.doc.value('(DateFirstHighTemperature)[1]','nvarchar(20)') AS DateFirstHighTemperature,
   t2.doc.value('(DateFirstLowTemperature)[1]','nvarchar(20)') AS DateFirstLowTemperature,
   t2.doc.value('(DateFirstWindDirection)[1]','nvarchar(20)') AS DateFirstWindDirection,
   t2.doc.value('(DateFirstWindPower)[1]','nvarchar(20)') AS DateFirstWindPower,
   
   /* 冰淇凌指数 */
   t2.doc.value('(DateFirstBinqilin)[1]','nvarchar(20)') AS DateFirstBinqilin,
   t2.doc.value('(DateFirstBinqilinClass)[1]','nvarchar(20)') AS DateFirstBinqilinClass,
   t2.doc.value('(DateFirstBinqilinDescription)[1]','nvarchar(20)') AS DateFirstBinqilinDescription,
   
   /* 穿衣指数 */
   t2.doc.value('(DateFirstChuanyi)[1]','nvarchar(20)') AS DateFirstChuanyi,
   t2.doc.value('(DateFirstChuanyiClass)[1]','nvarchar(20)') AS DateFirstChuanyiClass,
   t2.doc.value('(DateFirstChuanyiDescription)[1]','nvarchar(20)') AS DateFirstChuanyiDescription,
   
   /* 体感温度（舒适指数） */
   t2.doc.value('(DateFirstTiganwendu)[1]','nvarchar(20)') AS DateFirstTiganwendu,
   t2.doc.value('(DateFirstTiganwenduClass)[1]','nvarchar(20)') AS DateFirstTiganwenduClass,
   t2.doc.value('(DateFirstTiganwenduDescription)[1]','nvarchar(20)') AS DateFirstTiganwenduDescription,
   
   /* 污染指数 */
   t2.doc.value('(DateFirstWuran)[1]','nvarchar(20)') AS DateFirstWuran,
   t2.doc.value('(DateFirstWuranClass)[1]','nvarchar(20)') AS DateFirstWuranClass,
   t2.doc.value('(DateFirstWuranDescription)[1]','nvarchar(20)') AS DateFirstWuranDescription,
   
   /* 饮料指数 */
   t2.doc.value('(DateFirstYinliao)[1]','nvarchar(20)') AS DateFirstYinliao,
   t2.doc.value('(DateFirstYinliaoClass)[1]','nvarchar(20)') AS DateFirstYinliaoClass,
   t2.doc.value('(DateFirstYinliaoDescription)[1]','nvarchar(20)') AS DateFirstYinliaoDescription,
   
   /* 紫外线指数 */
   t2.doc.value('(DateFirstZiwaixian)[1]','nvarchar(20)') AS DateFirstZiwaixian,
   t2.doc.value('(DateFirstZiwaixianClass)[1]','nvarchar(20)') AS DateFirstZiwaixianClass,
   t2.doc.value('(DateFirstZiwaixianDescription)[1]','nvarchar(20)') AS DateFirstZiwaixianDescription,
   
   /* 第二天所有参数 */
   t2.doc.value('(DateSecond)[1]','nvarchar(20)') AS DateSecond,
   t2.doc.value('(DateSecondWeatherAbstract)[1]','nvarchar(20)') AS DateSecondWeatherAbstract,
   t2.doc.value('(DateSecondHighTemperature)[1]','nvarchar(20)') AS DateSecondHighTemperature,
   t2.doc.value('(DateSecondLowTemperature)[1]','nvarchar(20)') AS DateSecondLowTemperature,
   t2.doc.value('(DateSecondWindDirection)[1]','nvarchar(20)') AS DateSecondWindDirection,
   t2.doc.value('(DateSecondWindPower)[1]','nvarchar(20)') AS DateSecondWindPower,
   
   /* 冰淇凌指数 */
   t2.doc.value('(DateSecondBinqilin)[1]','nvarchar(20)') AS DateSecondBinqilin,
   t2.doc.value('(DateSecondBinqilinClass)[1]','nvarchar(20)') AS DateSecondBinqilinClass,
   t2.doc.value('(DateSecondBinqilinDescription)[1]','nvarchar(20)') AS DateSecondBinqilinDescription,
   
   /* 穿衣指数 */
   t2.doc.value('(DateSecondChuanyi)[1]','nvarchar(20)') AS DateSecondChuanyi,
   t2.doc.value('(DateSecondChuanyiClass)[1]','nvarchar(20)') AS DateSecondChuanyiClass,
   t2.doc.value('(DateSecondChuanyiDescription)[1]','nvarchar(20)') AS DateSecondChuanyiDescription,
   
   /* 体感温度（舒适指数） */
   t2.doc.value('(DateSecondTiganwendu)[1]','nvarchar(20)') AS DateSecondTiganwendu,
   t2.doc.value('(DateSecondTiganwenduClass)[1]','nvarchar(20)') AS DateSecondTiganwenduClass,
   t2.doc.value('(DateSecondTiganwenduDescription)[1]','nvarchar(20)') AS DateSecondTiganwenduDescription,
   
   /* 污染指数 */
   t2.doc.value('(DateSecondWuran)[1]','nvarchar(20)') AS DateSecondWuran,
   t2.doc.value('(DateSecondWuranClass)[1]','nvarchar(20)') AS DateSecondWuranClass,
   t2.doc.value('(DateSecondWuranDescription)[1]','nvarchar(20)') AS DateSecondWuranDescription,
   
   /* 饮料指数 */
   t2.doc.value('(DateSecondYinliao)[1]','nvarchar(20)') AS DateSecondYinliao,
   t2.doc.value('(DateSecondYinliaoClass)[1]','nvarchar(20)') AS DateSecondYinliaoClass,
   t2.doc.value('(DateSecondYinliaoDescription)[1]','nvarchar(20)') AS DateSecondYinliaoDescription,
   
   /* 紫外线指数 */
   t2.doc.value('(DateSecondZiwaixian)[1]','nvarchar(20)') AS DateSecondZiwaixian,
   t2.doc.value('(DateSecondZiwaixianClass)[1]','nvarchar(20)') AS DateSecondZiwaixianClass,
   t2.doc.value('(DateSecondZiwaixianDescription)[1]','nvarchar(20)') AS DateSecondZiwaixianDescription
FROM
(SELECT WEATHERXML FROM @TEMPWEA) c
CROSS apply 
WEATHERXML.nodes('/DomesticCityWeatherTable/CityWeather') as t2(doc)

SELECT * FROM @BA_WEATHER

Answer 2

贴个d:\DomesticCityWeather.xml的文件内容，用于测试

Answer 3

d:\DomesticCityWeather.xml

Answer 4

interl e5200 + 4g ddr ram改用临时表前，3300-3600ms
改用临时表后，2200-2500ms数据量还不够大，还大的话，可能区别更明显。 100多条记录，从索引效果不明显。DECLARE @st DATETIME
SET @st = GETDATE()
DECLARE @BA_WEATHER TABLE (
    [CityID] [nvarchar](20) NULL,
    [CityName] [nvarchar](20) NULL,
    [DateFirst] [nvarchar](20) NULL,
    [DateFirstWeatherAbstract] [nvarchar](20) NULL,
    [DateFirstHighTemperature] [nvarchar](20) NULL,
    [DateFirstLowTemperature] [nvarchar](20) NULL,
    [DateFirstWindDirection] [nvarchar](20) NULL,
    [DateFirstWindPower] [nvarchar](20) NULL,
    [DateFirstBinqilin] [nvarchar](20) NULL,
    [DateFirstBinqilinClass] [nvarchar](20) NULL,
    [DateFirstBinqilinDescription] [nvarchar](20) NULL,
    [DateFirstChuanyi] [nvarchar](20) NULL,
    [DateFirstChuanyiClass] [nvarchar](20) NULL,
    [DateFirstChuanyiDescription] [nvarchar](20) NULL,
    [DateFirstTiganwendu] [nvarchar](20) NULL,
    [DateFirstTiganwenduClass] [nvarchar](20) NULL,
    [DateFirstTiganwenduDescription] [nvarchar](20) NULL,
    [DateFirstWuran] [nvarchar](20) NULL,
    [DateFirstWuranClass] [nvarchar](20) NULL,
    [DateFirstWuranDescription] [nvarchar](20) NULL,
    [DateFirstYinliao] [nvarchar](20) NULL,
    [DateFirstYinliaoClass] [nvarchar](20) NULL,
    [DateFirstYinliaoDescription] [nvarchar](20) NULL,
    [DateFirstZiwaixian] [nvarchar](20) NULL,
    [DateFirstZiwaixianClass] [nvarchar](20) NULL,
    [DateFirstZiwaixianDescription] [nvarchar](20) NULL,
    [DateSecond] [nvarchar](20) NULL,
    [DateSecondWeatherAbstract] [nvarchar](20) NULL,
    [DateSecondHighTemperature] [nvarchar](20) NULL,
    [DateSecondLowTemperature] [nvarchar](20) NULL,
    [DateSecondWindDirection] [nvarchar](20) NULL,
    [DateSecondWindPower] [nvarchar](20) NULL,
    [DateSecondBinqilin] [nvarchar](20) NULL,
    [DateSecondBinqilinClass] [nvarchar](20) NULL,
    [DateSecondBinqilinDescription] [nvarchar](20) NULL,
    [DateSecondChuanyi] [nvarchar](20) NULL,
    [DateSecondChuanyiClass] [nvarchar](20) NULL,
    [DateSecondChuanyiDescription] [nvarchar](20) NULL,
    [DateSecondTiganwendu] [nvarchar](20) NULL,
    [DateSecondTiganwenduClass] [nvarchar](20) NULL,
    [DateSecondTiganwenduDescription] [nvarchar](20) NULL,
    [DateSecondWuran] [nvarchar](20) NULL,
    [DateSecondWuranClass] [nvarchar](20) NULL,
    [DateSecondWuranDescription] [nvarchar](20) NULL,
    [DateSecondYinliao] [nvarchar](20) NULL,
    [DateSecondYinliaoClass] [nvarchar](20) NULL,
    [DateSecondYinliaoDescription] [nvarchar](20) NULL,
    [DateSecondZiwaixian] [nvarchar](20) NULL,
    [DateSecondZiwaixianClass] [nvarchar](20) NULL,
    [DateSecondZiwaixianDescription] [nvarchar](20) NULL
) 
/* 加载本地数据 */
--CREATE TABLE #TEMPWEA(ID INT IDENTITY(1,1),WEATHERXML XML)
--INSERT #TEMPWEA SELECT CAST(A AS XML) WEATHERXML FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);    
        SELECT CAST(A AS XML) WEATHERXML,id=IDENTITY(INT) INTO #TEMPWEA FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);    

ALTER TABLE #TEMPWEA ADD CONSTRAINT idx_p PRIMARY KEY (id)
CREATE PRIMARY XML INDEX idx_x_p ON #TEMPWEA(WEATHERXML)
--CREATE XML INDEX idx_x_1 ON #TEMPWEA(WEATHERXML)
--USING XML INDEX idx_x_p FOR PATH        

Answer 5

/* 将符合要求的城市天气预报放入表变量 */
        INSERT INTO @BA_WEATHER
        SELECT t2.doc.value('(CityID)[1]','nvarchar(20)') AS CityID,
               t2.doc.value('(CityName)[1]','nvarchar(20)') AS CityName,
               
               /* 第一天所有参数 */
               t2.doc.value('(DateFirst)[1]','nvarchar(20)') AS DateFirst,
               t2.doc.value('(DateFirstWeatherAbstract)[1]','nvarchar(20)') AS DateFirstWeatherAbstract,
               t2.doc.value('(DateFirstHighTemperature)[1]','nvarchar(20)') AS DateFirstHighTemperature,
               t2.doc.value('(DateFirstLowTemperature)[1]','nvarchar(20)') AS DateFirstLowTemperature,
               t2.doc.value('(DateFirstWindDirection)[1]','nvarchar(20)') AS DateFirstWindDirection,
               t2.doc.value('(DateFirstWindPower)[1]','nvarchar(20)') AS DateFirstWindPower,
               
               /* 冰淇凌指数 */
               t2.doc.value('(DateFirstBinqilin)[1]','nvarchar(20)') AS DateFirstBinqilin,
               t2.doc.value('(DateFirstBinqilinClass)[1]','nvarchar(20)') AS DateFirstBinqilinClass,
               t2.doc.value('(DateFirstBinqilinDescription)[1]','nvarchar(20)') AS DateFirstBinqilinDescription,
               
               /* 穿衣指数 */
               t2.doc.value('(DateFirstChuanyi)[1]','nvarchar(20)') AS DateFirstChuanyi,
               t2.doc.value('(DateFirstChuanyiClass)[1]','nvarchar(20)') AS DateFirstChuanyiClass,
               t2.doc.value('(DateFirstChuanyiDescription)[1]','nvarchar(20)') AS DateFirstChuanyiDescription,
               
               /* 体感温度（舒适指数） */
               t2.doc.value('(DateFirstTiganwendu)[1]','nvarchar(20)') AS DateFirstTiganwendu,
               t2.doc.value('(DateFirstTiganwenduClass)[1]','nvarchar(20)') AS DateFirstTiganwenduClass,
               t2.doc.value('(DateFirstTiganwenduDescription)[1]','nvarchar(20)') AS DateFirstTiganwenduDescription,
               
               /* 污染指数 */
               t2.doc.value('(DateFirstWuran)[1]','nvarchar(20)') AS DateFirstWuran,
               t2.doc.value('(DateFirstWuranClass)[1]','nvarchar(20)') AS DateFirstWuranClass,
               t2.doc.value('(DateFirstWuranDescription)[1]','nvarchar(20)') AS DateFirstWuranDescription,
               
               /* 饮料指数 */
               t2.doc.value('(DateFirstYinliao)[1]','nvarchar(20)') AS DateFirstYinliao,
               t2.doc.value('(DateFirstYinliaoClass)[1]','nvarchar(20)') AS DateFirstYinliaoClass,
               t2.doc.value('(DateFirstYinliaoDescription)[1]','nvarchar(20)') AS DateFirstYinliaoDescription,
               
               /* 紫外线指数 */
               t2.doc.value('(DateFirstZiwaixian)[1]','nvarchar(20)') AS DateFirstZiwaixian,
               t2.doc.value('(DateFirstZiwaixianClass)[1]','nvarchar(20)') AS DateFirstZiwaixianClass,
               t2.doc.value('(DateFirstZiwaixianDescription)[1]','nvarchar(20)') AS DateFirstZiwaixianDescription,
               
               /* 第二天所有参数 */
               t2.doc.value('(DateSecond)[1]','nvarchar(20)') AS DateSecond,
               t2.doc.value('(DateSecondWeatherAbstract)[1]','nvarchar(20)') AS DateSecondWeatherAbstract,
               t2.doc.value('(DateSecondHighTemperature)[1]','nvarchar(20)') AS DateSecondHighTemperature,
               t2.doc.value('(DateSecondLowTemperature)[1]','nvarchar(20)') AS DateSecondLowTemperature,
               t2.doc.value('(DateSecondWindDirection)[1]','nvarchar(20)') AS DateSecondWindDirection,
               t2.doc.value('(DateSecondWindPower)[1]','nvarchar(20)') AS DateSecondWindPower,
               
               /* 冰淇凌指数 */
               t2.doc.value('(DateSecondBinqilin)[1]','nvarchar(20)') AS DateSecondBinqilin,
               t2.doc.value('(DateSecondBinqilinClass)[1]','nvarchar(20)') AS DateSecondBinqilinClass,
               t2.doc.value('(DateSecondBinqilinDescription)[1]','nvarchar(20)') AS DateSecondBinqilinDescription,
               
               /* 穿衣指数 */
               t2.doc.value('(DateSecondChuanyi)[1]','nvarchar(20)') AS DateSecondChuanyi,
               t2.doc.value('(DateSecondChuanyiClass)[1]','nvarchar(20)') AS DateSecondChuanyiClass,
               t2.doc.value('(DateSecondChuanyiDescription)[1]','nvarchar(20)') AS DateSecondChuanyiDescription,
               
               /* 体感温度（舒适指数） */
               t2.doc.value('(DateSecondTiganwendu)[1]','nvarchar(20)') AS DateSecondTiganwendu,
               t2.doc.value('(DateSecondTiganwenduClass)[1]','nvarchar(20)') AS DateSecondTiganwenduClass,
               t2.doc.value('(DateSecondTiganwenduDescription)[1]','nvarchar(20)') AS DateSecondTiganwenduDescription,
               
               /* 污染指数 */
               t2.doc.value('(DateSecondWuran)[1]','nvarchar(20)') AS DateSecondWuran,
               t2.doc.value('(DateSecondWuranClass)[1]','nvarchar(20)') AS DateSecondWuranClass,
               t2.doc.value('(DateSecondWuranDescription)[1]','nvarchar(20)') AS DateSecondWuranDescription,
               
               /* 饮料指数 */
               t2.doc.value('(DateSecondYinliao)[1]','nvarchar(20)') AS DateSecondYinliao,
               t2.doc.value('(DateSecondYinliaoClass)[1]','nvarchar(20)') AS DateSecondYinliaoClass,
               t2.doc.value('(DateSecondYinliaoDescription)[1]','nvarchar(20)') AS DateSecondYinliaoDescription,
               
               /* 紫外线指数 */
               t2.doc.value('(DateSecondZiwaixian)[1]','nvarchar(20)') AS DateSecondZiwaixian,
               t2.doc.value('(DateSecondZiwaixianClass)[1]','nvarchar(20)') AS DateSecondZiwaixianClass,
               t2.doc.value('(DateSecondZiwaixianDescription)[1]','nvarchar(20)') AS DateSecondZiwaixianDescription
        FROM
        (SELECT WEATHERXML FROM #TEMPWEA) c
        CROSS apply 
        WEATHERXML.nodes('/DomesticCityWeatherTable/CityWeather') as t2(doc)
        
        SELECT * FROM @BA_WEATHERTRUNCATE TABLE #TEMPWEA
DROP TABLE #TEMPWEA
SELECT DATEDIFF(ms,@st,GETDATE())

Answer 6

如果数据量再大几倍，可以nodes后插入临时表，索引创建在第二个临时表上。

Answer 7

--改用DOM试下，速度应该会快些。
--我这是3s左右.
IF OBJECT_ID('TEMPDB..#BA_WEATHER') IS NOT NULL
DROP TABLE #BA_WEATHER
GO
CREATE TABLE  #BA_WEATHER  (
    [CityID] [nvarchar](20) NULL,
    [CityName] [nvarchar](20) NULL,
    [DateFirst] [nvarchar](20) NULL,
    [DateFirstWeatherAbstract] [nvarchar](20) NULL,
    [DateFirstHighTemperature] [nvarchar](20) NULL,
    [DateFirstLowTemperature] [nvarchar](20) NULL,
    [DateFirstWindDirection] [nvarchar](20) NULL,
    [DateFirstWindPower] [nvarchar](20) NULL,
    [DateFirstBinqilin] [nvarchar](20) NULL,
    [DateFirstBinqilinClass] [nvarchar](20) NULL,
    [DateFirstBinqilinDescription] [nvarchar](20) NULL,
    [DateFirstChuanyi] [nvarchar](20) NULL,
    [DateFirstChuanyiClass] [nvarchar](20) NULL,
    [DateFirstChuanyiDescription] [nvarchar](20) NULL,
    [DateFirstTiganwendu] [nvarchar](20) NULL,
    [DateFirstTiganwenduClass] [nvarchar](20) NULL,
    [DateFirstTiganwenduDescription] [nvarchar](20) NULL,
    [DateFirstWuran] [nvarchar](20) NULL,
    [DateFirstWuranClass] [nvarchar](20) NULL,
    [DateFirstWuranDescription] [nvarchar](20) NULL,
    [DateFirstYinliao] [nvarchar](20) NULL,
    [DateFirstYinliaoClass] [nvarchar](20) NULL,
    [DateFirstYinliaoDescription] [nvarchar](20) NULL,
    [DateFirstZiwaixian] [nvarchar](20) NULL,
    [DateFirstZiwaixianClass] [nvarchar](20) NULL,
    [DateFirstZiwaixianDescription] [nvarchar](20) NULL,
    [DateSecond] [nvarchar](20) NULL,
    [DateSecondWeatherAbstract] [nvarchar](20) NULL,
    [DateSecondHighTemperature] [nvarchar](20) NULL,
    [DateSecondLowTemperature] [nvarchar](20) NULL,
    [DateSecondWindDirection] [nvarchar](20) NULL,
    [DateSecondWindPower] [nvarchar](20) NULL,
    [DateSecondBinqilin] [nvarchar](20) NULL,
    [DateSecondBinqilinClass] [nvarchar](20) NULL,
    [DateSecondBinqilinDescription] [nvarchar](20) NULL,
    [DateSecondChuanyi] [nvarchar](20) NULL,
    [DateSecondChuanyiClass] [nvarchar](20) NULL,
    [DateSecondChuanyiDescription] [nvarchar](20) NULL,
    [DateSecondTiganwendu] [nvarchar](20) NULL,
    [DateSecondTiganwenduClass] [nvarchar](20) NULL,
    [DateSecondTiganwenduDescription] [nvarchar](20) NULL,
    [DateSecondWuran] [nvarchar](20) NULL,
    [DateSecondWuranClass] [nvarchar](20) NULL,
    [DateSecondWuranDescription] [nvarchar](20) NULL,
    [DateSecondYinliao] [nvarchar](20) NULL,
    [DateSecondYinliaoClass] [nvarchar](20) NULL,
    [DateSecondYinliaoDescription] [nvarchar](20) NULL,
    [DateSecondZiwaixian] [nvarchar](20) NULL,
    [DateSecondZiwaixianClass] [nvarchar](20) NULL,
    [DateSecondZiwaixianDescription] [nvarchar](20) NULL
) /* 加载本地数据 */
DECLARE @TEMPWEA TABLE(WEATHERXML XML)
INSERT INTO @TEMPWEA
SELECT CAST(A AS XML) FROM OPENROWSET(BULK N'd:\DomesticCityWeather.xml',SINGLE_BLOB) A(A);    DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SELECT  @xmlDocument =CAST(WEATHERXML as NVARCHAR(MAX)) FROM @TEMPWEAEXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocumentINSERT INTO #BA_WEATHER
SELECT * 
FROM OPENXML(@docHandle, N'/DomesticCityWeatherTable/CityWeather',2)
WITH  #BA_WEATHER
 
EXEC sp_xml_removedocument @docHandleSELECT * FROM #BA_WEATHER
 

Answer 8

我的机器的配置,
Intel Core Duo CPU T2450 2.0Ghz
RAM ; 2GB原代码的十次测试用时非常接近4秒
-----------------
3843
3830
3840
3803
3940
4096
3856
3846
3803
3810
18楼答案优化后接近3秒加200分

Answer 9

我的机器配置：Intel(R) Pentium 1.29G RAM:1.25G 
平均在2S左右

Answer 10

机器配置不同,SQL版本可能也不同的区别.

Answer 11

23楼Garnett_KG的答案400分,
已经达到耗秒级别了
目前为止最优的写法,震撼了!

Answer 12

怎么把分加到600分
顺便问一下SQL版打击倒分力度如何

Answer 13

KG用dom的方式确实很快，我机器平均执行时间550ms

Answer 14

SQL版果然藏龙卧虎,
我想办法兑现诺言

Answer 15

CREATE XML SCHEMA COLLECTION SCwea
AS '
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="DomesticCityWeatherTable">
        <xsd:complexType>
            <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                    <xsd:sequence>
                        <xsd:element name="CityWeather" maxOccurs="unbounded">
                            <xsd:complexType>
                                <xsd:complexContent>
                                    <xsd:restriction base="xsd:anyType">
                                        <xsd:sequence> <xsd:element name="CityID" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="CityName" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirst" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWeatherAbstract" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstHighTemperature" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstLowTemperature" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWindDirection" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWindPower" type="xsd:string" maxOccurs="unbounded" /> <xsd:element name="DateFirstBinqilin" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstBinqilinClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstBinqilinDescription" type="xsd:string" maxOccurs="unbounded" /> <xsd:element name="DateFirstChuanyi" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstChuanyiClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstChuanyiDescription" type="xsd:string" maxOccurs="unbounded" /> <xsd:element name="DateFirstTiganwendu" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstTiganwenduClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstTiganwenduDescription" type="xsd:string" maxOccurs="unbounded" />  
    
<xsd:element name="DateFirstWuran" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWuranClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstWuranDescription" type="xsd:string" maxOccurs="unbounded" />   <xsd:element name="DateFirstYinliao" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstYinliaoClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstYinliaoDescription" type="xsd:string" maxOccurs="unbounded" />   <xsd:element name="DateFirstZiwaixian" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstZiwaixianClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateFirstZiwaixianDescription" type="xsd:string" maxOccurs="unbounded" />   <xsd:element name="DateSecond" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWeatherAbstract" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondHighTemperature" type="xsd:string" maxOccurs="unbounded" />  
<xsd:element name="DateSecondLowTemperature" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWindDirection" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWindPower" type="xsd:string" maxOccurs="unbounded" />   <xsd:element name="DateSecondBinqilin" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondBinqilinClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondBinqilinDescription" type="xsd:string" maxOccurs="unbounded" />   <xsd:element name="DateSecondChuanyi" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondChuanyiClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondChuanyiDescription" type="xsd:string" maxOccurs="unbounded" /> <xsd:element name="DateSecondTiganwendu" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondTiganwenduClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondTiganwenduDescription" type="xsd:string" maxOccurs="unbounded" /> <xsd:element name="DateSecondWuran" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWuranClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondWuranDescription" type="xsd:string" maxOccurs="unbounded" /> <xsd:element name="DateSecondYinliao" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondYinliaoClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondYinliaoDescription" type="xsd:string" maxOccurs="unbounded" /> <xsd:element name="DateSecondZiwaixian" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondZiwaixianClass" type="xsd:string" maxOccurs="unbounded" />
<xsd:element name="DateSecondZiwaixianDescription" type="xsd:string" maxOccurs="unbounded" />                                        </xsd:sequence>
                                        <xsd:attribute name="newsid" type="xsd:string" />
                                    </xsd:restriction>
                                </xsd:complexContent>
                            </xsd:complexType>
                        </xsd:element>
                    </xsd:sequence>
                </xsd:restriction>
            </xsd:complexContent>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

Answer 16

转换为类型化xml,执行时间在1650-1760ms之间DECLARE @st DATETIME
SET @st = GETDATE()
DECLARE @BA_WEATHER TABLE (
    [CityID] [nvarchar](20) NULL,
    [CityName] [nvarchar](20) NULL,
    [DateFirst] [nvarchar](20) NULL,
    [DateFirstWeatherAbstract] [nvarchar](20) NULL,
    [DateFirstHighTemperature] [nvarchar](20) NULL,
    [DateFirstLowTemperature] [nvarchar](20) NULL,
    [DateFirstWindDirection] [nvarchar](20) NULL,
    [DateFirstWindPower] [nvarchar](20) NULL,
    [DateFirstBinqilin] [nvarchar](20) NULL,
    [DateFirstBinqilinClass] [nvarchar](20) NULL,
    [DateFirstBinqilinDescription] [nvarchar](20) NULL,
    [DateFirstChuanyi] [nvarchar](20) NULL,
    [DateFirstChuanyiClass] [nvarchar](20) NULL,
    [DateFirstChuanyiDescription] [nvarchar](20) NULL,
    [DateFirstTiganwendu] [nvarchar](20) NULL,
    [DateFirstTiganwenduClass] [nvarchar](20) NULL,
    [DateFirstTiganwenduDescription] [nvarchar](20) NULL,
    [DateFirstWuran] [nvarchar](20) NULL,
    [DateFirstWuranClass] [nvarchar](20) NULL,
    [DateFirstWuranDescription] [nvarchar](20) NULL,
    [DateFirstYinliao] [nvarchar](20) NULL,
    [DateFirstYinliaoClass] [nvarchar](20) NULL,
    [DateFirstYinliaoDescription] [nvarchar](20) NULL,
    [DateFirstZiwaixian] [nvarchar](20) NULL,
    [DateFirstZiwaixianClass] [nvarchar](20) NULL,
    [DateFirstZiwaixianDescription] [nvarchar](20) NULL,
    [DateSecond] [nvarchar](20) NULL,
    [DateSecondWeatherAbstract] [nvarchar](20) NULL,
    [DateSecondHighTemperature] [nvarchar](20) NULL,
    [DateSecondLowTemperature] [nvarchar](20) NULL,
    [DateSecondWindDirection] [nvarchar](20) NULL,
    [DateSecondWindPower] [nvarchar](20) NULL,
    [DateSecondBinqilin] [nvarchar](20) NULL,
    [DateSecondBinqilinClass] [nvarchar](20) NULL,
    [DateSecondBinqilinDescription] [nvarchar](20) NULL,
    [DateSecondChuanyi] [nvarchar](20) NULL,
    [DateSecondChuanyiClass] [nvarchar](20) NULL,
    [DateSecondChuanyiDescription] [nvarchar](20) NULL,
    [DateSecondTiganwendu] [nvarchar](20) NULL,
    [DateSecondTiganwenduClass] [nvarchar](20) NULL,
    [DateSecondTiganwenduDescription] [nvarchar](20) NULL,
    [DateSecondWuran] [nvarchar](20) NULL,
    [DateSecondWuranClass] [nvarchar](20) NULL,
    [DateSecondWuranDescription] [nvarchar](20) NULL,
    [DateSecondYinliao] [nvarchar](20) NULL,
    [DateSecondYinliaoClass] [nvarchar](20) NULL,
    [DateSecondYinliaoDescription] [nvarchar](20) NULL,
    [DateSecondZiwaixian] [nvarchar](20) NULL,
    [DateSecondZiwaixianClass] [nvarchar](20) NULL,
    [DateSecondZiwaixianDescription] [nvarchar](20) NULL
) 
/* 加载本地数据 */
CREATE TABLE TEMPWEA(ID INT IDENTITY(1,1),WEATHERXML XML(SCwea))
INSERT TEMPWEA SELECT CAST(A AS XML) WEATHERXML FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);    
        --SELECT CAST(A AS XML) WEATHERXML,id=IDENTITY(INT) INTO TEMPWEA FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);    

ALTER TABLE TEMPWEA ADD CONSTRAINT idx_p PRIMARY KEY (id)
CREATE PRIMARY XML INDEX idx_x_p ON TEMPWEA(WEATHERXML)
--CREATE XML INDEX idx_x_1 ON TEMPWEA(WEATHERXML)
--USING XML INDEX idx_x_p FOR PATH        

Answer 17

23楼Garnett_KG大侠请访问以下连接
http://topic.csdn.net/u/20090426/21/33d6b626-219d-45f5-8a72-fccef1cd47ac.html
http://topic.csdn.net/u/20090426/21/319b5b76-d29d-40e1-a239-8e38ed404903.html
http://topic.csdn.net/u/20090426/21/7d8e88a6-3c7e-4502-897c-fa1224d2245e.html
http://topic.csdn.net/u/20090426/21/145e32c3-33ab-4540-904c-acb031b11ee2.html

Answer 18

/* 将符合要求的城市天气预报放入表变量 */
        INSERT INTO @BA_WEATHER
        SELECT t2.doc.value('(CityID)[1]','nvarchar(20)') AS CityID,
               t2.doc.value('(CityName)[1]','nvarchar(20)') AS CityName,
               
               /* 第一天所有参数 */
               t2.doc.value('(DateFirst)[1]','nvarchar(20)') AS DateFirst,
               t2.doc.value('(DateFirstWeatherAbstract)[1]','nvarchar(20)') AS DateFirstWeatherAbstract,
               t2.doc.value('(DateFirstHighTemperature)[1]','nvarchar(20)') AS DateFirstHighTemperature,
               t2.doc.value('(DateFirstLowTemperature)[1]','nvarchar(20)') AS DateFirstLowTemperature,
               t2.doc.value('(DateFirstWindDirection)[1]','nvarchar(20)') AS DateFirstWindDirection,
               t2.doc.value('(DateFirstWindPower)[1]','nvarchar(20)') AS DateFirstWindPower,
               
               /* 冰淇凌指数 */
               t2.doc.value('(DateFirstBinqilin)[1]','nvarchar(20)') AS DateFirstBinqilin,
               t2.doc.value('(DateFirstBinqilinClass)[1]','nvarchar(20)') AS DateFirstBinqilinClass,
               t2.doc.value('(DateFirstBinqilinDescription)[1]','nvarchar(20)') AS DateFirstBinqilinDescription,
               
               /* 穿衣指数 */
               t2.doc.value('(DateFirstChuanyi)[1]','nvarchar(20)') AS DateFirstChuanyi,
               t2.doc.value('(DateFirstChuanyiClass)[1]','nvarchar(20)') AS DateFirstChuanyiClass,
               t2.doc.value('(DateFirstChuanyiDescription)[1]','nvarchar(20)') AS DateFirstChuanyiDescription,
               
               /* 体感温度（舒适指数） */
               t2.doc.value('(DateFirstTiganwendu)[1]','nvarchar(20)') AS DateFirstTiganwendu,
               t2.doc.value('(DateFirstTiganwenduClass)[1]','nvarchar(20)') AS DateFirstTiganwenduClass,
               t2.doc.value('(DateFirstTiganwenduDescription)[1]','nvarchar(20)') AS DateFirstTiganwenduDescription,
               
               /* 污染指数 */
               t2.doc.value('(DateFirstWuran)[1]','nvarchar(20)') AS DateFirstWuran,
               t2.doc.value('(DateFirstWuranClass)[1]','nvarchar(20)') AS DateFirstWuranClass,
               t2.doc.value('(DateFirstWuranDescription)[1]','nvarchar(20)') AS DateFirstWuranDescription,
               
               /* 饮料指数 */
               t2.doc.value('(DateFirstYinliao)[1]','nvarchar(20)') AS DateFirstYinliao,
               t2.doc.value('(DateFirstYinliaoClass)[1]','nvarchar(20)') AS DateFirstYinliaoClass,
               t2.doc.value('(DateFirstYinliaoDescription)[1]','nvarchar(20)') AS DateFirstYinliaoDescription,
               
               /* 紫外线指数 */
               t2.doc.value('(DateFirstZiwaixian)[1]','nvarchar(20)') AS DateFirstZiwaixian,
               t2.doc.value('(DateFirstZiwaixianClass)[1]','nvarchar(20)') AS DateFirstZiwaixianClass,
               t2.doc.value('(DateFirstZiwaixianDescription)[1]','nvarchar(20)') AS DateFirstZiwaixianDescription,
               
               /* 第二天所有参数 */
               t2.doc.value('(DateSecond)[1]','nvarchar(20)') AS DateSecond,
               t2.doc.value('(DateSecondWeatherAbstract)[1]','nvarchar(20)') AS DateSecondWeatherAbstract,
               t2.doc.value('(DateSecondHighTemperature)[1]','nvarchar(20)') AS DateSecondHighTemperature,
               t2.doc.value('(DateSecondLowTemperature)[1]','nvarchar(20)') AS DateSecondLowTemperature,
               t2.doc.value('(DateSecondWindDirection)[1]','nvarchar(20)') AS DateSecondWindDirection,
               t2.doc.value('(DateSecondWindPower)[1]','nvarchar(20)') AS DateSecondWindPower,
               
               /* 冰淇凌指数 */
               t2.doc.value('(DateSecondBinqilin)[1]','nvarchar(20)') AS DateSecondBinqilin,
               t2.doc.value('(DateSecondBinqilinClass)[1]','nvarchar(20)') AS DateSecondBinqilinClass,
               t2.doc.value('(DateSecondBinqilinDescription)[1]','nvarchar(20)') AS DateSecondBinqilinDescription,
               
               /* 穿衣指数 */
               t2.doc.value('(DateSecondChuanyi)[1]','nvarchar(20)') AS DateSecondChuanyi,
               t2.doc.value('(DateSecondChuanyiClass)[1]','nvarchar(20)') AS DateSecondChuanyiClass,
               t2.doc.value('(DateSecondChuanyiDescription)[1]','nvarchar(20)') AS DateSecondChuanyiDescription,
               
               /* 体感温度（舒适指数） */
               t2.doc.value('(DateSecondTiganwendu)[1]','nvarchar(20)') AS DateSecondTiganwendu,
               t2.doc.value('(DateSecondTiganwenduClass)[1]','nvarchar(20)') AS DateSecondTiganwenduClass,
               t2.doc.value('(DateSecondTiganwenduDescription)[1]','nvarchar(20)') AS DateSecondTiganwenduDescription,
               
               /* 污染指数 */
               t2.doc.value('(DateSecondWuran)[1]','nvarchar(20)') AS DateSecondWuran,
               t2.doc.value('(DateSecondWuranClass)[1]','nvarchar(20)') AS DateSecondWuranClass,
               t2.doc.value('(DateSecondWuranDescription)[1]','nvarchar(20)') AS DateSecondWuranDescription,
               
               /* 饮料指数 */
               t2.doc.value('(DateSecondYinliao)[1]','nvarchar(20)') AS DateSecondYinliao,
               t2.doc.value('(DateSecondYinliaoClass)[1]','nvarchar(20)') AS DateSecondYinliaoClass,
               t2.doc.value('(DateSecondYinliaoDescription)[1]','nvarchar(20)') AS DateSecondYinliaoDescription,
               
               /* 紫外线指数 */
               t2.doc.value('(DateSecondZiwaixian)[1]','nvarchar(20)') AS DateSecondZiwaixian,
               t2.doc.value('(DateSecondZiwaixianClass)[1]','nvarchar(20)') AS DateSecondZiwaixianClass,
               t2.doc.value('(DateSecondZiwaixianDescription)[1]','nvarchar(20)') AS DateSecondZiwaixianDescription
        FROM
        (SELECT WEATHERXML FROM TEMPWEA) c
        CROSS apply 
        WEATHERXML.nodes('/DomesticCityWeatherTable/CityWeather') as t2(doc)
        
        SELECT * FROM @BA_WEATHERTRUNCATE TABLE TEMPWEA
DROP TABLE TEMPWEA
SELECT DATEDIFF(ms,@st,GETDATE())

Answer 19

最终，测试失败，因为 sp_xml_preparedocument  不支持类型化xml.KG的写法应该是根限了。500ms, 比之3400ms, 提高85%不使用dom的话， t-sql实现，改用临时表+xml索引+类型化xml 结果为1600ms左右。提搞 53%

Answer 20

fc,我猜原来的慢是慢在ＣＲＯＳＳ　ＡＰＰＬｙ那里，若是想办法不要做CROSS APPLY，那速度就上来了。

Answer 21

提示"元数据中不存在指定的集合:'SCwea'"

Answer 22

fcuandy大侠请访问以下连接,真有钻研精神!佩服
http://topic.csdn.net/u/20090426/21/cd6ed36e-34c1-4641-a248-1a71af9d688d.html
http://topic.csdn.net/u/20090426/21/cd6ed36e-34c1-4641-a248-1a71af9d688d.html

Answer 23

不是， 那个cross apply左输入只有一行，不会有太多消耗的。
主要原因在于xml解析和拆分。
因为这，用了类型化xml才会有一半多的性能提升。dom中应该是非托管类代码的处理(比较底层的算法),比起直接nodes，value之类这样的方式效率是好很多。不用cross apply,速度只提高二三十ms. 相对于43楼的做法
DECLARE @TEMPWEA TABLE(WEATHERXML XML)
INSERT INTO @TEMPWEA
SELECT CAST(A AS XML) FROM OPENROWSET(BULK N'e:\tmp\DomesticCityWeather.xml',SINGLE_BLOB) A(A);    DECLARE @x XML(SCwea) -- or xml type
SELECT  @x = WEATHERXML FROM @TEMPWEA        /* 将符合要求的城市天气预报放入表变量 */
        INSERT INTO @BA_WEATHER
        SELECT t2.doc.value('(CityID)[1]','nvarchar(20)') AS CityID,
               t2.doc.value('(CityName)[1]','nvarchar(20)') AS CityName,
               
               /* 第一天所有参数 */
               t2.doc.value('(DateFirst)[1]','nvarchar(20)') AS DateFirst,
               t2.doc.value('(DateFirstWeatherAbstract)[1]','nvarchar(20)') AS DateFirstWeatherAbstract,
               t2.doc.value('(DateFirstHighTemperature)[1]','nvarchar(20)') AS DateFirstHighTemperature,
               t2.doc.value('(DateFirstLowTemperature)[1]','nvarchar(20)') AS DateFirstLowTemperature,
               t2.doc.value('(DateFirstWindDirection)[1]','nvarchar(20)') AS DateFirstWindDirection,
               t2.doc.value('(DateFirstWindPower)[1]','nvarchar(20)') AS DateFirstWindPower,
               
               /* 冰淇凌指数 */
               t2.doc.value('(DateFirstBinqilin)[1]','nvarchar(20)') AS DateFirstBinqilin,
               t2.doc.value('(DateFirstBinqilinClass)[1]','nvarchar(20)') AS DateFirstBinqilinClass,
               t2.doc.value('(DateFirstBinqilinDescription)[1]','nvarchar(20)') AS DateFirstBinqilinDescription,
               
               /* 穿衣指数 */
               t2.doc.value('(DateFirstChuanyi)[1]','nvarchar(20)') AS DateFirstChuanyi,
               t2.doc.value('(DateFirstChuanyiClass)[1]','nvarchar(20)') AS DateFirstChuanyiClass,
               t2.doc.value('(DateFirstChuanyiDescription)[1]','nvarchar(20)') AS DateFirstChuanyiDescription,
               
               /* 体感温度（舒适指数） */
               t2.doc.value('(DateFirstTiganwendu)[1]','nvarchar(20)') AS DateFirstTiganwendu,
               t2.doc.value('(DateFirstTiganwenduClass)[1]','nvarchar(20)') AS DateFirstTiganwenduClass,
               t2.doc.value('(DateFirstTiganwenduDescription)[1]','nvarchar(20)') AS DateFirstTiganwenduDescription,
               
               /* 污染指数 */
               t2.doc.value('(DateFirstWuran)[1]','nvarchar(20)') AS DateFirstWuran,
               t2.doc.value('(DateFirstWuranClass)[1]','nvarchar(20)') AS DateFirstWuranClass,
               t2.doc.value('(DateFirstWuranDescription)[1]','nvarchar(20)') AS DateFirstWuranDescription,
               
               /* 饮料指数 */
               t2.doc.value('(DateFirstYinliao)[1]','nvarchar(20)') AS DateFirstYinliao,
               t2.doc.value('(DateFirstYinliaoClass)[1]','nvarchar(20)') AS DateFirstYinliaoClass,
               t2.doc.value('(DateFirstYinliaoDescription)[1]','nvarchar(20)') AS DateFirstYinliaoDescription,
               
               /* 紫外线指数 */
               t2.doc.value('(DateFirstZiwaixian)[1]','nvarchar(20)') AS DateFirstZiwaixian,
               t2.doc.value('(DateFirstZiwaixianClass)[1]','nvarchar(20)') AS DateFirstZiwaixianClass,
               t2.doc.value('(DateFirstZiwaixianDescription)[1]','nvarchar(20)') AS DateFirstZiwaixianDescription,
               
               /* 第二天所有参数 */
               t2.doc.value('(DateSecond)[1]','nvarchar(20)') AS DateSecond,
               t2.doc.value('(DateSecondWeatherAbstract)[1]','nvarchar(20)') AS DateSecondWeatherAbstract,
               t2.doc.value('(DateSecondHighTemperature)[1]','nvarchar(20)') AS DateSecondHighTemperature,
               t2.doc.value('(DateSecondLowTemperature)[1]','nvarchar(20)') AS DateSecondLowTemperature,
               t2.doc.value('(DateSecondWindDirection)[1]','nvarchar(20)') AS DateSecondWindDirection,
               t2.doc.value('(DateSecondWindPower)[1]','nvarchar(20)') AS DateSecondWindPower,
               
               /* 冰淇凌指数 */
               t2.doc.value('(DateSecondBinqilin)[1]','nvarchar(20)') AS DateSecondBinqilin,
               t2.doc.value('(DateSecondBinqilinClass)[1]','nvarchar(20)') AS DateSecondBinqilinClass,
               t2.doc.value('(DateSecondBinqilinDescription)[1]','nvarchar(20)') AS DateSecondBinqilinDescription,
               
               /* 穿衣指数 */
               t2.doc.value('(DateSecondChuanyi)[1]','nvarchar(20)') AS DateSecondChuanyi,
               t2.doc.value('(DateSecondChuanyiClass)[1]','nvarchar(20)') AS DateSecondChuanyiClass,
               t2.doc.value('(DateSecondChuanyiDescription)[1]','nvarchar(20)') AS DateSecondChuanyiDescription,
               
               /* 体感温度（舒适指数） */
               t2.doc.value('(DateSecondTiganwendu)[1]','nvarchar(20)') AS DateSecondTiganwendu,
               t2.doc.value('(DateSecondTiganwenduClass)[1]','nvarchar(20)') AS DateSecondTiganwenduClass,
               t2.doc.value('(DateSecondTiganwenduDescription)[1]','nvarchar(20)') AS DateSecondTiganwenduDescription,
               
               /* 污染指数 */
               t2.doc.value('(DateSecondWuran)[1]','nvarchar(20)') AS DateSecondWuran,
               t2.doc.value('(DateSecondWuranClass)[1]','nvarchar(20)') AS DateSecondWuranClass,
               t2.doc.value('(DateSecondWuranDescription)[1]','nvarchar(20)') AS DateSecondWuranDescription,
               
               /* 饮料指数 */
               t2.doc.value('(DateSecondYinliao)[1]','nvarchar(20)') AS DateSecondYinliao,
               t2.doc.value('(DateSecondYinliaoClass)[1]','nvarchar(20)') AS DateSecondYinliaoClass,
               t2.doc.value('(DateSecondYinliaoDescription)[1]','nvarchar(20)') AS DateSecondYinliaoDescription,
               
               /* 紫外线指数 */
               t2.doc.value('(DateSecondZiwaixian)[1]','nvarchar(20)') AS DateSecondZiwaixian,
               t2.doc.value('(DateSecondZiwaixianClass)[1]','nvarchar(20)') AS DateSecondZiwaixianClass,
               t2.doc.value('(DateSecondZiwaixianDescription)[1]','nvarchar(20)') AS DateSecondZiwaixianDescription
        FROM
        --(SELECT WEATHERXML FROM TEMPWEA) c
        --CROSS apply 
        @x.nodes('/DomesticCityWeatherTable/CityWeather') as t2(doc)
        
        SELECT * FROM @BA_WEATHERSELECT DATEDIFF(ms,@st,GETDATE())

Answer 24

不要用临时表，用实表，因为临时表存在于tempdb里。 创建schema时，元数据在当前库。

Answer 25

Garnett_KG大侠的结果测试有误,最后耗时再乘以10就对了,我写了除以10忘记去掉了,
不过至此仍然是毫秒级别,回贴说明一下,