Improve SQL Server 2005 Query Performance

Question

I have a course search engine and when I try to do a search, it takes too long to show search results. You can try to do a search here

http://76.12.87.164/cpd/testperformance.cfm

At that page you can also see the database tables and indexes, if any.

I'm not using Stored Procedures - the queries are inline using Coldfusion.

I think I need to create some indexes but I'm not sure what kind (clustered, non-clustered) and on what columns.

Thanks

Answer 1

You need to create indexes on columns that appear in your WHERE clauses. There are a few exceptions to that rule:

• If the column only has one or two unique values (the canonical example of this is "gender" - with only "Male" and "Female" the possible values, there is no point to an index here). Generally, you want an index that will be able to restrict the rows that need to be processed by a significant number (for example, an index that only reduces the search space by 50% is not worth it, but one that reduces it by 99% is).
• If you are search for x LIKE '%something' then there is no point for an index. If you think of an index as specifying a particular order for rows, then sorting by x if you're searching for "%something" is useless: you're going to have to scan all rows anyway.

So let's take a look at the case where you're searching for "keyword 'accounting'". According to your result page, the SQL that this generates is:

SELECT
  *
FROM (
  SELECT TOP 10
    ROW_NUMBER() OVER (ORDER BY sq.name) AS Row,
    sq.*
  FROM (
    SELECT
      c.*,
      p.providername,
      p.school,
      p.website,
      p.type
    FROM
      cpd_COURSES c, cpd_PROVIDERS p
    WHERE
      c.providerid = p.providerid AND
      c.activatedYN = 'Y' AND
      (
        c.name like '%accounting%' OR
        c.title like '%accounting%' OR
        c.keywords like '%accounting%'
      )
  ) sq
) AS temp
WHERE
  Row >= 1 AND Row <= 10 

In this case, I will assume that cpd_COURSES.providerid is a foreign key to cpd_PROVIDERS.providerid in which case you don't need an index, because it'll already have one.

Additionally, the activatedYN column is a T/F column and (according to my rule above about restricting the possible values by only 50%) a T/F column should not be indexed, either.

Finally, because searching with a x LIKE '%accounting%' query, you don't need an index on name, title or keywords either - because it would never be used.

So the main thing you need to do in this case is make sure that cpd_COURSES.providerid actually is a foreign key for cpd_PROVIDERS.providerid.

SQL Server Specific

Because you're using SQL Server, the Management Studio has a number of tools to help you decide where you need to put indexes. If you use the "Index Tuning Wizard" it is actually usually pretty good at tell you what will give you the good performance improvements. You just cut'n'paste your query into it, and it'll come back with recommendations for indexes to add.

You still need to be a little bit careful with the indexes that you add, because the more indexes you have, the slower INSERTs and UPDATEs will be. So sometimes you'll need to consolidate indexes, or just ignore them altogether if they don't give enough of a performance benefit. Some judgement is required.

Answer 2

Is this the real live database data? 52,000 records is a very small table, relatively speaking, for what SQL 2005 can deal with.

I wonder how much RAM is allocated to the SQL server, or what sort of disk the database is on. An IDE or even SATA hard disk can't give the same performance as a 15K RPM SAS disk, and it would be nice if there was sufficient RAM to cache the bulk of the frequently accessed data.

Having said all that, I feel the " (c.name like '%accounting%' OR c.title like '%accounting%' OR c.keywords like '%accounting%') " clause is problematic.

Could you create a separate Course_Keywords table, with two columns "courseid" and "keyword" (varchar(24) should be sufficient for the longest keyword?), with a composite clustered index on courseid+keyword

Then, to make the UI even more friendly, use AJAX to apply keyword validation & auto-completion when people type words into the keywords input field. This gives you the behind-the-scenes benefit of having an exact keyword to search for, removing the need for pattern-matching with the LIKE operator...

Answer 3

Using CF9? Try using Solr full text search instead of %xxx%?

Answer 4

You'll want to create indexes on the fields you search by. An index is a secondary list of your records presorted by the indexed fields.

Think of an old-fashioned printed yellow pages - if you want to look up a person by their last name, the phonebook is already sorted in that way - Last Name is the clustered index field. If you wanted to find phone numbers for people named Jennifer or the person with the phone number 867-5309, you'd have to search through every entry and it would take a long time. If there were an index in the back with all the phone numbers or first names listed in order along with the page in the phonebook that the person is listed, it would be a lot faster. These would be the unclustered indexes.

Answer 5

I would try changing your IN statements to an EXISTS query to see if you get better performance on the Zip code lookup. My experience is that IN statements work great for small lists but the larger they get, you get better performance out of EXISTS as the query engine will stop searching for a specific value the first instance it runs into.

<CFIF zipcodes is not "">
    EXISTS (
        SELECT zipcode
        FROM cpd_CODES_ZIPCODES
        WHERE zipcode = p.zipcode
            AND 3963 * (ACOS((SIN(#getzipcodeinfo.latitude#/57.2958) * SIN(latitude/57.2958)) +
            (COS(#getzipcodeinfo.latitude#/57.2958) * COS(latitude/57.2958) *
            COS(longitude/57.2958 - #getzipcodeinfo.longitude#/57.2958)))) <= #radius#
    )
</CFIF>