存储过程优化mssql分页性能

MSsql分页效率: mysql分页,一般使用的方法有3中(这里不再重复),公认效率较好的是使用 id < min(id)的这一种,于是我平时也在使用这种。但在半年前,测试网站时,总感觉着分页显示速度跟理论值相差太大,于是做下面的测试:

msSQL2005 300万数据,mid,rid为索引。
在我的机器上比较明显(AMD1.4G。448M内存)
在公司比较好的服务器运行,这2种写法区别不出来——弱机器的优点在这里体现出来了。。
————-写法1 (0秒)  巨快。
Declare @top2 int
select @top2=min(mid) from (select top 1 mid from [musicbox] where rid=’87503′ order by mid desc) as db2
SELECT top 20 mid,rid,mname,mauthor,madddate FROM [musicbox] as a   where rid=’87503′ and a.mid <=@top2

————-写法2 (40秒) 巨慢。
SELECT top 20 mid,rid,mname,mauthor,madddate FROM [musicbox] as a  where rid=’87503′ and
a.mid <=(
select min(mid) from (select top 1 mid from [musicbox] where rid=’87503′ order by mid desc
) as db2)
order by mid desc
————————–

ASP操作XML类(两种)

1、
<%
class cls_xml

private sfilename
private sxmlfile
private sobjxml

property let filename(value)
sfilename=value
end property

property get filename
filename=sfilename
end property

property get objxml
set objxml=sobjxml
end property

private sub class_initialize()
sfilename=””
sxmlfile=””
sobjxml=null
end sub

private sub class_terminate()
sobjxml.close
set sobjxml=nothing
end sub

private sub createfile()’创建xml文件
Set oPI=sobjXML.createProcessingInstruction(“xml”, “version=””1.0″” encoding=””GB2312″””)
sobjXML.insertBefore oPI,sobjXML.childNodes(0)
savefile
end sub

public function loadfile()’读取xml文件
sxmlfile=server.MapPath(sfilename)
set sobjxml=server.CreateObject(“msxml2.domdocument”)
sobjxml.load(sxmlfile)
sobjxml.async=false
if sobjxml.parseError.ErrorCode = 0 then
loadfile=true
else
createfile
end if
end function

public sub addnode(byval parentnode,byval childnode,byval nodetext)’添加节点
if parentnode=”” then
sobjxml.appendchild (sobjxml.createElement(childnode))
else
set child=sobjxml.createElement(childnode)
child.text=nodetext
sobjxml.selectsinglenode(parentnode).appendchild child
end if
savefile
end sub

public sub addattr(byval parentnode,byval attrtype,byval attrname,byval attrtext)’添加属性
set sattribute=sobjxml.createNode(attrtype,attrname,””)
sattribute.text=attrtext
if parentnode=”” then
sobjxml.documentElement.setattributenode sattribute
else
sobjxml.documentElement.selectsinglenode(parentnode).setattributenode sattribute
end if
savefile
end sub

private function getnode(byval nodepath,byval attrname,byval attrvalue)’获得节点对象
if attrname=”” then
set getnode=sobjxml.documentElement.selectsinglenode(nodepath)
else
set getnode=sobjxml.documentElement.selectsinglenode(“//”&nodepath&”[@”&attrname&”='”&attrvalue&”‘]”)
end if
end function

public function getnodename(byval nodepath,byval attrname,byval attrvalue)’获得节点名称
getnodename=getnode(nodepath,attrname,attrvalue).nodename
end function

public function getnodevalue(byval nodepath,byval attrname,byval attrvalue)’获得节点内容
getnodevalue=getnode(nodepath,attrname,attrvalue).text
end function

public sub modifynodevalue(byval nodepath,byval newtext,byval attrname,byval attrvalue)’修改节点内容
set newnode=getnode(nodepath,attrname,attrvalue)
newnode.text=newtext
savefile
end sub

public function getnodeattrname(byval nodepath,byval attrname,byval attrname_,byval attrvalue_)’获得节点属性名称
getnodeattrname=getnode(nodepath,attrname_,attrvalue_).getattributenode(attrname).nodename
end function

public function getnodeattrvalue(byval nodepath,byval attrname,byval attrname_,byval attrvalue_)’获得节点属性值
getnodeattrvalue=getnode(nodepath,attrname_,attrvalue_).getattributenode(attrname).nodevalue
end function

public sub modifynodeattrvalue(byval nodepath,byval attrname,byval newtext,byval attrname_,byval attrvalue_)’修改节点属性值
set newattr=getnode(nodepath,attrname_,attrvalue_).getattributenode(attrname)
newattr.text=newtext
savefile
end sub

public sub delnode(byval parentnodepath,byval parentnodeattrname,byval parentnodeattrvalue,byval childnodepath,byval childnodeattrname,byval childnodeattrvalue)’删除节点
if parentnodepath=”” then
set parentnode=sobjxml.documentElement
else
set parentnode=getnode(parentnodepath,parentnodeattrname,parentnodeattrvalue)
end if
set childnode=getnode(childnodepath,childnodeattrname,childnodeattrvalue)
parentnode.removechild childnode
savefile
end sub

public function getchildlength(byval nodename,byval attrname,byval attrvalue)’获得节点长度
if nodename=”” then
getchildlength=sobjxml.documentElement.childnodes.length
else
getchildlength=getnode(nodename,attrname,attrvalue).childnodes.length
end if
end function

private sub savefile()’保存文件
sobjxml.save(sxmlfile)
end sub

end class
%>

2、
<%’个人网站:http://blog.aq82.com
Class clsXML
 ‘strFile 必须是完整的路径 如:C:XMLXMLFile.XML
 ‘objDoc 是 XML Object
 Private strFile, objDoc

 ‘类的初使化:
 Private Sub Class_Initialize()
 strFile = “”
 End Sub

 ‘Terminate and unload all created objects
 Private Sub Class_Terminate()
 Set objDoc = Nothing
 End Sub

 ‘*********************************************************************
 ‘ Properties
 ‘*********************************************************************

 ‘Set XML File and objDoc
 ‘设置XML和DOC对象
 Public Property Let File(str)
 Set objDoc = Server.CreateObject(“Microsoft.XMLDOM”)
 objDoc.async = False
 strFile = str
 objDoc.Load strFile
 End Property

 ‘Get XML File
 Public Property Get File()
 File = strFile
 End Property

 ‘*********************************************************************
 ‘ Functions
 ‘*********************************************************************

 ‘创建XML文件
 Public Function createFile(strPath, strRoot)
 Dim objFSO, objTextFile
 Set objFSO = Server.CreateObject(“Scripting.FileSystemObject”)
 Set objTextFile = objFSO.CreateTextFile(strPath, True)
 objTextFile.WriteLine(“<?xml version=””1.0″”?>”)
 objTextFile.WriteLine(“<” & strRoot & “/>”)
 objTextFile.Close
 Me.File = strPath
 Set objTextFile = Nothing
 Set objFSO = Nothing
 End Function

 ‘读取节点数据
 Public Function getField(strXPath)
 Dim objNodeList, arrResponse(), i
 Set objNodeList = objDoc.documentElement.selectNodes(strXPath)
 ReDim arrResponse(objNodeList.length)
 For i = 0 To objNodeList.length – 1
 arrResponse(i) = objNodeList.item(i).Text
 Next
 getField = arrResponse
 End Function

 ‘根据条件更新数据
 Public Function updateField(strXPath, strData)
 Dim objField
 For Each objField In objDoc.documentElement.selectNodes(strXPath)
 objField.Text = strData
 Next
 objDoc.Save strFile
 Set objField = Nothing
 updateField = True
 End Function

 ‘创建子节点
 Public Function createRootChild(strNode)
 Dim objChild
 Set objChild = objDoc.createNode(1, strNode, “”)
 objDoc.documentElement.appendChild(objChild)
 objDoc.Save strFile
 Set objChild = Nothing
 End Function

 ‘创建节点属性值
 Public Function createRootNodeWAttr(strNode, attr, val)
 Dim objChild, objAttr
 Set objChild = objDoc.createNode(1, strNode, “”)
 If IsArray(attr) And IsArray(val) Then
 If UBound(attr)-LBound(attr) <> UBound(val)-LBound(val) Then
 Exit Function
 Else
 Dim i
 For i = LBound(attr) To UBound(attr)
 Set objAttr = objDoc.createAttribute(attr(i))
 objChild.setAttribute attr(i), val(i)
 Next
 End If
 Else
 Set objAttr = objDoc.createAttribute(attr)
 objChild.setAttribute attr, val
 End If
 objDoc.documentElement.appendChild(objChild)
 objDoc.Save strFile
 Set objChild = Nothing
 End Function

 ‘在一个指定的节点下创建一个子节点
 Public Function createChildNode(strXPath, strNode)
 Dim objParent, objChild
 For Each objParent In objDoc.documentElement.selectNodes(strXPath)
 Set objChild = objDoc.createNode(1, strNode, “”)
 objParent.appendChild(objChild)
 Next
 objDoc.Save strFile
 Set objParent = Nothing
 Set objChild = Nothing
 End Function

 ‘在一个有属性值符合条件的XPath节点下创建一个子节点
 Public Function createChildNodeWAttr(strXPath, strNode, attr, val)
 Dim objParent, objChild, objAttr
 For Each objParent In objDoc.documentElement.selectNodes(strXPath)
 Set objChild = objDoc.createNode(1, strNode, “”)
 If IsArray(attr) And IsArray(val) Then
 If UBound(attr)-LBound(attr) <> UBound(val)-LBound(val) Then
 Exit Function
 Else
 Dim i
 For i = LBound(attr) To UBound(attr)
 Set objAttr = objDoc.createAttribute(attr(i))
 objChild.SetAttribute attr(i), val(i)
 Next
 End If
 Else
 Set objAttr = objDoc.createAttribute(attr)
 objChild.setAttribute attr, val
 End If
 objParent.appendChild(objChild)
 Next
 objDoc.Save strFile
 Set objParent = Nothing
 Set objChild = Nothing
 End Function

 ‘删除节点
 Public Function deleteNode(strXPath)
 Dim objOld
 For Each objOld In objDoc.documentElement.selectNodes(strXPath)
 objDoc.documentElement.removeChild objOld
 Next
 objDoc.Save strFile
 Set objOld = Nothing
 End Function
End Class
%>

jQuery Plugins

概述

jQuery 是继 prototype 之后又一个优秀的 Javascript 框架。其宗旨是—写更少的代码,做更多的事情。它是轻量级的 js 库(压缩后只有21k) ,这是其它的 js 库所不jquery及的,它兼容 CSS3,还兼容各种浏览器(IE 6.0+, FF 1.5+, Safari 2.0+, Opera 9.0+)。 jQuery 是一个快速的,简洁的 javaScript 库,使用户能更方便地处理 HTML documents、events、实现动画效果,并且方便地为网站提供 AJAX 交互。 jQuery 还有一个比较大的优势是,它的文档说明很全,而且各种应用也说得很详细,同时还有许多成熟的插件可供选择。 jQuery 能够使用户的 html 页保持代码和 html 内容分离,也就是说,不用再在 html 里面插入一堆js来调用命令了,只需定义 id 即可。今天在Kollermedia.at上发现了一篇JQuery插件列表的文章,特推荐如下。

文件上传(File upload)JQuery_001

Ajax File Upload.
jQUploader.
Multiple File Upload plugin
jQuery File Style.
Styling an input type file.
Progress Bar Plugin.

表单验证(Form Validation)

jQuery Validation.
Auto Help.
Simple jQuery form validation.
jQuery XAV – form validations.
jQuery AlphaNumeric.
Masked Input.
TypeWatch Plugin.
Text limiter for form fields.
Ajax Username Check with jQuery.

表单-选取框(Form – Select Box stuff)

jQuery Combobox.
jQuery controlled dependent (or Cascadign) Select List.
Multiple Selects.
Select box manipulation.
Select Combo Plugin.
jQuery – LinkedSelect
Auto-populate multiple select boxes.
Choose Plugin (Select Replacement).

表单基本、输入框、选择框等(Form Basics, Input Fields, Checkboxes etc.)

jQuery Form Plugin.
jQuery-Form.
jLook Nice Forms.
jNice.
Ping Plugin.
Toggle Form Text.
ToggleVal.
jQuery Field Plugin.
jQuery Form’n Field plugin.
jQuery Checkbox manipulation.
jTagging.
jQuery labelcheck.
Overlabel.
3 state radio buttons.
ShiftCheckbox jQuery Plugin.
Watermark Input.
jQuery Checkbox (checkboxes with imags).
jQuery SpinButton Control.
jQuery Ajax Form Builder.
jQuery Focus Fields.
jQuery Time Entry.

时间、日期和颜色选取(Time, Date and Color Picker)

jQuery UI Datepicker.
jQuery date picker plugin.
jQuery Time Picker.
Time Picker.
ClickPick.
TimePicker.
Farbtastic jQuery Color Picker Plugin.
Color Picker by intelliance.fr.

投票插件(Rating Plugins)

jQuery Star Rating Plugin.
jQuery Star Rater.
Content rater with asp.net, ajax and jQuery.
Half-Star Rating Plugin.

搜索插件(Search Plugins)

jQuery Suggest.
jQuery Autocomplete.
jQuery Autocomplete Mod.
jQuery Autocomplete by AjaxDaddy.
jQuery Autocomplete Plugin with HTML formatting.
jQuery Autocompleter.
AutoCompleter (Tutorial with PHP&MySQL).
quick Search jQuery Plugin.

编辑器(Inline Edit & Editors)JQuery_002

jTagEditor.
WYMeditor.
jQuery jFrame.
Jeditable – edit in place plugin for jQuery
jQuery editable.
jQuery Disable Text Select Plugin.
Edit in Place with Ajax using jQuery.
jQuery Plugin – Another In-Place Editor.
TableEditor.
tEditable – in place table editing for jQuery.

多媒体、视频、Flash等(Audio, Video, Flash, SVG, etc)

jMedia – accessible multi-media embedding.
JBEdit – Ajax online Video Editor.
jQuery MP3 Plugin.
jQuery Media Plugin.
jQuery Flash Plugin.
Embed QuickTime.
SVG Integration.

图片(Photos/Images/Galleries)

ThickBox.
jQuery lightBox plugin.
jQuery Image Strip.
jQuery slideViewer.
jQuery jqGalScroll 2.0.
jQuery – jqGalViewII.
jQuery – jqGalViewIII.
jQuery Photo Slider.
jQuery Thumbs – easily create thumbnails.
jQuery jQIR Image Replacement.
jCarousel Lite.
jQPanView.
jCarousel.
Interface Imagebox.
Image Gallery using jQuery, Interface & Reflactions.
simple jQuery Gallery.
jQuery Gallery Module.
EO Gallery.
jQuery ScrollShow.
jQuery Cycle Plugin.
jQuery Flickr.
jQuery Lazy Load Images Plugin.
Zoomi – Zoomable Thumbnails.
jQuery Crop – crop any image on the fly.
Image Reflection.

Google地图(Google Map)

jQuery Plugin googlemaps.
jMaps jQuery Maps Framework.
jQmaps.
jQuery & Google Maps.
jQuery Maps Interface forr Google and Yahoo maps.
jQuery J Maps – by Tane Piper.

游戏(Games)

Tetris with jQuery.
jQuery Chess.
Mad Libs Word Game.
jQuery Puzzle.
jQuery Solar System (not a game but awesome jQuery Stuff).

表格等(Tables, Grids etc.)

UI/Tablesorter.JQuery_003 
jQuery ingrid.
jQuery Grid Plugin.
Table Filter – awesome!.
TableEditor.
jQuery Tree Tables.
Expandable “Detail” Table Rows.
Sortable Table ColdFusion Costum Tag with jQuery UI.
jQuery Bubble.
TableSorter.
Scrollable HTML Table.
jQuery column Manager Plugin.
jQuery tableHover Plugin.
jQuery columnHover Plugin.
jQuery Grid.
TableSorter plugin for jQuery.
tEditable – in place table editing for jQuery.
jQuery charToTable Plugin.
jQuery Grid Column Sizing.
jQuery Grid Row Sizing.

统计图(Charts, Presentation etc.)

jQuery Wizard Plugin .
jQuery Chart Plugin.
Bar Chart.

边框、圆角、背景(Border, Corners, Background)

jQuery Corner.
jQuery Curvy Corner.
Nifty jQuery Corner.
Transparent Corners.
jQuery Corner Gallery.
Gradient Plugin.

文字和超链接(Text and Links)

jQuery Spoiler plugin.
Text Highlighting.
Disable Text Select Plugin.
jQuery Newsticker.
Auto line-height Plugin.
Textgrad – a text gradient plugin.
LinkLook – a link thumbnail preview.
pager jQuery Plugin.
shortKeys jQuery Plugin.
jQuery Biggerlink.
jQuery Ajax Link Checker.

鼠标提示(Tooltips)

jQuery Plugin – Tooltip.
jTip – The jQuery Tool Tip.
clueTip.
BetterTip.
Flash Tooltips using jQuery.
ToolTip.

菜单和导航(Menus, Navigations)

jQuery Tabs Plugin – awesome! . [demo nested tabs.]
another jQuery nested Tab Set example (based on jQuery Tabs Plugin).
jQuery idTabs.
jdMenu – Hierarchical Menu Plugin for jQuery.
jQuery SuckerFish Style.
jQuery Plugin Treeview.
treeView Basic.
FastFind Menu.
Sliding Menu.
Lava Lamp jQuery Menu.
jQuery iconDock.
jVariations Control Panel.
ContextMenu plugin.
clickMenu.
CSS Dock Menu.
jQuery Pop-up Menu Tutorial.
Sliding Menu. http://stilbuero.de/jquery/tabs_3/

幻灯、翻转等(Accordions, Slide and Toggle stuff)

jQuery Plugin Accordion.
jQuery Accordion Plugin Horizontal Way.
haccordion – a simple horizontal accordion plugin for jQuery.
Horizontal Accordion by portalzine.de.
HoverAccordion.
Accordion Example from fmarcia.info.
jQuery Accordion Example.
jQuery Demo – Expandable Sidebar Menu.
Sliding Panels for jQuery.
jQuery ToggleElements.
Coda Slider.
jCarousel.
Accesible News Slider Plugin.
Showing and Hiding code Examples.
jQuery Easing Plugin.
jQuery Portlets.
AutoScroll.
Innerfade.

拖放插件(Drag and Drop)

UI/Draggables.
EasyDrag jQuery Plugin.
jQuery Portlets.
jqDnR – drag, drop resize.
Drag Demos.

XML XSL JSON Feeds

XSLT Plugin.
jQuery Ajax call and result XML parsing.
xmlObjectifier – Converts XML DOM to JSON.
jQuery XSL Transform.
jQuery Taconite – multiple Dom updates.
RSS/ATOM Feed Parser Plugin.
jQuery Google Feed Plugin.

浏览器(Browserstuff)

Wresize – IE Resize event Fix Plugin.
jQuery ifixpng.
jQuery pngFix.
Link Scrubber – removes the dotted line onfocus from links.
jQuery Perciformes – the entire suckerfish familly under one roof.
Background Iframe.
QinIE – for proper display of Q tags in IE.
jQuery Accessibility Plugin.
jQuery MouseWheel Plugin.

对话框、确认窗口(Alert, Prompt, Confirm Windows)

jQuery Impromptu.
jQuery Confirm Plugin.
jqModal.
SimpleModal.

CSS

jQuery Style Switcher.
JSS – Javascript StyleSheets.
jQuery Rule – creation/manipulation of CSS Rules.
jPrintArea.

DOM、AJAX和其它JQuery插件(DOM, Ajax and other jQuery plugins)

FlyDOM.
jQuery Dimenion Plugin.
jQuery Loggin.
Metadata – extract metadata from classes, attributes, elements.
Super-tiny Client-Side Include Javascript jQuery Plugin.
Undo Made Easy with Ajax.
JHeartbeat – periodically poll the server.
Lazy Load Plugin.
Live Query.
jQuery Timers.
jQuery Share it – display social bookmarking icons.
jQuery serverCookieJar.
jQuery autoSave.
jQuery Puffer.
jQuery iFrame Plugin.
Cookie Plugin for jQuery.
jQuery Spy – awesome plugin.
Effect Delay Trick.
jQuick – a quick tag creator for jQuery.
Metaobjects
.
elementReady.

www.000webhost.com