一个数十万条近百万条记录的数据库的查询，高分求最最优化的查询方案。
感觉下面这个方法查询IP地区，真的是太慢太慢了，请高人指点最优查询方案吧。--------------------编程问答-------------------- 主要是数据库 的查询 问题
Public Function address(sip)
Dim aConnStr,aConn,adb
Dim str1,str2,str3,str4
Dim num
Dim country,city
Dim irs,SQL
If IsNumeric(Left(sip,2)) Then
If sip="127.0.0.1" Then sip="192.168.0.1"
str1=Left(sip,InStr(sip,".")-1)
sip=mid(sip,instr(sip,".")+1)
str2=Left(sip,instr(sip,".")-1)
sip=Mid(sip,InStr(sip,".")+1)
str3=Left(sip,instr(sip,".")-1)
str4=Mid(sip,instr(sip,".")+1)
If isNumeric(str1)=0 or isNumeric(str2)=0 or isNumeric(str3)=0 or isNumeric(str4)=0 Then
Else
num=CLng(str1)*16777216+CLng(str2)*65536+CLng(str3)*256+CLng(str4)-1
adb = "ip.mdb"
aConnStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(adb)
Set AConn = Server.CreateObject("ADODB.Connection")
aConn.Open aConnStr
sql="select * from ip where startip <="&num&" and endip >="&num&""
Set irs=aConn.execute(sql)
If irs.EOF And irs.bof Then
local="尚未收录"
Else
local=irs("country")&irs("local")
End If
Set irs=Nothing
Set aConn = Nothing
SqlQueryNum = SqlQueryNum+1
End If
address=local
Else
address="未知"
End If
End Function
这里给你2种解决方法
第一种 用 索引
第二种 用分页查找
具体方法见 百度~
--------------------编程问答-------------------- 太慢太慢了。 --------------------编程问答--------------------
--> liangCK小梁 于2008-09-20--------------------编程问答-------------------- 我也有同样的问题，试试这两种方法 --------------------编程问答--------------------
--> 生成测试数据: [tableip]
if object_id('[tableip]') is not null drop table [tableip]
create table [tableip] (id int,startip varchar(15),endip varchar(15),address nvarchar(6))
insert into [tableip]
select 1,'024.089.048.000','024.089.063.255','ARIN' union all
select 2,'024.089.064.000','024.089.127.255','加拿大' union all
select 3,'024.089.128.000','024.089.191.255','美国' union all
select 4,'024.089.192.000','024.089.255.255','加拿大' union all
select 5,'024.090.000.000','024.100.063.255','美国' union all
select 6,'024.100.064.000','024.103.255.255','ARIN' union all
select 7,'024.104.000.000','024.104.159.255','美国' union all
select 8,'024.104.160.000','024.104.255.255','ARIN'
--SQL查询如下:
go
--这个函数不用改.直接搬着来用.
CREATE FUNCTION dbo.f_IP2Int(
@ip varchar(15)
)RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re=0
SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
FROM(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)a
RETURN(@re)
END
GO
--要查的IP
DECLARE @ip VARCHAR(15);
SET @ip='24.93.121.22';
SELECT *
FROM [tableip]
WHERE dbo.f_IP2Int(@ip)>=dbo.f_IP2Int(startip)
and dbo.f_IP2Int(@ip)<=dbo.f_IP2Int(endip) ;
GO
--删除测试表,函数.
DROP TABLE [tableip]
DROP FUNCTION dbo.f_IP2Int
id startip endip address
----------- --------------- --------------- -------
5 024.090.000.000 024.100.063.255 美国
(1 行受影响)
你的程序应该不是ASP来的吧，运行时很多错误的。 --------------------编程问答-------------------- 上面是SQL的函数,你在ASP中直接调用这个函数就可以了. --------------------编程问答--------------------
你测试没出错？奇怪。 --------------------编程问答--------------------
CREATE FUNCTION dbo.f_IP2Int(
@ip varchar(15)
)RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re=0
SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
FROM(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)a
RETURN(@re)
END
错误类型：
Microsoft VBScript 编译器错误 (0x800A03EA)
语法错误
/shangchuanheluru/cs.asp, line 2, column 7
CREATE FUNCTION dbo.f_IP2Int(
------^
--------------------编程问答-------------------- 下面的方法超好超快，百万条记录查起来只需0.04秒左右。但我不会改，加个and ip2<xxxxxxx它就出错。希望能见到真正的高手到这里来，帮忙删除这里面分页的那些代码，以及改成最简查询IP地区的代码，无限感谢。
--------------------编程问答-------------------- 没有最优，只有更优 --------------------编程问答-------------------- --------------------编程问答-------------------- 上面的方法超好超快，百万条记录查起来只需0.04秒左右。但我不会改，加个and ip2<xxxxxxx它就出错。希望能见到真正的高手到这里来，帮忙删除这里面分页的那些代码，以及改成最简查询IP地区的代码，无限感谢。 --------------------编程问答-------------------- 看见你select * from xxx
<%@language = "vbscript" codepage="936"%>
<%option explicit%>
<%
dim intdatestart
intdatestart = timer()
rem ## 打开数据库连接
rem #################################################################
function f__openconn()
dim strdbpath
dim connstr
strdbpath = "text.mdb"
connstr = "provider=microsoft.jet.oledb.4.0;data source="
connstr = connstr & server.mappath(strdbpath)
set conn = server.createobject("adodb.connection")
conn.open connstr
end function
rem #################################################################
rem ## 关闭数据库连接
rem #################################################################
function f__closeconn()
if isobject(conn) then
conn.close
end if
set conn = nothing
end function
rem #################################################################
rem 获得执行时间
rem #################################################################
function gettimeover(iflag)
dim ttimeover
if iflag = 1 then
ttimeover = formatnumber(timer() - intdatestart, 6, true)
gettimeover = " 执行时间: " & ttimeover & " 秒"
else
ttimeover = formatnumber((timer() - intdatestart) * 1000, 3, true)
gettimeover = " 执行时间: " & ttimeover & " 毫秒"
end if
end function
rem #################################################################
class cls_pageview
private sbooinitstate
private sstrcookiesname
private sstrpageurl
private sstrpagevar
private sstrtablename
private sstrfieldslist
private sstrcondiction
private sstrorderlist
private sstrprimarykey
private sintrefresh
private sintrecordcount
private sintpagesize
private sintpagenow
private sintpagemax
private sobjconn
private sstrpageinfo
private sub class_initialize
call clearvars()
end sub
private sub class_terminate()
set sobjconn = nothing
end sub
public sub clearvars()
sbooinitstate = false
sstrcookiesname = ""
sstrpageurl = ""
sstrpagevar = "page"
sstrtablename = ""
sstrfieldslist = ""
sstrcondiction = ""
sstrorderlist = ""
sstrprimarykey = ""
sintrefresh = 0
sintrecordcount = 0
sintpagesize = 0
sintpagenow = 0
sintpagemax = 0
end sub
rem ## 保存记录数的 cookies 变量
public property let strcookiesname(value)
sstrcookiesname = value
end property
rem ## 转向地址
public property let strpageurl(value)
sstrpageurl = value
end property
rem ## 表名
public property let strtablename(value)
sstrtablename = value
end property
rem ## 字段列表
public property let strfieldslist(value)
sstrfieldslist = value
end property
rem ## 查询条件
public property let strcondiction(value)
if value <> "" then
sstrcondiction = " where " & value
else
sstrcondiction = ""
end if
end property
rem ## 排序字段, 如: [id] asc, [createdatetime] desc
public property let strorderlist(value)
if value <> "" then
sstrorderlist = " order by " & value
else
sstrorderlist = ""
end if
end property
rem ## 用于统计记录数的字段
public property let strprimarykey(value)
sstrprimarykey = value
end property
rem ## 每页显示的记录条数
public property let intpagesize(value)
sintpagesize = tonum(value, 20)
end property
rem ## 数据库连接对象
public property let objconn(value)
set sobjconn = value
end property
rem ## 当前页
public property let intpagenow(value)
sintpagenow = tonum(value, 1)
end property
rem ## 页面参数
public property let strpagevar(value)
sstrpagevar = value
end property
rem ## 是否刷新. 1 为刷新, 其他值则不刷新
public property let intrefresh(value)
sintrefresh = tonum(value, 0)
end property
rem ## 获得当前页
public property get intpagenow()
intpagenow = singpagenow
end property
rem ## 分页信息
public property get strpageinfo()
strpageinfo = sstrpageinfo
end property
rem ## 取得记录集, 二维数组或字串, 在进行循环输出时必须用 isarray() 判断
public property get arrrecordinfo()
if not sbooinitstate then
exit property
end if
dim rs, sql
sql = "select " & sstrfieldslist & _
" from " & sstrtablename & _
sstrcondiction & _
sstrorderlist
set rs = server.createobject("adodb.recordset")
rs.open sql, sobjconn, 1, 1
if not(rs.eof or rs.bof) then
rs.pagesize = sintpagesize
'rs.absolutepage = sintpagenow
if not(rs.eof or rs.bof) then
arrrecordinfo = rs.getrows(sintpagesize)
else
arrrecordinfo = ""
end if
else
arrrecordinfo = ""
end if
rs.close
set rs = nothing
end property
rem ## 初始化记录数
private sub initrecordcount()
sintrecordcount = 0
if not(sbooinitstate) then exit sub
dim sinttmp
sinttmp = tonum(request.cookies("_xp_" & sstrcookiesname), -1)
if ((sinttmp < 0) or (sintrefresh = 1))then
dim sql, rs
sql = "select count(" & sstrprimarykey & ")" & _
" from " & sstrtablename & _
sstrcondiction
set rs = sobjconn.execute(sql)
if rs.eof or rs.bof then
sinttmp = 0
else
sinttmp = rs(0)
end if
sintrecordcount = sinttmp
response.cookies("_xp_" & sstrcookiesname) = sinttmp
else
sintrecordcount = sinttmp
end if
end sub
rem ## 初始化分页信息
private sub initpageinfo()
sstrpageinfo = ""
if not(sbooinitstate) then exit sub
dim surl
surl = sstrpageurl
if instr(1, surl, "?", 1) > 0 then
surl = surl & "&" & sstrpagevar & "="
else
surl = surl & "?" & sstrpagevar & "="
end if
if sintpagenow <= 0 then sintpagenow = 1
if sintrecordcount mod sintpagesize = 0 then
sintpagemax = sintrecordcount \ sintpagesize
else
sintpagemax = sintrecordcount \ sintpagesize + 1
end if
if sintpagenow > sintpagemax then sintpagenow = sintpagemax
if sintpagenow <= 1 then
sstrpageinfo = "首页 上一页"
else
sstrpageinfo = sstrpageinfo & " <a href=""" & surl & "1"">首页</a>"
sstrpageinfo = sstrpageinfo & " <a href=""" & surl & (sintpagenow - 1) & """>上一页</a>"
end if
if sintpagemax - sintpagenow < 1 then
sstrpageinfo = sstrpageinfo & " 下一页 末页 "
else
sstrpageinfo = sstrpageinfo & " <a href=""" & surl & (sintpagenow + 1) & """>下一页</a> "
sstrpageinfo = sstrpageinfo & " <a href=""" & surl & sintpagemax & """>末页</a> "
end if
sstrpageinfo = sstrpageinfo & " 页次：<strong><font color=""#990000"">" & sintpagenow & "</font> / " & sintpagemax & " </strong>"
sstrpageinfo = sstrpageinfo & " 共 <strong>" & sintrecordcount & "</strong> 条记录 <strong>" & sintpagesize & "</strong> 条/页 "
end sub
rem ## 长整数转换
private function tonum(s, default)
s = s & ""
if s <> "" and isnumeric(s) then
tonum = clng(s)
else
tonum = default
end if
end function
rem ## 类初始化
public sub initclass()
sbooinitstate = true
if not(isobject(sobjconn)) then sbooinitstate = false
call initrecordcount()
call initpageinfo()
end sub
end class
dim strlocalurl
strlocalurl = request.servervariables("script_name")
dim intpagenow
intpagenow = request.querystring("page")
dim intpagesize, strpageinfo
intpagesize = 30
dim arrrecordinfo, i
dim conn
f__openconn
dim clsrecordinfo
set clsrecordinfo = new cls_pageview
clsrecordinfo.strtablename = "[ip]"
clsrecordinfo.strpageurl = strlocalurl
clsrecordinfo.strfieldslist = "[ip1], [ip2], [cs]"
clsrecordinfo.strcondiction = "[ip1]>=3701836888"
clsrecordinfo.strorderlist = "[ip1] asc"
clsrecordinfo.strprimarykey = "[ip1]"
clsrecordinfo.intpagesize = 1
clsrecordinfo.intpagenow = intpagenow
clsrecordinfo.strcookiesname = "recordcount"
clsrecordinfo.strpagevar = "page"
clsrecordinfo.intrefresh = 0
clsrecordinfo.objconn = conn
clsrecordinfo.initclass
arrrecordinfo = clsrecordinfo.arrrecordinfo
strpageinfo = clsrecordinfo.strpageinfo
set clsrecordinfo = nothing
f__closeconn
%>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<title>分页测试</title>
<style type="text/css">
<!--
.pageview {
font-size: 12px;
}
.pageview td {
border-right-style: solid;
border-bottom-style: solid;
border-right-color: #e0e0e0;
border-bottom-color: #e0e0e0;
border-right-width: 1px;
border-bottom-width: 1px;
}
.pageview table {
border-left-style: solid;
border-top-style: solid;
border-left-color: #e0e0e0;
border-top-color: #e0e0e0;
border-top-width: 1px;
border-left-width: 1px;
}
tr.header {
background: #eff7ff;
font-size: 14px;
font-weight: bold;
line-height: 120%;
text-align: center;
}
-->
</style>
<style type="text/css">
<!--
body {
font-size: 12px;
}
a:link {
color: #993300;
text-decoration: none;
}
a:visited {
color: #003366;
text-decoration: none;
}
a:hover {
color: #0066cc;
text-decoration: underline;
}
a:active {
color: #000000;
text-decoration: none;
}
table {
font-size: 12px;
}
-->
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td> <%= strpageinfo%></td>
</tr>
</table>
<div class="pageview">
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr class="header">
<td>id</td>
<td>描述</td>
<td>日期</td>
</tr>
<%
if isarray(arrrecordinfo) then
for i = 0 to ubound(arrrecordinfo, 2)
%>
<tr>
<td> <%= arrrecordinfo(0, i)%></td>
<td> <%= arrrecordinfo(1, i)%></td>
<td> <%= arrrecordinfo(2, i)%></td>
</tr>
<%
next
end if
%>
</table>
</div>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td> <%= strpageinfo%></td>
</tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td align="center"> <%= gettimeover(1)%></td>
</tr>
</table>
</body>
</html>
感觉有点麻烦了....哈哈 --------------------编程问答-------------------- 似乎CSDN真正的高手越来越少？ --------------------编程问答-------------------- --------------------编程问答-------------------- 是的,是这样的
--------------------编程问答-------------------- 你给的那代码速度之所以快是由于它分页来执行的,每次只需要显示呈现给用户看到的数据 --------------------编程问答-------------------- 那查一条数据应该如何简化呢？ --------------------编程问答-------------------- 列得这么详细，肯定有其深刻的道理的吧。若非，where xx=xx就得了啊。 --------------------编程问答-------------------- sql="select * from ip where startip <="&num&" and endip >="&num&""
改成
sql="select top 1 * from ip where startip <="&num&" and endip >="&num&""
--------------------编程问答-------------------- 这种方法不是根本解决的办法吧。将 * 换成各个相关的字段也不是根本的解决办法哦。
--------------------编程问答-------------------- --------------------编程问答-------------------- 上面的方法超好超快，百万条记录查起来只需0.04秒左右。 --------------------编程问答-------------------- 这问题。 --------------------编程问答-------------------- --------------------编程问答-------------------- 他的代码只不过封装的比较好，核心内容也比你多了个分页，打开模式。
你存储的应该是纯数字字段,加上唯一索引试试
SELECT count(1)
FROM IPtable
WHERE IP=value
--------------------编程问答-------------------- 非高手不会，高手是不是不肯指点一下呀？ --------------------编程问答-------------------- 提高速度有两条意见：一、建立索引；二、用command对象 --------------------编程问答-------------------- 可能就是startip <="&num&" and endip >="&num&"" 这样的语句挺耗时间吧。不知道你的ip表怎么做的，start和end具体指什么，一定要通过这样的方法才能得到地区吗，可以尝试尝试其他方法。 --------------------编程问答-------------------- 人家是在服务器端运行的,只打出结果传给你
服务器比你的PC机速度快
你要在服务器端运行的话只有使用存储过程,在服务器端编程,在PC机上调用,传过命令和参数去,让服务器执行,只把结果传给你
--------------------编程问答-------------------- ASP的程序不是在客户端执行的,这是本质上的差别
光看人家快有什么用?
--------------------编程问答--------------------
说的是这个好快。 --------------------编程问答-------------------- 不是算法快，是本机运行
你的客户端是通过网络折腾
机器又不行
--------------------编程问答--------------------
你试过吗？ --------------------编程问答-------------------- 这不是解决的根本办法哦。
补充：VB , API
