很郁闷的问题,用网通、电信查询使用时速度还说得过去,可使用铁通时一分钟有时都出不来,可直接读取列表时速度也比较快的,打开某个页面时也比较快的,就是查询时太慢了,下面我把部分代码贴出来,请各位帮我看看有哪里需要修改的地方,数据也就两三千条 string qy, wy, hx, ars, arb, zjs, zjb, bh, sub, tel, pass, orzj, ortj, oragent, orhz, deldate;
qy = safecode.htmlencode(Request.QueryString["qy"]);
wy = safecode.htmlencode(Request.QueryString["wy"]);
hx = safecode.htmlencode(Request.QueryString["hx"]);
ars = safecode.htmlencode(Request.QueryString["ars"]);
arb = safecode.htmlencode(Request.QueryString["arb"]);
zjs = safecode.htmlencode(Request.QueryString["zjs"]);
zjb = safecode.htmlencode(Request.QueryString["zjb"]);
bh = safecode.htmlencode(Server.UrlDecode(Request.QueryString["bh"]));
sub = safecode.htmlencode(Server.UrlDecode(Request.QueryString["subject"]));
tel = safecode.htmlencode(Server.UrlDecode(Request.QueryString["tel"]));
pass = safecode.htmlencode(Request.QueryString["pass"]);
orzj = safecode.htmlencode(Request.QueryString["orzj"]);
ortj = safecode.htmlencode(Request.QueryString["ortj"]);
oragent = safecode.htmlencode(Request.QueryString["oragent"]);
orhz = safecode.htmlencode(Request.QueryString["orhz"]);
deldate = safecode.htmlencode(Request.QueryString["deldate"]);
if (!(safecode.chkstr(qy) & safecode.chkstr(wy) & safecode.chkstr(hx) & safecode.chkstr(pass) & safecode.chkstr(orzj) & safecode.chkstr(ortj) & safecode.chkstr(oragent) & safecode.chkstr(orhz) & safecode.chkstr(deldate)))
{
safecode.chkalert("传递数据类型不对,请检查重试!", 3);
Response.End();
}
if (!(safecode.chkstr2(ars) & safecode.chkstr2(arb) & safecode.chkstr2(zjs) & safecode.chkstr2(zjb)))
{
safecode.chkalert("传递数据类型不对,请检查重试!", 3);
Response.End();
}
sqlstr = "Select count(*) From news where 1=1";
sqlstr2 = "Select * From news where 1=1";
if (qy != null && qy.Length != 0)
{
sqlstr += " and hareaid=" + qy;
sqlstr2 += " and hareaid=" + qy;
}
if (wy != null && wy.Length != 0)
{
sqlstr += " and hwylxid=" + wy;
sqlstr2 += " and hwylxid=" + wy;
}
if (hx != null && hx.Length != 0)
{
sqlstr += " and hhxid=" + hx;
sqlstr2 += " and hhxid=" + hx;
}
if (ars != null && ars.Length != 0)
{
sqlstr += " and hjz_area>=" + ars;
sqlstr2 += " and hjz_area>=" + ars;
}
if (arb != null && arb.Length != 0)
{
sqlstr += " and hjz_area <=" + arb;
sqlstr2 += " and hjz_area <=" + arb;
}
if (zjs != null && zjs.Length != 0)
{
sqlstr += " and hpay>=" + zjs;
sqlstr2 += " and hpay>=" + zjs;
}
if (zjb != null && zjb.Length != 0)
{
sqlstr += " and hpay <=" + zjb;
sqlstr2 += " and hpay <=" + zjb;
}
if (bh != null && bh.Length != 0)
{
sqlstr += " and hbh like '%" + bh + "%'";
sqlstr2 += " and hbh like '%" + bh + "%'";
}
if (sub != null && sub.Length != 0)
{
sqlstr += " and hwyname like '%" + sub + "%'";
sqlstr2 += " and hwyname like '%" + sub + "%'";
}
if (tel != null && tel.Length != 0)
{
sqlstr += " and htel like '%" + tel + "%'";
sqlstr2 += " and htel like '%" + tel + "%'";
}
if (pass != null && pass.Length != 0)
{
sqlstr += " and hpass=" + pass;
sqlstr2 += " and hpass=" + pass;
}
if (orzj != null && orzj.Length != 0)
{
sqlstr += " and horzj=" + orzj;
sqlstr2 += " and horzj=" + orzj;
}
if (ortj != null && ortj.Length != 0)
{
sqlstr += " and hortj=" + ortj;
sqlstr2 += " and hortj=" + ortj;
}
if (oragent != null && oragent.Length != 0)
{
sqlstr += " and horagent1=" + oragent;
sqlstr2 += " and horagent1=" + oragent;
}
if (orhz != null && orhz.Length != 0)
{
sqlstr += " and horhz=" + orhz;
sqlstr2 += " and horhz=" + orhz;
}
if (deldate != null && deldate.Length != 0)
{
if (deldate == "1")
{
sqlstr += " and datediff(dd,hadd_date+hdel_date,getdate())>7";
sqlstr2 += " and datediff(dd,hadd_date+hdel_date,getdate())>7";
}
else
{
sqlstr += " and datediff(dd,hadd_date+hdel_date,getdate()) <7";
sqlstr2 += " and datediff(dd,hadd_date+hdel_date,getdate()) <7";
}
}
sqlstr2 += " order by hadd_date desc,hid desc";
qy = safecode.htmlencode(Request.QueryString["qy"]);
wy = safecode.htmlencode(Request.QueryString["wy"]);
hx = safecode.htmlencode(Request.QueryString["hx"]);
ars = safecode.htmlencode(Request.QueryString["ars"]);
arb = safecode.htmlencode(Request.QueryString["arb"]);
zjs = safecode.htmlencode(Request.QueryString["zjs"]);
zjb = safecode.htmlencode(Request.QueryString["zjb"]);
bh = safecode.htmlencode(Server.UrlDecode(Request.QueryString["bh"]));
sub = safecode.htmlencode(Server.UrlDecode(Request.QueryString["subject"]));
tel = safecode.htmlencode(Server.UrlDecode(Request.QueryString["tel"]));
pass = safecode.htmlencode(Request.QueryString["pass"]);
orzj = safecode.htmlencode(Request.QueryString["orzj"]);
ortj = safecode.htmlencode(Request.QueryString["ortj"]);
oragent = safecode.htmlencode(Request.QueryString["oragent"]);
orhz = safecode.htmlencode(Request.QueryString["orhz"]);
deldate = safecode.htmlencode(Request.QueryString["deldate"]);
if (!(safecode.chkstr(qy) & safecode.chkstr(wy) & safecode.chkstr(hx) & safecode.chkstr(pass) & safecode.chkstr(orzj) & safecode.chkstr(ortj) & safecode.chkstr(oragent) & safecode.chkstr(orhz) & safecode.chkstr(deldate)))
{
safecode.chkalert("传递数据类型不对,请检查重试!", 3);
Response.End();
}
if (!(safecode.chkstr2(ars) & safecode.chkstr2(arb) & safecode.chkstr2(zjs) & safecode.chkstr2(zjb)))
{
safecode.chkalert("传递数据类型不对,请检查重试!", 3);
Response.End();
}
sqlstr = "Select count(*) From news where 1=1";
sqlstr2 = "Select * From news where 1=1";
if (qy != null && qy.Length != 0)
{
sqlstr += " and hareaid=" + qy;
sqlstr2 += " and hareaid=" + qy;
}
if (wy != null && wy.Length != 0)
{
sqlstr += " and hwylxid=" + wy;
sqlstr2 += " and hwylxid=" + wy;
}
if (hx != null && hx.Length != 0)
{
sqlstr += " and hhxid=" + hx;
sqlstr2 += " and hhxid=" + hx;
}
if (ars != null && ars.Length != 0)
{
sqlstr += " and hjz_area>=" + ars;
sqlstr2 += " and hjz_area>=" + ars;
}
if (arb != null && arb.Length != 0)
{
sqlstr += " and hjz_area <=" + arb;
sqlstr2 += " and hjz_area <=" + arb;
}
if (zjs != null && zjs.Length != 0)
{
sqlstr += " and hpay>=" + zjs;
sqlstr2 += " and hpay>=" + zjs;
}
if (zjb != null && zjb.Length != 0)
{
sqlstr += " and hpay <=" + zjb;
sqlstr2 += " and hpay <=" + zjb;
}
if (bh != null && bh.Length != 0)
{
sqlstr += " and hbh like '%" + bh + "%'";
sqlstr2 += " and hbh like '%" + bh + "%'";
}
if (sub != null && sub.Length != 0)
{
sqlstr += " and hwyname like '%" + sub + "%'";
sqlstr2 += " and hwyname like '%" + sub + "%'";
}
if (tel != null && tel.Length != 0)
{
sqlstr += " and htel like '%" + tel + "%'";
sqlstr2 += " and htel like '%" + tel + "%'";
}
if (pass != null && pass.Length != 0)
{
sqlstr += " and hpass=" + pass;
sqlstr2 += " and hpass=" + pass;
}
if (orzj != null && orzj.Length != 0)
{
sqlstr += " and horzj=" + orzj;
sqlstr2 += " and horzj=" + orzj;
}
if (ortj != null && ortj.Length != 0)
{
sqlstr += " and hortj=" + ortj;
sqlstr2 += " and hortj=" + ortj;
}
if (oragent != null && oragent.Length != 0)
{
sqlstr += " and horagent1=" + oragent;
sqlstr2 += " and horagent1=" + oragent;
}
if (orhz != null && orhz.Length != 0)
{
sqlstr += " and horhz=" + orhz;
sqlstr2 += " and horhz=" + orhz;
}
if (deldate != null && deldate.Length != 0)
{
if (deldate == "1")
{
sqlstr += " and datediff(dd,hadd_date+hdel_date,getdate())>7";
sqlstr2 += " and datediff(dd,hadd_date+hdel_date,getdate())>7";
}
else
{
sqlstr += " and datediff(dd,hadd_date+hdel_date,getdate()) <7";
sqlstr2 += " and datediff(dd,hadd_date+hdel_date,getdate()) <7";
}
}
sqlstr2 += " order by hadd_date desc,hid desc";
1、测试url字符串:保持现在的url,但是实际上只提交"Select * From news",如果故障依旧就不是查询的问题,而是url参数过长的问题;
2、测试sql字符串:如果故障依旧,写个存储过程放在服务器端,存储过程不需要输入参数,里面就执行
SELECT * FROM news WHERE …… AND …… AND …… AND (省略n个and)
然后在客户端调用这个存储过程,记着把url去掉;
3、如果还是故障依旧,那就是查询自身的问题了