SQL Server 跨库同步数据

最近有个需求是要跨库进行数据同步,两个数据库分布在两台物理计算机上,自动定期同步可以通过SQL Server代理作业来实现,但是前提是需要编写一个存储过程来实现同步逻辑处理。这里的存储过程用的不是opendatasource,而是用的链接服务器来实现的。存储过程创建在IP1:192.168.0.3服务器上,需要将视图v_custom的客户信息同步到IP2:192.168.0.10服务器上的t_custom表中。逻辑是如果不存在则插入,存在则更新字段。

如果没有正确配置,经常会出现 消息 7391,级别 16,状态 2,过程 xxxxx,第 XX 行 。无法执行该操作,因为链接服务器 "xxxxx" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务。   可以参照如下的配置: 具体可以参看:http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html   from:https://www.cnblogs.com/isaboy/p/sql_server_job.html

PowerDesigner中NAME和COMMENT的互相转换,需要执行语句

使用说明: 在【Tools】-【Execute Commands】-【Edit/Run Script】 下。输入下面你要选择的语句即可,也可以保存起来,以便下次使用,后缀为.vbs。 需要注意的问题是:运行语句时必须在Module模式下,如果是导出报表时执行会出现错误提示。 1.Name转到Comment注释字段。一般情况下只填写NAME,COMMENT可以运行语句自动生成。 将该语句保存为name2comment.vbs

2.将Comment内容保存到NAME中,comment2name.vbs 实习互换。语句为:

from:https://www.cnblogs.com/netsql/archive/2010/05/24/1742734.html

PowerDesigner从Sqlserver中反转为带注释的字典及快捷键操作

PowerDesigner的操作经常忘记,所以把常用的功能记录下来备忘。 1、修改反转过来的字段 PowerDesigner从数据库反转的时候,默认不带注释,需要先进行修改。 输入如下脚本: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 {OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, COMMENT, ExtIdentityIncrement, ExtIdentitySeed} select u.name, o.name, c.colid, c.name, case when (s.usertype < 100) then s.name else x.name end, c.prec, c.length, c.scale, z.text , case (c.status & 8) when 8 then 'NULL' else 'NOTNULL' end, case (c.status & 128) when 128 then 'identity' else " end, case when (s.usertype < 100) then " else s.name end, v.text, CONVERT(varchar, ISNULL(p.[value], ")) AS text, case (c.status & 128) when 128 then ident_incr(u.name + '.' + o.name) else null end, case (c.status & 128) when 128 then ident_seed(u.name + '.' + o.name) else null end from dbo.sysusers u join dbo.sysobjects o on (o.uid = u.uid and o.type in ('U', 'S', 'V')) join dbo.syscolumns c on (c.id = o.id) left outer join sys.extended_properties p on c.id = p.major_id and c.colid = p.minor_id left outer join dbo.systypes s on (c.usertype = s.usertype and s.xtype = c.xtype and c.usertype >= 0) left outer join dbo.systypes x on (s.usertype > 100 and s.xtype = x.xtype and x.usertype not in (0, 18, 80) and x.usertype < 100) left outer join dbo.syscomments z on (z.id = o.id and z.number = c.colid) left outer join dbo.sysobjects d on (d.id = c.cdefault and d.category <> 0) left outer join dbo.syscomments v on (v.id = d.id) where 1 = 1 [ and u.name = %.q:OWNER%] [ and o.name=%.q:TABLE%] order by 1, 2, 3  2、配置PowerDesigner数据库连接 下一步,点击“完成”——“测试数据库”——返回OK。 3、新建物理数据模型 点击“OK”后,将会开始进行反转PD字典工作。 PowerDesigner设计时表显示注释选项: 选定编辑的表,右键- >Properties- >Columns- >Customize Columns and Filter(或直接用快捷键Ctrl+U)- >Comment(前面打勾)- >OK 如果出现错误“[Microsoft][ODBC SQL Server Driver][SQL Server]无法预定义语句。 SQLSTATE = 37000” 解决办法:选择菜单栏中的Database -> Change Current DBMS ,给DBMS选择SQL Server2005。 PowerDesigner常用快捷键 一般快捷键 F4   打开检查模型窗口,检查模型 F5   如果图窗口内的图改变过大小,恢复为原有大小即正常大小 F6   放大图窗口内的图 F7   缩小图窗口内的图 F8   在图窗口内中查看全部图内容 F9   预览图窗口 F10   在图窗口中以一幅视图的高度显示图Shift+单击对象   连续选择对象 alt+0   显示或隐藏浏览器窗口 alt+1   显示或隐藏输出窗口 alt+2   显示或隐藏结果列表窗口 ctrl+双击或ctrl+"+"     打开包图 ctrl+tab   在图窗口中切换大小,在文本中插入制表符或   在属性表窗口中切换标签页 ctrl+page up/page down 在属性表标签页中切换标签页 ctrl+移动窗口   禁止Docking特性 ctrl+b   在浏览器窗口中查找对象 ctrl+d   打开选择图窗口,选择图 ctrl+e   打开报表列表窗口 ctrl+f   打开查找对象窗口,查找对象 ctrl+j   在对象符号中调整文本 ctrl+F4   关闭当前图窗口 ctrl+alt+F4   关闭模型 ctrl+F6   在多个属性表窗口间切换 ctrl+shift+F6   在多个属性表窗口间反向切换 ctrl+u   回到父图 shift+F2   打开Workspace shift+F3   保存Workspace shift+F4   关闭Workspace 对象操作快捷键表 Del   删除对象(需确认) shift+Del   删除对象(无需确认) alt+Enter   打开所选对象的属性表 ctrl+k   粘贴为快捷方式 ctrl+h   选择连接:水平连接 ctrl+l   选择连接:垂直连接 ctrl+改变大小   保持对象的中心改变对象大小 ctrl+shift+改变大小 保持对象的中心改变对象大小,并保持与原对象的长宽比例 工具面板快捷键表 双击光标工具(Point) 选择图中的所有符号或ctrl+a 双击删除工具(Delete) 删除所有已选择的符号(需确认) 双击移动工具(Grabber) 显示全局视图双击对象工具 选择同类型的所有符号shift+双击对象工具 保持前一个选择,添加选择同类型的所有符号 拖放快捷键表 shift+移动项   移动符号 ctrl+移动项   复制符号 ctrl+shift+移动项 创建快捷方式列表窗口快捷键表 F2   选择编辑的文本 ctrl+shift+Space 选择当前行 ctrl+a   选择所有行 ctrl+shift+a   取消所有选择 ctrl+n或ctrl+i   插入行 ctrl+d   删除行 浏览器窗口快捷键表 *(小键盘)   展开活动浏览器中的所有节点 +(小键盘)   展开活动浏览器中的子节点 -(小键盘)   合拢活动浏览器中的所有节点 F2   重命名 ctrl+shift+f   在图中查找选择的对象 powerdesigner15显示注释 Tools->Display Preferences->Content->Table->Advanced…->Columns->select 选中Code后,将它上移到第一位 这样可以将code、name、type三项显示在ER图中,然后运行脚本,将注释comment的内容替换到code或name中,即可。 执行如下脚本: vbs文件内容:

扩展:PowerDesigner导出表到word from:https://www.cnblogs.com/jiekzou/p/5721522.html

sqlserver的四种分页方式

第一种:ROW_NUMBER() OVER()方式 select * from ( select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels ) as b where RowId between 10 and 20 —where RowId BETWEEN 当前页数-1*条数 and 页数*条数— 执行结果是: 第二种方式:offset fetch next方式(SQL2012以上的版本才支持:推荐使用 ) select * from ArtistModels  order by ArtistId offset 4 rows fetch next 5 rows only --order by ArtistId offset 页数*条数 rows fetch next 条数 rows only —- 执行结果是: 第三种方式:--top not in方式 (适应于数据库2012以下的版本) select top 3 * from ArtistModels where ArtistId not in (select top 15 ArtistId from ArtistModels) ——where Id not in (select top 条数*页数  ArtistId  from ArtistModels) 执行结果: 第四种方式:用存储过程的方式进行分页 CREATE procedure page_Demo @tablename varchar(20), @pageSize int, @page int AS declare @newspage int, @res varchar(100) begin set @newspage=@pageSize*(@page – 1) set @res=’select * from ' +@tablename+ ' order by ArtistId offset '+CAST(@newspage as varchar(10)) +' rows fetch next '+ CAST(@pageSize as varchar(10)) +' rows only' exec(@res) end EXEC page_Demo @tablename=’ArtistModels',@pageSize=3,@page=5 执行结果: ps:今天搞了一下午的分页,通过上网查资料和自己的实验,总结了四种分页方式供大家参考,有问题大家一起交流学习   from:https://www.cnblogs.com/fengxiaojiu/p/7994124.html

SQL Server 数值四舍五入,小数点后保留2位

1.round() 函数是四舍五入用,第一个参数是我们要被操作的数据,第二个参数是设置我们四舍五入之后小数点后显示几位。 2.numeric 函数的2个参数,第一个表示数据长度,第二个参数表示小数点后位数。 例如: select   cast(round(12.5,2)   as   numeric(5,2))  结果:12.50 select   cast(round(12.555,2)   as   numeric(5,2))  结果:12.56 select   cast(round(122.5255,2)   as   numeric(5,2)) 结果:122.53 select   cast(round(1222.5255,2)   as   numeric(5,2)) 结果:报错了! 原因是:1222.5255,整数位是4,小数位是2,加起来4+2=6,超出了numeric设置的5位,所以为了保险,可以增减numeric的参数,例如numeric(20,2)。 此文系转载后作一调整,转载自:http://blog.csdn.net/caoyuanlang_11/article/details/5410833

解决SQL Server 2008无法连接127.0.0.1的问题

电脑操作系统是Win10中文版,新装的英文版SQL Server 2008,纯默认安装,没有做任何改动。 装完SQL Server 2008之后,发现只能用默认的机器名来登录: 如果用127.0.0.1登录就会报出如下的错误: 在网上查询一番之后,发现让开启SQL Server Configuration Manager中的Named Pipes就可以解决问题,然而试了并没有什么卵用。。。 于是开始自己动手尝试解决方案,经过一番尝试,功夫不负有心人啊!终于算是找到了,下面把步骤贴出来: 第一步,开启TCP/IP。因为装完SQL之后这项协议默认是不开通的: 第二步,设置TCP/IP中属性的"IP ALL"的端口为1433。众所周知,SQL Server的默认端口为1433,只是不知道为何,默认安装却没有这一项。   然后,重启SQL服务就可以用127.0.0.1登录上了:   from:https://www.cnblogs.com/goldenbiu/p/4726463.html

SQL语句优化(转载)

一、操作符优化 1、IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的,从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。 推荐方案:在业务密集的SQL当中尽量不采用IN操作符,用EXISTS 方案代替。 2、NOT IN操作符 此操作是强列不推荐使用的,因为它不能应用表的索引。 推荐方案:用NOT EXISTS 方案代替 3、IS NULL 或IS NOT NULL操作(判断字段是否为空) 判断字段是否为空一般是不会应用索引的,因为索引是不索引空值的。 推荐方案:用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。 4、> 及 < 操作符(大于或小于操作符) 大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。 5、LIKE操作符 LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。 一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。 6、UNION操作符 UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如: select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。 推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。 select * from gc_dfys union all select * from ls_jg_dfys 二、SQL书写的影响 1、同一功能同一性能不同写法SQL的影响。 如一个SQL在A程序员写的为  Select * from zl_yhjbqk B程序员写的为 Select * from dlyx.zl_yhjbqk(带表所有者的前缀) C程序员写的为 Select * from DLYX.ZLYHJBQK(大写表名) D程序员写的为 Select *  from DLYX.ZLYHJBQK(中间多了空格) 以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同,则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。 2、WHERE后面的条件顺序影响 WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如: Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下' 以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下’条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。 3、查询表顺序的影响 在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下,ORACLE会按表出现的顺序进行链接,由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接) 三、SQL语句索引的利用 1、操作符优化(同上) 2、对条件字段的一些优化 采用函数处理的字段不能利用索引,如: substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate), 优化处理:sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1) 进行了显式或隐式的运算的字段不能进行索引,如:ss_df+20>50,优化处理:ss_df>30 ‘X’ || hbs_bh>’X5400021452’,优化处理:hbs_bh>’5400021542’ sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5 hbs_bh=5401002554,优化处理:hbs_bh=’ 5401002554’,注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。 条件内包括了多个本表的字段运算时不能进行索引,如:ys_df>cx_df,无法进行优化 qc_bh || kh_bh=’5400250000’,优化处理:qc_bh=’5400’ and kh_bh=’250000’ 四、其他 ORACLE的提示功能是比较强的功能,也是比较复杂的应用,并且提示只是给ORACLE执行的一个建议,有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用,一般不建议开发人员应用ORACLE提示,因为各个数据库及服务器性能情况不一样,很可能一个地方性能提升了,但另一个地方却下降了,ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。 from:http://www.cnblogs.com/savorboard/p/3295242.html

SQLSERVER如何查看索引缺失

当大家发现数据库查询性能很慢的时候,大家都会想到加索引来优化数据库查询性能, 但是面对一个复杂的SQL语句,找到一个优化的索引组合对人脑来讲,真的不是一件很简单的事。 好在SQLSERVER提供了两种“自动”功能,给你建议,该怎么调整索引 第一种是使用DMV 第二种是使用DTA (database engine tuning advisor) 数据库引擎优化顾问 这篇文章主要讲第一种 从SQL2005以后,在SQLSERVER对任何一句语句做编译的时候,都会去评估一下, 这句话是不是缺少什么索引的支持,如果他认为是,他还会预估,如果有这麽一个索引 他的性能能提高多少   SQLSERVER有几个动态管理视图 sys.dm_db_missing_index_details sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_columns(index_handle)     sys.dm_db_missing_index_details 这个DMV记录了当前数据库下所有的missing index的信息,他针对的是SQLSERVER从启动以来所有运行的语句, 而不是针对某一个查询。DBA可以看看,哪些表格SQLSERVER对他是最有“意见”的 以下是这个DMV的各个字段的解释: 1、index_handle:标识特定的缺失索引。该标识符在服务器中是唯一的。index_handle 是此表的密钥 2、database_id :标识带有缺失索引的表所驻留的数据库 3、object_id :标识索引缺失的表 4、equality_columns:构成相等谓词的列的逗号分隔列表 即哪个字段缺失了索引会在这里列出来(简单来讲就是where 后面的筛选字段), 谓词的形式如下:table.column =constant_value 5、inequality_columns :构成不等谓词的列的逗号分隔列表,例如以下形式的谓词:table.column > constant_value “=”之外的任何比较运算符都表示不相等。 6、included_columns:用于查询的涵盖列的逗号分隔列表(简单来讲就是 select 后面的字段)。 7、statement:索引缺失的表的名称 比如下面这个查询结果 那么应该创建这样的索引

在ProductID上创建索引,SalesOrderID作为包含性列的索引   注意事项: 由 sys.dm_db_missing_index_details 返回的信息会在查询优化器优化查询时更新,因而不是持久化的。 缺失索引信息只保留到重新启动 SQL Server 前。如果数据库管理员要在服务器回收后保留缺失索引信息, 则应定期制作缺失索引信息的备份副本     sys.dm_db_missing_index_columns(index_handle) 返回与缺少索引(不包括空间索引)的数据库表列有关的信息,sys.dm_db_missing_index_columns 是一个动态管理函数 字段解释 index_handle:唯一地标识缺失索引的整数。     sys.dm_db_missing_index_groups   返回有关特定缺失索引组中包含的缺失索引(不包括空间索引)的信息   sys.dm_db_missing_index_group_stats 返回缺失索引组的摘要信息,不包括空间索引 这个视图说白了就是预估有这麽一个索引,他的性能能提高多少 有一个字段比较重要: avg_user_impact: 实现此缺失索引组后,用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组,则查询成本将按此百分比平均下降。 就是说,增加了这个缺失索引,性能可以提高的百分比   下面是MSDN给出的示例,缺失索引组句柄为 2

  示例代码:

  我估计XX大侠做的SQLSERVER索引优化器也使用了"sys.dm_db_missing_index_details" 这个DMV 刚才看了一下,好像有错别字:Total Cost不是Totol Cost 暂时不知道Total Cost跟Improvement Measure怎麽算出来的   注意: 最后大家还需要注意一下,虽然这些DMV给出的建议还是比较合理的。 但是,DBA还是需要去确认一下建议。因为这个建议完全是根据语句本身给出的, 没有考虑对其他语句的影响,也没有考虑维护索引的成本,所以是很片面的。 其准确性,也要再确认一下     上面几个DMV的字段解释,大家可以看一下MSDN,非常详细 sys.dm_db_missing_index_group_stats msdn:http://msdn.microsoft.com/zh-cn/library/ms345421.aspx sys.dm_db_missing_index_groups msdn:http://msdn.microsoft.com/zh-cn/library/ms345407.aspx sys.dm_db_missing_index_columns([sql_handle]) msdn:http://msdn.microsoft.com/zh-cn/library/ms345364.aspx sys.dm_db_missing_index_details msdn:http://msdn.microsoft.com/zh-cn/library/ms345434.aspx from:http://www.cnblogs.com/lyhabc/archive/2013/02/10/2909761.html

SQLSERVER排查CPU占用高的情况

今天中午,有朋友叫我帮他看一下数据库,操作系统是Windows2008R2 ,数据库是SQL2008R2 64位 64G内存,16核CPU 硬件配置还是比较高的,他说服务器运行的是金蝶K3软件,数据库实例里有多个数据库   现象 他说是这几天才出现的,而且在每天的某一个时间段才会出现CPU占用高的情况 内存占用不太高,只占用了30个G CPU占用100% 排查方向   一般排查都是用下面的脚本,一般会用到三个视图sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests

看一下当前的数据库用户连接有多少 然后使用下面语句看一下各项指标是否正常,是否有阻塞,这个语句选取了前10个最耗CPU时间的会话

如果想看具体的SQL语句可以执行下面的SQL语句,记得在SSMS里选择以文本格式显示结果

模拟了一些耗CPU时间的动作   还有查看CPU数和user scheduler数和最大工作线程数,检查worker是否用完也可以排查CPU占用情况

查看机器上的所有schedulers包括user 和system 通过下面语句可以看到worker是否用完,当达到最大线程数的时候就要检查blocking了 对照下面这个表 各种CPU和SQLSERVER版本组合自动配置的最大工作线程数 CPU数                 32位计算机                        64位计算机 <=4                     256                                   512 8                        288                                   576 16                       352                                   704 32                       480                                   960

  如果SQLSERVER存在要等待的资源,那么执行下面语句就会显示出会话中有多少个worker在等待 结合[sys].[dm_os_wait_stats]视图,如果当前SQLSERVER里面没有任何等待资源,那么下面的SQL语句不会显示任何结果

比如我当前执行了查询SalesOrderDetail_test表100次,由于表数据非常多,所以SSMS需要把SQLSERVER执行的结果慢慢的取走, 造成了ASYNC_NETWORK_IO等待

  问题源头 经过排查和这几天的观察情况,确定是某些表缺失索引导致,现在在这些表上增加了索引,问题解决

查询CPU占用高的语句

查询缺失索引

定位问题后,新建非聚集索引

CPU占用恢复正常   总结 从多次历史经验来看,如果CPU负载持续很高,但内存和IO都还好的话,这种情况下,首先想到的一定是索引问题,十有八九错不了。 注意文章开头贴出的客户机器负载情况图

实战:上亿数据如何秒查?

最近在忙着优化集团公司的一个报表。优化完成后,报表查询速度有从半小时以上(甚至查不出)到秒查的质变。从修改SQL查询语句逻辑到决定创建存储过程实现,花了我3天多的时间,在此总结一下,希望对朋友们有帮助。   数据背景 首先,项目是西门子中国在我司实施部署的MES项目,由于项目是在产线上运作(3 years+),数据累积很大。在项目的数据库中,大概上亿条数据的表有5个以上,千万级数据的表10个以上,百万级数据的表,很多… (历史问题,当初实施无人监管,无人监控数据库这块的性能问题。ps:我刚入职不久…) 不多说,直接贴西门子中国的开发人员在我司开发的SSRS报表中的SQL语句:

这个查询语句,实际上通过我的检测和调查,在B/S系统前端已无法查出结果,半小时,一小时 … 。因为我直接在SQL查询分析器查,半小时都没有结果。 (原因是里面对一张上亿级数据表和3张千万级数据表做全表扫描查询) 不由感慨,西门子中国的素质(或者说责任感)就这样? 下面说说我的分析和走的弯路(思维误区),希望对你也有警醒。   探索和误区 首先相关表的索引,没有建全的,把索引给建上。 索引这步完成后,发现情况还是一样,查询速度几乎没有改善。后来想起相关千万级数据以上的表,都还没有建立表分区。于是考虑建立表分区以及数据复制的方案。 这里有必要说明下:我司报表用的是一个专门的数据库服务器,数据从产线订阅而来。就是常说的“读写分离”。 如果直接在原表上建立表分区,你会发现执行表分区的事物会直接死锁。原因是:表分区操作本身会锁表,产线还在推数据过来,这样很容易“阻塞”,“死锁”。 我想好的方案是:建立一个新表(空表),在新表上建好表分区,然后复制数据过来。 正打算这么干。等等!我好像进入了一个严重的误区! 分析: 原SQL语句和业务需求,是对产线的数据做产品以及序列号的追溯,关键是查询条件里没有有规律的"条件"(如日期、编号),贸然做了表分区,在这里几乎没有意义!反而会降低查询性能! 好险!还是一步一步来,先做SQL语句分析。   一、对原SQL语句的分析 1、查询语句的where条件,有大量@var in … or (@var =") 的片段 2、where条件有like '%’+@var+’%' 3、where条件有 case … end 函数 4、多次连接同一表查询,另外使用本身已嵌套的视图表,是不是必须,是否可替代? 5、SQL语句有*号,视图中也有*号出现   二、优化设计 首先是用存储过程改写,好处是设计灵活。 核心思想是:用一个或多个查询条件(查询条件要求至少输入一个)得到临时表,每个查询条件如果查到集合,就更新这张临时表,最后汇总的时候,只需判断这个临时表是否有值。以此类推,可以建立多个临时表,将查询条件汇总。   这样做目前来看至少两点好处: 1、省去了对变量进行 =@var or (@var=")的判断; 2、抛弃sql拼接,提高代码可读性。   再有就是在书写存储过程,这个过程中要注意: 1、尽量想办法使用临时表扫描替代全表扫描; 2、抛弃in和not in语句,使用exists和not exists替代; 3、和客户确认,模糊查询是否有必要,如没有必要,去掉like语句; 4、注意建立适当的,符合场景的索引; 5、踩死 "*" 号; 6、避免在where条件中对字段进行函数操作; 7、对实时性要求不高的报表,允许脏读(with(nolock))。   三、存储过程 如果想参考优化设计片段的详细内容,请参阅SQL代码:

虽然牺牲了代码的可读性,但创造了性能价值。本人水平有限,还请各位不吝赐教! 最后,将SSRS报表替换成此存储过程后,SQL查询分析器是秒查的。B/S前端用时1~2秒!   四、总结 平常的你是否偶尔会因急于完成任务而书写一堆性能极低的SQL语句呢?写出可靠性能的SQL语句不难,难的是习惯。 本文的优化思想很简单,关键点是避免全表扫描 & 注重SQL语句写法 & 索引,另外,如果你查询的表有可能会在查询时段更新,而实际业务需求允许脏读,可加with(nolock)预防查询被更新事物阻塞。 作者:hangwei 出处:http://www.cnblogs.com/hangwei/ 关于作者:专注于微软平台项目的架构设计与开发、数据库调优等工作。如有问题或建议,请多多赐教!   from:http://www.oschina.net/news/74787/how-the-data-on-the-second-search

高级T_SQL语法(2005)

排名函数: RANK() 返回结果集的分区内每行的排名,FIELD为排名依据。行的排名是相关行之前的排名数加1.例如有两个并列第一,那么后一个将排名第三,而不是第二。 语法:RANK() OVER([PARTITION BY clause]<ORDER BY clause>) 示例: select *, rank() over (partition by ClassID order by Mark DESC )as rank from dbo.Student 结果: DENSE_RANK():返回结果集分区中行的排名。在排名中没有任何间断。行的排名等于所讨论的行之前的所有排名数加1.例如有两个并列第一,那么下一个将排名第二,而不是第三。 语法:DENSE_RANK() OVER([PARTITION BY clause]<ORDER BY clause>) 示例: select *, dense_rank() over (partition by ClassID order by MarkDESC ) as rank from dbo.Student 结果: NTILE():将有序分区内的行分发到指定数目的组中。各个组由编号。,编号从1开始,对于每个行,NITLE()将返回此行所属组的编号。 语法:NTILE (int_expressions) OVER([PARTITION BY clause]<ORDER BY clause>) int_expressions:正整数常量表达式。用于指定每个分区必须被划分成的组数。 示例: select *,NTILE(4) over ( order by Mark DESC ) as NewCLASS fromdbo.Student   ROW_NUMBER()一般用于数据库分页。返回结果集分区内行的序列号。每个分区的第一行从1开始。 语法:ROW_NUMBER () OVER([PARTITION BY clause]<ORDER BY clause>) 示例: select *, ROW_NUMBER() over (partition by ClassID order by MarkDESC ) as rank from dbo.Student 结果:   异常处理:SQL2005中对t-sql实现了类似c#语言中的异常处理机制。 语法:BEGIN TRY { sql_statement|statement_block} END TRY NEGIN CATCH{ sql_statement|statement_block } END CATCH 此模块捕捉所有严重级大于10但不终止数据库连接的错误。此处try catch不能宽约多个处理,特不能跨越多个t-sql语句块。例如不能跨越两个BEGIN END语句块,也不能跨越IF ELSE语句块。 此处,需要注意的是,catch并不会将消息传递给应用程序,所以catch中必须显式调用select结果集或RAISEROR和PRINT语句之类的机制执行此操作。 TRY CATCH可以嵌套。也可以从存储过程或者触发器捕捉未处理的错误。当然存储过程之类的也可以使用try catch。不能使用goto语句输入try catch语句块,也不可以跳转到同一个try catch块内的某个标签或离开try catch模块。不能在用户定义函数中使用try catch构造。 Catch块内用来捕捉错误异常信息的函数: ERROR_NUMBER() 返回错误号 ERROR_SERVERITY:返回严重性 ERROR_STATE:错误状态号 ERROR_PRODURE()返回出现错误的存储过程或者触发器的名称 ERROR_LINE()导致错误的例程中的行号 ERROR_MESSAGE()错误信息的完整文本 如果没有,则返回空值。   捕捉不倒的错误信息: 严重新小于等于10的错误 严重性高于等于20且终止SQLSERVERDATABASEENGINE任务处理的错误 需要关注的消息,如客户端终端请求或者客户端连接终端 系统管理员使用KILL语句终止会话时   以下类型错误发生界别与TRY catch构造的执行等级相通,则catch不会处理: 编译错误 语句级重新编译过程中出现的错误   APPLY操作符号:可以为实现查询操作的外部表表达式返回每个行调用表值函数。表值函数作为右输入,外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果,生成的行作为最终输出。APPLY运算符生成的列表是左输入中的列集后跟右输入返回列的列表。 两种形式: CROSS APPLY:仅返回外部表中通过表值函数生成结果集的行 OUTER APPLY:既返回生成结果集的行,也返回不生成结果集的行,不生成结果集的行,表值函数列中的值为NULL。 语法: function apply([thisObj : Object [,argArray : { Array | arguments }]])   PIVOT、UNPIVOT运算符:对结果集进行旋转,这样列变成行,行变成列。这成为旋转数据或者创建交叉报表。 示例: select ID,Name,[1] as aaa,[2] as bbb,[3] as ccc,[4] as ddd from dbo.ProductSale PIVOT ( Sum (Sale) for Quarter in ([1],[2],[3],[4]) ) as dddd 结果: SQL2005如果对上面这句话出现错误提示,那么请设置sql2005兼容性。设置兼容性的步骤: 选中具体数据库->右击->属性->选项->兼容级别设置,设置为最高的90就可以了。 UNPIVOT为相反操作,将如上面的结果这样的数据表变回去。   公用表达式CTE:公用表达式(CTE)是在select、insert、update或delete语句执行过程中暂时存储的结果集。利用CTE可使用递归查询,并可以通过取代临时表或试图来简化逻辑。 语法: with expression_name{ ( column_name[,…..,n])} as (CTE_QUERY_DEGINATION) 使用CTE与ROW_NUMBER()函数结合用于数据库分页: 示例: with cc as (     select *, ROW_NUMBER()     over         (partition by ClassID order by Math DESC )         as RowID     from dbo.Student ) select * from cc where RowID>2 and RowID<5 结果:   引用自身从而创建递归CTE。递归CTE是一个重复执行初始CTE以返回数据子集,指导获取完整结果集的公用表表达式。 递归由三个元素组成: 例程调用。递归CTE的第一个调用包括一个或者多个由UNION ALL、UNION、EXCEPT或INTERSECT运算符连接的CTE_QUERY_DEFINITIONS。由于这些查询定义了CTE的基准结果集。所以成为定位点成员。 例程的递归调用。递归调用包括一个或者多个由引用CTE本身CTE本身的UNION ALL运算符连接的CTE——QUERY——DEFINITIONS。这些查询被定义为递归成员。 终止检查。隐式的,当一个调用未返回行时,递归停止。   from:http://www.cnblogs.com/BLoodMaster/archive/2010/03/22/1691632.html