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


An alternative to split for performance benefits?


An alternative to split for performance benefits?

Author
Message
Rob-350472
Rob-350472
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 684
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?!
Steve Hall
Steve Hall
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2919 Visits: 11573
What split function are you referring to? Is it this - http://www.sqlservercentral.com/articles/Tally+Table/72993/ ?

Steve Hall
Linkedin
Blog Site
Animal Magic
Animal Magic
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1670 Visits: 13731
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)
Rob-350472
Rob-350472
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 684
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...
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1397 Visits: 1789
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90525 Visits: 41149
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 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
Rob-350472
Rob-350472
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 684
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!)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90525 Visits: 41149
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 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90525 Visits: 41149
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 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
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8320 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
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