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

Intelligent "bubble up exceptions"

Hi,

I basically understood how to build bubble up exceptions... You i.e. can mark (by changing the background color to red) a region if in one the cities below the sales are dramatically below the targets... That's quite simple MDX...

A customer now asks for "make this pro-active/intelligent" meaning "change the background color to red if something on any level below this value is worth a look"...

This is something the Data Mining Add-Ins for Excel can do... You have a table in Excel and the Add-Ins allow you to find "special values" and mark them...

Did someone already build this into a standard cube? What steps do you have to perform?

THANKS,

I have only seen this with the ProClarity KPI Builder, part of ProClarity Professional. There is a template for "Bubble up exceptions" and I have tried it in a project. It works well.

Regards

Thomas Ivarsson