Improving SQL Server 2005 Query Performance

Question

I have a course search engine, and when I try to search, it takes too much time to display the search results. You can try to search here

http://76.12.87.164/cpd/testperformance.cfm

On this page, you can also see database tables and indexes, if any.

I do not use stored procedures - queries are embedded using Coldfusion.

It seems to me that I need to create some indexes, but I'm not sure what type (clustered, non-clustered) and in which columns.

thank

Answer 1

, WHERE. :

• ( - "" - "" "" , ). , , , (, , 50%, , , 99 % is).
• x LIKE '%something', . , , x, "% something", : .

, , " ". , SQL, :

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 

, cpd_COURSES.providerid cpd_PROVIDERS.providerid, , .

, activatedYN T/F ( 50%), T/F .

, x LIKE '%accounting%' , , .

, , , - , cpd_COURSES.providerid cpd_PROVIDERS.providerid.

SQL Server

SQL Server, Management Studio , , . " ", , , , . , .

, , INSERT UPDATE. , . .

Answer 2

? 52 000 - , , , SQL 2005.

, SQL . IDE SATA ​​ , SAS 15 /, , .

, "(," % account% "" c.title ", " % account% "" c.keywords ", " % accounting% ")." .

Course_Keywords "courseid" "keyword" (varchar (24) ?), courseid +

, UI , AJAX , . , LIKE...

Answer 3

CF9? Solr %xxx%?

Answer 4

, . - , .

- , - Last Name - . 867-5309, , . , , , , . .

Answer 5

I would try changing my IN queries to an EXISTS query to find out if you have better performance when searching by Zip code. My experience is that IN statements are great for small lists, but the more they get, the better EXISTS performance, since the query engine will stop searching for the specific value in which it starts.

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