One of our users has created an Extraction Set that is taking a very long time to complete (like 3-4 hrs). We have no other performance issues with running queries etc., in fact performance across the database has been pretty snappy. This extraction set returns something like 20,000 rows and is linked to an Output Set.
Has anyone else run into issues with Extraction Set performance? I started to do a little digging, but didn't want to spend too much time on this if this is a known issue across multiple organizations (like T-Stats build time for instance).
Thanks!
I appreciate you for sharing all of this, Andrew and Brian! We have similar issues often, I’m going to find out if we also have a similar solution.
Thank you,
Dr. Laura House, Shared Services Database Manager
Kauffman Center for the Performing Arts
1601 Broadway | Kansas City, MO 64108
direct 816.994.7214 | cell 816.301.1304 | fax 816.994.9037
kauffmancenter.org | a non-profit 501(c)3 organization
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Andrew Robertson Sent: Wednesday, November 16, 2016 3:03 PM To: Laura House Subject: Re: [Tessitura Technical Forum] Extraction Set Performance
The issue was related to a view things, all of which other users in this post pointed out!
We were extracting to a network drive, not a drive on the database server. Moving the extraction folder on the database server sped things up a lot.
Running from a Client on the database server also sped things up.
Finally, when the jobs runs it populates the table tw_extract_cust_info with all the extraction data. It then removes that data once it's complete. However, if the jobs fail this data is not cleared out (neither is the temp job, table, view). So we were running against a table of 700k records with Index fragmentation of 99%. Clearing out this data improved performance massively.
From: Andrew Robertson <bounce-andrewrobertson2168@tessituranetwork.com> Sent: 11/16/2016 11:29:14 AM
I have opened a TASK ticket and am troubleshooting this with Tessitura. There are some great tips here also, thanks so much! I will try and update this ticket to let people know the resolution.
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Quick addendum to this.
We also noticed a lot (and I mean a lot) or performance issues with this List once we starting outputting to an Output Set that had Custom Elements in it.
Removing these Custom Elements improved performance massively.