Help in the script performance

Question

Hi, The following script is working fine but it takes too much time to execute and provide the result. Average time is more than 1 hr. Can execution time be reduced?

declare @text_to_find as nvarchar(max) = N'TOTS' --Type here to search content

declare @txt as nvarchar(max) = concat('%', replace(replace( replace( @text_to_find, N'[', N'[[]'), N'%', N'[%]'), N'', N'['), N'%')
declare @alenzi as nvarchar(max)
;
with Q as
(
select
concat(N'select ', quotename( concat(quotename(s.name), N'.', quotename(t.name)), N''''),
N' as TableName where exists( select * from ', quotename(s.name), N'.', quotename(t.name), N' where ',
string_agg( concat(cast(quotename(c.name) as nvarchar(max)), N' like @txt'), N' or '), N')' ) as x
from sys.tables t
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.columns c on c.object_id = t.object_id
inner join sys.types y on y.user_type_id = c.user_type_id
where y.name in (N'text', N'ntext', N'char', N'nchar', N'varchar', N'nvarchar', N'sysname', N'xml')
group by s.name, t.name
)
select @alenzi = string_agg(x, N' union all ')
from Q

exec sp_executesql @alenzi , N'@txt nvarchar(max)', @txt = @txt
select @@ROWCOUNT as 'Total tables'

Answer 1

So the purpose is to find a certain string in any table in the database? I've seen these kind of requests before, but I have always been thinking: how did they end up here?

In a well-modelled database, each table models a unique entity and each column models a unique attribute of that entity. Seen from this perspective, this kind of searches are a little funny. Of course, one situation could be that you are new to the database, and have not learnt the model yet. All you know is that they store cucumbers somewhere in the database, and in that case you may want to search for "cucumber" with a very wide net. Then again, those kind of queries should still be rare enough that you can accept that they take some time.

These sort of queries will be slow, since not only does every LIKE require a scan, but SQL Server also needs to scan the full string when you have leading and closing wild cards, and with a Windows collation or nvarchar, this will be quite expensive.

There are two ways to speed this up, though. One is to slap on a bunch of full-text indexes, but that is quite heavy artillery.

Here is a more moderate trick, which still will be slow, but 10-12 faster than with a normal collation:

declare @text_to_find as nvarchar(max) = N'TOTS' --Type here to search content

declare @txt as nvarchar(max) = concat('%', replace(replace( replace( @text_to_find, N'[', N'[[]'), N'%', N'[%]'), N'', N'['), N'%')
declare @sql as nvarchar(max)
;
with Q as
(
select
concat(N'select ', quotename( concat(quotename(s.name), N'.', quotename(t.name)), N''''),
N' as TableName where exists( select * from ', quotename(s.name), N'.', quotename(t.name), N' where ',
string_agg( concat('upper(', cast(quotename(c.name) as nvarchar(max)), N') COLLATE Latin1_General_BIN2 like upper(@txt)'), N' or '), N')' ) as x
from sys.tables t
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.columns c on c.object_id = t.object_id
inner join sys.types y on y.user_type_id = c.user_type_id
where y.name in (N'text', N'ntext', N'char', N'nchar', N'varchar', N'nvarchar', N'sysname', N'xml')
group by s.name, t.name
)
select @sql = string_agg(x, N' union all ')
from Q

PRINT @sql

exec sp_executesql @sql, N'@txt nvarchar(max)', @txt = @txt
select @@ROWCOUNT as 'Total tables'

The trick is to cast the search column to a binary collation, and then applying upper on both the search column and the search string to roll your own case-insensitivity.

Answer 2

Can execution time be reduced?

We don't have your database to test it, so the only suggestion is to check the query execution plan to see if suitable indexes are used.

Answer 3

LIKE '%something' will always be a performance issue.

See:

https://www.mssqltips.com/sqlservertip/5681/how-to-make-sql-server-wildcard-searches-faster/

Answer 4

I have run this query but giving the following error message.

Msg 8116, Line 1
Argument data type text is invalid for argument 1 of upper function

Answer 5

Argument data type text is invalid for argument 1 of upper function

So that data type has been deprecated for a long time, and if possible you should consider running

ALTER TABLE tbl ALTER COLUM  textcol varchar(MAX)

If it is not possible, try the updated script below:

declare @text_to_find as nvarchar(max) = N'TOTS' --Type here to search content
declare @txt as nvarchar(max) = concat('%', replace(replace( replace( @text_to_find, N'[', N'[[]'), N'%', N'[%]'), N'', N'['), N'%')
declare @sql as nvarchar(max)
;
with Q as
(
select concat(N'select ', quotename( concat(quotename(s.name), N'.', quotename(t.name)), N''''),
       N' as TableName where exists( select * from ', quotename(s.name), N'.', quotename(t.name), N' where ',
       string_agg( convert(nvarchar(MAX),
                        concat('upper(', 
                          CASE y.name WHEN 'text' THEN concat('convert(varchar(MAX), ', quotename(c.name), ')')
                                      WHEN 'ntext' THEN concat('convert(nvarchar(MAX), ', quotename(c.name), ')')
                                      ELSE quotename(c.name)
                          END, 
                          N') COLLATE Latin1_General_BIN2 like upper(@txt)')
                  ) , 
                          N' or '), N')' ) as x
from sys.tables t
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.columns c on c.object_id = t.object_id
inner join sys.types y on y.user_type_id = c.user_type_id
where y.name in (N'text', N'ntext', N'char', N'nchar', N'varchar', N'nvarchar', N'sysname', N'xml')
group by s.name, t.name
)
select @sql = string_agg(x, N' union all ')
from Q
PRINT @sql
exec sp_executesql @sql, N'@txt nvarchar(max)', @txt = @txt
select @@ROWCOUNT as 'Total tables'