Join Optimization - Set Conditons before Join

[SuperComputing]

I'm wondering if I can "filter" out what I need from a table before I join it to another table.

Table "Items" has 2,795 rows
Table "ItemsMonthly" has 39,352 rows

I need all rows returned from table ITEMS with the corresponding data from ITEMSMONTHLY that meet the following conditions: TYPE = 'IT' AND YEARID = '2008'

Here is my code:

SELECT Items.ItemNumber, Items.Description1,  ItemsMonthly.QtySold_1
FROM Items 
LEFT JOIN ItemsMonthly 
ON Items.ItemNumber = ItemsMonthly.ItemNumber
AND (ItemsMonthly.Type='IT' AND ItemsMonthly.YearId='2008')
ORDER BY Items.ItemNumber

It takes FOREVER to return the data. If I could just

SELECT * FROM ItemsMonthly WHERE Type='IT' AND YearID='2008'

before I did my Join, I would be working with a table of 1,300 rows instead of 39,000 rows.

Wikipedia tells me that a join is "the outcome of first taking the Cartesian product (or cross-join) of all records in the tables...then return all records which satisfy the join predicate."

Is there any way to reverse this to optimize my query?

 

[RiverGuy]

You can try a derived table.

SELECT Items.ItemNumber, Items.Description1,  ItemsMonthly.QtySold_1
FROM Items
LEFT JOIN 
  (SELECT * FROM ItemsMonthly
   WHERE (ItemsMonthly.Type='IT' AND ItemsMonthly.YearId='2008')) b
ON Items.ItemNumber = b.ItemNumber
ORDER BY Items.ItemNumber

Try both methods, and look at the execution plan for both.

 

[SuperComputing]

Thanks for the help, and your solution seemed simple enough to be universal. Now for the wholde story... I am querying Pervasive V8 and after I get the query like I want, I am going to use Excel to pull it on demand.

I am getting a syntax error at the following location:

... (SELECT <<???>> * FROM ItemsMontly ...

I can't understand why it crashed here, is it a pervasive syntax issue and should I switch threads? Any ideas?

 

[RiverGuy]

You've posted this in the SQL Server forum. Here's the Pervasive forum. forum318. Each DBMS has differences as far as syntax is concerned.

 

[SuperComputing]

Thanks for your help, and for pointing me in the right direction!

 

[gmmastros]

39,000 rows? Seriously? That's nothing.

SQL Server should be able to process that in it's sleep. I suspect you don't have any indexes on the table.

Also.... why are you LEFT joining the two tables. Inner join is more efficient, so if possible, use inner join instead.

RiverGuy... Using a derived table does not necessarily change the order in which filter conditions are applied.

To see what indexes exist:

[tt][blue]
sp_helpIndex 'Items'
sp_helpIndex 'ItemsMonthly'
[/blue][/tt]

At a minimum, there should be an index in each of these tables on the ItemNumber column.

You should also make sure your statistics are up to date. It can take a little while to do this, so if your db is mission critical, then you should wait until you can afford some down time. Anyway, I suggest your run:

[tt][blue]sp_updatestats[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SuperComputing]

Again I am getting syntax errors. I can't execute the sp_helpIndex commands. I will re-post in the Pervasive forum. Thank you all for your help.

 

[SuperComputing]

Another thread informed me that subqueries are not supported in Pervasive V8.

Does anyone have an idea on how it would be possible to optimize my query without performing a subquery within the Join?

Thanks again!