Click here to monitor SSC
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
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 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?!
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1064 Visits: 13728
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
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 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
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45187 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Rob-350472
Rob-350472
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45187 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45187 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3948 Visits: 6686
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)

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