none
Setting Timeout Value in Powerquery RRS feed

  • Question

  • I have a rather complex query that depending on options, can run two hours. It's wading through a lot of data and an hour is not unreasonable. When I run through PGAdmin ( it's postgresql) I get what I need but when it's embedded in PQ it times out long before done. I see a timeout value but I cant figure out where to set it.

    This is the query ( top half and the actual query removed)

    // GetData
    let

    // get the parameters and replace in the string 

         mySchema=GetUserParm("ActiveSchema"),
          TheDB = GetUserParm("ActiveHost"),
          TheRule = GetUserParm("ActiveRule"),
          RowCount = GetUserParm("RowReturn"),
          TheQuery = "SELECT DISTINCT very large Query here which I took out"  
          temp1SQL = Text.Replace(TheQuery, "[ActiveRule]", TheRule),
          temp2SQL = Text.Replace(temp1SQL, "[ActiveSchema]", mySchema),
          MySQL = Text.Replace(temp2SQL, "[RowReturn]", RowCount),  
          Result = PostgreSQL.Database(TheDB, "postgres", [Query=MySQL]),

    Thoughts ?

    Tuesday, July 19, 2016 8:54 PM

Answers

  • Based on what I see in the docs, this should be possible. In the Result step, where you have the [Query=MySQL], you would change it to something like:

    [Query=MySQL, CommandTimeout=#duration(0,3,0,0)]

    This should set the timeout to three hours. Let me know if that works.

    Ehren


    Tuesday, July 19, 2016 11:46 PM
    Owner
  • Thank you ! Your original response was correct, except that I needed (0,3,0,0) not (0,0,3,0) . Now I have a new problem with "reading past the end of the stream" but at least I am not timing out !

    Thanks again !

    Thursday, July 21, 2016 8:11 PM

All replies

  • Based on what I see in the docs, this should be possible. In the Result step, where you have the [Query=MySQL], you would change it to something like:

    [Query=MySQL, CommandTimeout=#duration(0,3,0,0)]

    This should set the timeout to three hours. Let me know if that works.

    Ehren


    Tuesday, July 19, 2016 11:46 PM
    Owner
  • It did not work, BUT i am not sure the issue is in the command timeout. It may be in the connection timeout as well. I notice that when I connect with PGADMIN, it takes a really long time to make the initial connection. I am wondering if I am chasing two interrelated issues. Is there a connection Timeout value? the documentation seems to show one but I'm not seeing where I can pass that in the connection string. 

    Thanks
    Thursday, July 21, 2016 1:52 PM
  • Thank you ! Your original response was correct, except that I needed (0,3,0,0) not (0,0,3,0) . Now I have a new problem with "reading past the end of the stream" but at least I am not timing out !

    Thanks again !

    Thursday, July 21, 2016 8:11 PM
  • Glad it worked! I've corrected my answer above.

    Ehren

    Thursday, July 21, 2016 8:38 PM
    Owner