Timeline for Are key lookups from non-clustered indexes always slower than a second query that does the lookup?
Current License: CC BY-SA 4.0
5 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 2, 2020 at 6:22 | comment | added | J.D. | But I have ran into a few cases so far where to reduce read locks and query time while ad-hoc reporting on the data, instead of letting the single query do a key lookup, it's a lot faster to break it into two queries as stated in my original post. | |
| Feb 2, 2020 at 6:22 | comment | added | J.D. | I don't disagree with index tuning when applicable, but I just started a new job where I don't have the authorization (yet) to make many index changes myself. Additionally it's difficult for us to make DDL changes because we have almost no maintenance window (the application can't go offline and can't really afford to be delayed) and the tables are pretty big (hundreds of millions to tens of billions of rows). Also these queries I'm turning are generally ad-hoc or low use that wouldn't really warrant structural changes to the database by themselves. | |
| Feb 2, 2020 at 2:36 | comment | added | Martin Cairney | Unless it is a 3rd party app where changing indexes can break support agreements, then if there is any case where changing an index can improve performance then I can't see why there would be any reason not to implement it. Really what you are describing above is a query tuning exercise anyway. | |
| Jan 30, 2020 at 22:52 | comment | added | J.D. | I know I can do that for index tuning, but in a lot of cases I'm not really able to make those changes. | |
| Jan 30, 2020 at 22:13 | history | answered | Martin Cairney | CC BY-SA 4.0 |