SQL 函数的应用及比较对于海量数据查询优化

      一.Exists与In的执行效率；其实一样，在SQL2000中。

                 select title,price from titles where title_id in (select title_id from sales where qty>30)

                       

                select title,price from titles where exists (select * from sales where sales.title_id=titles.title and qty>30)

                         

      

      二.函数CharIndex()和通配符%的Like执行效率一样。

                select gid,title,fariqi,read from Tgongwen where CharIndex('旺财',reader)>0  and fariqi>'2014-02-03'

                          

                select gid,title,fariqi,read from Tgongwen where reader like '%旺财%' and fariqi>'2014-02-23'

                           

      

      三.Union绝对比Or执行效率高(Union扫描的是索引，而Or是全表扫描)；如果Where子句需要筛选的字段一样则Union比Or执行效率要低。

                 select gid,title,fariqi,read,neibuyonghu from Tgongwen where fariqi>'2014-02-20' or gid>500

                     

                 select gid,title,fariqi,read,neibuyonghu from Tgongwen where fariqi>'2014-02-20'

                 union

                 select gid,title,fariqi,read,neibuyonghu from Tgongwen where gid>500

                       

                select gid,title,fariqi,read,neibuyonghu from Tgongwen where fariqi='2014-02-20'  or fariqi='2013-02-20'

                      

                select gid,title,fariqi,read,neibuyonghu from Tgongwen where fariqi='2014-02-20' 

                union

                select gid,title,fariqi,read,neibuyonghu from Tgongwen where fariqi='2013-02-20' 

                       

       

      四.Order by 按聚集索引列排序效率最高。