Use or abuse of full text searching
-
What are the issues when full text CONTAINS statements are used with parameters like these against varchar (225) varchar (400) columns like this:
@DerivedTable01_BORROWER_FULL_NAMES114='"DIXON,r*"',@DerivedTable01_OWNER_FULL_NAMES135='"DIXON,r*"'CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, '"Leslie*" AND "M*" AND "Opat*"')
OR ( CONTAINS(AccountOwnershipDocSummary02.OWNER_FULL_NAMES, '"Leslie*" AND "M*" AND "Opat*"'The sort following the full text table valued function actually has more cost than the TVF itself and the seek on the table clustered index is highest by far
-
All "Costs" are really just estimates. The real key here is... is there actually a performance problem? How long does the query typical take to execute, how many CPU milliseconds does it take, and how many logical reads are occurring?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Run times during the weekday business hours can vary from a few seconds to 2 minutes generally. Logical reads actually seem small compared to many of our ORM-generated queries. But this is an extremely common and frequent query for our customers so would like to optimize. I do need to look at the indexes on the table with the clustered index seek ( 142 million records, 32 indexes and 39 columns ). Full text was set up a couple of years ago and does get nightly reorganization ( ALTER FULLTEXT CATALOG [SummaryTableSearchFullNames] REORGANIZE )
Full text is configured as follows:exec sp_executesql N'SELECT TOP 100 AccountOwnershipDocSummary02.ACCOUNT_ID AS AccountId,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ID AS OwnershipDocId,AccountOwnershipDocSummary02.OWNER_FULL_NAMES AS Owners,AccountOwnershipDocSummary02.BORROWER_FULL_NAMES AS Borrowers,AccountOwnershipDocSummary02.OWNERSHIP_DOC_MODIFIED_MANUFACTURER_ID AS OwnershipDocVin,AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS ModifiedAccountVin,AccountOwnershipDocSummary02.ORIGINAL_ACCOUNT_MANUFACTURER_ID AS OriginalAccountVin,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR AS OwnershipDocState,AccountOwnershipDocSummary02.CLIENT_ID AS ClientId,AccountOwnershipDocSummary02.LIENHOLDER_IDENTIFIER AS LienholderIdentifier,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 AS AccountNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_2 AS LoanNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_4 AS Branch,AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR AS AccountState,AccountOwnershipDocSummary02.STATUS AS Status,AccountOwnershipDocSummary02.LICENSE_NUMBER AS LicenseNumber,AccountOwnershipDocSummary02.OWNERSHIP_DOC_DOCUMENT_NUMBER AS TitleNumber,Client13.SHORT_NAME AS ClientShortName, @LargeClient7 AS LargeClient,AccountOwnershipDocSummary02.ID AS AccountOwnershipDocSummary02_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN (ORGANIZATION AS Client13 INNER JOIN USR_ORGANIZATION_AUTHORIZATION AS UserOrganizationAuthorization14 ON Client13.ORGANIZATION_ID=UserOrganizationAuthorization14.SECURED_ORGANIZATION_ID) ON AccountOwnershipDocSummary02.CLIENT_ID=Client13.ORGANIZATION_ID WHERE ((UserOrganizationAuthorization14.USR_ID = @DerivedTable01_USR_ID30 AND AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID51 AND ((AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR = @DerivedTable01_EXPECTED_TITLING_STATE_ABBR72) OR (AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR = @DerivedTable01_OWNERSHIP_DOC_ISSUING_STATE_ABBR93)) AND ( CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, @DerivedTable01_BORROWER_FULL_NAMES114) OR ( CONTAINS(AccountOwnershipDocSummary02.OWNER_FULL_NAMES, @DerivedTable01_OWNER_FULL_NAMES135)))) AND ((Client13.CONCRETE_TYPE IN ( @DerivedTable01_156)))) ORDER BY 21 DESC',N'@DerivedTable01_USR_ID30 int,@DerivedTable01_CLIENT_ID51 int,@DerivedTable01_EXPECTED_TITLING_STATE_ABBR72 varchar(8000),@DerivedTable01_OWNERSHIP_DOC_ISSUING_STATE_ABBR93 varchar(8000),@DerivedTable01_BORROWER_FULL_NAMES114 varchar(8000),@DerivedTable01_OWNER_FULL_NAMES135 varchar(8000),@DerivedTable01_156 varchar(8000),@LargeClient7 int',@DerivedTable01_USR_ID30=95236,@DerivedTable01_CLIENT_ID51=11330,@DerivedTable01_EXPECTED_TITLING_STATE_ABBR72='MS',@DerivedTable01_OWNERSHIP_DOC_ISSUING_STATE_ABBR93='MS',@DerivedTable01_BORROWER_FULL_NAMES114='"DIXON,r*"',@DerivedTable01_OWNER_FULL_NAMES135='"DIXON,r*"',@DerivedTable01_156='Fdi.Po.Client',@LargeClient7=1exec sp_executesql N'SELECT TOP 100 AccountOwnershipDocSummary02.ACCOUNT_ID AS AccountId,AccountOwnexec sp_executesql N'SELECT TOP 100 AccountOwnershipDocSummary02.ACCOUNT_ID AS AccountId,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ID AS OwnershipDocId,AccountOwnershipDocSummary02.OWNER_FULL_NAMES AS Owners,AccountOwnershipDocSummary02.BORROWER_FULL_NAMES AS Borrowers,AccountOwnershipDocSummary02.OWNERSHIP_DOC_MODIFIED_MANUFACTURER_ID AS OwnershipDocVin,AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS ModifiedAccountVin,AccountOwnershipDocSummary02.ORIGINAL_ACCOUNT_MANUFACTURER_ID AS OriginalAccountVin,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR AS OwnershipDocState,AccountOwnershipDocSummary02.CLIENT_ID AS ClientId,AccountOwnershipDocSummary02.LIENHOLDER_IDENTIFIER AS LienholderIdentifier,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 AS AccountNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_2 AS LoanNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_4 AS Branch,AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR AS AccountState,AccountOwnershipDocSummary02.STATUS AS Status,AccountOwnershipDocSummary02.LICENSE_NUMBER AS LicenseNumber,AccountOwnershipDocSummary02.OWNERSHIP_DOC_DOCUMENT_NUMBER AS TitleNumber,Client13.SHORT_NAME AS ClientShortName, @LargeClient7 AS LargeClient,AccountOwnershipDocSummary02.ID AS AccountOwnershipDocSummary02_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN (ORGANIZATION AS Client13 INNER JOIN USR_ORGANIZATION_AUTHORIZATION AS UserOrganizationAuthorization14 ON Client13.ORGANIZATION_ID=UserOrganizationAuthorization14.SECURED_ORGANIZATION_ID) ON AccountOwnershipDocSummary02.CLIENT_ID=Client13.ORGANIZATION_ID WHERE ((UserOrganizationAuthorization14.USR_ID = @DerivedTable01_USR_ID30 AND AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID51 AND ((AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR = @DerivedTable01_EXPECTED_TITLING_STATE_ABBR72) OR (AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR = @DerivedTable01_OWNERSHIP_DOC_ISSUING_STATE_ABBR93)) AND ( CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, @DerivedTable01_BORROWER_FULL_NAMES114) OR ( CONTAINS(AccountOwnershipDocSummary02.OWNER_FULL_NAMES, @DerivedTable01_OWNER_FULL_NAMES135)))) AND ((Client13.CONCRETE_TYPE IN ( @DerivedTable01_156)))) ORDER BY 21 DESC',N'@DerivedTable01_USR_ID30 int,@DerivedTable01_CLIENT_ID51 int,@DerivedTable01_EXPECTED_TITLING_STATE_ABBR72 varchar(8000),@DerivedTable01_OWNERSHIP_DOC_ISSUING_STATE_ABBR93 varchar(8000),@DerivedTable01_BORROWER_FULL_NAMES114 varchar(8000),@DerivedTable01_OWNER_FULL_NAMES135 varchar(8000),@DerivedTable01_156 varchar(8000),@LargeClient7 int',@DerivedTable01_USR_ID30=95236,@DerivedTable01_CLIENT_ID51=11330,@DerivedTable01_EXPECTED_TITLING_STATE_ABBR72='MS',@DerivedTable01_OWNERSHIP_DOC_ISSUING_STATE_ABBR93='MS',@DerivedTable01_BORROWER_FULL_NAMES114='"DIXON,r*"',@DerivedTable01_OWNER_FULL_NAMES135='"DIXON,r*"',@DerivedTable01_156='Fdi.Po.Client',@LargeClient7=1ershipDocSummary02.OWNERSHIP_DOC_ID AS OwnershipDocId,AccountOwnershipDocSummary02.OWNER_FULL_NAMES AS Owners,AccountOwnershipDocSummary02.BORROWER_FULL_NAMES AS Borrowers,AccountOwnershipDocSummary02.OWNERSHIP_DOC_MODIFIED_MANUFACTURER_ID AS OwnershipDocVin,AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS ModifiedAccountVin,AccountOwnershipDocSummary02.ORIGINAL_ACCOUNT_MANUFACTURER_ID AS OriginalAccountVin,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR AS OwnershipDocState,AccountOwnershipDocSummary02.CLIENT_ID AS ClientId,AccountOwnershipDocSummary02.LIENHOLDER_IDENTIFIER AS LienholderIdentifier,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 AS AccountNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_2 AS LoanNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_4 AS Branch,AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR AS AccountState,AccountOwnershipDocSummary02.STATUS AS Status,AccountOwnershipDocSummary02.LICENSE_NUMBER AS LicenseNumber,AccountOwnershipDocSummary02.OWNERSHIP_DOC_DOCUMENT_NUMBER AS TitleNumber,Client13.SHORT_NAME AS ClientShortName, @LargeClient5 AS LargeClient,AccountOwnershipDocSummary02.ID AS AccountOwnershipDocSummary02_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN (ORGANIZATION AS Client13 INNER JOIN USR_ORGANIZATION_AUTHORIZATION AS UserOrganizationAuthorization14 ON Client13.ORGANIZATION_ID=UserOrganizationAuthorization14.SECURED_ORGANIZATION_ID) ON AccountOwnershipDocSummary02.CLIENT_ID=Client13.ORGANIZATION_ID WHERE ((UserOrganizationAuthorization14.USR_ID = @DerivedTable01_USR_ID30 AND AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID51 AND ( CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, @DerivedTable01_BORROWER_FULL_NAMES72) OR ( CONTAINS(AccountOwnershipDocSummary02.OWNER_FULL_NAMES, @DerivedTable01_OWNER_FULL_NAMES93)))) AND ((Client13.CONCRETE_TYPE IN ( @DerivedTable01_114)))) ORDER BY 21 DESC',N'@DerivedTable01_USR_ID30 int,@DerivedTable01_CLIENT_ID51 int,@DerivedTable01_BORROWER_FULL_NAMES72 varchar(8000),@DerivedTable01_OWNER_FULL_NAMES93 varchar(8000),@DerivedTable01_114 varchar(8000),@LargeClient5 int',@DerivedTable01_USR_ID30=74778,@DerivedTable01_CLIENT_ID51=12227,@DerivedTable01_BORROWER_FULL_NAMES72='"WOODY*" AND "S*"',@DerivedTable01_OWNER_FULL_NAMES93='"WOODY*" AND "S*"',@DerivedTable01_114='Fdi.Po.Client',@LargeClient5=1SELECT TOP 100 AccountOwnershipDocSummary02.ACCOUNT_ID AS AccountId,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ID AS OwnershipDocId,AccountOwnershipDocSummary02.OWNER_FULL_NAMES AS Owners,AccountOwnershipDocSummary02.BORROWER_FULL_NAMES AS Borrowers,AccountOwnershipDocSummary02.OWNERSHIP_DOC_MODIFIED_MANUFACTURER_ID AS OwnershipDocVin,AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS ModifiedAccountVin,AccountOwnershipDocSummary02.ORIGINAL_ACCOUNT_MANUFACTURER_ID AS OriginalAccountVin,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR AS OwnershipDocState,AccountOwnershipDocSummary02.CLIENT_ID AS ClientId,AccountOwnershipDocSummary02.LIENHOLDER_IDENTIFIER AS LienholderIdentifier,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 AS AccountNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_2 AS LoanNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_4 AS Branch,AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR AS AccountState,AccountOwnershipDocSummary02.STATUS AS Status,AccountOwnershipDocSummary02.LICENSE_NUMBER AS LicenseNumber,AccountOwnershipDocSummary02.OWNERSHIP_DOC_DOCUMENT_NUMBER AS TitleNumber,Client13.SHORT_NAME AS ClientShortName, 1 AS LargeClient,AccountOwnershipDocSummary02.ID AS AccountOwnershipDocSummary02_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN (ORGANIZATION AS Client13 INNER JOIN USR_ORGANIZATION_AUTHORIZATION AS UserOrganizationAuthorization14 ON Client13.ORGANIZATION_ID=UserOrganizationAuthorization14.SECURED_ORGANIZATION_ID) ON AccountOwnershipDocSummary02.CLIENT_ID=Client13.ORGANIZATION_ID WHERE ((UserOrganizationAuthorization14.USR_ID = 48603 AND AccountOwnershipDocSummary02.CLIENT_ID = 12001 AND ((AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR = 'CA') OR (AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR = 'CA')) AND ( CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, '"MARAVICH*" AND "MICHAEL*"') OR ( CONTAINS(AccountOwnershipDocSummary02.OWNER_FULL_NAMES, '"MARAVICH*" AND "MICHAEL*"')))) AND ((Client13.CONCRETE_TYPE IN ( 'Fdi.Po.Client')))) ORDER BY 21 DESC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply