<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
An alternative to split for performance...
An alternative to split for performance benefits?
Rate Topic
Display Mode
Topic Options
Author
Message
Rob-350472
Rob-350472
Posted Thursday, December 06, 2012 3:59 AM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 3:01 AM
Points: 283,
Visits: 614
Guys,
I'm wondering if you can help me think of a different idea/way of tacking something which will produce faster results...
Essentially someone will be importing a CSV file of email addresses, we're then running this collection of email addresses against a prodceure using the split function to supply it with the email addresses.
With 500 or so email adddresses it's slugish but okay, I've been told it could be up to 30,000 addresses!
I tried running the split function into a temp table and running against that but it seemed slower, I don't know if there's a totally different approach I can take to make things run along a bit quicker.
Any ideas?!
Post #1393420
BrainDonor
BrainDonor
Posted Thursday, December 06, 2012 4:11 AM
Ten Centuries
Group: General Forum Members
Last Login: Friday, June 14, 2013 8:33 AM
Points: 1,402,
Visits: 6,950
What split function are you referring to? Is it this -
http://www.sqlservercentral.com/articles/Tally+Table/72993/
?
BrainDonor
Linkedin
Post #1393424
Animal Magic
Animal Magic
Posted Thursday, December 06, 2012 5:26 AM
SSC Eights!
Group: General Forum Members
Last Login: Monday, June 10, 2013 7:21 AM
Points: 983,
Visits: 13,356
What format is the file in? Is it just one continuous string of email addresses? (i assume it is as your asking about string splitters)
Post #1393453
Rob-350472
Rob-350472
Posted Thursday, December 06, 2012 6:23 AM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 3:01 AM
Points: 283,
Visits: 614
I'd have to review the split function you linked to - I opened it and had a quick look but I've not had the time to read it yet.
As for the format, these addresses will be in an xls and the user will create a CSV to upload, they don't have to create a CSV though of course, it's just how we've done things before...
Post #1393475
CapnHector
CapnHector
Posted Thursday, December 06, 2012 3:45 PM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, June 06, 2013 3:00 PM
Points: 939,
Visits: 1,713
Rob-350472 (12/6/2012)
I'd have to review the split function you linked to - I opened it and had a quick look but I've not had the time to read it yet.
As for the format, these addresses will be in an xls and the user will create a CSV to upload, they don't have to create a CSV though of course, it's just how we've done things before...
The article is a very good explination of Jeff Moden's Delimited split 8k. If you can post a couple of lines from the file (data obfucated of course) im sure us here on the forum can come up with something close to help you out.
For faster help in answering any problems Please read
How to post data/code on a forum to get the best help - Jeff Moden
for the best way to ask your question.
For performance Issues see how we like them posted here:
How to Post Performance Problems - Gail Shaw
Need to Split some strings?
Jeff Moden's DelimitedSplit8K
Jeff Moden's
Cross tab and Pivots Part 1
Jeff Moden's
Cross tab and Pivots Part 2
Jeremy Oursler
Post #1393777
Jeff Moden
Jeff Moden
Posted Thursday, December 06, 2012 5:28 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:05 PM
Points: 33,112,
Visits: 27,039
Rob-350472 (12/6/2012)
I'd have to review the split function you linked to - I opened it and had a quick look but I've not had the time to read it yet.
As for the format, these addresses will be in an xls and the user will create a CSV to upload, they don't have to create a CSV though of course, it's just how we've done things before...
It might be that you don't really need a splitter. Depending on the format of each line in the file, the use of BULK INSERT might be a lot more appropriate and MUCH faster.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1393802
Rob-350472
Rob-350472
Posted Friday, December 07, 2012 2:15 AM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 3:01 AM
Points: 283,
Visits: 614
I'm actually looking at the bulk insert technique now - in terms of data it's very simple, an Excel file with an email address per row, saved as a CSV, that's literally all.
The bulk insert I'm trying keeps appending them all into one column rather than multiple rows though which is a bit annoying!
IF object_id('tempdb..#EmailAddresses') IS NOT NULL
BEGIN
DROP TABLE #EmailAddresses
END
CREATE TABLE #EmailAddresses(
Email Varchar(max) NOT NULL
) ON [PRIMARY]
BULK INSERT #EmailAddresses
FROM 'C:\Program Files\Microsoft SQL Server\Book1.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
And that just spits out email addresses with a space between e.g. x@x.com y@y.com....
I tried with 0x0a as the rowterminator instead but it generated the same results, I need to play some more with this (I've only used it once before a v long time ago!)
Post #1393929
Jeff Moden
Jeff Moden
Posted Friday, December 07, 2012 4:53 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:05 PM
Points: 33,112,
Visits: 27,039
Rob-350472 (12/7/2012)
I'm actually looking at the bulk insert technique now - in terms of data it's very simple, an Excel file with an email address per row, saved as a CSV, that's literally all.
The bulk insert I'm trying keeps appending them all into one column rather than multiple rows though which is a bit annoying!
IF object_id('tempdb..#EmailAddresses') IS NOT NULL
BEGIN
DROP TABLE #EmailAddresses
END
CREATE TABLE #EmailAddresses(
Email Varchar(max) NOT NULL
) ON [PRIMARY]
BULK INSERT #EmailAddresses
FROM 'C:\Program Files\Microsoft SQL Server\Book1.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
And that just spits out email addresses with a space between e.g. x@x.com y@y.com....
I tried with 0x0a as the rowterminator instead but it generated the same results, I need to play some more with this (I've only used it once before a v long time ago!)
How many email addresses are there on each line of the CSV file? Also, have yhou looked at the file to make sure it has commas in it?
Heh... Duh! That was in the very first paragraph of that post. Sorry.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1394287
Jeff Moden
Jeff Moden
Posted Friday, December 07, 2012 4:59 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:05 PM
Points: 33,112,
Visits: 27,039
Rob-350472 (12/7/2012)
I tried with 0x0a as the rowterminator instead but it generated the same results, I need to play some more with this (I've only used it once before a v long time ago!)
Just take out all mention of ROWTERMINATOR in your BULK INSERT command and see what happens that way. It the answer is "same thing" or worse, then you need to look at the hex values for the row terminators in the file itself to determine what the proper terminator should be.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1394288
ScottPletcher
ScottPletcher
Posted Monday, December 10, 2012 4:27 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333,
Visits: 1,803
Jeff Moden (12/7/2012)
Rob-350472 (12/7/2012)
I tried with 0x0a as the rowterminator instead but it generated the same results, I need to play some more with this (I've only used it once before a v long time ago!)
Just take out all mention of ROWTERMINATOR in your BULK INSERT command and see what happens that way. It the answer is "same thing" or worse, then you need to look at the hex values for the row terminators in the file itself to determine what the proper terminator should be.
The other typical row terminators are '\r' (carriage return/char(13)), '\~n' (new line/char(10)), '\r'~n' (CR+LF) [where backslash~n is just backslash n: backslash n by itself doesn't display at all].
SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1394813
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
