SQL 视图效率和连接超时设置
来源:互联网 发布:java aes文件加密 编辑:程序博客网 时间:2024/06/05 03:00
在使用视图的过程中碰到了很多问题,其实试图并不能真正提高效率,只是更加方便的给用户提供了操作,使用户有了更加条理的思路,使用视图时能用连接就用连接,而子查询效率明显差很远。
以下是我的对比:
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 配置属性,选择连接选项,修改连接时间.
将这两个修改后,应该没问题了..................<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
- SQL 视图效率和连接超时设置
- SQL 视图效率和连接超时设置
- Socket设置连接超时
- 设置socket超时连接
- Oracle 连接超时设置
- IIS设置连接超时
- 设置socket超时连接
- MQ 连接超时设置
- TCP超时连接设置
- 设置ssh连接超时
- 3389连接超时设置
- AndroidHttpTransport 设置连接超时
- 设置mysql_real_connect连接超时
- sqlserver 视图和sql语句的效率对比
- SQL视图和多表连接
- Android在Http连接中设置超时和代理
- Android在Http连接中设置超时和代理
- Android在Http连接中设置超时和代理?
- 一些SQL Server的应用实例
- SQLSERVER中,数据库同步的实现
- 深入理解SQL Server 中的错误处理
- 三种SQL分页法效率分析
- sql server 2005中的except和INTERSECT运算
- SQL 视图效率和连接超时设置
- sql server的保留关键字
- 如何实现SQL Server 2005快速web分页
- SQL2000 关于 Java JDBC 驱动的安装和设定
- SQL Server2005数据库查询中使用CTE
- 安装SQL Server 2005实例环境图解
- 如何使用SQL Server数据库查询累计值
- SQL Server查询处理器机制与结构(下)
- Sql Sever数据库自动备份