对于一个有1500万条数据的sql server表,如何做查询速度更快些?

对于一个有1500万条数据的sql server表,如何做查询速度更快些?
谁有没有现成的分页代码?
如果用网上的千万级通用分页存储过程的话,asp里该怎么调用?

我想到的办法是,
1 建立适当索引
2 尽量缩小查寻范围
3 利用存储过程
4 sql语句用select top 嵌套
5 分表查询

看大家有没有更好的方法?或者请多指教

35 个解决方案

#1

#2

存储过程

top翻页

我只能这么多了

#3

还有千万不要用Select * 语句

#4

谁有现成的分页程序

#5

一个分页程序
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<%
'设置你一页要显示多少记录
Const NumPerPage = 10

'取得当前所在页
Dim CurPage
If Request.QueryString("CurPage") = "" then
CurPage = 1 'We're on the first page
Else
CurPage = Request.QueryString("CurPage")
End If

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=MyDB"

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

'设置游标属性
rs.CursorLocation = adUseClient

'设置缓存大小 = 每页需显示的记录数目
rs.CacheSize = NumPerPag

Dim strSQL
strSQL = "SELECT Name,Salary FROM Employee ORDER BY Name"
rs.Open strSQL, Conn

rs.MoveFirst
rs.PageSize = NumPerPage

'计算最多有多少页
Dim TotalPages
TotalPages = rs.PageCount

rs.AbsolutePage = CurPage

Dim count
%>

<HTML>
<BODY>
<B>姓名 - 薪水</B><BR>
<%
Count = 0
Do While Not rs.EOF And Count < rs.PageSize
Response.Write(rs("Name") & " - " & rs("Salary") & "<BR>")
Count = Count + 1
rs.MoveNext
Loop

Response.Write("第 " & CurPage & " 页，共 " & TotalPages & "页<P>")

'显示 Next / Prev 按钮
if CurPage > 1 then
Response.Write("<INPUT TYPE=BUTTON VALUE=PREV
ONCLICK=""document.location.href='thisfile.asp?curpage=" & curpage - 1
& "';"">")
End If

if CInt(CurPage) <> CInt(TotalPages) then
Response.Write("<INPUT TYPE=BUTTON VALUE=NEXT
ONCLICK=""document.location.href='thisfile.asp?curpage=" & curpage + 1
& "';"">")
End If

%>

</BODY>
</HTML>
select distinct au_lname from authors where aulname="jim"
distinct 是取出完全符合条件的记录,有多少条记录就取多少条记录,而top只是取出符合条件的前多少条记录

<script language="JavaScript" type="text/JavaScript">

</script>

<script language="javascript" type="text/javascript">

</script>

<%@ language="vbscript" %>
<% option explicit %>

<%
const NumPerPage=10
dim CurPage
if Request.Querystring("curpage")="" then
curpage=1
else
curpage=request.querystring("curpage")
end if

dim conn
set conn=server.createobject("adodb.connection")
conn.open "DSN=mydb"

dim rs
set rs=server.createobject("adodb.recordset")

rs.cursorlacation=aduseclient
rs.cachesize=numperage
dim strsql
strsql="select name,salary from employee orderby name"
rs.open strsql,conn

rs.movefirst
rs.pagesize=numperpage

dim totalpages
totalpages=rs.pagecount
rs.absolutepage=curpage
dim count
%>

#6

我想到的办法是,
1 建立适当索引
2 尽量缩小查寻范围
3 利用存储过程
4 sql语句用select top 嵌套
5 分表查询

赞同.....select不要用*

#7

1500w条记录，就楼上这个方法优化远远不够
最好是分区分表查询

#8

分页，加上合理的设计。

#9

楼上不是废话嘛

别人就是在问怎么才算合理设计的啊

#10

mycfsq 给的分页程序肯定不行,在有10左右的数据量时候你的分页程序就不行了,明显减慢

#11

还是用存储过程来分页吧~~

#12

我菜鸟，临时写的一点代码，不知道行不行
const intPageSize=20
const intPageCount=10
'预定义部分放到inc文件里面
'在进入分页页面前初始化数据

<%
intID = session("intID")
tempPagei=request.Form("pagei") 'pagei是表单提交过来的分页数。

intID = (pagei-(pagei mod intPageCount)) * intPageSize * intPageCount

strRecordNum = cstr(intPageSize*intPageCount)
strSQL="select top " & strRecordNum & " from table1 where id >= " & intID & "order by id" '
set rs = Server.CreateObject("adodb.recordset")
rs.pagesize=intPageSize
rs.absolutepage = pagei mod intPageCount
%>

其中缺少大量的判断。我也只能想到这个办法了。不知道会不会加快速度
数据库我不太懂。尤其缺乏大型数据库经验。希望抛砖引玉吧。

#13

楼上的肯定不行
只要用ado自带的pagesize阿reocrdcount阿什么的都不行

#14

如果只是做输出用的话，那么可以用cookie记录recordcount，pagesize什么的用计算的，速度还可以。
40W也就50ms左右
有一个遗憾不能解决条件搜索

#15

另外，很好奇你的1500W记录是记啥的？^_^

#16

我也好奇，都1500W了为什么不用oracle？应该是个很牛的项目，还有就是你们没有专门的数据库管理和设计人员？

#17

数据量太大，尽量把数据的处理留用数据库系统做，ASP只用从ADO返回的小量内容

#18

建议把网站关闭：）哈哈

我觉得最主要的是好服务器
有那么大的数据量你应该不缺钱买个好的服务器吧

#19

现在已经用存储过程或select top 两种方法基本解决,不过发现一个新问题,就是如果要跳转的页码太大的话就会超时
比如,我设置每页显示20条,然后跳转到180000条,结果就会有问题超时
郁闷

#20

纠正,然后跳转到180000页

#21

同意liujun999999的方法

1500w条记录，就楼上这个方法优化远远不够
最好是分区分表查询

#22

下个叶子的分页~~~~~

#23

在存储过程中写上一个分页程序，还有就是查询语句尽量不要用in之类的函数！

#24

适当建些View吧.

ps:
1500也不算多,不过数据量真的巨大就不要用ms的啦.

#25

有两种方法并用:
1.用索引来优化系统性能
2.在分页显示的时候你不要一下子把数据里面的记录一下子全部显示出来可以先取出前面几百条,进行分页显示,对接下来的数据再进行超级连接,再用单独的一个页面来显示另外下面的几页,下面的依次类推.因为数据集一旦取出所以的记录的话,哪它就全部都占在内容中,所以会影响机器显示速度的.而你如果用我上面的这种方法,则可以避免这些问题了,一次性只有很少的记录在内容中速度自然就快啦!

#26

不要用存储过程或者视图，在我的实际使用上没有任何使用价格，1500万数据不多的，问题是你不要联合查询，就是JOIN啊，select a.*,b.* from tablea a tableb之类的，可能的话把联合查询的部分FSO成静态文件以平面式的数组保存，数据拿出来的时候直接就把数组的INDEX写上去，就是需要联合查询的数据了；再就是不要用select *，把字段值写上去。上面都做完之后，不会太慢的

至于分页，第一页、中间的跳页、最后一页，不能用TOP，不然拿不出来总数
然后下一页的连接，可以用TOP，并且把起始ID写上去（就是当页最后的ID，例如ID>123）
上一页的话可以ID<123 ORDER BY ID DESC，然后把游标移动最后，把MOVENEXT和MOVEPREVIOUS调转

可能你会不明白的，可以QQ找我聊聊：1726158，我给你一个类

#27

在做據庫中做索引吧，，

#28

标记

#29

晕了已经

#30

“然后把游标移动最后”游标是真正的性能杀手~~~

建议分库分表~~~1500W分十个表，每个表也就150W~~~~用邹建大哥的那个分页存储过程绰绰有余~~

#31

mark

#32

顶，，，好大的数呀

#33

贴一个我在用的存储过程，是这里的一位高人写的，谁我也想不大起来，速度不错，但会生成三个记录集，运行以后next两次才能得到正确的记录集，可能仅仅这点不大好

CREATE procedure p_splitpage
@sql nvarchar(4000), --要执行的sql语句
@currentpage int=2, --要显示的页码
@pagesize int=10, --每页的大小
@pagecount int=0 output --总页数
as
set nocount on
declare @p1 int

exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output

select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@currentpage=(@currentpage-1)*@pagesize+1
select @currentpage
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
GO

#34

1 建立适当索引
2 尽量缩小查寻范围
3 利用存储过程
4 sql语句用select top 嵌套
5 分表查询

赞同.....select不要用*

把分页的作成DLL 文件,效率提高200倍....
我有现成的DLL分页VB代码和已生成的组件,要的话传你一份,调用就2句代码.

#35

我收藏了一个，现在一直用这个觉得速度很不错。

CREATE PROCEDURE pageX

@tblName   varchar(255),       -- 表名

@strGetFields varchar(1000) = '*',  -- 需要返回的列

@fldName varchar(255)='',      -- 排序的字段名

@PageSize   int = 10,          -- 页尺寸

@PageIndex  int = 1,           -- 页码

@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回

@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序

@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)       -- 主语句

declare @strTmp   varchar(110)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型

if @doCount != 0

  begin

    if @strWhere !=''

    set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

    else

    set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

if @OrderType != 0

begin

    set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

--如果@OrderType不是0，就执行降序，这句很重要！

end

else

begin

    set @strTmp = ">(select max"

    set @strOrder = " order by [" + @fldName +"] asc"

end

if @PageIndex = 1

begin

    if @strWhere != ''

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from [" + @tblName + "] where " + @strWhere + " " + @strOrder

     else

     set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["+ @tblName + "] "+ @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["

    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

if @strWhere != ''

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["

        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["

        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

end

end
print @strSql
exec (@strSQL)
GO

#1

#2