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)
好了,本篇只是对LECCO SQL Expert软件进行简单应用实例说明.ok,这次就到这了.
