sql 视图效率和连接超时设置_数据库技巧

在使用视图的过程中碰到了很多问题，其实试图并不能真正提高效率，只是更加方便的给用户提供了操作，使用户有了更加条理的思路，使用视图时能用连接就用连接，而子查询效率明显差很远。

以下是我的对比:

CREATE VIEW DCLSVIEW AS SELECT ksjbxx.zkzh, ksjbxx.xm, ksjbxx.sfzh, ksjbxx.xb, ksjbxx.csny,ksjbxx.szxx, isnull((SELECT sfbhb.mc   FROM sfbhb  WHERE sfbhb.bh = ksjbxx.szsf),  ) AS sfmc,isnull((SELECT mc FROM kdxx WHERE kdxx.bh = ksjbxx.ksdd), ) AS ksdd, ksjbxx.ksh,isnull((SELECT kslbbhb.mc  FROM kslbbhb  WHERE kslbbhb.bh = ksjbxx.kslb), ) AS kslb,isnull((SELECT zyfxbhb.mc  FROM zyfxbhb  WHERE zyfxbhb.bh = ksjbxx.zyfx), ) AS zyfx,ksjbxx.txdz,ksjbxx.cf AS cf , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =001 ),0) as zyf001 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =002 ),0) as zyf002 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =003 ),0) as zyf003 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =004 ),0) as zyf004 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =005 ),0) as zyf005 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =006 ),0) as zyf006 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =007 ),0) as zyf007 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =008 ),0) as zyf008 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =009 ),0) as zyf009 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =010 ),0) as zyf010 , isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =01 ),0) as whf01, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =02 ),0) as whf02, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =03 ),0) as whf03, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =04 ),0) as whf04, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =05 ),0) as whf05, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =06 ),0) as whf06, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =07 ),0) as whf07, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =08 ),0) as whf08, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =09 ),0) as whf09 , isnull((select jzzf from zyfzfb where zkzh =ksjbxx.zkzh),0) as zyfjzzf,isnull((select place from zyfzfb where zkzh =ksjbxx.zkzh),0) as place ,isnull((select sfplace from zyfzfb where zkzh =ksjbxx.zkzh),0) as sfplace ,isnull((select zyzysx from syb where syb.zkzh = ksjbxx.zkzh and syb.zyzysx = 1),) as zysx1 ,isnull(( select mc from whklbbhb where whklbbhb.bh in (select whkslb from whfs where whfs.zkzh = ksjbxx.zkzh)),) as whkslb,isnull((select whfzf from whfs where whfs.zkzh = ksjbxx.zkzh ),) as whfzf ,isnull((select whfzfdl from whfs where whfs.zkzh = ksjbxx.zkzh),) as whfzfdl , isnull((select sfgsk from whfs where whfs.zkzh = ksjbxx.zkzh),) as sfgsk  ,isnull((select case lqzt when 1 then 是 when 0 then 否 else null end  from ylqxx where ylqxx.zkzh = ksjbxx.zkzh ),) as ylqzt  ,isnull((select case sfzzlq when 1 then 是 when 0 then 否 else null end  from zzlqxx where zzlqxx.zkzh = ksjbxx.zkzh ),) as zzlqzt  from ksjbxx

以上试图的查询可能会用到三分多钟，

如果用下面师徒的创建过程，可能你只需要十秒不到的时间

SELECT dbo.ksjbxx.zkzh, dbo.ksjbxx.xm, dbo.ksjbxx.sfzh, dbo.ksjbxx.xb, dbo.ksjbxx.csny,
      dbo.ksjbxx.szxx, dbo.sfbhb.mc AS sfmc, dbo.kdxx.mc AS ksdd, dbo.ksjbxx.ksh,
      dbo.kslbbhb.mc AS kslb, dbo.zyfxbhb.mc AS zyfx, dbo.ksjbxx.txdz, dbo.ksjbxx.cf,
      dbo.zyfzfb.zyfzf, dbo.zyfzfb.jzzf AS zyfjzzf, dbo.zyfzfb.place, dbo.zyfzfb.sfplace,
      dbo.whklbbhb.mc AS whkslb, dbo.whfs.whfzf, dbo.whfs.whfzfdl, dbo.whfs.sfgsk,
      dbo.ylqxx.lqzt AS ylqzt, zyfxbhb_1.mc AS ylqzy, dbo.zzlqxx.sfzzlq AS zzlqzt,
      zyfxbhb_2.mc AS zzlqzy
FROM dbo.ksjbxx LEFT OUTER JOIN
      dbo.sfbhb ON dbo.ksjbxx.szsf = dbo.sfbhb.bh LEFT OUTER JOIN
      dbo.kslbbhb ON dbo.ksjbxx.kslb = dbo.kslbbhb.bh LEFT OUTER JOIN
      dbo.zyfxbhb ON dbo.ksjbxx.zyfx = dbo.zyfxbhb.bh LEFT OUTER JOIN
      dbo.kdxx ON dbo.ksjbxx.ksdd = dbo.kdxx.bh LEFT OUTER JOIN
      dbo.zyfzfb ON dbo.ksjbxx.zkzh = dbo.zyfzfb.zkzh LEFT OUTER JOIN
      dbo.whfs ON dbo.ksjbxx.zkzh = dbo.whfs.zkzh LEFT OUTER JOIN
      dbo.whklbbhb ON dbo.ksjbxx.wllb = dbo.whklbbhb.bh LEFT OUTER JOIN
      dbo.ylqxx ON dbo.ksjbxx.zkzh = dbo.ylqxx.zkzh LEFT OUTER JOIN
      dbo.zyfxbhb zyfxbhb_1 ON dbo.ylqxx.lqzy = zyfxbhb_1.bh LEFT OUTER JOIN
      dbo.zzlqxx ON dbo.ksjbxx.zkzh = dbo.zzlqxx.zkzh LEFT OUTER JOIN
      dbo.zyfxbhb zyfxbhb_2 ON dbo.zzlqxx.zyfx = zyfxbhb_2.bh

还有如果在操作时出现了：

你有两步走：

企业管理器–>数据库–>属性,在属性里边有数据文件和事务日志,这两个文件有增长规则,按照%增长和按照字节增长,一般默认是按照10%增长.如果数据库过大,按照10%增长,这样就会造成数据长时间无法响应.同时在任务管理器中也是查不到SQL无响应,所以造成连接超时,建议将这个调小.控制在5%以内(较佳)

同时将数据库连接时间设置调大些, 企业管理器–>工具–>SQL Server 配置属性,选择连接选项,修改连接时间.

将这两个修改后，应该没问题了………………