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.

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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.