Forum Discussion

robarivas's avatar
robarivas
Post Patron
9 years ago

Large Table performance

I'm connecting to an IBM DB2 database (z/OS) via ODBC (cause I could never get the built-in connector to work). Most tables are viable. However, I cannot find a way to get the transactions table data...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi robarivas,

 

Get rid of any columns that have unique ID's and if you have a column that is Date & Time, split that into two columns in Power Query (or just drop time altogether).

 

Do any of those help?

  • robarivas's avatar
    robarivas
    Post Patron
    9 years ago

    Hi Phil_Seamark

     

    I don't have those kinds of columns there because one of my first steps is to remove unneeded columns. The first step in which I perform a filter seems to cause query folding to stop happening.

    • yan's avatar
      yan
      Advocate I
      9 years ago

      Same issue here on a simple group by - no query folding so PD/PQ loads it all (20M rcds) before aggregating...

      I use Teradata through ODBC because it is the only option (still) to authenticate through LDAP.

       

      I bet it is due to the use of ODBC: PB/PQ doesn't know what DBMS it needs to translate the SQL too.

       

      (De)selecting columns though does translate to native SQL, probably because it is pretty standard. Anything invoking a WHERE or GROUP BY is not.

       

      The alternative is to copy/write your own "hardcoded" custom SQL in the data source step. Not ideal if you want business users to share a baseline and create their own steps (unless folding after that is not a must).

      • robarivas's avatar
        robarivas
        Post Patron
        9 years ago

        Finally got a "proper" connection to the DB2 database...no more ODBC. I'm using the Microsoft driver. However, still no Query Folding!!! :smileyfrustrated:

         

        I wonder why. Anyone have a theory? Again, all I'm doing is a simple dates between filter on a date column (as my test of Query Folding).

    • robarivas's avatar
      robarivas
      Post Patron
      9 years ago

      Why would Power Query stop Query Folding upon the simple act of filtering a column?

       

      let
           Source = Odbc.DataSource("dsn=ABCD", [HierarchicalNavigation=true]),
           ABC_Schema = Source{[Name="ABC",Kind="Schema"]}[Data],
           ABC1234_View = ABC_Schema{[Name="ABC1234",Kind="View"]}[Data],
           #"Removed Other Columns" = Table.SelectColumns(ABC1234_View,{"TX_DATE_POST", "ORDER_ID", "CORP_ID", "ORDER_SITE", "PRODUCT_ID", "ORDER_TYPE", "TX_ID", "TX_AMOUNT", "TX_QTY"}),
           #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [CORP_ID] = "54"),

       

      I'm querying an IBM DB2 (z/OS) database via ODBC (could never get the built-in connector to work).  The table (or view) I'm querying is an extremely large transactions table (nearly 900 million rows), I just want to pull a subset but cannot because the filtering takes forever. I know folding is not working because when I right click on the filtering step the "View Native Query" option is dithered out. The steps prior, however, are folding because on those steps the "View Native Query" option is selectable.