Re: SQL View vs CTE

<Greg>
I have a complex SQL statement that I'm going to use as a data source for a browser application. In ACS Run SQL scripts it consistently takes about 22 seconds to run. The statement contains a CTE followed by a join to the CTE. The files involved have a large number of records.

So I'm wondering if someone can tell me whether creating an SQL View in place of the CTE would yield any performance increase (I haven't spent much time looking at the Visual Explain in ACS).
I'm thinking it wouldn't make much difference.
</Greg>

Greg,
in theorie runtime shouldn't be dependent on the way to define a Resultset. In other words: two SQL statements, describing the same resultset should have the same runtime and ressource consumption. In real world, the query engine doesn't calculate the optimization problem to ist end. optimization might end by timeout or other restrictions (the optimizer might not look into UDTFs or other functions) or when the access plan seems to be good enough.

Views are part of the SQL standard to decouple applications from the physical layer (tables, indexes, functions, stored procedures etc.). In this perspective the view variant is better than the CTE variant.

I will try to elaborate on this by example:
22 seconds could be sufficient today, maybe in a few weeks (or some months) max. 1 second is the requirement. One way to solve this problem might be denormalisation (adding a calculated column to a table), this would be unvisable to the application in most cases and all views and programms using this column would have huge benefit.

Another aspect is:
22 seconds is a very bad runtime and seldom sufficient for interactive like applications. At the first look it seems, that an appropriate index could help. In batch like szenarios joining temporary substracts outperform the one statement variants.

D*B