Question

我需要从桌旁随机抽走。首先,我写了这样的“特殊”:

SELECT * FROM 
    (SELECT TOP 1 
     *
     FROM PeopleTales PT
     LEFT JOIN PeopleTalesCategories PTC ON PT.CategoryAn = PTC.Analit
     WHERE LEN(PT.Text) > 900
     ORDER BY NEWID()
    ) t1

这在时间统计方面并不很好:

www.un.org/Depts/DGACM/index_spanish.htm 平均执行时间=200。

其次,我尝试手工操作:

WITH CTE_t
AS
(
        SELECT
        ROW_NUMBER () OVER (ORDER BY PT.Analit) AS RowNumber,
        *
        FROM PeopleTales PT
        LEFT JOIN PeopleTalesCategories PTC ON PT.CategoryAn = PTC.Analit
        WHERE LEN(PT.Text) > 900
)


SELECT * FROM CTE_t
WHERE CTE_t.RowNumber = 1 + ABS(CHECKSUM(NewId())) % (SELECT COUNT(CTE_t.RowNumber) FROM CTE_t)

处决时间越好,但有时甚至有零增长,有时有1 2或甚至3个增长。 i 知道,使用CTE isn t非常好。它不作决定。

www.un.org/Depts/DGACM/index_spanish.htm 平均执行时间=60

因此,下一步是:

SELECT * FROM (
    SELECT
        ROW_NUMBER () OVER (ORDER BY PT.Analit) AS RowNumber,
        *
    FROM PeopleTales PT
    LEFT JOIN PeopleTalesCategories PTC ON PT.CategoryAn = PTC.Analit
    WHERE LEN(PT.Text) > 900
    ) t1
WHERE t1.RowNumber = (SELECT 1 + ABS(CHECKSUM(NewId())) % (SELECT 
                                                            COUNT(Analit) 
                                                            FROM PeopleTales
                                                            WHERE LEN(Text) > 900))

任何时候,我只剩下一行,而且全时执行。

www.un.org/Depts/DGACM/index_spanish.htm 平均执行时间=60

因此,是否有更多的办法随意浏览或优化我的询问?

答复时间。

Answer 1

How many rows are in the table, and is it possible to modify your schema? One problem is that all the selects are based on LEN(Column), so SQL Server can t use an index effectively.

What you might want to consider is a computed column, and placing an index on it. Something like:

ALTER TABLE PeopleTales ADD TextLength AS LEN(PT.Text)
GO
CREATE INDEX IX_PeopleTales_TextLen ON PeopleTales (Text, CategoryAN)
GO

页: 1 因此,合并完全通过指数实现(这意味着服务器确实必须做笔记或钥匙考查)。

然后,显然改变你的询问,以便<代码> WHERE LEN(PT.Text) > 900 改为。

友情链接