Index when doing %word% search

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

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.

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.

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.

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

Select allOpen in new window

AuthUser.sql
script.sql

@Camillia:

I'm downloading the data now.  Will be later today before I can load it and review it.

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

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.

Let me look at CHARINDEX. Thanks,

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 :))

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.

>>(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.

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)

I'm curious if charindex was any better?

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.

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

Select allOpen in new window

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.

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.

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

Select allOpen 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

Select allOpen 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

Select allOpen in new window

What is the data type of u.SerialNo?  If a numeric data type, might be an implicit data type conversion issue?

SerialNo is nvarchar(64)

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.

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.

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)

Select allOpen 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) 

Select allOpen in new window

script.sql

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

Select allOpen 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

Select allOpen in new window

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

for-EE-Execution-plan.sqlplan

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.

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.

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?

Yes, good idea to try. I'll remove the indexes and see. I'll post back.

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.

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.

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.

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)
)

Select allOpen in new window

Then perform some magic so when they search for %word%, you convert it to word% and drow%

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.

>> 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?

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.

>>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.

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.

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

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 :--).

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.

Just curious, are columnstore indexes an option for you. What version of SQL Server are you running?

SQL 2014. I don't know about "columnstore" indexes. Have to look it up.

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.