Intermittently Slow query - Left Join

Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:

Descriptions table:

ID | Title
Doc1 | Document 1
Doc2 | Document 2

Documents table:

ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189

View:

ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)

So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.

In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.

It's running on SQL Server 2005 with 2GB of RAM.

Any suggestions on tracking down the reason for the slowness would be great.

Thanks in advance!!!

-DanPost the query you are executing.|||SELECT
dbo.Table1.ItemType,
dbo.Table1.ItemLabel,
dbo.Table1.ItemParent,
dbo.Table1.ItemID,
dbo.Table1.ProjectID,
dbo.Table1.ItemDate,
dbo.Table1.Active,
dbo.Table1.ItemBaseLabel,
dbo.Table2.ItemDescription
FROM dbo.Table1 LEFT OUTER JOIN dbo.Table2
ON dbo.Table1.ProjectID = dbo.Table2.ProjectID
AND dbo.Table1.ItemBaseLabel = dbo.Table2.ItemBaseLabel

Here you go!

-Dan|||where are the indices placed? have you looked at the execution plan?|||That's weird... I just checked the indices on the base table and there are three. One is By ItemID. Another is by ItemLabel and another is by ItemLabel, ItemParent, Item ID, and another field. Could they be throwing off the way the data is being stored?

There are no indices on the view and I cannot add one. It comes up with an error saying that it can't add an index because it's not schema based. Please, bear with me - I'm new to 2005. This was originally stored on MSSQL 7.0 and it worked fine there.

Thanks!

-Dan|||typically you want indices on search conditions, primary keys (there is usually a clustered one there) and the foriegn keys but you should examine the execution plan first to make sure this will help. if they are already there, make sure they are not fragmented, the statistics are up to date, and then if this is a sp, recompile it. I am not working with 2K5 just yet.|||I checked the indices and everything seems to be in order. I also tried it through a stored procedure and it still takes way too long.

Any other ideas?

-Dan|||for the third time... have you looked at the execution plan?|||Sorry. The execution plan said that a table scan on the dbo.Table2 was 100% cost. So I looked at the fields it was referencing and added a column to the Table2 index and then re-ran the execution plan and it was more divided among the tasks, now with 2 index scans.

Went back to the query and now it's blazing fast.

Apparently my listening skills are about as good as my databasing skills...

Thanks Thrasymachus. :)

-Dan