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
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 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
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.