avoid left outer join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

avoid left outer join

Author

Topic

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-25 : 19:19:40

SELECT COUNT(inventory.AuditCategoryCode)
FROM Inventory AS inventory
INNER JOIN InventoryDetail as inventoryDetail
ON inventory.InventoryID = inventoryDetail.InventoryID
LEFT OUTER JOIN InventoryBundle AS InventoryBundle
ON inventory.InventoryID = InventoryBundle.InventoryID
LEFT OUTER JOIN Drug AS d
ON inventory.InventoryID = d.InventoryID
LEFT OUTER JOIN PreventiveCare AS pc
ON inventory.InventoryID = pc.InventoryID

I need to create an index on a view so anyway to avoid left outer joins>?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-25 : 19:21:06

Sure, change left to right and then rearrange the tables.

Have you thought about indexing the underlying tables instead of an indexed view? Indexed views are a royal pain and rarely have an advantage over just properly indexing the tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-25 : 19:26:56

i dont wanna use outer join either
well i just wanna try to find out about the perfroamcne...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-25 : 19:30:22

There is nothing wrong with using an OUTER JOIN in your query. It does not affect performance. Sure an INNER JOIN is better but that only works if you want just the matches between the two tables.

Properly design and properly index your system, that's how you get good performance.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-25 : 19:32:57

just to school u for free... ..u cant index a view when there is a outr join

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-25 : 19:49:17

You should create a LEFT OUTER CLUSTERED INDEX on all the tables, then INNER JOIN them in your views (assuming your view is SCHEMABOUND), r you can just do this

SELECT COUNT(inventory.AuditCategoryCode)
FROM Inventory AS inventory
INNER JOIN InventoryDetail as inventoryDetail
ON inventory.InventoryID = inventoryDetail.InventoryID

which will you give you the same results as LEFT OUTER JOINING on all the tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-26 : 12:10:18

quote:
Originally posted by lamujerdetuhermano10

just to school u for free... ..u cant index a view when there is a outr join

I know that, and that's exactly why I said to index the underlying tables. Duh!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-27 : 01:18:20

Funketekun is giving you a hard time?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources