SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String splitter to table weird result


String splitter to table weird result

Author
Message
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19129 Visits: 10042
WayneS (5/2/2010)
Digs (5/1/2010)
Jeff,

All works fine: And Fast !

I will keep your name on my list when I need to spend $$$ on my project .

Minutes have gone down to seconds !!:-):-D:-P;-)w00tCoolHehe


Jeff must be slipping :-D... usually when he gets involved, minutes go down to milliseconds! w00t


Nah - he managed a solution in milliseconds (bcp), but was constrained by the OP's requirements. Cool

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Digs
Digs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1615 Visits: 564
jeff or anyone another question


SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.

SQL Server Execution Times: CPU time = 437 ms, elapsed time = 475 ms.(20485 row(s) affected)



The above stats were posted by Jeff.

How do I get my MS SQL 2005 management studio to give me the same info

NOTE I get this after a SELECT query test

(20485 row(s) affected)
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29433 Visits: 19002
Try:


Set Statistics TIME on



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214822 Visits: 41979
Jeffrey Williams-493691 (5/2/2010)
WayneS (5/2/2010)
Digs (5/1/2010)
Jeff,

All works fine: And Fast !

I will keep your name on my list when I need to spend $$$ on my project .

Minutes have gone down to seconds !!:-):-D:-P;-)w00tCoolHehe


Jeff must be slipping :-D... usually when he gets involved, minutes go down to milliseconds! w00t


Nah - he managed a solution in milliseconds (bcp), but was constrained by the OP's requirements. Cool


Heh... thanks guys and true enough on the constraints here... the real killer is VARCHAR(MAX) on this one. Just using that causes double the duration even when the number of characters is less than 8000. I know you'll think that I've tanked but Phil Factor has shown that a very tight While Loop on things that use VARCHAR(MAX) will sometimes outperform a Tally Table solution for VARCHAR(MAX) datatypes.

As we've seen on dozens of posts, an XML splitter would not have performed so well.

And finally, the one thing I don't use would probably be the best solution here (other than loading it from a file using BULK INSERT, of course) but the OP can't use that either... a very well written CLR to do the split.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Digs
Digs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1615 Visits: 564
Cheers, that did it !:-)
Digs
Digs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1615 Visits: 564
.."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..
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214822 Visits: 41979
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
steve-893342
steve-893342
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1191 Visits: 2670
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


tosscrosby
  tosscrosby
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5726 Visits: 5316
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214822 Visits: 41979
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search