DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
Nagac



Group memberships:
Premium Members

Joined: 29 Mar 2011
Posts: 127
Location: India
Points: 1061

Post Posted: Fri Jan 31, 2020 3:38 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi

I am trying to Update and insert a table in Snowflake via separate Jobs. Update is taking long time and not finishing in Monitor it says only 1 row is being processing all the time and it didn't go more than that.

I am using ODBC Stage and updating the data using 3 key columns and 5 non key columns are being updated

Volume:30000

Would you please advise if we can load quicker using another way such as bulk or any methods?
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Fri Jan 31, 2020 5:25 am Reply with quote    Back to top    

Snowflake, eh? The database that says it has "the instant and near-infinite performance, concurrency and scale your organization requires"? Hmmm.

Clarify your "key columns" statement, please. All it means in a tool like this is they are bound into the WHERE clause so... do you have a composite index over them? And I am also curious if you are running the insert and update jobs at the same time.

I think your last question should be posted on whatever peer-to-peer support site the Snowflake peoples have.

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
Nagac



Group memberships:
Premium Members

Joined: 29 Mar 2011
Posts: 127
Location: India
Points: 1061

Post Posted: Fri Jan 31, 2020 8:52 am Reply with quote    Back to top    

Thanks chulett

There are no index build on table and i am running Update job then insert job in sequence.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Sat Feb 01, 2020 5:21 am Reply with quote    Back to top    

Well... then understand that every update will need to do a full table scan in an effort to find the record it is looking for. There isn't a slower way possible to update a table that I can think of. So, unless there's some other kind of Snowflake magic you can use, get a index on that table!

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours