August 6, 2008 at 1:10 pm
I have a Bulkload process that runs 4 million rows very quickly, under 1 minute.
I need to then move these rows from the Staging table into the Production Table. I am transforming some Varchars into datetime.
Example: '20080431 083045' is Transformed in an insert query:
Left(myDate,4)+'/'+substring(myDate,5,2)+'/'+substring(myDate,7,2)
+ ' '+substring(myDate,9,2)
+ ':'+substring(myDate,11,2)
+ ':'+substring(myDate,13,2)
This step take much longer to execute. I understand there is more overhead for an insert, however is the way I am creating the string causing alot of stress on the server, is there a faster way to get this done?
Thx.
August 6, 2008 at 1:18 pm
I don't know of any other way to convert that format string to datetime. If you have some way to limit the # of inserts through a join or some other means you might get better performance by batching the insert into the new table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2008 at 2:24 pm
Thanks for the Response.
I was reading that perhaps creating a UDF (User Defined Function) to process the String would be faster, or perhaps a CLR assembly? Does anyone have an opinion on this? We are currentlly Chugging through 60 GIG's (after inserting) a day of flatfiles any speed improvements would be noticeable.
August 6, 2008 at 3:45 pm
c.holman (8/6/2008)
Thanks for the Response.I was reading that perhaps creating a UDF (User Defined Function) to process the String would be faster, or perhaps a CLR assembly?
No and no. In-line SQL is almost always the fastest way.
You will need to give us more information in order for us to give you good optimization advice: like the query, execution plan, execution statistics, and table defs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 6, 2008 at 4:02 pm
Again thank you. I really can't post the SQL Due to confidentiality issues, and reworking it to be annonymous would be time consuming. I do appreciate the fact that Tuning Queries is not easy without information, my questions were more a syntax/procedural issue.
Basically in at a very high level I am inserting a Bulkload table of all Varchar Datatypes into another table of the same datatypes. I believe it is just the sheer size of the data being moved/inserted that is the issue. (Currently 81 Million Rows). The ETL is completing in about 1.5 to 2 hours per day for this table.
Again thanks for the feedback.
August 6, 2008 at 4:18 pm
Then can you provide the run-time statisitics?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 6, 2008 at 5:10 pm
I am not certain what that entails, I am not a DBA, but a lowly dev.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply