1

I have a SQL Server 2017 select statement that is executed by the front end application. The query has three joins and UNION. Each join statement has a clause where NAME LIKE '%ibm%'.

When I change %ibm% to NAME LIKE %services% is runs fast as usual.

I rebuilt all the indexes on all of the tables. I've used SQL Server Profiler and Database Tuning Advisor to analyze the query. It found missing statistics on some columns, which I created. It also suggested creating three filtered indexed views, but that is not an option in our process. All this has not changed anything in the query performance.

Query

SELECT  companyName FROM (
       SELECT  TOP 25  upper(rtrim(ltrim(OrgTeamingNameTx))) as companyName
       FROM
       MyDB.test.CompanyProfile CP WITH (NOLOCK)
       INNER JOIN  MyDB.test.OrgTeaming orgTeaming WITH (nolock) ON orgTeaming.OrgId = CP.OrgID
       WHERE (1=0    OR OrgTeamingNameTx LIKE '%ibm%')
UNION
       SELECT  TOP 25 upper(rtrim(ltrim(Vendor.NameTx))) AS companyName
       FROM
       MyDB.test.CompanyProfile CP WITH (NOLOCK)
       INNER JOIN MyDB.test.Vendor Vendor WITH (NOLOCK) ON CP.VendorID = Vendor.VendorID
       WHERE (1=0 OR Vendor.NameTx LIKE '%ibm%')
UNION
       SELECT  TOP 25 upper(rtrim(ltrim(vd.Company_Name))) companyName
       FROM
       MyDB.test.VendorRegistrationPOCPersisted vr WITH (NOLOCK)
       INNER JOIN  MyDB.test.VendorDNB vd WITH (NOLOCK) ON vr.VendorID = vd.VendorID
       WHERE (1=0 OR vd.Company_Name LIKE '%ibm%')
) AS companyInfo
ORDER BY companyName ASC

Live Statistics

Live Stats_part1

Live Stats part 2

Time and IO Stats

Using '%services%' is 0 sec.

===============================================================
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(70 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorDNB'. Scan count 4637, logical reads 23529, physical reads 50, read-ahead reads 1014, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorRegistrationPOCPersisted'. Scan count 1, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CompanyProfile'. Scan count 50, logical reads 205, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Vendor'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrgTeaming'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 244 ms.*

Using '% ibm %' almost 3 sec.

*SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.*

*SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.*

*SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.*

(25 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorRegistrationPOCPersisted'. Scan count 1, logical reads 18501, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'VendorDNB'. Scan count 1, logical reads 17819, physical reads 0, read-ahead reads 51, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'CompanyProfile'. Scan count 35, logical reads 132, physical reads 0, read-ahead reads 62, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Vendor'. Scan count 1, logical reads 7782, physical reads 0, read-ahead reads 1279, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrgTeaming'. Scan count 1, logical reads 1472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*SQL Server Execution Times:
   CPU time = 2875 ms,  elapsed time = 2894 ms.*

Additional information

  • When I use %services% I'm getting a total of 25 records; 67,114 when I use % ibm %
  • When I use a string that definitely does not exist like %ZZQQXXFF% it takes just 2 seconds
  • I've tried removing 1=0 and replacing UNION with UNION ALL, but that did not lead to any improvements in terms of physical scans, logical reads, and CPU time.
  • 1=0 is appended to form dynamic queries for select fields to cover the case where the user didn’t input any search term and simple click the drop-down list.
  • Adding USE HINT('DISABLE_OPTIMIZER_ROWGOAL') did not improve the query performance.
  • Covering index is not possible since a user can search by any value.

The query:

SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) as companyName
FROM MyDB.test.OrgTeaming orgTeaming
WHERE OrgTeamingNameTx LIKE '%ibm%'

looks like:

 SQL Server parse and compile time: 
   CPU time = 10 ms, elapsed time = 10 ms. 

(1 row affected)

Table 'CompanyProfile'. Scan count 10, logical reads 30, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OrgTeaming'. Scan count 1, logical reads 1607, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 20 ms.
  • Couldn't this be as simple as the query happens to encounter 25 rows with "services" in the names sooner than it encounters 25 rows with " IBM " in the name? – RDFozz Feb 8 '18 at 19:09
  • Have you thought about using Full Text Indexes on that column? When you are doing unanchored wildcard searches it really is the faster technology. Under the hood, it stores the field expanded out into words. – Jonathan Fite Feb 15 '18 at 13:35
2

Let's start with a simpler question. What will performance of this query look like?

SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) as companyName
FROM MyDB.test.OrgTeaming orgTeaming
WHERE OrgTeamingNameTx LIKE '%ibm%'

Putting a wild card on both sides of the LIKE expression means that SQL Server doesn't have a lot of options. You defined a clustered index on your table, so it'll scan an index until it finds 25 rows or runs out of rows to scan. If there is a smaller, covering index that includes the columns that you need for the query then the query optimizer is likely to select that index for the query. It'll be cheaper to scan an object with fewer pages. Your query plan shows two clustered index scans. Creating a smaller, covering indexes on all tables with the wild card search may improve performance, especially for the cases where there aren't 25 matches in the table.

Going back to your query in the question, the other important part from a performance point of view has to do with how the joins are implemented to the other tables. Based on the STATISTICS IO output, the query optimizer makes significantly different choices for what you're filtering against. There isn't enough information in the question to say much more than that. The TOP introduces a row goal which could lead to suboptimal plan choices if SQL Server scans more rows than expected during the clustered index scans. My suggestion is to look at the actual and estimated number of rows for the slow plan and to look for significant differences. You can also compare the slow and the fast plan for differences. One last option is to try the USE HINT('DISABLE_OPTIMIZER_ROWGOAL') query hint with the slow query to see what happens.

  • Hi Joe, I checked the Estimated Number of rows to be read is 18748 and Actual number of rows read is 10. That corresponds to the first clustered index scan on the first query (total query cost of 4%). – enigma6205 Feb 19 '18 at 0:36
  • Similar situation for the second query for nonclustered index IX_Vendor_NameTx - 1,790,242 estimated rows and actual 25 rows read (total query cost of 42%). And third query - estimated 2,001,709 and 20 actual 20 rows read. (total query cost of 36%) – enigma6205 Feb 19 '18 at 0:44
0

If MyDB.test.CompanyProfile relationship with MyDB.test.OrgTeaming is one to one then Use inner join.If it is one to many then use EXISTS clause. and see that both column are index.similarly with other,

CREATE table #tmp(companyName varchar(200) not null)

INSERT INTO #tmp (companyName)
SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) AS companyName
FROM MyDB.test.CompanyProfile CP WITH (NOLOCK)
INNER JOIN MyDB.test.OrgTeaming orgTeaming WITH (NOLOCK) ON orgTeaming.OrgId = CP.OrgID
WHERE (
        1 = 0
        OR OrgTeamingNameTx LIKE '%ibm%'
        )
ORDER BY OrgTeamingNameTx ASC

-- OR --

SELECT TOP 25 upper(rtrim(ltrim(OrgTeamingNameTx))) AS companyName
FROM MyDB.test.CompanyProfile CP WITH (NOLOCK)
WHERE EXISTS (
        SELECT 1
        FROM MyDB.test.OrgTeaming orgTeaming WITH (NOLOCK)
        WHERE orgTeaming.OrgId = CP.OrgID
        )
    AND (
        1 = 0
        OR OrgTeamingNameTx LIKE '%ibm%'
        )
ORDER BY OrgTeamingNameTx ASC

if you have "distinct type reason" then use UNION else use UNION ALL no matter what.

In this solution if you need to filter distinct record then you can use EXISTS clause to do so.

Write second query using same logic as above

INSERT INTO #tmp (companyName)
SELECT TOP 25 upper(rtrim(ltrim(Vendor.NameTx))) AS companyName
FROM MyDB.test.CompanyProfile CP WITH (NOLOCK)
INNER JOIN MyDB.test.Vendor Vendor WITH (NOLOCK) ON CP.VendorID = Vendor.VendorID
WHERE (
        1 = 0
        OR Vendor.NameTx LIKE '%ibm%'
        )
-- don't use if it is not required
-- and not exists(select 1 from #tmp t where t.companyName=Vendor.NameTx)
ORDER BY Vendor.NameTx

Similarly write third query

Also you can find which of the 3 query is more offending.

  • Hi Kumar, the suggested queries still have the same performance. – enigma6205 Feb 19 '18 at 0:33
  • @enigma6205, ok one last query.Suppose you use top 250 or top 300 or top 500 then time improve or not,using your query or my query or both.if top 250 is still same try 300 and so on. – KumarHarsh Feb 19 '18 at 7:39
  • This query is used by the web application to display 25 results in the dropdown list. It is limited to display only 25 results at once – enigma6205 Feb 19 '18 at 15:53
  • @enigma6205,that I can make out that your requirement is 25 rows only.I just wanted to know if top 250 or top 300 or top 500 are fast or not when executed individually. – KumarHarsh Feb 20 '18 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.