Timeline for Alternatives to running query for rarely changed data everytime on large table
Current License: CC BY-SA 3.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 12, 2018 at 0:43 | comment | added | Vladimir Baranov | @jpmc26, an index object has the same number of rows as the main table, 10M in this case. An indexed view object has 80 rows in this case - the number of rows that is returned by the view query. It is faster to read 80 rows than 10M rows and takes less disk space. | |
| Apr 11, 2018 at 23:18 | comment | added | jpmc26 | What advantage over just indexing the column does this have? | |
| Apr 6, 2018 at 13:57 | comment | added | Vladimir Baranov |
@ZacFaragher, you are right and one should use the NOEXPAND hint to get the predictable behaviour. The intro article that I linked in the answer has this caution: "You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint."
|
|
| Apr 6, 2018 at 7:16 | vote | accept | JaggenSWE | ||
| Apr 6, 2018 at 5:52 | comment | added | Zac Faragher | That's an excellent suggestion, however, SQL Server may still decide not to use it. See Paul White's excellent answer here: dba.stackexchange.com/a/197968/116838 | |
| Apr 6, 2018 at 3:44 | history | answered | Vladimir Baranov | CC BY-SA 3.0 |