效率低下的SQL查询 [英] Inefficient SQL query

问题描述

我有3张桌子，我加入了，由于某种原因，我的查询非常慢，需要30秒 - 1分钟才能执行。



我没有经验在编写SQL时，除了编写简单的SELECT语句或调用DB开发人员编写的storedProc之外。

 // trdopt rows> 100,000 
 // instropt rows> 100,000 
 // exchopt rows = 10; 
 
 SELECT trddata.date，instr.sym，instr.under，instr.exp，exch.name，trddata.prc 
 FROM trdopt trddata 
 JOIN instropt instr 
 ON trddata.optid = instr.id 
 JOIN exchopt exch 
 ON trddata.exchcode = exch.id 
 WHERE DATE_FORMAT（trddata.date，''％Y-％m-％d％T ''）='''2013-04-02''; 

有人可以帮助我并展示/解释问题所在是和实现此查询的正确方法。



提前致谢，

-DA

解决方案

可能缺少索引，这总是第一个检查的地方。



打开实际执行计划（icon在SSMS工具栏中）并运行查询。完成后，您将看到一个使用执行计划的选项卡。您可能有一个漂亮的小绿色部分建议额外的索引，将其复制/粘贴到新的查询窗口并创建索引。重新运行您的查询。



查询优化是一种ART而非科学，它需要经验，您最好的选择是开始阅读如何使用执行计划。

I have 3 tables that I''ve joined and for some reason my query is VERY slow, takes 30secs - 1min to execute.

I have no experience at writing SQL other than writing simple SELECT statements or calling a storedProc that a DB developer wrote.

//trdopt rows > 100,000
//instropt rows > 100,000
//exchopt rows = 10;

SELECT trddata.date, instr.sym, instr.under, instr.exp, exch.name, trddata.prc
FROM   trdopt trddata
JOIN   instropt instr
ON     trddata.optid = instr.id
JOIN   exchopt exch
ON     trddata.exchcode = exch.id
WHERE DATE_FORMAT(trddata.date, ''%Y-%m-%d %T'') = ''2013-04-02'';

Can someone please help me out and show/explain what the problem is and the correct way to implement this query.

Thanks in advance,
-DA

解决方案

It is likely to be a lack of indexes, this is always the first place to check.

Turn on the Actual Execution plan (icon in SSMS toolbar) and run the query. When it is completed you will have a tab with the execution plan used. You MAY have a nice little green section recommending additional indexing, copy/paste this to a new query window and create the indexes. Rerun your query.

Query optimisation is an ART not a science, it takes experience, your best bet would be to start reading up on how to use the Execution plan.

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