MSSQL数据库十万条数据，查询代码要怎么改效率才会提高，有没好人帮我解决一下？

显示全部楼层 · 2011-3-23 01:00:34

代码如下：
MyCity = WRMPS.GetCookies(\"MyCity\")
set rst = conn.execute(\"select wm_id from wm_area where wm_name=\'\"defds123mycitydefds123\"\'\")
if not rst.eof then
MyCityID = rst(0)
else
MyCityID = 0
end if
rst.close

千问 · 2011-3-23 01:00:34

<pre id=\"best-answer-content\" class=\"reply-text mb10\">1、建索引按照二楼，三楼的方法，一般都能解决查询慢的问题。
2、建索引提升的效果也不明显的话，请用存储过程。
CREATE PROCEDURE [dbo].[Search]
@mycity
varchar(20)
AS
DECLARE @MyCityID AS smallint
IF EXISTS(select top 1 wm_id from wm_area where wm_name = @mycity)
BEGIN

select top 1 @MyCityID = wm_id from wm_area where wm_name = @mycity

SELECT @MyCityID
END
ELSE
BEGIN

SELECT 0
END

输出返回结果即可。