SQL query to get ITM% for hyper turbos HUSNG on Pokerstars

Discuss how to create custom stats, reports and HUD profiles and share your creations.

Moderators: WhiteRider, kraada, Flag_Hippo, morny, Moderators

SQL query to get ITM% for hyper turbos HUSNG on Pokerstars

Postby erdnase17 » Mon Jun 04, 2012 7:43 pm

I turned on postgres logging to show the executed SQL so I can figure how to calculate the ITM% for hyper turbos HUSNG on Pokerstars.
I am trying to tweak the query and this is what I got so far but it takes ages to return a result (I have around 30k tournaments in my DB).
Can someone please show me how to make this query?

Thanks.

Code: Select all
select avg("cnt_itm")
from
(SELECT
             ((tourney_summary.id_tourney)) AS "cnt_tourneys",
             (((CASE when(tourney_results.amt_won > 0) THEN 1 ELSE 0 END))) AS "cnt_itm"
            
      FROM tourney_blinds,
           tourney_table_type ,
           tourney_summary tsttt,
                           tourney_hand_summary,
                           tourney_hand_player_statistics ,
                           tourney_results,
                           tourney_summary
      WHERE (tourney_results.id_tourney = tourney_hand_player_statistics.id_tourney
             AND tourney_results.id_player = tourney_hand_player_statistics.id_player)
        AND (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney)
        AND (tourney_results.id_tourney = tourney_summary.id_tourney)
        AND (tourney_hand_player_statistics.id_player =
               (SELECT id_player
                FROM player
                WHERE player_name_search='my sn here'
                  AND id_site='100'))
        AND (tourney_hand_summary.id_hand = tourney_hand_player_statistics.id_hand)
        AND (tourney_blinds.id_blinds = tourney_hand_player_statistics.id_blinds)
        AND (tsttt.id_tourney = tourney_hand_player_statistics.id_tourney
             AND tsttt.id_table_type = tourney_table_type.id_table_type)
        AND ((((((((tourney_summary.id_table_type IN
                      (SELECT lttt.id_table_type
                       FROM tourney_table_type lttt
                       WHERE lttt.val_seats = 2))))))
               AND (((((tourney_hand_summary.id_gametype = 1))
                      AND ((tourney_blinds.flg_nl))))
                    AND ((((tourney_hand_summary.id_site IN (100))))))))
             AND ((tourney_hand_summary.id_gametype IN(1))
                  AND (tourney_table_type.val_speed = 'S')
                  ))
      ) as data
erdnase17
 
Posts: 227
Joined: Wed Feb 15, 2012 10:07 am

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby kraada » Tue Jun 05, 2012 8:06 am

The easier way to do this is to create a report in My Reports, have it group by Player and add the ITM stat to it. Then you can look at the SQL we use directly in our log file - click File --> Show User Data Folder. In the window that appears, open the PokerTracker4 text file. Your query will be near the bottom.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby erdnase17 » Tue Jun 05, 2012 8:17 am

kraada wrote:The easier way to do this is to create a report in My Reports, have it group by Player and add the ITM stat to it. Then you can look at the SQL we use directly in our log file - click File --> Show User Data Folder. In the window that appears, open the PokerTracker4 text file. Your query will be near the bottom.


This what I did and this query was obtained form the log file but it takes a long time. Also PT4 hangs for this reason when making that report.
erdnase17
 
Posts: 227
Joined: Wed Feb 15, 2012 10:07 am

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby kraada » Tue Jun 05, 2012 8:31 am

Download, import and try the attached report here. I just tested on a database with almost a thousand tournaments and it took about 1.5 seconds to complete.
Attachments
ITM Report.zip
(946 Bytes) Downloaded 97 times
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby erdnase17 » Tue Jun 05, 2012 8:43 am

kraada wrote:Download, import and try the attached report here. I just tested on a database with almost a thousand tournaments and it took about 1.5 seconds to complete.


Ok thanks. My database has 30k tournaments, I don't know if this is the reason the report does not work.
erdnase17
 
Posts: 227
Joined: Wed Feb 15, 2012 10:07 am

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby kraada » Tue Jun 05, 2012 9:01 am

Let me know how long that report takes to load for you.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby erdnase17 » Tue Jun 05, 2012 9:10 am

Running your report for all tournaments has not finished running yet.
I ran it for this month only (I played just a bit over 300 tournaments) it took more than the 1.5 seconds you reported for 1k tournaments.

It seems like some database problem? My computer is relatively decent i5-2500 CPU @ 3.30GHz, 8GB RAM running Windows 7 Ultimate.

These are the last 3 entries of pg_log:

Code: Select all
2012-06-05 14:05:00 BST LOG:  duration: 23072.000 ms  statement: SELECT id_player, id_site, str_player_name, count(distinct cnt_tourneys), sum(cnt_itm) FROM ( SELECT (tourney_hand_player_statistics.id_player) as "id_player", (player_real.id_site) as "id_site", (player.player_name) as "str_player_name", ((tourney_summary.id_tourney)) as "cnt_tourneys", (( (case when(tourney_results.amt_won > 0) then  1 else  0 end) )) as "cnt_itm" FROM  tourney_blinds,      tourney_table_type , tourney_summary tsttt, tourney_hand_summary, tourney_hand_player_statistics , player, tourney_summary, tourney_results, player player_real WHERE  (player.id_player = tourney_hand_player_statistics.id_player)  AND (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney)  AND (tourney_results.id_tourney = tourney_hand_player_statistics.id_tourney  AND tourney_results.id_player = tourney_hand_player_statistics.id_player)  AND (player_real.id_player = tourney_hand_player_statistics.id_player_real)  AND (player.id_player = tourney_results.id_player)  AND (tourney_results.id_tourney = tourney_summary.id_tourney)  AND (player_real.id_player = tourney_results.id_player_real)   AND (tourney_hand_player_statistics.id_player = (SELECT id_player FROM player WHERE player_name_search='erdnase1973'  AND id_site='100'))   AND (tourney_hand_summary.id_hand = tourney_hand_player_statistics.id_hand)       AND (tourney_blinds.id_blinds = tourney_hand_player_statistics.id_blinds)    AND (tsttt.id_tourney = tourney_hand_player_statistics.id_tourney  AND tsttt.id_table_type = tourney_table_type.id_table_type) AND ((((((((tourney_summary.id_table_type in (SELECT lttt.id_table_type from tourney_table_type lttt where lttt.val_seats = 2))))))AND (((((tourney_hand_summary.id_gametype = 1))AND ((tourney_blinds.flg_nl))))AND ((((tourney_hand_summary.id_site in (100))))))))AND ((tourney_hand_summary.id_gametype in(1))AND (tourney_table_type.val_speed = 'S')AND (tourney_summary.date_start >= (to_char(current_timestamp, 'YYYY-MM-01 00:00:00')::timestamp + INTERVAL '-1 HOURS') AND tourney_summary.date_start <= (to_char(current_timestamp + INTERVAL '1 MONTH', 'YYYY-MM-01 00:00:00')::timestamp - INTERVAL '1 SECOND' + INTERVAL '-1 HOURS'))))  GROUP BY tourney_results.amt_won, tourney_summary.id_tourney, (tourney_hand_player_statistics.id_player), (player_real.id_site), (player.player_name) ) AS tnySumGroup GROUP BY id_player, id_site, str_player_name
2012-06-05 14:05:15 BST LOG:  duration: 0.999 ms  statement: SELECT typrelid FROM pg_catalog.pg_type WHERE typname='tourney_cache';
2012-06-05 14:05:15 BST LOG:  duration: 0.999 ms  statement: SELECT pa.attname, pt.typname FROM pg_catalog.pg_attribute pa, pg_catalog.pg_type pt WHERE pa.attrelid=18721 AND pa.attstattarget <> 0 AND pa.atttypid=pt.oid ORDER BY pa.attnum;
erdnase17
 
Posts: 227
Joined: Wed Feb 15, 2012 10:07 am

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby kraada » Tue Jun 05, 2012 10:53 am

You've got extra filters on in that query for heads up and super turbo - if you turn those filters (in the sidebar in blue) off and uncheck all checkboxes for tournament type, flags and other such things, does it finish considerably faster for you?
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby erdnase17 » Tue Jun 05, 2012 11:22 am

Yes without the filters it is faster. However I am interested in the filters. Any workaround?
erdnase17
 
Posts: 227
Joined: Wed Feb 15, 2012 10:07 am

Re: SQL query to get ITM% for hyper turbos HUSNG on Pokersta

Postby kraada » Tue Jun 05, 2012 12:19 pm

I think it's the table joins that are causing the slowdown. I'll look into it but I don't know how much there is that we can do (other than waiting and letting the actual query finish).
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY


Return to Custom Stats, Reports and HUD Profiles

Who is online

Users browsing this forum: No registered users and 13 guests

cron