Slow performing query, looking for ideas for solution

I'm using SQL Server 2008.

I have a view called testView

In the view one of the columns is using another query taken from this page - http://www.simple-talk.com/sql/t开发者_高级运维-sql-programming/concatenating-row-values-in-transact-sql/

ie along this format

Create View testView as
    SELECT p1.CategoryId,    
           ( SELECT ProductName + ','    
               FROM Northwind.dbo.Products p2    
              WHERE p2.CategoryId = p1.CategoryId    
              ORDER BY ProductName    
                FOR XML PATH('') ) AS Products    
    FROM Northwind.dbo.Products p1

When the following query is run, it is taking about 110 seconds for when there are 60,000 rows in the view.

select * from testView where Products like '%asdf%'

What suggestions can be provided for improving this query?

I would use the Query Analyzer built in to SQL Server to explore the execution plan and find out where it is taking the most amount of time.

http://www.sql-server-performance.com/2006/query-analyzer/

Specifically turn on Show Execution Plan, and Statistics IO and Time.

A common thing to look at is when performance tuning logical and physical reads. You want to reduce the number of physical reads so that reads are done on the cache, not on the disk.

The first thing I would do would be to run the query through an explain function. This will detail the queries processing to you so you can see where the database is going to spend the time. Look for where it is doing full table scans as these are usually the culprits and indicators of where you need to apply indexes.