Does using CTE (Common Table Expression) create a type of "cache" in the database?

Asked

Viewed 85 times

4

I have an appointment with several JOIN and queries, and when executed, it takes about 7 seconds to return.

Getting the same result, using CTE’s, the query takes around 8 seconds of the first execution, but the next times (I couldn’t tell the interval, but it’s a few hours) the query is returned in less than 1 second.

Doubts

  • When I use CTE’s, the database creates a cache type?
  • What would be relevant, in the choice of use, between JOIN’s and SUBQUERIES’s or CTE’s?
sql database performance cte

  • Which sgbd was used?

  • @Jeffersonquesado Which I used in question, is Sqlserver 2008 R2, from an ERP system. There are several tables, several joins, cases, calculations, etc. What I did was practically separate steps by CTE.

  • He has a tool that allows to see the execution plan, but exactly now I can not send print because I’m on the mobile. There is also a change in the execution of the query that shows what was actually executed, making it possible to find bottlenecks and even suggesting indexes to improve the query

  • Whoa, cool it there... Then put it on for us!

1 answer

4


When I use CTE’s, the database creates a cache type?

No, these concepts are unrelated. The cache is created as required by a number of circumstances. Of course, CTE can take advantage of what’s in the cache, you can cache things and take advantage of them later, but that’s circumstantial and it occurs as a side effect, not because CTE depends on it.

You may have gotten a different result for several reasons, it may be because the query can be better processed, better planned with more details. But of course the cache may be influencing as well. They are too variable to state without knowing the concrete case in detail.

If you don’t know how to take the test you may be gathering false information.

What would be relevant, in the choice of use, between JOIN’s and SUBQUERIES’s or CTE’s?

The question doesn’t make much sense. It has to use what is most pertinent with what it needs and what gives best result in the specific case, and it is not restricted to these 3 forms, which are even antagonistic. In general it is possible to do anything without these 3 things, but it can be easier or more optimized to choose one of them in a certain scenario.

The question speaks in an example scenario, but it has no example, it has a result that is not even known how it was obtained.

  • There really is no example scenario, even took. I was actually going to, but it’s very large and extremely particular of a system, so it wouldn’t be useful. I believe that what must have affected most were the JOIN’s, which are many, and by CTE, end up getting more "light" by being executed in a "sequence", right!? But the "strange" is how with CTE reduced extremely significantly for a certain time. I think I would have to set up a test scenario to get to the bottom of it. Next time I’ll try to do.

  • 1

    This, it organizes better, gives more context. In fact, it is extremely complicated to test without giving false results.

Browser other questions tagged sql database performance cte

You are not signed in. Login or sign up in order to post.