2

I am looking for specific pieces of software across our network by querying the SCCM Database. My problem is that, for various reasons, sometimes I can search by the program's name and other times I need to search for a specific EXE.

When I run the query below, does it take 13 seconds to run if the where clause contains an AND, but it will run for days with no results if the AND is replaced with OR. I'm assuming it is doing this because I am not properly joining the tables. How can I fix this?

select vrs.Name0
FROM v_r_system as vrs
join v_GS_INSTALLED_SOFTWARE as VIS on VIS.resourceid = vrs.resourceid
join v_GS_SoftwareFile as sf on SF.resourceid = vrs.resourceid

where 
VIS.productname0 LIKE '%office%' AND SF.Filename LIKE 'Office2007%'

GROUP BY vrs.Name0

Thanks!

Carrie Kendall
  • 11,124
  • 5
  • 61
  • 81
recklessmortal
  • 31
  • 1
  • 5

2 Answers2

1

Your LIKE clause contains a wildcard match at the start of a string:

LIKE '%office%'

This prevents SQL Server from using an index on this column, hence the slow running query. Ideally you should change your query so your LIKE clause doesn't use a wildcard at the start.

In the case where the WHERE clause contains an AND its querying based on the Filename clause first (it is able to use an index here and so this is relatively quick) and then filtering this reduced rowset based on your productname0 clause. When you use an OR however it isn't restricted to just returning rows that match your Filename clause, and so it must search through the entire table checking to see if each productname0 field matches.

Justin
  • 84,773
  • 49
  • 224
  • 367
1

Here's a good Microsoft article http://msdn.microsoft.com/en-us/library/ms172984.aspx on improving indexes. See the section on Indexes with filter clauses (reiterates the previous answer.)

Have you tried something along these lines instead of a like query?

... where product name in ('Microsoft Office 2000','Microsoft Office xyz','Whateverelse')

Emmanuel
  • 11
  • 2