November 20, 2012 at 4:39 am
Bhuvnesh (11/20/2012)
tonyarp05 61903 (11/20/2012)
We are developing one tool(Backend : SQL server), that tool should be search the string from entire database.But i have some codes,which takes min 30 to 45mins.
Even i tried with following string search also, its taking more than 30mins to search the string entire database.
http://qa.sqlservercentral.com/scripts/String/89388/
Is there any other way we could get the result within few mins.. Please help me on this.
i think you should start thinking to go with partitioning , so that you can divert the load to any designated disk (based on the keyword search)
here partitioning will be done on the basis of alphabets seggregation
But then you would have to partition every table based on every column based on every letter of the alphabet as you never know which table or which column the value being searched on appears in, so there could be an infinate possibility.
November 20, 2012 at 4:45 am
anthony.green (11/20/2012)
Bhuvnesh (11/20/2012)
tonyarp05 61903 (11/20/2012)
We are developing one tool(Backend : SQL server), that tool should be search the string from entire database.But i have some codes,which takes min 30 to 45mins.
Even i tried with following string search also, its taking more than 30mins to search the string entire database.
http://qa.sqlservercentral.com/scripts/String/89388/
Is there any other way we could get the result within few mins.. Please help me on this.
i think you should start thinking to go with partitioning , so that you can divert the load to any designated disk (based on the keyword search)
here partitioning will be done on the basis of alphabets seggregation
But then you would have to partition every table based on every column based on every letter of the alphabet as you never know which table or which column the value being searched on appears in, so there could be an infinate possibility.
+ 100 🙂 . but this requirement kept me in dark that how can this be achieved. from OP explanation its is looking quite practical to have this kind of requirement BUT also i can sense that it might be poor DB design here where search is on almost every column and every table :unsure:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 20, 2012 at 5:07 am
Bhuvnesh (11/20/2012)
anthony.green (11/20/2012)
Bhuvnesh (11/20/2012)
tonyarp05 61903 (11/20/2012)
We are developing one tool(Backend : SQL server), that tool should be search the string from entire database.But i have some codes,which takes min 30 to 45mins.
Even i tried with following string search also, its taking more than 30mins to search the string entire database.
http://qa.sqlservercentral.com/scripts/String/89388/
Is there any other way we could get the result within few mins.. Please help me on this.
i think you should start thinking to go with partitioning , so that you can divert the load to any designated disk (based on the keyword search)
here partitioning will be done on the basis of alphabets seggregation
But then you would have to partition every table based on every column based on every letter of the alphabet as you never know which table or which column the value being searched on appears in, so there could be an infinate possibility.
+ 100 🙂 . but this requirement kept me in dark that how can this be achieved. from OP explanation its is looking quite practical to have this kind of requirement BUT also i can sense that it might be poor DB design here where search is on almost every column and every table :unsure:
Its a question I have seen a number of times where the end user has inserted a value, but then the DBA doesnt know in which table or column the data has been inserted, so the only way is to look in every table in every column in every row to find every possibility of the value inserted.
You would need to have an index on every column of every table but even then you need to ensure that the search query is SARG'able to actually use the indexes effectivly, but then you run into the over indexing problem as maintaining every index adds time to update, delete, insert statements, so it is a case of what is more important, general day to day runnings or actually being able to do the search.
November 20, 2012 at 5:43 am
But still if i get the solution for this,it will be very thankful for you.
I am using following code to find the string
else if @kbm_function = 'search_all' AND ISNULL(@SearchStr, '') <> ''
BEGIN
CREATE TABLE #SearchResults (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
CREATE Index ColumnName_Index ON #SearchResults(ColumnName)
CREATE Index ColumnValue_Index ON #SearchResults(ColumnValue)
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
;WITH TableCTE (Table_Name) AS
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
SELECT @TableName = Table_Name FROM TableCTE
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
;WITH ColumnCTE (Column_Name) AS
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
-- AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
SELECT @ColumnName = Column_Name FROM ColumnCTE
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #SearchResults
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #SearchResults
END
But its taking more than 30mins to execute. Please help me on this to improve the performance of this above query.
I know am disturbing you all.. but still if i get the answer for this i will be giving Big Treat for you!!:-)
November 20, 2012 at 5:44 am
tonyarp05 61903 (11/19/2012)
The reason is, Suppose i want to search a string 'Family' in entire database. so that i could get the output which are tables(Fields) are having the value of 'Family'
The big issue with this kind of search, is that it must involve a TABLE SCAN of every table in the database.
If you have any tables with millions of rows, this is going to be excruciatingly slow.
this kind of solution is intended for a DBA investigation,and should never be given to or deployed to end users. It could easily cripple your server as it tries to process billions of rows across lots of tables.
Another long time poster on these forums, Sean Lange and myself have posted this pair of solutions many times. His does some things differently than mine and depending on your situation may be better suited. Both these scripts have a huge caveat: The same caveat goes with his. It is slow, it will cripple your sql server while it is running. DO NOT run this in production!!!
Sean Lange
I put this next piece of code together several years for just this sort of thing. DO NOT run this in production. Expect it to take a long time. It will eventually give you the results you are after.
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
declare @SearchVal varchar(200)
set @SearchVal = '%your search val here%'
declare @ColName varchar (250)
set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'
declare SearchList cursor for
select distinct so.name,sc.name from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name like @ColName
and so.type = 'U'
open SearchList
fetch next from SearchList into @table_name, @column_name
while(@@fetch_status = 0)
begin
select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
This is my version, parameterized as a procedure; it's going to return SQL statements that allow a drill down to the offending tables/columns and their data.
CREATE PROCEDURE sp_UGLYSEARCH
/*
--Purpose: to search every string column in a database for a specific word
--returns sql statement as a string which identifies the matching table
-- or when the optional parameter is used, the sql statement for the specific matching column.
--usage:
-- EXEC sp_UGLYSEARCH 'Provisional'
-- EXEC sp_UGLYSEARCH 'TEST'
-- creates one SQL for each table that actually has a match for the searched value i.e.
-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'
--optional parameter SEARCHBYCOLUMN
-- EXEC sp_UGLYSEARCH 'TEST',1
-- creates one SQL for each Column that actually has a match for the searched value i.e.
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%'
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%TEST%'
*/
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(max),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNNAME VARCHAR(100),
@COLZ VARCHAR(max)
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))
SELECT
SCHEMA_NAME(schema_id) AS SCHEMANAME,
objz.name AS TBLNAME,
colz.name AS COLNAME,
TYPE_NAME(colz.user_type_id) AS DATATYPE
INTO #TEMP
FROM sys.objects objz
INNER JOIN sys.columns colz ON objz.object_id = colz.object_id
WHERE objz.type='U'
AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--PRINT @COLZ
SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
END
ELSE --@SEARCHBYCOLUMN <> 0
BEGIN
DECLARE C2 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C2
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')
INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''
SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C2
DEALLOCATE C2
END --@SEARCHBYCOLUMN <> 0
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
Lowell
November 20, 2012 at 5:48 am
You cannot improve the performance.
As I have said, what you are doing is very intensive and will take time to execute.
The best whole database string search query I have seen is that of Sean's which I have already provided the link to.
Remember what your asking SQL to do, look in every table, in every column, in every row to see if it contains 'somestring', in X amount of GB's worth of data. Now as your database grows and you add more data and more tables it will only take longer and longer and longer to complete.
As I have said before, seriously go away and rethink what you are trying to do.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply