10 ms·

Not only is it sometimes faster, and sometimes slower, but the answer can change, for the same schema, for the same query, depending on the volume and distribut

by jpitz 10y ago

Not only is it sometimes faster, and sometimes slower, but the answer can change, for the same schema, for the same query, depending on the volume and distribution of data.

nine_k 10y ago
Can you please provide an example of answer changing?
- jpitz 10y ago
  Offhand, I don't remember the specifics - I observed this a decade ago. Sorry. I don't have a SQL Server around to try to craft it either. The gist of it is, the query planner for some database engines ( pg and mssql ) often takes cardinality into account when selecting plans. Therefore those plans change with the size of the dataset. EDIT: It occurs to me to wonder if you're asking if the resultset changed. No, I've not seen that answer change.
- matwood 10y ago
  It's been while since I have spent time with MSSQL, but I remember seeing this with sprocs and cardinality. The sproc would compile with 1 plan that dealt with tables with little data. The plan would not always get updated as the tables fill with data. The underlying reason is that as data changes the plan changes. For example, on a small table, it is faster/more efficient to do a table scan than go to the index and then to the table for data. At some threshold this changes. If table size can change the plan, then the query itself could also need changing as the table grows, indexes grow, and differing index cardinality emerges.
- swasheck 10y ago
  Yes. Every day. The developers here write SQL that runs acceptably for a given schema at a given point in time. Unfortunately, the code pattern isn't scalable (which is generally why people say that RDBMS' can't "scale" anyway, but that's a different rant) and performance changes, sometimes dramatically. Where a LEFT OUT JOIN ... WHERE outer.value is NULL worked great for an anti-semijoin earlier, NOT EXISTS may be better. What jpitz is talking about are statistics that are tracked by the engine. They are a key part of how SQL Server's CBO determines the best plan to access the data. As data grows, chances are you're going to get skew on the keys that you've defined. If your data is such that the counts and distinct counts of your keys are guaranteed to remain consistent, then you have a lower chance of plan changes as data scales.

by jpitz 10y ago

Not only is it sometimes faster, and sometimes slower, but the answer can change, for the same schema, for the same query, depending on the volume and distribution of data.

nine_k 10y ago

Can you please provide an example of answer changing?

jpitz 10y ago
Offhand, I don't remember the specifics - I observed this a decade ago. Sorry. I don't have a SQL Server around to try to craft it either. The gist of it is, the query planner for some database engines ( pg and mssql ) often takes cardinality into account when selecting plans. Therefore those plans change with the size of the dataset. EDIT: It occurs to me to wonder if you're asking if the resultset changed. No, I've not seen that answer change.
matwood 10y ago
It's been while since I have spent time with MSSQL, but I remember seeing this with sprocs and cardinality. The sproc would compile with 1 plan that dealt with tables with little data. The plan would not always get updated as the tables fill with data. The underlying reason is that as data changes the plan changes. For example, on a small table, it is faster/more efficient to do a table scan than go to the index and then to the table for data. At some threshold this changes. If table size can change the plan, then the query itself could also need changing as the table grows, indexes grow, and differing index cardinality emerges.
swasheck 10y ago
Yes. Every day. The developers here write SQL that runs acceptably for a given schema at a given point in time. Unfortunately, the code pattern isn't scalable (which is generally why people say that RDBMS' can't "scale" anyway, but that's a different rant) and performance changes, sometimes dramatically. Where a LEFT OUT JOIN ... WHERE outer.value is NULL worked great for an anti-semijoin earlier, NOT EXISTS may be better. What jpitz is talking about are statistics that are tracked by the engine. They are a key part of how SQL Server's CBO determines the best plan to access the data. As data grows, chances are you're going to get skew on the keys that you've defined. If your data is such that the counts and distinct counts of your keys are guaranteed to remain consistent, then you have a lower chance of plan changes as data scales.