SQL 查询 - 性能优化 [英] SQL Query - Performance Optimization

问题描述

我不太擅长 SQL，所以在编写查询时请大家帮忙.
SQL 查询 - 表连接问题
我得到了答案，它有效！它只是明显缓慢.我讨厌这样做，但我真的希望有人想推荐一些优化查询的方法.我什至没有自己尝试过，因为我对 SQL 的了解不够，甚至无法开始使用谷歌搜索.

Im not so good at SQL, so I asked you guys for help on writing a query.
SQL Query - Table Joining Problems
I got an answer and it works! Its just noticeably slow. I hate to do it but Im really hoping someone out there wants to recommend some ways to optimize the query. I have not even attempted this on my own because I dont know enough about SQL to even begin googling.

推荐答案

在要加入的列上创建索引可能会有所帮助.例如；

What might help is to create indexes on the columns you're joining with. For example;

CREATE INDEX name_for_index ON Main (StatusID);

它为此列生成一个查找表，执行查询的算法将使用该查找表.

It generates a look-up table for this column that the algoritm that performs the query will use.

如果不允许您更改数据库，您可能会不走运.我见过在 JOIN 语句上放宽提高性能的情况，就是这样；

If you're not allowed to change the database, you may be out of luck. I have seen cases where easing on the JOIN statements improved the performance, that would be this;

...
FROM
  Main m, Status st, Secondary s
WHERE
  st.ID = m.StatusID
  AND s.MainID = m.ID
  AND 
  ( s.MainID IS NULL AND m.WhenDate = <YourDate>
    OR
    s.MainID IS NOT NULL AND s.WhenDate = <YourDate> )
  AND TypeId = <TypeFilter>
  AND ... other filters, if you need any ...

然后处理您的其他情况，其中 INNER JOIN 需要更详细一些.

And then handling your other case, where the INNER JOIN is needed a bit more verbose.

这篇关于SQL 查询 - 性能优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！