Can't purge Tournament PLayer DB data

[zubs1aa]

Hi-

I play a lot of tourneys and am sure my DB is way larger then need be. (I do use the postreg sql version.)

So I'd like to prune from it to speed things up by removing TOURNAMENT players with less then 50 hands.

Is there any way to purge all players with less than X hands? Or some similar way of doing so without going one by one ?

[b]there are over 400,000 players in there.!!!!!!!!!!/b]

[ptrack pat]

There is no easy way to do this. Do you this many player names in your database because you've imported a lot of tournament summaries from PokerStars from large tournaments where there are 1000's of player names?

You can start a new database or use the "Delete Players w/ no hands loaded or winnings" button on the File > Tournament Results - Manual Entry > Poker Stars window. You would have to do this for each of the large tournaments. Then use the Purge Players button on the Utilities > Database Maintenance and Options window to actually have the names removed from the database.

[zubs1aa]

I know you guys have a lot to do here, but that answer is really not sufficient. (Or perhaps it's the functionality...)

1) I have no idea where all these players came from. I've played thousands + of tournaments on FTP pstars and cereus.

So it's not a remotely practical possibility to go to every tourney summary i have and click delete players for EVERY tourney since I don't know where they are. And then you'd have me go to the purge players button, which is actually on the RING HANDS tab - so does that even work.

Long story short, you have a function that lets you hide tourney players by number of tourneys played, but that's not practical either as I could have them for 1 hand or 400 hands in the same tourney.

So how the heck do I clean out the junk here? I am exasperated!

2) You also suggested making a new DB-- which is really not a good solution either. How often do I need to make a new db????????

And, if I did make a new db, how do i remove these players...you did n't tell me even how that would work????

3) is this fixed in PT3 so that you can prune your tourney players appropriately? I've resisted upgrading but will consider it further if PT3 is going to let me clean this junk out.

4) I'm at the point of over-stressing the machine so I know i need to clean that dang db up as much as possible- i'v edone everything suggested that I can find.... but this cleanup i am sure would help a great deal.....It's crazy when PT kills the machine so much that it locks up my tables and I cant play hands! cmon! I need some help! (and I'm techie, so it's not like I don't know how to normally fix stuff like this.........sorry - I am just flabbergasted and exasperated both.

[ptrack pat]

And then you'd have me go to the purge players button, which is actually on the RING HANDS tab - so does that even work.

Yes, it does work, this button will delete any player name from the players table that has no hand histories/tournament summaries associated with them regardless of if it's for ring or tournaments. I know how the program functions, I wouldn't have told you this if it didn't work for tournament players.

1) I already told you there is no easy way to do this. And you can't just delete other players data if they have hands played because that's going to delete your own hands as well. So if you don't want to try what I've listed above then consider using the Advanced Purge button on the Utilities > Database Maintenance and Options window and deleting some old data if you don't want to have all of it saved. For instance, you can purge all the data that's older than 1 year and then use the Purge Players button after that to clean up all the names. As I said, using the advanced purge option is also going to delete your hand histories/tournament summary data as well though so don't use it if you want to keep your data intact.

2) If you make a new database, it starts out with no players in it, there is nothing to remove. How often you do it is up to you. The only way I can see you having obtained 400,000 player names by importing some huge tournament summaries from PokerStars that included 1000's of names that you never even played against but they still get imported because they are in the tournament summary. There is a function on the File > Tournament Results - Manual Entry > PokerStars window that allows you to tell it what names get imported (i.e. only players you played against) which keeps the players table from getting huge with names you'll likely never see again.

3) PT3 has similar purging that PT2 does as far as I know but I'm not involved with it so I don't know it's full functionality.

[zubs1aa]

1) I already told you there is no easy way to do this. And you can't just delete other players data if they have hands played because that's going to delete your own hands as well. So if you don't want to try what I've listed above then consider using the Advanced Purge button on the Utilities > Database Maintenance and Options window and deleting some old data if you don't want to have all of it saved. For instance, you can purge all the data that's older than 1 year and then use the Purge Players button after that to clean up all the names. As I said, using the advanced purge option is also going to delete your hand histories/tournament summary data as well though so don't use it if you want to keep your data intact.

Look at this from my point of view. I cant delete playrs I played against or it removes my data. I get that. That option is out.

Advanced purge also doesn't work because it screws my own data up- obviously this isn't a practical solution either.

Need I go through X thousand tournies 1 by 1 and delete the players with no hands played? That's insane too.


2) If you make a new database, it starts out with no players in it, there is nothing to remove. How often you do it is up to you. The only way I can see you having obtained 400,000 player names by importing some huge tournament summaries from PokerStars that included 1000's of names that you never even played against but they still get imported because they are in the tournament summary. There is a function on the File > Tournament Results - Manual Entry > PokerStars window that allows you to tell it what names get imported (i.e. only players you played against) which keeps the players table from getting huge with names you'll likely never see again.

OK, I found the setting for stars to only record players that i have hand histories for. That should reduce the number of players going forward, but of course still leaves me F'd for the present.

This just leaves the option of creating a new database. What you haven't told me is how to import my existing data into a new database, without all the extra garbage players!!!!!!!!!!!! (Or are you suggesting I just start from scratch with new tournies-- which would be a giant nightmare as i would have no useful statistics to go off of and my earning numbers etc would be all F'd.)

PLEASE THINK ABOUT THIS FROM THE POOR USER PERSPECTIVE - SHORT OF TOSSING ALL TH OLD DATA AND STARTING FROM SCRATCH, WHAT THE f IS SENSIBLE TO DO--- FROM *MY PERSPECTIVE* NOT THE DEVELOPER PERSPECTIVE.

IF IT WILL SPEED THE GODDAMN THING UP I WILL GO THROUGH THE PAIN OF CREATING A NEW DB..........BUT ONLY IF I CAN BRING IN THE DATA I WANT TO IT------ AND YOU HAVE NOT ONCE TOLD ME HOW TO DO THAT. HAVE YOU JUST NEGLECTED TO DO SO? OR ARE YOU BASICALLY TELLING ME THAT I CANT HAVE CONTIGUOUS DATA AND NEED TO START A NEW DB FROM THIS POINT FORWARD--- THEREBY RENDERING LARGE PARTS OF THE FUNCTIONALITY OF THIS SOFTWARE FREEKING USELESS BECAUSE i CANT LOOK AT THINGS OVER TIME OR IN ONE PLACE?


PLEEEEEEEEEEEEASE TELL ME THEIR WOULD BE A WAY TO IMPORT EVERYTHING INTO A NEW DB THAT WOULD RUN THROUGH THE APPROPRIATE FILTERS FOR IMPORTS (SUCH AS THE PSTARS FLAG TO ONLY BRING EM IN WHERE THE HH EXISTS) - BRINGING IN THE HH'S AS WELL AS THE TOURNEY SUMMARIES THAT I ALREADY HAVE RECORDED ON MY MACHINE.

SORRY TO YELL, BUT MY HEAD IS ABOUT TO EXPLODE. OBVIOUSLY YOU KNOW THERE IS AN ISSUE WITH STARS TOURNEY SUMMARY IMPORTS--- BUT I DON'T RECALL A WARNING ON THOSE SWITCH SETTINGS TELLING ME WHEN I WAS SETTING THIS THING UP THT I MIGHT WELL WANT TO AVID THE PLAYERS WITH NO HANDS PLAYED....ELSE MY DB MIGHT BLOWUP. SO MUCH FOR AUTO-IMPORT.




3) PT3 has similar purging that PT2 does as far as I know but I'm not involved with it so I don't know it's full functionality.

[ptrack pat]

Why don't you just open the File > Tournament Results - Manual Import > PokerStars window and use the down arrow key to scroll through the list. Look at the # of entrants field in the bottom left data section as you scroll. When you see a tournament that has over a certain number of players (like 500) stop, click the Delete Players w/ No hands... button then continue scrolling. This isn't going to take that long if you just look for the larger tournaments and it's going to be a heck of a lot faster than exporting/re-importing all of your data. You may find some tournaments where you have 10,000 or more entrants and using this function for just those alone will clear out a ton of names.

After that use the Purge Players button to physically delete the player names from that database.

Why is it also out of the question to start a brand new empty database? If you use PokerAce, all your data from the 2 databases will still be combined, you'll still get the stats on the players from both databases displayed on the table. And you can still look at both databases in PT, it's not as if the data won't be available anymore. If you want to export and reimport the data into a new db you can do that and I will provide more details, but it will take hours to reimport everything which is a lot longer than what I described doing above. Also, with this process of reimporting, you would have to re-enter, manually, every tournament summary from FT or Cereus that you had entered manually into PT2. So any summary data you manually entered into PT2 (like FT summaries before they started generating them) you would have to re-enter that info. if you exported and reimported everything.

If you want to export/reimport then you need to do the following...

1. Use the File > Maintain Database Names window. Click the Add button and create a new, empty Access database. When prompted, opt to not copy anything from the current db.
2. Use the Utilities > Convert Database option to convert the new database you just created to PostgreSQL format.
3. Use the Utilities > Export Hand Histories To File option to export the hands from the current database to a folder on your hard drive. If you want to narrow down what gets exported, use the filters on this window to restrict what gets exported. Maybe test it with just your last couple months worth of data first to make sure it worked the way you wanted. Then if it did, you can export the rest of the data and reimport as I'll detail below later.
4. Back to the File > Maintain Database Names window. Click the "Dflt" box next to the new PostgreSQL database click OK to save then CLOSE AND REOPEN PT.
5. Open the Utilities > Manual Player Entry window. Add your PokerStars screen name here. Enter you sn and select PokerStars from the list and save the changes.
6. Now go to the File > Tournament Results - Manual Entry > PokerStars window and click the Customize Summary Info. To Import button. Use one of the options there, like only won $ and/or I played against. Make sure to select your screen name from the list. Save the changes.
7. Now, to make sure it's working, open the File > Import Hand Histories/Tournament Summaries window. Click the Select Files To Import button. Navigate to the folder where you exported the files to in step 3 and find one of the summary files there (the file name should have "summary" in it so you know it's a tournament summary). Import it, then go to the File > Tournament Results - Manual Entry > PokerStars window and make sure it only imported the names as you told it to do when you setup the customize window.
8. If that's all working, go to the File > Auto-Import > Generic Setup window. Select the "Look for files on my PC" option, tell it what folder to look in (the one you exported to in step 3) and click OK. On the next window, click Stop Timer. Then click the Force Import button and just let it run. It's going to take a while for this to run. Figure PT2 imports about 30,000 hands/hour and that doesn't include having to import the tournament summaries.

[zubs1aa]

I AM NOW OFFICIALLY INSANE AND FOREVER DESTINED TO HAVE A DB SYSTEM THAT CHOKES ITSELF TO DEATH ALONG WITH MY PC WHEN I USE POKERTRAKER!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

There are a host of reasons for this, but your fix suggestion WILL NOT WORK!

PLEASE ANSWER ALL MY QUESTIONS- I'VE NUMBERED THEM TO MAKE SURE THEY DON'T GET MISSED.

You were blaming it on pstars tourney summaries. There might be some pstars big tournies in there sure. However, it's exploding because of FULL TILT SUMMARIES!!!!!!!!!!! I play 99% of my volume there on FTP and their tourney summaries have all the detail just like stars summaries do.... Plus, you don't even have an option like for stars to exclude tilt players in summaries without a hand history from being imported!

So, using your solution, since I auto-import the HH as I play to get updated reads on the others in the tournies, the tourney summaries get sucked in too and load up all the players. And almost every non satelitte I play has over 1k players in it. We are talking serious volume of tournies here, every day.

Just for fun I filtered the data to look and there are 554 tournies in my system with 500+ players. I'm supposed to manually go back and delete on allo f these and then rinse wash repeat every day after playing 8-10 on FTP? That defeats the purpose of having the damn tool for record-keeping! It's not a small number of items we are talking about.

1) So, given them apples what do you propose now? fixing these on a backwards basis will take forever.


2) So how the bleep do I ever get this to be a functional tool now going forward??? thousands of players are going to be added every day even in a new db, which isn't where I want to go anyway.


I've read every forum pst on optimazation i could find and have tried everything, but no joke, this chokes my machine. I did get a marginal improvement by doing some manual tweaks to postsql per the Performance Tuning PostgreSQL thread - that helped a bit. But once a few tables are opened etc and im in a couple of tournies, eventually it completely maxes my machine to death.

Also, opening the stats file takes like 10 minutes since it is so large - in fact it made me get an out of memory error first time I tried it today to look at stats.

3) Please correct me if I am wrong about FTP tournies - that they are going to grow my DB exponentially because unlike pstars i cant get rid of the guys with no hands????

-------------------------------

Now, you wrote a long item about why starting a new db would work fine. Unfortunately, there isn't sufficient detail in your post to explain why what you propose would be a good solution.

Why is it also out of the question to start a brand new empty database? If you use PokerAce, all your data from the 2 databases will still be combined, you'll still get the stats on the players from both databases displayed on the table.

4) I don't know why, you tell me. What is the relative speed difference (better/worse) for PAH tp look at multiple db's, one of which has the 440k players in it and one which contains an ever growing new list? Will this make PAH run quicker/slower/no different then at present? Please elaborate on why, I'm not a DB expert.

And you can still look at both databases in PT, it's not as if the data won't be available anymore.

5) well that's nice, but won't retrieving it be just as slow as it always was?

Starting a new DB and losing all the manual data is just not a solution.

6) I found a link to a PT3 thread where someone wrote a query to remove in PT3 all at once all the player data I want to remove. Can't you guys look at that query and modify it slightly to the PT2 structure? If so I'll galdly run it at the command line, but I wouln't understand the DB schema or SQL enough to trey and re-write it myself. Here's the link.

http://www.pokertracker.com/forums/viewtopic.php?f=16&t=15740&start=0

(NOTE: I think coming up with this ought to be EZ for one of you SQL guys and would GREATLY benefit every bleepin tourney player out there on PT2.)

PLz. some good answers one time? I want to play poker, not mess around with technical support......

[ptrack pat]

1/2/3 - this has never been an issue before because until recently FTP never supplied tournament summaries and after they started, you are the first person that has brought up any issues with the number of players. See the rest of my reply in #6.

4 - PAHUD shouldn't be so affected by the number of player names in the db.

5 - Even if you were able to purge 300,000 names, if the issue you are having it with retrieving the stats window then it's still going to be slow unless you apply some filters on the Preferences tab so it doesn't retrieve the entire database every time you open the window.

6 - I could look into writing a query for you that would delete the tourney_summary records for players in your db that had no hands played. You could run it on a regular basis to clear out the names. If you want to look into this, make a backup of your PostgreSQL database using the Utilities > PostgreSQL Database Functions window so I can test it thoroughly. Let me know the size of the .backup file and I can tell you how to send it to me.

[zubs1aa]

1/2/3 - this has never been an issue before because until recently FTP never supplied tournament summaries and after they started, you are the first person that has brought up any issues with the number of players. See the rest of my reply in #6.

Well how come there is a lengthy thread that I pointed out to you - actually i think i saw at least 3 threads on this - in the PT3 forums all dealing with this exact problem dating back several months? Maybe 99% of the serious players already moved to PT3?


4 - PAHUD shouldn't be so affected by the number of player names in the db.

I think you misunderstood my question. I was asking if having multiple databases needing to be addressed for data would slow things down- which is slightly different than just how many player names there are....

5 - Even if you were able to purge 300,000 names, if the issue you are having it with retrieving the stats window then it's still going to be slow unless you apply some filters on the Preferences tab so it doesn't retrieve the entire database every time you open the window.
Are you telling me it's not going to be faster getting 18k names vs 400k? cmon.....


6 - I could look into writing a query for you that would delete the tourney_summary records for players in your db that had no hands played. You could run it on a regular basis to clear out the names. If you want to look into this, make a backup of your PostgreSQL database using the Utilities > PostgreSQL Database Functions window so I can test it thoroughly. Let me know the size of the .backup file and I can tell you how to send it to me.

it's almost 500mb...You want me to send that to you how???? can't you just copy what was written for PT3 and adjust the elements to PT2 and then test on some deent sized db's laing around? The code is all in the referenced thread - the logic is all laid out.
Also, I guarantee I wouldn't be the only one that would find this useful if you let people know it was out there. Most people that it is affecting probably wouldnt even think to look into this nor look for a solution. Again, with the caveat that maybe the user base is migrating so fast over to PT3 that it's otherwise a moot point.

[ptrack pat]

1. I have no dealings with PT3 at all, I have turned over all rights to PT to the PAHUD guys and they have control of everything now, including PT3 which they developed. All I do is support PT2 in whatever time is remaining that it will be supported.

4. PAHUD is retrieving the stats for the players at your table. Unless you have tons and tons of hands for all these players then it shouldn't be an issue.

5. I'm telling you that no matter how many names there are, if you have that many hands FOR YOUR OWN PLAYER NAME, then opening the statistics window is going to be slow, no matter what. If you have 100,000 hands imported for yourself, nothing I do with deleting players will speed up the retrieval on the stats window which is why you would need to set a filter on the preferences tab so it only tries to retrieve a sub-set of the data when the window opens, like the last months worth of data. Then if you want to see all the data at some time, you can remove the filters and add them back once you are done looking at it.

6. I don't have any database that even comes close to the amount of tournament data you claim to have and looking at the SQL for PT3 is useless, the database structure isn't even remotely close to what PT2 is. It would be extremely helpful to have your database to test this with. I can give directions on FTPing me the backup file if you agree to it.