LECCO SQL Expert工具之优化sql语句

程序开发,特别是web开发,对性能的要求比较高,在线列表信息要快又要占用减少服务器资源,在大众化的网络中可以已较快的速度读取数据,在程序方面优化,主要只书写代码质量,这里对代码优化不进行讨论,此篇主是针对数据库查询语句的优化.

编程开手或DBA通过自己的多年的经验可能重写性能低下的sql语句,但对于我这样菜鸟来说,写出高性能的sql有些困难.哎…,不用怕,我现在介绍个工具, LECCO SQL Expert,这个工具的下载及使用帮助让大家baidu下,就有一大堆.这里不提供下载链接了.

好了,开始我们今天的话题:

1.打开软件,登录后,出现以下界面.

找到我要进行测试的sql语句,如下:

SELECT dbo.合同申请表.ID,

dbo.合同申请表.ApplyUser,

dbo.UserTable.UserName,

dbo.合同申请表.ApplyType,

dbo.合同申请表.Name,

dbo.合同申请表.Object,

dbo.合同申请表.Intention,

dbo.合同申请表.Delivery,

dbo.合同申请表.Howmuch,

dbo.合同申请表.Payment,

dbo.合同申请表.Transit,

dbo.合同申请表.Others,

dbo.合同申请表.Memo,

dbo.合同申请表.Status,

dbo.合同状态表.title AS StatusTitle,

dbo.合同申请表.Attime,

dbo.UserTable.Phone,

dbo.合同申请表.Serial

FROM dbo.合同申请表,

dbo.UserTable,

dbo.合同状态表

WHERE dbo.合同申请表.status = @nStatus

and dbo.合同申请表.ApplyType = @cType

and dbo.合同申请表.WorkerID = @nOfficer

AND dbo.合同申请表.Status = dbo.合同状态表.id

AND dbo.合同申请表.ApplyUser = dbo.UserTable.ID

2.将其copy到LECCO SQL Expert 的sql编辑器中,如图:

3.点击工具栏的优化按钮,输入参数(sql语句用的是变量@nStatus, @cType, @nOfficer),如图:

4.输入相应的变量值,点击确定,进行优化语句查询:如图:

找到5个可选的执行计划.,批运行这5个sql,看运行时间最短的,其是最好的优化sql

5.批运行,并显示这5个运行时间

结果显示:sql4是最优的,其语句为:

SELECT dbo.合同申请表.ID,

dbo.合同申请表.ApplyUser,

dbo.UserTable.UserName,

dbo.合同申请表.ApplyType,

dbo.合同申请表.Name,

dbo.合同申请表.Object,

dbo.合同申请表.Intention,

dbo.合同申请表.Delivery,

dbo.合同申请表.Howmuch,

dbo.合同申请表.Payment,

dbo.合同申请表.Transit,

dbo.合同申请表.Others,

dbo.合同申请表.Memo,

dbo.合同申请表.Status,

dbo.合同状态表.title AS StatusTitle,

dbo.合同申请表.Attime,

dbo.UserTable.Phone,

dbo.合同申请表.Serial

FROM dbo.合同申请表

INNER JOIN dbo.合同状态表

ON dbo.合同申请表.Status = dbo.合同状态表.id

INNER JOIN dbo.UserTable

ON dbo.合同申请表.ApplyUser = dbo.UserTable.ID

WHERE dbo.合同申请表.status = @nStatus

and dbo.合同申请表.ApplyType = @cType

and dbo.合同申请表.WorkerID = @nOfficer

OPTION (FORCE ORDER)