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

olga

　　经过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;


}












}
?>