Home | Webstore
Latest News: OOTP 25 Available - FHM 10 Available - OOTP Go! Available

Out of the Park Baseball 25 Buy Now!

  

Go Back   OOTP Developments Forums > Out of the Park Baseball 25 > OOTP Mods > OOTP Mods - Database Tools

OOTP Mods - Database Tools Do you need to take a dump? SQL gurus welcome

Reply
 
Thread Tools
Old 10-29-2016, 06:21 PM   #1
ruffyen
Minors (Single A)
 
Join Date: Jan 2016
Posts: 61
[FIXED] StatsLab Team Warroom Slow

So I have been fighting team warroom slowness. It was taking upwards of 3 mins just to load the players eligible for the draft. I did some performance debug sessions and the following two query seemed to drastically help

create index idx_player_id on players_fielding(player_id);

create index idx_player_id on players_batting(player_id);
create index idx_player_id on amateur_draft(player_id);
create index idx_player_id on players_pitching(player_id);
create index idx_player_id on ootp_sql_draft_list(player_id);
create index idx_player_id on players(player_id);

The query went from taking over 2 mins to run to running in around 1 sec for a league with around 13 years of play data.

I love StatsLab and I hope that this little diddy will help anyone who is starting to see things take longer and longer.

If you are curious of the steps taken I used the following resource(s):
http://stackoverflow.com/a/7470567 -- This was for finding the query
https://www.sitepoint.com/using-expl...mysql-queries/ -- This was for the query analysis

Edit: After the first IDX that I created i started digging in deeper after we switched to showing potentials / ratings / stats in statslab draft and found additional tables to index on player_id. The queries are now sub second...0.04 secs for 500 rows.

Last edited by ruffyen; 11-01-2016 at 08:16 AM.
ruffyen is offline   Reply With Quote
Old 02-14-2017, 10:34 PM   #2
anthonywvalentine
All Star Reserve
 
anthonywvalentine's Avatar
 
Join Date: Jul 2010
Location: Connecticut
Posts: 647
Quote:
Originally Posted by ruffyen View Post
So I have been fighting team warroom slowness. It was taking upwards of 3 mins just to load the players eligible for the draft. I did some performance debug sessions and the following two query seemed to drastically help

create index idx_player_id on players_fielding(player_id);

create index idx_player_id on players_batting(player_id);
create index idx_player_id on amateur_draft(player_id);
create index idx_player_id on players_pitching(player_id);
create index idx_player_id on ootp_sql_draft_list(player_id);
create index idx_player_id on players(player_id);

The query went from taking over 2 mins to run to running in around 1 sec for a league with around 13 years of play data.

I love StatsLab and I hope that this little diddy will help anyone who is starting to see things take longer and longer.

If you are curious of the steps taken I used the following resource(s):
monitoring - How can I view live MySQL queries? - Stack Overflow -- This was for finding the query
https://www.sitepoint.com/using-expl...mysql-queries/ -- This was for the query analysis

Edit: After the first IDX that I created i started digging in deeper after we switched to showing potentials / ratings / stats in statslab draft and found additional tables to index on player_id. The queries are now sub second...0.04 secs for 500 rows.
For someone who this is complete gibberish too... What exactly do I need to edit to help speed up the Team Warroom?
anthonywvalentine is online now   Reply With Quote
Old 02-17-2017, 02:15 PM   #3
ruffyen
Minors (Single A)
 
Join Date: Jan 2016
Posts: 61
@anthonywvalentine

So rename the attached file from .txt to .sql

Upload it into your SQL files directory on the server (same place all the other SQL files go)

and when you go to run the sql files to update stats lab it should run this one last due to the name of the file.
Attached Files
File Type: txt z_create_indexes.mysql.txt (232 Bytes, 227 views)
ruffyen is offline   Reply With Quote
Reply

Bookmarks

Tags
index, indexes, performance, statslab, warroom

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 02:19 PM.

 

Major League and Minor League Baseball trademarks and copyrights are used with permission of Major League Baseball. Visit MLB.com and MiLB.com.

Officially Licensed Product – MLB Players, Inc.

Out of the Park Baseball is a registered trademark of Out of the Park Developments GmbH & Co. KG

Google Play is a trademark of Google Inc.

Apple, iPhone, iPod touch and iPad are trademarks of Apple Inc., registered in the U.S. and other countries.

COPYRIGHT © 2023 OUT OF THE PARK DEVELOPMENTS. ALL RIGHTS RESERVED.

 

Powered by vBulletin® Version 3.8.10
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Copyright © 2020 Out of the Park Developments