php mssql 分页SQL语句优化 持续影响

经过SQL优化后的分面查询速度能够得到大幅提高。

 

<?php
/**
 * @Filename :page.sql.class.php
 * @CreatTime :2009-01-06
 * @Descrition :此类为SQL语句处理类。
 * @UpdateTime-1 :null
 * @Version  :jswweb1.0.0
 * @Author  :fkedwgwy
 * @Dome :

     $sql//SQL语句

     $allcount//总记录数
     $pagesize//页面显示记录条数

     $page//当前页

 

      $sqlc= new sqlpage($sql,$allcount,$pagesize,$page);
      $sql=$sqlc->getsql();

优化后的语句：

 

SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 270 Lsh,Ztm,Dyzrsm,Dyzzfs,Cbsm,Cbny,Ssh,Fbsl,jcsl from ts_gcb where Ssh like 'C%' order by Lsh asc) AS inner_tbl ORDER BY Lsh DESC) AS outer_tbl ORDER BY Lsh asc

 

 */
class sqlpage{

              function sqlpage($sql,$allcount,$pagesize,$page){
             
                        $this->sql= $sql;//查询语名
                       
                        $this->allcount= intval($allcount);//总记录数
                       
                        $this->pagesize= intval($pagesize);//页面大小（显示记录数）
                       
                        $this->page= intval($page);//当前页
                       
                        $this->getpage();
                       
                        $this->gettop();

              }
              function getpage(){   //获取当前页
             
                                     $this->allpage=ceil( $this->allcount/$this->pagesize);//去当前小数的最大整数
    
                                   if ($this->page=="" or $this->page>$this->allpage or $this->page<0 or $this->page==0){
                                  
                                          $this->page2=1;
                                         
                                   }else{
                                  
                                          $this->page2=intval($this->page);//将页码转换为数字
                                         
                                   }
             
             
              }
              function gettop(){ //获取子查询2的TOP大小
             
                                 if ($this->page2<$this->allpage){
                                 
                                      $this->top2=$this->pagesize;
                                     
                                 }else{
                                 
                                      $this->top2=$this->allcount-$this->pagesize*($this->allpage-1);
                                     
                                 }
             
             
             
              }
             /* function getsql(){//获取SQL语句
             
                                  $this->s=preg_replace("/select/i","",$this->sql);
                                 
                                  $this->top1=$this->pagesize*$this->page2;
                                 
                                  $this->sql1="SELECT TOP $this->top1 $this->s";
                                 
                                  if (strpos($this->sql,"asc")){//升序
                                 
                                             $this->sql_e="select * from (   select TOP $this->top2 * FROM ( $this->sql1 ) as aSysTable   ORDER BY $this->order DESC ) as bSysTable   ORDER BY $this->order ASC";
                                             
                                  }else
                                 
                                  //$this->sql_e="select * from (   select TOP $this->top2 * FROM ( $this->sql1 ) as aSysTable   ORDER BY $this->order DESC ) as bSysTable   ORDER BY $this->order ASC";
                                 
                                  if (strpos($this->sql,"desc")){//降序
                                 
                                              $this->sql_e="select * from (   select TOP $this->top2 * FROM ( $this->sql1 ) as aSysTable   ORDER BY $this->order asc ) as bSysTable   ORDER BY $this->order desc";
                                             
                                  }else{//不处理排序的情况
                                 
                                              $this->sql_e="select * from (   select TOP $this->top2 * FROM ( $this->sql1 ) as aSysTable   ORDER BY $this->order DESC ) as bSysTable   ORDER BY $this->order ASC";
                                 
                                  }
                                 // echo $this->sql_e;
                                  return $this->sql_e;
                                 
                    }*/




  function getsql()
     {
     
        $sql=$this->sql;

        $this->top1=$this->pagesize*$this->page2;


        $orderby = stristr($sql, 'ORDER BY');
        if ($orderby !== false) {
            $sort = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
            $order = str_ireplace('ORDER BY', '', $orderby);
            $order = trim(preg_replace('//bASC/b|/bDESC/b/i', '', $order));
        }

        $sql = preg_replace('/^SELECT/s/i', 'SELECT TOP ' . ($this->top1) . ' ', $sql);

        $sql = 'SELECT * FROM (SELECT TOP ' . $this->top2 . ' * FROM (' . $sql . ') AS inner_tbl';
        if ($orderby !== false) {
            $sql .= ' ORDER BY ' . $order . ' ';
            $sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
        }
        $sql .= ') AS outer_tbl';
        if ($orderby !== false) {
            $sql .= ' ORDER BY ' . $order . ' ' . $sort;
        }
           echo $sql;
        return $sql;
       
       
    }












}
?>