Index when doing %word% search

I had this question after viewing Is this correct: first % in "Like" ignores indexes?.

I asked the question above the other day and I understand it but hoping for a miracle, workaround something :)

From Scott:
A prefix of % does not make SQL "ignore" the index, it means that SQL can't do a seek on the index.  It could still scan the index to satisfy the query

I think I came across a link that had a workaround and now I can't find it. I can't use FTS and can't have word% search.

Anyway to speed up a search using %word% by adding indexes? I've already broken down the stored proc and get chunks of data but it would be great if I could add an index to 5 columns in the "where" clause that I'm searching on.

If not doable, then it is what it is.
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperCommented:
When you use %word, SQL Server, at best, can do a full index scan, but it will never do a seek. If you are doing a search of last_name using '%son' and there is an index on last_name, that will speed things up some, since SQL Server can do the scan on the index rather than having to do a full table scan, but you will NEVER see the performance you would by searching for 'br%'. There is no work-around for this.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Depending on your exact requirements you might get a performance increase by using Full Text Search and a contains query over a like query.

This type of index isn't a normal index and shouldn't be seen as an easy replacement.  There are special considerations you need to know about when doing full text indexing.

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-2017
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
You really have 2x choices.

1) Use %foo% searches, which require doing a full index scan. This might or might not work.

If your data tables are small no problem. If you have many rows with many potential search columns, this can fail abysmally.

2) Use FTS (Full Text Searches) which build special indexes to optimize searching.

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-2017 provides a good starting point for your research on this subject.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
if I could add an index to 5 columns in the "where" clause

Yes, that offers a much better chance of improving performance.  Please post the WHERE clause conditions.  The %string% column can be an included column in the index, while the other WHERE conditions could be key searches (seeks).
CamilliaAuthor Commented:
Thanks Scott. It's the join you've been helping me with but there's more to it. I'll post sample data and the sp here soon.

Would be great if this can be done.
slightwv (䄆 Netminder) Commented:
Sure, you can probably build an index that contains enough columns to satisfy the query.  What you need to ask is:  How many columns are in the table to start with and what data types are those?

Just because you can create an index to let they query use it doesn't mean performance will improve.  Not ALL full table scans are bad and not ALL index uses are good.
Doug BishopDatabase DeveloperCommented:
"Not ALL full table scans are bad and not ALL index uses are good."

How true.  My last job had a table that had an index that consisted of 45% of the data the table consumed on disk. It had 5 indexed columns and 12 included columns. The table only had 20 columns total. I argued for years to remove it, especially when DMV showed the index had only been used a small handful of times/month. The data was loaded nightly via an ETL process that could dump millions of rows of data into the table. To make matters worse, the front-end process that accessed the table, 99% of the time, pulled not only the indexed and included columns, but the rest of the data from the table as well. The index was named such that it was evident someone had used the Database Tuning Advisor to suggest indexes (DTA_xxxxxx). It probably improved one very specific query that was executed once/quarter and gave 5% performance boost.
CamilliaAuthor Commented:
I understand but if there's a way to speed this up, that would be great . If not, then I'll let my moody manager know that we have to live with this.

@scott I've PMed you a link to get the data (bigassetsearch). SQL is too big to post here. I don't want to remove data just in case it's needed for tuning.

The other 2 scripts are attached here for tables that are used in the stored proc. No customer identifying info in these scripts.

I've broken the search into 3 temp tables because searching was too slow. I found using 3 temp tables, then joining is faster. If there's a better way, I'm open to it. For now, if I can put some indexes to speed this search stored proc, it would be great



/****** Object:  StoredProcedure [dbo].[usp_SearchAssets]    Script Date: 11/29/2018 10:53:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[usp_SearchAssets]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN

---*************** how to call it  *************************
-- exec [dbo].[usp_SearchAssets] '%MX7T11243364%', 14307
-- exec  [dbo].[usp_SearchAssets] '%019%', 14307

--DECLARE  @AssetType nvarchar(4000)--,  in code for now. ** comment later 
--DECLARE  @searchtext nvarchar(4000),
--declare @searchtext2 nvarchar(4000)

--DECLARE @userId INT --*** pass this in

--SET @userId = 88 -- 14307  --*** remove and pass in *************

 --SET @AssetType = 'N/A - Non-Managed' --**comment later
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 --SET @searchtext2 = '%MX7T11243364%'--'%O19%' --**************** remove *******************************************

 --account for 0 and O that code has. Ex: if user enters 0019, code checks for 0019, OO19. Another example, if user enters 0O19, code checks for 0O19, 0019 and OO19, and so on
DECLARE @results TABLE(SearchText NVARCHAR(4000))

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


 
 --*** unit search. First do unit search and get the data
INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where

		  --(u.[SerialNo] LIKE @searchtext)
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2)
        
    --      OR
		   (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )
		  OR exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext) --account for 0 and O that C# code has


--Schedule search. We don't want dups from #unitsearch
INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
      AND
      (
          (s.[Sched] LIKE @searchtext)
      
          OR 
		  (s.[LesseeName] LIKE @searchtext )
      )


--master lease search. Dont want dups from #unitsearch and #schedsearch
INSERT INTO #MLsearch
(
    rowid
)
SELECT m.rowid
FROM dbo.BigAssetSearch m
LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
WHERE
us.rowid IS NULL
AND 
sc.rowid IS NULL
and 
		   (m.[CustomerName] LIKE @searchtext )


--result
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 
		  

			--INTO camillasptest
			FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
									  AND (au.FilterUDFs = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.UdFilterValue))  
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE
	 AND     (au.FilterUDFs = 0 OR (au.FilterUDFs = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID)))

 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch


 End

Open in new window

AuthUser.sql
script.sql
Scott PletcherSenior DBACommented:
@Camillia:

I'm downloading the data now.  Will be later today before I can load it and review it.
slightwv (䄆 Netminder) Commented:
From a quick scan of the code, looks like you are wanting to perform a text search for a value across several different table columns using an OR.

This screams full text index to me.

You can provide a column list to the create index command.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-2017
CamilliaAuthor Commented:
Thanks, Scott.

@slightwv, I'll look at your link and learn about full text index. And yes, I have one table and I want to search 8 columns. But, I'm also doing %word% search. Not sure if that would make a difference but I'll look at the link you posted.
slightwv (䄆 Netminder) Commented:
>>But, I'm also doing %word% search. Not sure if that would make a difference but I'll look at the link you posted.

I can only speak to Oracle Text indexes as that is what I've used.  I only know SQL Server Full Text from reading.

I know Oracle Text can handle wildcards efficiently.  I assume SQL Server can as well?  In Oracle you can even tell the Text index to plan for prefix and substring queries.  Makes the index a lot bigger but queries with wildcards can perform even better.

While Googling to see it SQL Server has similar features I came across several posts that state CHARINDEX queries are much faster than LIKE and don't require the Full Text index.

You might try that as well?

http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex
CamilliaAuthor Commented:
Let me look at CHARINDEX. Thanks,
CamilliaAuthor Commented:
I read the Full Text Index link. But for that, looks like I need Full Text Search. We can't use that because for 4 or 5 years, users are getting results by doing %word% search.  Full Text Search searches for complete words and not for wildcards (I had another question about this and already tried it).

I'll wait and see if Scott has ideas (I mean a miracle solution :))
CamilliaAuthor Commented:
I added indexes on the 8 columns , looked at the execution plan and I see scan and not seek (what I expected from what's been discussed here). Didn't see the query faster. I'll wait and see if Scott has time to look at this.
slightwv (䄆 Netminder) Commented:
>>(what I expected from what's been discussed here)

Not sure what gave you that idea.  I believe everyone has stated that a left wildcard search cannot do a seek on an index.  It must do a scan.  There are no leading characters to find the correct path through the tree.

When Scott mentioned the seek, I believe he was talking about the OTHER columns in the where clause being part of an index.
CamilliaAuthor Commented:
yeah, that's what I meant...  I didn't expect a seek. I expected a scan and that's what I saw. (i phrased it wrong..I just looked at what I posted earlier)
slightwv (䄆 Netminder) Commented:
I'm curious if charindex was any better?
CamilliaAuthor Commented:
I didn't try it. My manager is on my case to wrap this up. I'll try it off hours, on my own time, this weekend and see how it works.
CamilliaAuthor Commented:
I tried the CHARINDEX and it doesn't bring back the same amount of rows as "like".

1. I cleared cache/buffers like the link above about charindex
2. I put indexes on the 8 columns in the "like" where clause of the three temp tables
3.  For one, row, I think it was faster. 8 seconds. "Like" took about 10 to 11 seconds.
4. For s earch that brings back more than one row, I get less rows. So, results isn't correct with CHARINDEX. --> exec  [dbo].[usp_SearchAssetsTest] '%019%', 14307

This is what I changed it to using charindex

/****** Object:  StoredProcedure [dbo].[usp_SearchAssets]    Script Date: 11/29/2018 10:53:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_SearchAssetsTest]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN



---*** how to call it
-- exec [dbo].[usp_SearchAssetsTest] '%nnn%', 14503
-- exec  [dbo].[usp_SearchAssetsTest] '%MX7T11243364%', 14307
-- exec  [dbo].[usp_SearchAssetsTest] '%019%', 14307


--DECLARE  @AssetType nvarchar(4000)--,  in code for now. ** comment later 
--DECLARE  @searchtext nvarchar(4000),
--declare @searchtext2 nvarchar(4000)

--DECLARE @userId INT --*** pass this in

--SET @userId = 88 -- 14307  --*** remove and pass in *************

 --SET @AssetType = 'N/A - Non-Managed' --**comment later
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 --SET @searchtext2 = '%MX7T11243364%'--'%O19%' --**************** remove *******************************************

 --account for 0 and O that code has. Ex: if user enters 0019, code checks for 0019, OO19. Another example, if user enters 0O19, code checks for 0O19, 0019 and OO19, and so on
DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


 
 --*** unit search. First do unit search and get the data
INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where

		  --(u.[SerialNo] LIKE @searchtext)
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2)
        
        -- OR
	CHARINDEX(@searchtext,SUBSTRING(u.[CustomerRef], 1, LEN(@searchtext)),0) > 0
	OR CHARINDEX(@searchtext,SUBSTRING(u.[Model], 1, LEN(@searchtext)),0) > 0
	OR CHARINDEX(@searchtext,SUBSTRING(u.[Manufacturer], 1, LEN(@searchtext)),0) > 0
	OR CHARINDEX(@searchtext,SUBSTRING(u.[SiteAddress], 1, LEN(@searchtext)),0) > 0
	
		  --(u.[CustomerRef] LIKE @searchtext )
    --      OR (u.[Model] LIKE @searchtext )
    --      OR (u.[Manufacturer] LIKE @searchtext)
    --      OR (u.[SiteAddress] LIKE @searchtext )
		  OR exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext) --account for 0 and O that C# code has


----Schedule search. We don't want dups from #unitsearch
INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
      AND
      (
	      CHARINDEX(@searchtext,SUBSTRING(s.[Sched], 1, LEN(@searchtext)),0) > 0
         -- (s.[Sched] LIKE @searchtext)
      
          OR 
		  CHARINDEX(@searchtext,SUBSTRING(s.[LesseeName], 1, LEN(@searchtext)),0) > 0
		 -- (s.[LesseeName] LIKE @searchtext )
      )


----master lease search. Dont want dups from #unitsearch and #schedsearch
INSERT INTO #MLsearch
(
    rowid
)
SELECT m.rowid
FROM dbo.BigAssetSearch m
LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
WHERE
us.rowid IS NULL
AND 
sc.rowid IS NULL
and 
		   --(m.[CustomerName] LIKE @searchtext )
		   CHARINDEX(@searchtext,SUBSTRING(m.[CustomerName], 1, LEN(@searchtext)),0) > 0


----result
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 
		  

			--INTO camillasptest
			FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
									  AND (au.FilterUDFs = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.UdFilterValue))  
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE
	 AND     (au.FilterUDFs = 0 OR (au.FilterUDFs = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID)))

 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch


 End

Open in new window

slightwv (䄆 Netminder) Commented:
Remember, I'm not a SQL Server person so I'm going with only a basic knowledge.

>>This is what I changed it to using charindex

I don't see where you set searchtext to a value but I hope you didn't include the wildcard characters?  With charindex you don't use them.

I'm also not sure what you are doing with:
CHARINDEX(@searchtext,SUBSTRING(u.[CustomerRef], 1, LEN(@searchtext)),0) > 0

It looks like that is only searching the first part of the file not the ENTIRE field for the searchtext.  That would limit the rows compared to a double wildcard like query.
CamilliaAuthor Commented:
Oh, the substring.  I copied it from the link. That's probably it. Let me look at another example of charindex  . I dont want to substring.
CamilliaAuthor Commented:
Getting closer...

1. I removed the substring
2. I need to call the stored proc with CHARINDEX like this
 exec  [dbo].[usp_SearchAssetsTest] '019', 14307

The one with "like", I call it like this --> exec  [dbo].[usp_SearchAssetsTest] '%019%', 14307
---
Now, CHARINDEX looks faster but I have a problem with one line of SQL. This line brings back correct rows when I use like but I get less rows when I use CHARINDEX. I've noted it below. I need to find the correct equivalent of that line of code using CHARINDEX.

I ran a test: If I remove this line OR exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext) from the "Like" version of the stored proc
 and remove  OR exists (Select 1 from @results r WHERE CHARINDEX(@searchtext,u.SerialNo) > 0) from the "Charindex" version of the stored proc...I get correct results in both.

So that line in Charindex is not correct.

It's this:

DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where

	CHARINDEX(@searchtext,u.[CustomerRef]) > 0
	OR CHARINDEX(@searchtext,u.[Model] )> 0
	OR CHARINDEX(@searchtext,u.[Manufacturer] )> 0
	OR CHARINDEX(@searchtext,u.[SiteAddress]) > 0
        OR exists (Select 1 from @results r WHERE CHARINDEX(@searchtext,u.SerialNo) > 0) --**** this brings less rows
       
                   --(u.[CustomerRef] LIKE @searchtext )
                   -- OR (u.[Model] LIKE @searchtext )
                   --OR (u.[Manufacturer] LIKE @searchtext)
                   --OR (u.[SiteAddress] LIKE @searchtext )
                   --OR exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext) --*** if I use LIKE, this brings back the correct rows

Open in new window


This is the entire SP I have using CHARINDEX

/****** Object:  StoredProcedure [dbo].[usp_SearchAssets]    Script Date: 11/29/2018 10:53:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_SearchAssetsTest]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN



---*** how to call it
-- exec [dbo].[usp_SearchAssetsTest] '%nnn%', 14503
-- exec  [dbo].[usp_SearchAssetsTest] 'MX7T11243364', 14307
-- exec  [dbo].[usp_SearchAssetsTest] '019', 14307


--DECLARE  @AssetType nvarchar(4000)--,  in code for now. ** comment later 
--DECLARE  @searchtext nvarchar(4000),
--declare @searchtext2 nvarchar(4000)

--DECLARE @userId INT --*** pass this in

--SET @userId = 88 -- 14307  --*** remove and pass in *************

 --SET @AssetType = 'N/A - Non-Managed' --**comment later
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 --SET @searchtext2 = '%MX7T11243364%'--'%O19%' --**************** remove *******************************************

 --account for 0 and O that code has. Ex: if user enters 0019, code checks for 0019, OO19. Another example, if user enters 0O19, code checks for 0O19, 0019 and OO19, and so on
DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


 
 --*** unit search. First do unit search and get the data
INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where

		  --(u.[SerialNo] LIKE @searchtext)
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2)
        
        -- OR
	CHARINDEX(@searchtext,u.[CustomerRef]) > 0
	OR CHARINDEX(@searchtext,u.[Model] )> 0
	OR CHARINDEX(@searchtext,u.[Manufacturer] )> 0
	OR CHARINDEX(@searchtext,u.[SiteAddress]) > 0
	
		  --(u.[CustomerRef] LIKE @searchtext )
    --      OR (u.[Model] LIKE @searchtext )
    --      OR (u.[Manufacturer] LIKE @searchtext)
    --      OR (u.[SiteAddress] LIKE @searchtext )
		  OR exists (Select 1 from @results r WHERE CHARINDEX(@searchtext,u.SerialNo) > 0) --account for 0 and O that C# code has


----Schedule search. We don't want dups from #unitsearch
INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
      AND
      (
	      CHARINDEX(@searchtext,s.[Sched]) > 0
         -- (s.[Sched] LIKE @searchtext)
      
          OR 
		  CHARINDEX(@searchtext,s.[LesseeName]) > 0
		 -- (s.[LesseeName] LIKE @searchtext )
      )


----master lease search. Dont want dups from #unitsearch and #schedsearch
INSERT INTO #MLsearch
(
    rowid
)
SELECT m.rowid
FROM dbo.BigAssetSearch m
LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
WHERE
us.rowid IS NULL
AND 
sc.rowid IS NULL
and 
		   --(m.[CustomerName] LIKE @searchtext )
		   CHARINDEX(@searchtext,m.[CustomerName]) > 0


----result
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 
		  

			--INTO camillasptest
			FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
									  AND (au.FilterUDFs = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.UdFilterValue))  
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE
	 AND     (au.FilterUDFs = 0 OR (au.FilterUDFs = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID)))

 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch


 End

Open in new window


Entire SP using LIKE

/****** Object:  StoredProcedure [dbo].[usp_SearchAssets]    Script Date: 12/20/2018 12:39:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_SearchAssets]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN

---*** how to call it
-- exec [dbo].[usp_SearchAssets] '%nnn%', 14503
-- exec  [dbo].[usp_SearchAssets] '%019%', 14307

--DECLARE  @AssetType nvarchar(4000)--,  in code for now. ** comment later 
--DECLARE  @searchtext nvarchar(4000),
--declare @searchtext2 nvarchar(4000)

--DECLARE @userId INT --*** pass this in

--SET @userId = 88 -- 14307  --*** remove and pass in *************

 --SET @AssetType = 'N/A - Non-Managed' --**comment later
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 --SET @searchtext2 = '%MX7T11243364%'--'%O19%' --**************** remove *******************************************

 --account for 0 and O that code has. Ex: if user enters 0019, code checks for 0019, OO19. Another example, if user enters 0O19, code checks for 0O19, 0019 and OO19, and so on
DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


 
 --*** unit search. First do unit search and get the data
INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where

		  --(u.[SerialNo] LIKE @searchtext)
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2)
        
    --      OR
		   (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )
		 OR exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext) --account for 0 and O that C# code has


--Schedule search. We don't want dups from #unitsearch
INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
      AND
      (
          (s.[Sched] LIKE @searchtext)
      
          OR 
		  (s.[LesseeName] LIKE @searchtext )
      )


--master lease search. Dont want dups from #unitsearch and #schedsearch
INSERT INTO #MLsearch
(
    rowid
)
SELECT m.rowid
FROM dbo.BigAssetSearch m
LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
WHERE
us.rowid IS NULL
AND 
sc.rowid IS NULL
and 
		   (m.[CustomerName] LIKE @searchtext )


--result
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 
		  

			--INTO camillasptest
			FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
									  AND (au.FilterUDFs = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.UdFilterValue))  
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE
	 AND     (au.FilterUDFs = 0 OR (au.FilterUDFs = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID)))

 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch


 End

Open in new window

slightwv (䄆 Netminder) Commented:
What is the data type of u.SerialNo?  If a numeric data type, might be an implicit data type conversion issue?
CamilliaAuthor Commented:
SerialNo is nvarchar(64)
slightwv (䄆 Netminder) Commented:
I'm pretty much out of ideas.  Might be different if I had access to a SQL Server system (not any of the online testing ones like sqlfiddle) where I could set up a test case and run things.

Guess you'll have to wait for some actual SQL Server folks if you cannot figure it out on your own.
CamilliaAuthor Commented:
Thanks. You've been a great help. I'll work on figuring out that one line of SQL, wait for the SQL experts and go from there.
CamilliaAuthor Commented:
I know why CHARINDEX gives 66 less rows when I search for 019 (that's zero one nine). I ran a test and looked at the rows that CHARINDEX doesn't bring back. It doesn't pick up O (that's oh)

Run this with the SQL script attached. Why wouldn't CHARINDEX work?

This is with charindex and doesn't bring rows


DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892
DECLARE @searchtext nvarchar(4000) = '019'

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

SELECT * FROM @results

SELECT --TOP 10 
*

FROM

	[dbo].[camillaEE]  AS u
	
where
          -- exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext) 
		   exists (Select 1 from @results r WHERE CHARINDEX(@searchtext,u.SerialNo) > 0)

Open in new window


This is with Like and brings rows

DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892
DECLARE @searchtext nvarchar(4000) = '%019%'

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

SELECT * FROM @results

SELECT --TOP 10 
*

FROM

	[dbo].[camillaEE]  AS u
	
where
           exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext) 

Open in new window

script.sql
slightwv (䄆 Netminder) Commented:
Not sure if this is a typo or a reason but it looks like there is an "@" missing on searchtext:
WHERE u.SerialNo LIKE @searchtext
CamilliaAuthor Commented:
Geez, yes, that was it, slightww. I was missing @ in the like version of the stored proc.

Now that this is figured out , I can compare the results and see how long like vs charindex takes.

So, this is what I have so far...

1. I put indexes on the 8 columns I have "where" clause on ... in the temp tables
2. I have a stored proc that uses like
3. I have a stored proc that uses Charindex
4. Not sure if I need the indexes. I had to create 3 indexes for the 8 coumns. I couldn't add them to one index. I was getting a 900 byte size limit error , if I wanted to add them to one index.

These are my stored procs now

@Scott, if you're still checking this, please chime in if you have any input on this madness :) slightwv has been enjoying it :)

With Charindex

/****** Object:  StoredProcedure [dbo].[usp_SearchAssets]    Script Date: 11/29/2018 10:53:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_SearchAssetsTest]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN



---*** how to call it
-- exec [dbo].[usp_SearchAssetsTest] '%nnn%', 14503
-- exec  [dbo].[usp_SearchAssetsTest] 'MX7T11243364', 14307
-- exec  [dbo].[usp_SearchAssetsTest] '019', 14307


--DECLARE  @AssetType nvarchar(4000)--,  in code for now. ** comment later 
--DECLARE  @searchtext nvarchar(4000),
--declare @searchtext2 nvarchar(4000)

--DECLARE @userId INT --*** pass this in

--SET @userId = 88 -- 14307  --*** remove and pass in *************

 --SET @AssetType = 'N/A - Non-Managed' --**comment later
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 --SET @searchtext2 = '%MX7T11243364%'--'%O19%' --**************** remove *******************************************

 --account for 0 and O that code has. Ex: if user enters 0019, code checks for 0019, OO19. Another example, if user enters 0O19, code checks for 0O19, 0019 and OO19, and so on
DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


 
 --*** unit search. First do unit search and get the data
INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where

		  --(u.[SerialNo] LIKE @searchtext)
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2)
        
        -- OR
	CHARINDEX(@searchtext,u.[CustomerRef]) > 0
	OR CHARINDEX(@searchtext,u.[Model] )> 0
	OR CHARINDEX(@searchtext,u.[Manufacturer] )> 0
	OR CHARINDEX(@searchtext,u.[SiteAddress]) > 0
	
		  --(u.[CustomerRef] LIKE @searchtext )
    --      OR (u.[Model] LIKE @searchtext )
    --      OR (u.[Manufacturer] LIKE @searchtext)
    --      OR (u.[SiteAddress] LIKE @searchtext )
		  OR exists (Select 1 from @results r WHERE CHARINDEX(@searchtext,u.SerialNo) > 0) --account for 0 and O that C# code has
--SELECT * 
--INTO camillacharindextest
--FROM #unitsearch
 

----Schedule search. We don't want dups from #unitsearch
INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
      AND
      (
	      CHARINDEX(@searchtext,s.[Sched]) > 0
         -- (s.[Sched] LIKE @searchtext)
      
          OR 
		  CHARINDEX(@searchtext,s.[LesseeName]) > 0
		 -- (s.[LesseeName] LIKE @searchtext )
      )


----master lease search. Dont want dups from #unitsearch and #schedsearch
INSERT INTO #MLsearch
(
    rowid
)
SELECT m.rowid
FROM dbo.BigAssetSearch m
LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
WHERE
us.rowid IS NULL
AND 
sc.rowid IS NULL
and 
		   --(m.[CustomerName] LIKE @searchtext )
		   CHARINDEX(@searchtext,m.[CustomerName]) > 0


----result
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 
		  

			--INTO camillasptest
			FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
									  AND (au.FilterUDFs = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.UdFilterValue))  
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE
	 AND     (au.FilterUDFs = 0 OR (au.FilterUDFs = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID)))

 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch


 End

Open in new window


With Like

/****** Object:  StoredProcedure [dbo].[usp_SearchAssets]    Script Date: 12/20/2018 12:39:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_SearchAssets]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN

---*** how to call it
-- exec [dbo].[usp_SearchAssets] '%nnn%', 14503
-- exec  [dbo].[usp_SearchAssets] '%MX7T11243364%', 14307

--DECLARE  @AssetType nvarchar(4000)--,  in code for now. ** comment later 
--DECLARE  @searchtext nvarchar(4000),
--declare @searchtext2 nvarchar(4000)

--DECLARE @userId INT --*** pass this in

--SET @userId = 88 -- 14307  --*** remove and pass in *************

 --SET @AssetType = 'N/A - Non-Managed' --**comment later
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 --SET @searchtext2 = '%MX7T11243364%'--'%O19%' --**************** remove *******************************************

 --account for 0 and O that code has. Ex: if user enters 0019, code checks for 0019, OO19. Another example, if user enters 0O19, code checks for 0O19, 0019 and OO19, and so on
DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


 
 --*** unit search. First do unit search and get the data
INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where

		  --(u.[SerialNo] LIKE @searchtext)
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2)
        
    --      OR
		   (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )
		 OR exists (Select 1 from @results r WHERE u.SerialNo LIKE @searchtext) --account for 0 and O that C# code has

--		 SELECT * 
--INTO camillaliketest
--FROM #unitsearch

--Schedule search. We don't want dups from #unitsearch
INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
      AND
      (
          (s.[Sched] LIKE @searchtext)
      
          OR 
		  (s.[LesseeName] LIKE @searchtext )
      )


--master lease search. Dont want dups from #unitsearch and #schedsearch
INSERT INTO #MLsearch
(
    rowid
)
SELECT m.rowid
FROM dbo.BigAssetSearch m
LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
WHERE
us.rowid IS NULL
AND 
sc.rowid IS NULL
and 
		   (m.[CustomerName] LIKE @searchtext )


--result
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 
		  

			--INTO camillasptest
			FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
									  AND (au.FilterUDFs = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.UdFilterValue))  
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE
	 AND     (au.FilterUDFs = 0 OR (au.FilterUDFs = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID)))

 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch


 End

Open in new window

CamilliaAuthor Commented:
I used the Charindex stored proc and ran the Execution Plan on it. I wonder if any of the table scans or anything in the execution plan can be fixed/improved.

I've attached it (I know it will probably hard to analyze this)

For example, the table scan on the @results table

e1.pngfor-EE-Execution-plan.sqlplan
slightwv (䄆 Netminder) Commented:
Were the times noticeably different/better?

I have nothing to parse that XML and even if I did, not sure I would recognize a SQL Server plan anyway.
CamilliaAuthor Commented:
It was.... Like was 20 seconds. Charindex was around 10 seconds for 5000 something rows.

I'll wait and see if Scott has any input.
slightwv (䄆 Netminder) Commented:
While you wait for Scott...

Try without all the new indexes.  Over-indexing is a common issue I see with many databases.  If they don't offer a proven benefit, why maintain them?
CamilliaAuthor Commented:
Yes, good idea to try. I'll remove the indexes and see. I'll post back.
CamilliaAuthor Commented:
I removed and added the indexes back and ran the stored proc with Like and with Charindex:

LIKE with no indexes takes about 9 seconds
Charindex with no indexes takes about 20 seconds

Like with indexes about 11 seconds
Charindex with indexes about 8 seconds

--
I still think 8 seconds for one row is too long. If nothing else can be done with this, then it is what it is and my moody manager (he was upset 2 days ago :))  has to live with it.
CamilliaAuthor Commented:
My manager said to change something in the logic that might speed it up. I'll post back my new stored proc and results , when done.
CamilliaAuthor Commented:
What he wanted me to do was to move all the filtering at the bottom of the stored proc to #unitsearch. Do all the filtering up for a user. But that made it even worse.

I'll leave it as is and see if Scott has any other ideas.
slightwv (䄆 Netminder) Commented:
Now sure what has happened to Scott.

While researching this for you I still wonder how you all work in SQL Server...  Oracle has some really nice things like suffix searching in a Text index!!!

I keep seeing references to a work around with reversing the columns and indexing those.

I'm wondering if a reversed computed column will help?  Then you can index those as well and convert your double ended wildcard search to two prefix searches and use a normal index or Full Text index.  I'm still thinking Full Text but your mileage will vary.

create table mytest (
	Model varchar(20),
	Manufacturer varchar(20),
	SiteAddress varchar(20),
	revModel as reverse(Model),
	revManufacturer as reverse(Manufacturer),
	revSiteAddress as reverse(SiteAddress)
)

Open in new window


Then perform some magic so when they search for %word%, you convert it to word% and drow%
Doug BishopDatabase DeveloperCommented:
Reversing the string and indexing won't help because he is not looking specifically for a suffix (where column like '%searchstring%') so the search value could be in the middle of the string.
Doug BishopDatabase DeveloperCommented:
Indexing will help because SQL Server  will only need to read in the index pages and not full table rows. What I'm not sure about is if each column is indexed if SQL Server can use multiple indexes in a single query.
What version of SQL Server are you using. I don't think you can in 2014 but in 2016 you can create columnstore indexes in an OLTP environment that are dynamically maintained. You might consider that as an alternative. If you can go the columnstore indeed result, build one that includes all the columns you are going to search on. You'll probably be very surprised at the results.
slightwv (䄆 Netminder) Commented:
>> so the search value could be in the middle of the string.

Agreed for a normal index.  I lost focus for a minute...  ;)

So the reversed column with a Full Text index should still in play with reversing the columns?
Doug BishopDatabase DeveloperCommented:
I am still not certain full text indexing would help as it can only find full words or prefixes (word%). Reversing the string would help if he knew the search string would be a suffix, but then there would need to be a computed column for each one he wanted to run the search on. Normally, a computed column does not consume space in the table but it is a different story if it is indexed. Those columns will consume space in the table. I would still look at the possibility of columnstore indexes if available to him.
slightwv (䄆 Netminder) Commented:
>>if he knew the search string would be a suffix,

Should be simple enough to parse the search string for a left wildcard. and make the appropriate changes.

>>Those columns will consume space in the table.

Agreed but there is no free lunch here.  Especially reversing and indexing those columns as well.  Double the index space.

>> would still look at the possibility of columnstore indexes if available to him.

I'm not a SQL Server person so cannot comment on that as a solution.  It does seem like it is worth investigating as an option.


I suppose another option is an outside indexing solution like Solr, Elasticsearch or Cassandra.  Likely overkill for this but something to look at if the SQL Server searches keep coming up short.
CamilliaAuthor Commented:
Now sure what has happened to Scott.

Scott is enjoying the holidays and eating apple pie :)

I think 8 to 20 seconds is good but still...let's see if Scott can look at this.
CamilliaAuthor Commented:
Thanks, guys. I'll close this. I'm sure if Scott has time, he'll respond. For now, I'm using CHARINDEX and the stored proc. It's better than 2+ mins
Scott PletcherSenior DBACommented:
Sorry, I'm just swamped at the moment.  

One thing, it was time for my self-review toward getting a raise.  Oddly enough, I put a very high priority on that, for whatever reason :--).
CamilliaAuthor Commented:
haha. I hope you got a good raise. You help out a lot on this forum. If you didn't get a good raise, give me your manager's number and I'll vouche for you :)

Let's leave this ticket closed. If I need help, I'll post back.
Doug BishopDatabase DeveloperCommented:
Just curious, are columnstore indexes an option for you. What version of SQL Server are you running?
CamilliaAuthor Commented:
SQL 2014. I don't know about "columnstore" indexes. Have to look it up.
Doug BishopDatabase DeveloperCommented:
Unfortunately, for SQL Server 2014, there are quite a few limitations on using columnstore indexes. A clustered columnstore index will be updated automatically by normal DDL operations (insert, update, delete) but in order to update tables with a non-clustered columnstore index, the index needs to first be dropped and then recreated after the DDL operation(s).

If you have a clustered columnstore index, there can't be any other indexes, unique, primary or foreign key constraints on the table, and there can't be certain data types (XML, TEXT, VARCHAR(MAX) and the table cannot contain any computed or sparse columns; a lot of limitations. SQL Server 2016 and 2017 takes steps to eliminate a lot of these limitations. I've seen up to 20x performance increase with columnstore indexes and have read reports where some have noticed processes running 50-100 times faster.

When they first came out they were primarily designed for data warehouse systems, where you have basically a read-only database that was updated nightly on a daily or weekly basis. Indexes could be dropped, data loaded, indexes rebuilt, but they are making their way into OLTP systems. They have come a long way since being introduced in SQL Server 2012.

If your table is not updated frequently, you can do without a primary key and have no foreign key constraints, can drop other indexes, and build a clustered columnstore index on the table, I'm betting you'll see a performance increase. You might even, if during the day, the data is static enough, consider running a nightly job that clones the table and builds a columnstore index on the clone and run your queries against that table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.