Iterating through a large table with performance in mind

Question

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).

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.

Answer 1

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

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

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

Answer 2

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.

Answer 3

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

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

Answer 4

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:

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.

Answer 5

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:

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

Answer 6

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

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.

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'

Answer 7

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

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