英文:
How to define expensive operation in sql
问题
"Is expensive a negative word so it should always be avoided?"
"昂贵是一个负面词汇，所以应该总是避免使用吗？"
英文:
I'm trying to apprehend the idea of expensive. Here's an example based on my understanding.If I want to find the id of all users aged above 18 select * from table where age > 18 select * is then expensive as I only wanted id.
Is expensive a negative word so it should always be avoided?
答案1
得分: 1
是的，expensive 和 cheap 经常用来衡量某个执行计划是否比另一个更好。我想这是因为引擎正在计算可能执行计划的“成本”并选择“成本较低”的计划。
例如，在 posgresql（但在其他关系型数据库中类似），我们有：
成本以任意单位表示。一个常见的误解是它们以毫秒或其他时间单位表示，但事实并非如此。
成本单位（默认情况下）以单个顺序页面读取为1.0单位（seq_page_cost）为基准。每处理的行会增加0.01（cpu_tuple_cost），而每个非顺序页面读取会增加4.0（random_page_cost）。
因此，根据您的操作符，引擎正在确定查询的成本，我们可以说最好避免昂贵的操作。 SQL性能调整可能包括将一些业务逻辑移到应用程序中，以避免繁重（速度不够快）的操作。
英文:
Yes, expensive and cheap are often use to measure if some execution plan is better then an other. I guess it is based on the fact that the engine is calculating the cost of possible execution plans and choosing the cheaper one.
For example, in posgresql (but similar in others RDMBs) we have:
> The costs are in an arbitrary unit. A common misunderstanding is that
> they are in milliseconds or some other unit of time, but that’s not
> the case.
>
> The cost units are anchored (by default) to a single sequential page
> read costing 1.0 units (seq_page_cost). Each row processed adds 0.01
> (cpu_tuple_cost), and each non-sequential page read adds 4.0
> (random_page_cost).
So, based on your operators, the engine is determining the cost of your query and we can say that is better to avoid expensive operations. Some of the SQL performance tuning may include moving some of the business logic in the application in order to avoid heavy (not fast enough) operations.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论