在考虑到性能的大表中进行迭代

If I need to check an entire table to see if a condition is true (e.g. every Ticket column has an ID with a certain suffix).

如果我需要检查整个表以查看条件是否为真(例如,每个Ticket列都有一个带有特定后缀的ID)。

What would be a good way of going about this, performance wise? The table is quite large so if I go through every row, that's a lot of time hitting the database. Cursors are slow, so that wouldn't really be an elegant solution

表现明智的方法是什么?该表非常大,所以如果我遍历每一行,那么很多时候都会访问数据库。游标很慢,所以这不是一个优雅的解决方案

Also, for the future, you can always validate your parameters, but this is not the case in the past with this scenario.

此外,对于将来,您始终可以验证您的参数,但过去情况并非如此。

7 个解决方案

#1

pseudo sql (works on Oracle, syntax for other RDBMS might vary)

伪sql(适用于Oracle,其他RDBMS的语法可能有所不同)

 select * from tab where col1 not like '%suffix'

This will give you all rows that don't have your required suffix.

这将为您提供没有所需后缀的所有行。

#2

What are you trying to do with these rows?

你想用这些行做什么?

If just:

SELECT COUNT(*)
FROM tbl
WHERE col NOT LIKE '%suffix'

This could be a table scan or an index scan.

这可以是表扫描或索引扫描。

It's still just one call to the DB and it returns a single row at most. The DB is going to do the work quicker than any alternative.

它仍然只是对数据库的一次调用,它最多返回一行。数据库将比任何替代方案更快地完成工作。

If your DB is changing and you need to be able to manage this criteria regularly and are willing to tradeoff a little space and processing during INSERTs and UPDATEs, you could use a persisted computed column:

如果您的数据库正在更改,并且您需要能够定期管理此条件并且愿意在INSERT和UPDATE期间权衡一点空间和处理,则可以使用持久计算列:

CASE WHERE col LIKE '%suffix' THEN 1 ELSE 0 END

and create an index on that.

并在其上创建一个索引。

ALTER TABLE tbl
ADD COLUMN IsSuffix AS (
    CASE WHERE col LIKE '%suffix' THEN 1 ELSE 0 END
) PERSISTED NOT NULL

Then CREATE INDEX on that column.

然后在该列上创建INDEX。

#3

if you do this all the time, create a computed column on REVERSE(YourColumn), and add an index:

如果你一直这样做,在REVERSE(YourColumn)上创建一个计算列,并添加一个索引:

ALTER TABLE dbo.YourTable ADD
    ReverseColumn  AS REVERSE(YourColumn)
GO
CREATE NONCLUSTERED INDEX IX_YourTable_ReverseColumn ON dbo.YourTable 
    (
    ReverseColumn
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

use it this way:

以这种方式使用它:

DECLARE @Suffix  varchar(10)
SET @Suffix='abc'

SET @Suffix=REVERSE(@Suffix)+'%'
SELECT * FROM YourTable where ReverseColumn LIKE @Suffix

#4

A well-designed database query against an table (one with an index would be even faster) would be most efficient and far better than trying iterating through each row or retrieving the entire table. The SQL 'like' operator would do what you describe.

针对表(一个索引更快)的精心设计的数据库查询将是最有效的,并且比尝试迭代每行或检索整个表要好得多。 SQL'like'运算符将执行您描述的操作。

#5

I'm not sure if you're looking for a suggestion in SQL or C#. If you want something in C# with LINQ you can do this

我不确定你是否在SQL或C#中寻找建议。如果你想在C#中使用LINQ,你就可以做到这一点

pTable.Any(pRow => pRow.Column == someValue)

#6

Looking at the SQL that @Vasu Balakrishnan's solution produces, if all you want to know is if there are any invalid rows, you can do something like this:

看看@Vasu Balakrishnan的解决方案产生的SQL,如果您只想知道是否存在任何无效行,您可以执行以下操作:

SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [Empty]
            FROM [Ticket]
            WHERE [ID] NOT LIKE '%_SUFFIX'
            ) THEN 0
        ELSE 1
     END) AS [AllColumnsAreValid]

#7

Regarding performance and matching a suffix with the LIKE operator - this will be relatively slow since suffix matching cannot use an index.

关于性能并将后缀与LIKE运算符匹配 - 这将相对较慢,因为后缀匹配不能使用索引。

If you need to do that often, modify the table to contain a field that has your TickedID string in reverse and add an index to it. For example, you could add a trigger that does that AFTER INSERT. It is also possible to put an index on a calculated column.

如果您需要经常这样做,请修改表以包含反向具有TickedID字符串的字段并为其添加索引。例如,您可以添加一个执行该操作的触发器。也可以在计算列上放置索引。

SELECT * FROM tab WHERE TicketIDReverse LIKE REVERSE('%suffix')

matches a prefix, actually, so it can use an index and should perform faster than:

实际上匹配一个前缀,所以它可以使用一个索引,并且执行速度应该比:

SELECT * FROM tab WHERE TicketID LIKE '%suffix'

#1