SQL Server Full Text Search Optimization using CONTAINSTABLE

A tip for large searches  when using WHERE predicates. CONTAINSTABLE returns a table with rows . If using character based data types in precise or fuzzy matches to single words , CONTAINSTABLE may suit the purpose.

For example:

SELECT *
FROM ads p
INNER JOIN containstable(properties, description, 'car') t
ON p.PropertyID = t.[key]
WHERE p.type = 'red'

 This returns , 1,200,000 records back to the optimizer, which is then filtered down to a further 150,000 'cars' that are 'red'.

The solution proposed in the article, uses embedded text in the text column,and then some substring manipulation to take out the embedded text. Inelegant, but very effective

Related Posts

Optimize query for LIKE

Author: Jack Vamvas (http://www.sqlserver-dba.com)

OpenXML - more than 8000 characters

An excellent script to deal with XML documents that are greater than 8000 characters. This deals with the SQL Server 2000 limitation. SQL Server 2005 and beyond deasl with more than 8000 characters

Use it like :

DECLARE @hDoc int
EXEC usp_OpenXML_From_File 'c:\myXMLfile.xml', @hDoc OUTPUT

SELECT *
FROM OPENXML(@hDOC, '/rss/channel/item',2)
WITH 
(
  title varchar(200),
  link varchar(400),
  description nvarchar(2000)
  
)

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc usp_OpenXML_From_File(@strPathToFile varchar(500), @hdoc int output) as
declare @objStream int, @retval int, @filesize int, @chunknum int
declare @chunkstr nvarchar(4000), @strSQL nvarchar(4000)
declare @str1 nvarchar(4000), @str2 nvarchar(4000), @str3 nvarchar(4000), @str4 nvarchar(4000), @str5 nvarchar(4000),
     @str6 nvarchar(4000), @str7 nvarchar(4000), @str8 nvarchar(4000), @str9 nvarchar(4000), @str10 nvarchar(4000),
     @str11 nvarchar(4000), @str12 nvarchar(4000), @str13 nvarchar(4000), @str14 nvarchar(4000), @str15 nvarchar(4000),
     @str16 nvarchar(4000), @str17 nvarchar(4000), @str18 nvarchar(4000), @str19 nvarchar(4000), @str20 nvarchar(4000)
declare @ErrorSource varchar(255),  @ErrorDesc varchar(255)

-- create Stream Object from file
exec @retval = sp_OACreate 'ADODB.Stream', @objStream OUT
--open the stream object.  default stream type is text and should fit our needs
exec sp_OASetProperty @objStream, 'Type', 2 -- text(default)
exec sp_OASetProperty @objStream, 'CharSet', 'utf-8'
exec sp_OAMethod @objStream, 'Open'
--load file content into stream
exec @retval = sp_OAMethod @objStream, 'LoadFromFile', Null, @strPathToFile
IF @retval = 0 -- succesfully loaded file
begin
     -- get file size
     exec sp_OAGetProperty @objStream, 'Size', @filesize OUT
     -- you need to check file size and decide if it's too big or not
     -- depending on the number of @strN you declared : 
     -- eg with @str1 to @str20, you can handle files up to 20*3700 = 74000 characters
     if @filesize <= 74000
     begin
          set @chunknum = 1
          while @filesize > 0
          begin
               exec sp_OAMethod @objStream, 'ReadText', @chunkstr out, 3700
               set @chunkstr = replace(@chunkstr, char(39), char(39) + char(39))
               select      @str1 = isnull(case when @chunknum = 1 then @chunkstr else @str1 end,''),
                    @str2 = isnull(case when @chunknum = 2 then @chunkstr else @str2 end,''),
                    @str3 = isnull(case when @chunknum = 3 then @chunkstr else @str3 end,''),
                    @str4 = isnull(case when @chunknum = 4 then @chunkstr else @str4 end,''),
                    @str5 = isnull(case when @chunknum = 5 then @chunkstr else @str5 end,''),
                    @str6 = isnull(case when @chunknum = 6 then @chunkstr else @str6 end,''),
                    @str7 = isnull(case when @chunknum = 7 then @chunkstr else @str7 end,''),
                    @str8 = isnull(case when @chunknum = 8 then @chunkstr else @str8 end,''),
                    @str9 = isnull(case when @chunknum = 9 then @chunkstr else @str9 end,''),
                    @str10 = isnull(case when @chunknum = 10 then @chunkstr else @str10 end,''),
                    @str11 = isnull(case when @chunknum = 11 then @chunkstr else @str11 end,''),
                    @str12 = isnull(case when @chunknum = 12 then @chunkstr else @str12 end,''),
                    @str13 = isnull(case when @chunknum = 13 then @chunkstr else @str13 end,''),
                    @str14 = isnull(case when @chunknum = 14 then @chunkstr else @str14 end,''),
                    @str15 = isnull(case when @chunknum = 15 then @chunkstr else @str15 end,''),
                    @str16 = isnull(case when @chunknum = 16 then @chunkstr else @str16 end,''),
                    @str17 = isnull(case when @chunknum = 17 then @chunkstr else @str17 end,''),
                    @str18 = isnull(case when @chunknum = 18 then @chunkstr else @str18 end,''),
                    @str19 = isnull(case when @chunknum = 19 then @chunkstr else @str19 end,''),
                    @str20 = isnull(case when @chunknum = 20 then @chunkstr else @str20 end,''),
                    @chunknum = @chunknum + 1, @filesize = @filesize - 3700
          end
     end
end

exec('declare @handle int;exec sp_xml_preparedocument @handle out, ''' + @str1 + @str2 + @str3 + @str4 + @str5
 + @str6 + @str7 + @str8 + @str9 + @str10 + @str11 + @str12 + @str13 + @str14 + @str15
 + @str16 + @str17 + @str18 + @str19 + @str20+ ''';declare c1 cursor global for select @handle')
open global c1
fetch c1 into @hdoc
close global c1
deallocate global c1
exec sp_OAMethod @objStream, 'Close'
exec sp_OADestroy @objStream

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 Related Posts

SET ARITHABORT ON

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Search for text in stored procedures

Search for text in stored procedures is useful, a typical example is object renaming\remapping.

Two code examples below are SQL Server 2005 certified - the first one looks for one object , the second one for two objects.

USE MyDatabase
GO
--e.g Search for Address table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Address%'
GO
--e.g Search for Address table and Street column in the same search
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Address%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Street%'
GO

 Related Posts

Stored Procedure Checklist

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server Log Check

Returns records from the SQL Server error log based on search terms. Integrating with Powershell , makes automation easier check Powershell and SQL Error Logs 

DECLARE @SERVER NVARCHAR(50)

SET @SERVER = 'SERVER1\INSTANCE'

DECLARE @sqlStatement1 VARCHAR(200)

SET @sqlStatement1 = @SERVER + '.master.dbo.xp_readerrorlog'

CREATE TABLE #Errors (vchMessage varchar(2000), ID int)

--CREATE INDEX idx_msg ON #Errors(ID, vchMessage)

INSERT #Errors EXEC @sqlStatement1

--select * from #errors

--INSERT INTO TBL_MONITOR_SQL_ERROR_LOG(server,dbName,logMessage,capturedDate)

SELECT @server,'na',RTRIM(LTRIM(vchMessage)),GETDATE() FROM #Errors WHERE

([vchMessage] like '%error%'

or [vchMessage] like '%fail%'

or [vchMessage] like '%Warning%'

or [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%'

or [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%'

or [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%'

or [vchMessage] like '% is full%'

or [vchMessage] like '% blocking processes%'

or [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%'

)

and [vchMessage] not like '%\ERRORLOG%'

and [vchMessage] not like '%Attempting to cycle errorlog%'

and [vchMessage] not like '%Errorlog has been reinitialized.%'

and [vchMessage] not like '%found 0 errors and repaired 0 errors.%'

and [vchMessage] not like '%without errors%'

and [vchMessage] not like '%This is an informational message%'

and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%'

and [vchMessage] not like '%The error log has been reinitialized%'

and [vchMessage] not like '%Setting database option ANSI_WARNINGS%'

and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%'

and [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.'
 

DROP TABLE #Errors

Related Posts 

Powershell and SQL Error Logs

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server BACKUP

SQL Server BACKUP
*This script read the backup folder as defined in the windows registry

/******************************************************************
*
* SQL Server BACKUP 
*This script read the backup folder as defined in the windows registry

******************************************************************/

CREATE  PROCEDURE BACKUP_DB
AS 
DECLARE @BACKUP_DIR VARCHAR(200) 
DECLARE @ALTERNATIVE_DIR VARCHAR(200)
DECLARE @CMD VARCHAR(1000)
SET @ALTERNATIVE_DIR = '\\SERVER2\SERVER1\BACKUP\'
EXECUTE master..xp_regread   
     'HKEY_LOCAL_MACHINE',
     'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 
     ' BackupDirectory ', 
      @BACKUP_DIR OUTPUT 
      SET @CMD = 'COPY "' + @BACKUP_DIR + '\*MASTER*" ' +  @ALTERNATIVE_DIR 
      EXEC master..xp_cmdshell @CMD

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 Related Posts

Copy only backup in SQL server 2005 and not break the backup chain

Author: Jack Vamvas (http://www.sqlserver-dba.com)