php mssql 分頁SQL語句優(yōu)化 持續(xù)影響
更新時(shí)間:2009年04月26日 00:56:32 作者:
經(jīng)過SQL優(yōu)化后的分面查詢速度能夠得到大幅提高。
復(fù)制代碼 代碼如下:
<?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//總記錄數(shù)
$pagesize//頁面顯示記錄條數(shù)
$page//當(dāng)前頁
$sqlc= new sqlpage($sql,$allcount,$pagesize,$page);
$sql=$sqlc->getsql();
優(yōu)化后的語句:
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);//總記錄數(shù)
$this->pagesize= intval($pagesize);//頁面大?。@示記錄數(shù))
$this->page= intval($page);//當(dāng)前頁
$this->getpage();
$this->gettop();
}
function getpage(){ //獲取當(dāng)前頁
$this->allpage=ceil( $this->allcount/$this->pagesize);//去當(dāng)前小數(shù)的最大整數(shù)
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);//將頁碼轉(zhuǎn)換為數(shù)字
}
}
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;
}
}
?>
相關(guān)文章
PHP 動(dòng)態(tài)隨機(jī)生成驗(yàn)證碼類代碼
這個(gè)利用PHP動(dòng)態(tài)隨機(jī)生成驗(yàn)證碼的類是在LAMP的書上看到的。感覺寫的很好就收藏了下來。2010-04-04Thinkphp實(shí)現(xiàn)自動(dòng)驗(yàn)證和自動(dòng)完成
這篇文章主要介紹了Thinkphp實(shí)現(xiàn)自動(dòng)驗(yàn)證和自動(dòng)完成的相關(guān)資料,需要的朋友可以參考下2015-12-12使用NetBeans + Xdebug調(diào)試PHP程序的方法
前些天發(fā)現(xiàn)通過Notepad++的DBGP插件結(jié)合PHP的xdebug擴(kuò)展可以實(shí)現(xiàn)PHP文件調(diào)試,同時(shí),介紹說包含了單步調(diào)試、監(jiān)視變量還有跨文件調(diào)試。2011-04-04php函數(shù)間的參數(shù)傳遞(值傳遞/引用傳遞)
函數(shù)間的參數(shù)傳遞包括值傳遞和引用傳遞,詳細(xì)示例接下來為大家詳細(xì)介紹下,感興趣的朋友不要錯(cuò)過2013-09-09ajax 的post方法實(shí)例(帶循環(huán))
在最近的項(xiàng)目中,為了能解決在大數(shù)據(jù)查詢中出現(xiàn)的超時(shí)問題,需要將大數(shù)據(jù)拆分成小數(shù)據(jù),然后進(jìn)行循環(huán)處理。本人經(jīng)驗(yàn)不足,技術(shù)有限,只能想到用ajax來傳送數(shù)據(jù)。2011-07-07PHP CURL實(shí)現(xiàn)模擬登陸并上傳文件操作示例
這篇文章主要介紹了PHP CURL實(shí)現(xiàn)模擬登陸并上傳文件操作,結(jié)合實(shí)例形式分析了PHP使用curl進(jìn)行模擬登陸與文件傳輸操作具體實(shí)現(xiàn)技巧,需要的朋友可以參考下2020-01-015種PHP創(chuàng)建數(shù)組的實(shí)例代碼分享
在本文將數(shù)組的各種創(chuàng)建方式用PHP實(shí)例代碼的方式分享給大家,感興趣的朋友可以了解下2014-01-01php求圓周率的簡(jiǎn)單實(shí)現(xiàn)方法
這篇文章主要介紹了php求圓周率的簡(jiǎn)單實(shí)現(xiàn)方法,涉及簡(jiǎn)單的php數(shù)學(xué)運(yùn)算技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2016-05-05