如何提高性能SELECT TOP n * FROM [tablename] ORDER BY NEWID()

[loncyaygc]

想从数据库随机获得一条记录
在网上查了一下全都是这个方法
但性能太差 千万级的表要20秒以上
如何提高性能
SELECT TOP 1 * FROM [tablename] ORDER BY NEWID()

[soyaku]

学习 中

[解释多余的]

路过，学习了

[dkmuss]

学习 研究用用

[Andy-Xiejf]

学习... O(∩_∩)O哈哈~

[jackingod]

强帖， 顶上去。

[w_head]

,,,,,,,,,

[情殇无限]

再来顶下

[hfrll]

.........................

[yeehu1984]

悲剧，现在还不懂聚集索引是什么，哪位大侠顺便说下啊

[ycl1314]

每天回帖即可获得10分可用分！小技巧：教您如何更快获得可用分

[程序园苑]

luguo,学习

[w360826]

貌似这东西不错

[Jermaine]

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

[Jermaine]

如果不想对全表进行顺序扫描的话，也可以对num_value做index以后执行下列语句，注意如果有index的话，查询max是constant time。

SELECT * FROM table 
WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table) 
LIMIT 1；

[HsuChine]

学习 看各位高手解答

[华芸智森]

DECLARE @TMPTABLE TABLE ( ID INT)
DECLARE @IntID INT;
DECLARE @IntRowCount INT;

SET @IntID=0
SELECT @IntRowCount=COUNT(*) FROM Tablename
WHILE @IntID<=5 BEGIN
INSERT INTO @TMPTABLE VALUES(RAND()* @IntRowCount)
SET @IntID=@IntID+1
END

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY 主键) AS RowNumber,* FROM Pro_Tablename
) AS T WHERE RowNumber IN (SELECT ID FROM @TMPTABLE)

[华芸智森]

DECLARE @TMPTABLE TABLE ( ID INT NOT NULL)
DECLARE @IntID INT;
DECLARE @IntRowCount INT;

SET @IntID=0
SELECT @IntRowCount=COUNT(*) FROM Pro_PriceSet
WHILE @IntID<=5 BEGIN
	INSERT INTO @TMPTABLE VALUES(RAND()* @IntRowCount)  
	SET @IntID=@IntID+1
END

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ProNum) AS RowNumber,* FROM Pro_PriceSet 
) AS T WHERE RowNumber IN (SELECT TOP 5 ID FROM @TMPTABLE ORDER BY ID)

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ProNum) AS RowNumber,* FROM Pro_PriceSet 
) AS T WHERE RowNumber IN (SELECT TOP 5 ID FROM @TMPTABLE ORDER BY ID)

[linguangfei2007]

路过,路过,学习

[wjlsmail]

1、先用 random() 族函数取id的随机数；
2、再按id取记录。（id字段有索引。）

[qddoit]

3楼的好用，高人~