title: snowflake query performance tuning
body: <p>I have a snowflake query with multiple ctes and inserting into a table using a Talend job. It takes more than 90 minutes to execute the query. It is multiple cascading ctes, one is calling other and other is calling the other.</p>
  
  <p>I want to improve the performance of the query. It is like 1000 lines of code and I can't paste it here. As I checked the profile and it is showing all the window functions and aggregate functions which slows down the query.</p>
  
  <p>For example, the top slower is, </p>
  
  <pre><code>ROW_NUMBER​(​​)​ OVER ​(​PARTITION BY LOWER​(​S​.​SUBSCRIPTIONID​)​ 
                     ORDER BY S​.​ISROWCURRENT DESC NULLS FIRST, 
                        TO_NUMBER​(​S​.​STARTDATE​)​ DESC NULLS FIRST, 
                        IFF​(​S​.​ENDDATE IS NULL, '29991231', S​.​ENDDATE​)​ DESC NULLS FIRST​)​ 
  </code></pre>
  
  <p>takes 7.3% of the time. Can you suggest an alternative way to improve the performance of the query please?</p>