Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

String splitter to table weird result Expand / Collapse
Author
Message
Posted Sunday, May 2, 2010 12:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:16 PM
Points: 4,389, Visits: 9,526
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 !!


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


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


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #914385
Posted Sunday, May 2, 2010 3:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:00 PM
Points: 420, Visits: 560
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)

Post #914401
Posted Sunday, May 2, 2010 3:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:24 PM
Points: 7,139, Visits: 15,191
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?
Post #914404
Posted Sunday, May 2, 2010 4:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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 !!


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


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


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #914410
Posted Sunday, May 2, 2010 4:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:00 PM
Points: 420, Visits: 560
Cheers, that did it !
Post #914411
Posted Sunday, May 2, 2010 4:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:00 PM
Points: 420, Visits: 560
.."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..
Post #914417
Posted Sunday, May 2, 2010 6:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #914429
Posted Monday, May 3, 2010 9:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 2:08 PM
Points: 405, Visits: 2,670
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

Post #914760
Posted Tuesday, May 4, 2010 8:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
WayneS (5/2/2010)
Jeff must be slipping ... usually when he gets involved, minutes go down to milliseconds!


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???


-- You can't be late until you show up.
Post #915340
Posted Sunday, January 8, 2012 4:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1232169
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse