May 2, 2010 at 4:36 pm
.."As we've seen on dozens of posts, an XML splitter would not have performed so well."..
Jeff you have seen the yahoo .csv link I posted in this thread.
I too investigated XML transfer of OHLCV data.
So you Say that plain TEXT splitting is fast than XML splitting into a DateOHLCV table.
Interesting..
May 2, 2010 at 6:00 pm
Digs (5/2/2010)
.."As we've seen on dozens of posts, an XML splitter would not have performed so well."..Jeff you have seen the yahoo .csv link I posted in this thread.
I too investigated XML transfer of OHLCV data.
So you Say that plain TEXT splitting is fast than XML splitting into a DateOHLCV table.
Interesting..
If I understand you correctly, you're asking if I think that plain text splitting is faster than shredding properly formed XML. My answer is... I don't know... I've not had to worry about XML downloads.
What I was saying is that a Tally table splitter is usually faster than using FOR XML to do the split on CSV text for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2010 at 9:19 am
You can also tackle this problem using OPENROWSET BULK.
The data is first referenced using OPENROWSET BULK via the CTE cteFileData. This uses the unstructured format file DataSampleBulk.fmt which has a single field called BulkColumn defined as SQLCHAR 8000 and terminated with #
The second CTE cteData then splits the file vertically using CROSS APPLY/Tally split method and in the same query reassembles the data into the 8 column components using PIVOT. The final SELECT on cteData resolves each column into the correct data type
;
WITH cteFileData AS
(
SELECT BulkColumn FROM OPENROWSET (BULK 'C:\DataSample.txt', FORMATFILE = 'C:\DataSampleBulk.fmt') AS X
)
,
cteData AS
(
SELECT [1],[2],[3],[4],[5],[6],[7],[8] FROM cteFileData
CROSS APPLY
(
SELECT [1],[2],[3],[4],[5],[6],[7],[8] FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
SUBSTRING(BulkColumn + ',', N, CHARINDEX(',', BulkColumn + ',', N) - N) AS Value
FROM master.dbo.Tally
WHERE N < LEN(BulkColumn) + 2 AND SUBSTRING(',' + BulkColumn + ',', N, 1) = ','
) AS Z
PIVOT
(
MAX(Value) FOR ROW IN
(
[1],[2],[3],[4],[5],[6],[7],[8]
)
)
AS pvt
)
AS Y
)
SELECT
CONVERT(VARCHAR(20), [1]) AS COL1,
CONVERT(DATETIME, [2]) AS COL2,
CONVERT(DECIMAL(9,2), [3]) AS COL3,
CONVERT(DECIMAL(9,2), [4]) AS COL4,
CONVERT(DECIMAL(9,2), [5]) AS COL5,
CONVERT(DECIMAL(9,2), [6]) AS COL6,
CONVERT(BIGINT, [7]) AS COL7,
CONVERT(DECIMAL(9,2), [8]) AS COL8
FROM cteData
May 4, 2010 at 8:14 am
WayneS (5/2/2010)
Jeff must be slipping :-D... usually when he gets involved, minutes go down to milliseconds! :w00t:
And then the $$$ to secure his services goes way up when that happens. Maybe he's simply trying to draw him into his lair before pouncing??? :w00t:
-- You can't be late until you show up.
January 8, 2012 at 4:49 pm
Digs (5/2/2010)
.."As we've seen on dozens of posts, an XML splitter would not have performed so well."..Jeff you have seen the yahoo .csv link I posted in this thread.
I too investigated XML transfer of OHLCV data.
So you Say that plain TEXT splitting is fast than XML splitting into a DateOHLCV table.
Interesting..
Sorry for resurrecting a post that's more than a year old... I can finally answer this with some good bit of authority... please see the following article... particularly the comparison charts.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy