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; 
} 
} 
?>