Timeline for For a query over multiple databases, is it faster to do GROUP BY first or UNION first?
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 31, 2014 at 13:32 | comment | added | pepr | I have asked the related question at stackoverflow.com/q/26675900/1346705 | |
| Oct 31, 2014 at 12:58 | comment | added | pepr | Thanks a lot for your help. I can accept only one answer, and PenutReaper was faster ;) | |
| Oct 30, 2014 at 12:59 | comment | added | rolfl | No, nothing is stored in memory by the CTE. Views (and thus the CTE) are code, not data. The code is included in the query that uses the view, and only the relevant parts of the code that are relevant to the query. There is no data in the CTE... there is just a way to locate the data. The data is 'materialized' when the code is put in to an actual query. | |
| Oct 30, 2014 at 12:47 | comment | added | pepr | I did read the doc you linked. Say, I have about 7000 rows in the unioned tables. With the CTE, are the rows stored in memory when the view with CTE is used? If the subexpression were used, would the code/memory structures behind the execution be similar or different in principle? (It may be worth of a separate question on StackOverflow if you suggest it.) | |
| Oct 30, 2014 at 12:05 | comment | added | rolfl |
I included the link to the CTE in my actual post. The performance of the view, and the memory it uses, is directly related to the query you run when you access the view. Views themselves take no memory, or space. The query you run when you access the view 'duplicates' the logic of the view (or whatever parts of the view are needed), and then goes with that. For example, select id from MyView will probably never do a min or sum, so will take less space/time than select *
|
|
| Oct 30, 2014 at 11:57 | comment | added | pepr | I am new to CTE. Using it, is the result obtained faster or does it consume less memory? I did read the doc; however, I do not understand the ideas behind. Can you point me to some "big picture" article that introduces CTE? | |
| Oct 30, 2014 at 11:16 | history | answered | rolfl | CC BY-SA 3.0 |