• Replies 9 replies
  • Subscribers 337 subscribers
  • Views 79 views
  • Users 0 members are here
Related

Extraction Set Performance

Andrew Robertson
$organization

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!

 

 

 

  • $organization

    Hello,

    We just experienced this same problem. A 20K output set took more than 8 hours to process. We were able to reduce the time by running the output set directly on our server vs. a client PC.

    Best regards, 

    Christine Kohls

    Director of Development

    The Cleveland Museum of Natural History

    ckohls@cmnh.org

  • $organization

    You may wish to put in a ticket with TASK.  We had an issue recently where our annual brochure (a big extraction) was simply not completing.  The cause wound up being our extraction database simply being too large.  Tessitura was able to help us essentially trim old extraction results until our database was about 90% smaller, and this allowed the extraction to complete.  Even trimming the database wound up being a mammoth task that took over a week to finish.

  • $organization

    We’ve had some issues here as well and have found that saving the file as a .csv works much better than .xls.    Not sure that will solve your issues but it made a difference here.

     

    Michelle Usadel
    Tessitura Application Specialist | The Phoenix Symphony | Tel.  602-452-0441 | Fax. 602-253-1772
    www.phoenixsymphony.org | 1 N. 1st Street, Ste. 200, Phoenix, AZ 85004
     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Christine Kohls
    Sent: Tuesday, November 15, 2016 2:21 PM
    To: Michelle Usadel <musadel@phoenixsymphony.org>
    Subject: Re: [Tessitura Technical Forum] Extraction Set Performance

     

    Hello,

    We just experienced this same problem. A 20K output set took more than 8 hours to process. We were able to reduce the time by running the output set directly on our server vs. a client PC.

    Best regards, 

    Christine Kohls

    Director of Development

    The Cleveland Museum of Natural History

    ckohls@cmnh.org

    From: Andrew Robertson <bounce-andrewrobertson2168@tessituranetwork.com>
    Sent: 11/15/2016 3:35:35 PM

    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!

     

     

     




    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!
  • $organization

    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.

  • $organization in reply to Andrew Robertson

    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.

     

     

     

  • $organization

    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

    Kauffman-Center-Logo_5 Years_SMALL

     

     

    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!

  • $organization in reply to Laura House

    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.

     

     

  • $organization in reply to Andrew Robertson
    Additional Addendum here for anyone referencing this ticket. Always make sure to include !. in the Data Select Column within TR_QUERY_ELEMENT. Tessitura will actually write the SQL to the output set differently if you do not include !. in the Data Select, which will adversely effect performance.
  • $organization in reply to Andrew Robertson
    Great tip re: checking for leftover rows in TW_EXTRACT_CUST_INFO. I found 500k in ours leftover just now. Adding this to a daily data cleanup script.