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屋！