Performance of Scripts in SQL Server 2000

Question

Performance of Scripts in SQL Server 2000

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Crud... guess I need to start another rant! This forum won't allow me to post a "backslash-n" even in the code window. Line 188 of the BCP format file needs it... I'm attaching the file so you have the whole thing correctly...

...and, of course, it wouldn't allow me to upload it with the "fmt" extension I'd originally used... I had to change it to a "txt" extension, just so you know. You can either change it after you download it or use it as it is because Bulk Insert doesn't care what you call it.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 2

Whitt: can you not 'just';

1. Use your data cleaner (.NET app) and have it out put a well formatted CSV file.

then

2. Use SQL Server to import the data into a table via bulk insert.

Or have I missed something?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 3

Sorry - had not read all of the posts before posting!:rolleyes:

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Shaun McGuile (1/16/2008)
Sorry - had not read all of the posts before posting!:rolleyes:
--Shaun

Heh! Man, I had to laugh out loud! I've done that many times myself... it's normally sometime before the 4 gallons of coffee I need just to get my heart started 😀

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Jeff Moden (1/16/2008)
Well.... I should have known better... as soon as I saw that it was a Government agency, I should have known that the record layout they provided wasn't going to be accurate when compared to the data. Many of the columns are, in fact, wider than their record layout states. Heh... figures... with all their fancy pants ISO standards, they can't even produce a file according to their own specs. :hehe:

I didn't feel like typing the 6 pages of column names, data types, etc...so I just let it do its thing and make all fields varchar(255) (wasteful if you do that long-term, but helpful if you want to see the data fairly quickly and make sure the format is consistent.

What was most impressive to me was that the NPI column was in fact unique, and non-null. That's the primary piece of data they're trying to disseminate, and the fact that they don't have it duplicating/spawning a dozen different rows was impressive enough (especially in comparison to the UPIN data files they used to send out....:).

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

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 6

Jeff: Would those be USA gallons then? (English gallons are bigger!) 😉

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Heh... spot on, Matt... Just like you did, I load what I call a "discovery load". The only difference is that I don't even take the time to change to VARCHAR(255)... I leave it at 8000 (SS2k default) just 'cause I'm lazy... 😉

The only mistake I made was not following my own rules... Right after the Discovery Load, I normally run a piece of add hoc code that generates other code to report the max length of each column. I screwed up and said "It's gotta be right... it's from the Government". :hehe:

I also made a piece of code a while back that generates the necessary BCP format file with the "crazy" quoted delimiters that "real" CSV files have in them. Haven't taken the time to tweak the first and last row requirements of the format file but it only takes seconds to do that manually, anyway.

Since most of the columns were VARCHAR to begin with, wasn't much of a problem to take it over the hump and change the length for the VARCHAR columns (especially since there's a repeatable pattern) and change the small handful of other columns to a data type.

The real story is, it's been a bit since I've had to do a wide-load, using these methods, on a large amount of data I've never seen and that I couldn't open with TextPad to verify. I actually used it to my benefit by practicing on some data that I've never seen before. :w00t:

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Shaun McGuile (1/16/2008)
Jeff: Would those be USA gallons then? (English gallons are bigger!) 😉
--Shaun

Heh... only if I forget to take the brick out of my coffee pot from the water crisis 😀

http://www.teaching-english-in-japan.net/conversion/gallons

http://www.devx.com/vb2themax/Tip/19692

http://www.csgnetwork.com/fuelvolumeconverter.html

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 9

Current petrol prices here are abou $7.73 per gallon(US)!!!!!

:crying:$6.18 of that is tax!!!!!:crying:

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Shaun McGuile (1/16/2008)
Current petrol prices here are abou $7.73 per gallon(US)!!!!!
:crying:$6.18 of that is tax!!!!!:crying:
--Shaun

Dang! You got Grandholm for a Governor, too? 😛 They should leave out gasoline taxes and start a penny a bottle tax on bottled water and Mountain Dew... they could even get rid of income taxes if they did that 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DiverKas SSCrazy Points: 2049 More actions · Answer 11

Dang! You got Grandholm for a Governor, too? 😛 They should leave out gasoline taxes and start a penny a bottle tax on bottled water and Mountain Dew... they could even get rid of income taxes if they did that 😛

Oh my... another Michigander...

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

You from Michigan, Kevin?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DiverKas SSCrazy Points: 2049 More actions · Answer 13

Jeff Moden (1/16/2008)
You from Michigan, Kevin?

Originally from the Waterford area, now in Redford, you?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Squirrel and S. Blvd...

We should get together sometime and do a couple of 12 oz. curls...

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 15

Shaun McGuile (1/16/2008)
Current petrol prices here are abou $7.73 per gallon(US)!!!!!
:crying:$6.18 of that is tax!!!!!:crying:
--Shaun

I remember paying $0.80 per gallon in Texas in the mid '90s 🙂