威凡网全力打造:网页编程、软件开发编程、平面设计、服务器端开发、操作系统等在线学习平台!学编程,上威凡网!
PHP教程>> PHP基础 PHP技巧 PHP实例 PHP文摘 PHP模板 PHP总结
当前位置:首页 > PHP教程 > PHP总结
上一节 下一节
 OracleSQLtuning数据库优化步骤图文教程

sql turning 是quest公司出品的quest central软件中的一个工具。quest central是一款集成化、图形化、跨平台的数据库管理解决方案,可以同时管理 oracle、db2 和 sql server 数据库。 一、sql tuning for sql server简介 sql语句的优化对发挥数据库的最佳性

  sql turning 是quest公司出品的quest central软件中的一个工具。quest central是一款集成化、图形化、跨平台的数据库管理解决方案,可以同时管理 oracle、db2 和 sql server 数据库。

  一、sql tuning for sql server简介

  sql语句的优化对发挥数据库的最佳性能非常关键。然而不幸的是,应用优化通常由于时间和资源的因素而被忽略。sql tuning (sql优化)模块可以对比和评测特定应用中sql语句的运行性能,提出智能化的优化建议,帮助用户改善应用的响应时间。sql优化模块具有非介入式sql采集、自动优化和专家建议等功能,全面改善sql优化工作。

  二、sql tuning for sql server的使用

  1、打开quest database management solutions弹出窗口如图1所示

  图1

  2、在红色标记处打开sql tuning 优化sql

  (1)建立连接。

  在quest central主界面上的“database”树上选择“sql server”,然后在下方出现的“tools”框中选择“sql tuning”选项,打开“lanch sql tuning for sql server connections”对话框(图2、图3)。我们在这里建立数据库服务器的连接,,以后的分析工作都会在它上面完成。

  图2 “建立连接”对话框

  图3

  双击“new connection”图标,在弹出窗口中输入数据库的信息,单击“ok”,然后单击“connect”即可。

  (2)分析原始sql语句 ,在单击“connect”后将弹出一个新窗口,如图4

  图4

  在打开窗口的“oriangal sql”文本框内输入需要分析的原始sql语句,红色标记处选择对应的数据库名,sql语句代码如下:

  图5 分析原始sql语句

  原始sql语句

  然后点击工具栏上的“execute”按钮,执行原始的sql语句,sql tuning会自动分析sql的执行计划,并把分析结果显示到界面上(图5)。

  (3)优化sql。

  现在我们点击工具栏上的“optimize statement”按钮,让sql tuning开始优化sql,完成后,可以看到sql tuning产生了19条与原始sql等价的优化方案(图6)。

  图6 sql优化方案

  (4)获得最优sql。

  接下来,我们来执行上面产生的优化方案,以选出性能最佳的等效sql语句。在列表中选择需要执行的优化方案(默认已全部选中),然后点击工具栏上的“execute”按钮旁边的下拉菜单,选择“execute selected”。等到所有sql运行完成后,点击界面左方的“tuning resolution”按钮,

  可以看到最优的sql已经出来啦,运行时间竟然可以提高21%!(图7)

  图7 “tuning resolution”界面

  最优的sql语句如下:

  5)学习书写专家级的sql语句 。

  优化后的sql语句

  select dbo.person_basicinfo.*,

  dbo.graduater_graduaterregist.registno as registno,

  dbo.graduater_graduaterregist.registtime as baodaotime,

  dbo.graduater_graduaterregist.registman as registman,

  dbo.graduater_business.comefrom as comefrom,

  dbo.graduater_business.code as code,

  dbo.graduater_business.status as status,

  dbo.graduater_business.approveresult as approveresult,

  dbo.graduater_business.newcorp as newcorp,

  dbo.graduater_business.commendnumber as commendnumber,

  dbo.graduater_business.employstatus as employstatus,

  dbo.graduater_business.newcommendtime as newcommendtime,

  dbo.graduater_business.getsource as getsource,

  dbo.graduater_business.employtime as employtime,

  dbo.graduater_business.job as job,

  dbo.graduater_business.fillman as fillman,

  dbo.graduater_business.filltime as filltime,

  dbo.graduater_business.iscommendok as iscommendok,

  dbo.graduater_business.approveuser as approveuser,

  dbo.graduater_business.approvetime as approvetime,

  dbo.graduater_business.registtime as registtime,

  dbo.graduater_business.employcorp as employcorp,

  dbo.graduater_business.jobremark as jobremark,

  case when dbo.graduater_business.comefrom = 'ws' then '网上登记'

  when dbo.graduater_business.comefrom = 'hp' then '华普大厦'

  when dbo.graduater_business.comefrom = 'jd' then '精典大厦'

  when dbo.graduater_business.comefrom = 'mc' then '赛马场'

  when comefrom = 'zx' then '高指中心' end as comefromname,

  dbo.person_contact.address as address,

  dbo.person_contact.zip as zip,

  dbo.person_contact.telephone as telephone,

  dbo.person_contact.mobile as mobile,

  dbo.person_contact.email as email,

  dbo.person_contact.im as im,

  dbo.person_skill.foreignlanguage as foreignlanguage,

  dbo.person_skill.foreignlanguagelevel as foreignlanguagelevel,

  dbo.person_skill.cantoneselevel as cantoneselevel,

  dbo.person_skill.mandarinlevel as mandarinlevel,

  dbo.person_skill.language as language,

  dbo.person_skill.technicaltitle as technicaltitle,

  dbo.person_skill.computerlevel as computerlevel,

  dbo.person_employpurpose.jobtype as jobtype,

  dbo.person_employpurpose.vocation as vocation,

  dbo.person_employpurpose.jobplace as jobplace,

  dbo.person_employpurpose.salary as salary,

  dbo.person_employpurpose.onjobdate as onjobdate,

  dbo.person_employpurpose.corptype as corptype,

  dbo.person_employpurpose.job as requirejob,

  year(getdate()) - year(dbo.person_basicinfo.birthday) as age,

  dbo.graduater_business.employtype as employtype,

  dbo.graduater_business.employtypecode as employtypecode,

  dbo.graduater_business.employcorptype as employcorptype,

  case when dbo.graduater_business.printstatus = '已打印' then '已打印'

  else '未打印' end as printstatus,

  dbo.graduater_business.printtime as printtime,

  case when dbo.graduater_business.employstatus = '是' then '已就业'

  else '未就业' end as employstatusview

  from dbo.person_basicinfo

  inner join dbo.graduater_business

  on dbo.person_basicinfo.personid = dbo.graduater_business.personid

  left outer join dbo.graduater_graduaterregist

  on dbo.graduater_business.gradbusinessid = dbo.graduater_graduaterregist.graduaterguid

  inner join dbo.person_contact

  on dbo.person_basicinfo.personid = dbo.person_contact.personid

  inner join dbo.person_skill

  on dbo.person_basicinfo.personid = dbo.person_skill.personid

  inner join dbo.person_employpurpose

  on dbo.person_basicinfo.personid = dbo.person_employpurpose.personid

  option (force order)

  


申明:本教程内容由威凡网编辑整理并提供IT程序员分享学习,如文中有侵权行为,请与站长联系(QQ:254677821)!
上一节 下一节
相关教程  
其他教程  
PHP基础
PHP技巧
PHP实例
PHP文摘
PHP模板
PHP总结

违法和不良信息举报中心】邮箱:254677821@qq.com
Copyright©威凡网 版权所有 苏ICP备2023020142号
站长QQ:254677821