SQL code choice in TI process
Posted: Fri Mar 24, 2017 5:57 pm
This post is more for curiosity than anything else. It has been a long week!
I have created a TI process whose data source is ODBC. The process itself does a CellIncrementN to a cube. The process actually works fine and takes around 4 minutes to run.
When writing the SQL code I had 2 options:
1. Exclude the Group by clause and return 54,000 rows
2. Include the Group by clause and return 5,000 rows (I have kept with the CellIncrementN method even though I don't need it)
For method 1 the SQL code compiled in seconds but obviously there are 54,000 rows for the process to run over. For method 2 the SQL codes took a few minutes to compile but there are only 5,000 rows to run over.
When testing each method, the first was slightly quicker, maybe by 30 seconds. On balance I decided to go with method 2, because as the data grows I estimate this will turn out to be the most efficient in the end.
My basic query is, has anyone else faced this choice and is there anything I need to consider other than the time it takes?
I have created a TI process whose data source is ODBC. The process itself does a CellIncrementN to a cube. The process actually works fine and takes around 4 minutes to run.
When writing the SQL code I had 2 options:
1. Exclude the Group by clause and return 54,000 rows
2. Include the Group by clause and return 5,000 rows (I have kept with the CellIncrementN method even though I don't need it)
For method 1 the SQL code compiled in seconds but obviously there are 54,000 rows for the process to run over. For method 2 the SQL codes took a few minutes to compile but there are only 5,000 rows to run over.
When testing each method, the first was slightly quicker, maybe by 30 seconds. On balance I decided to go with method 2, because as the data grows I estimate this will turn out to be the most efficient in the end.
My basic query is, has anyone else faced this choice and is there anything I need to consider other than the time it takes?