Abhijit More (5/14/2013)

---

Hello!

I have one stored proc which returns data by querying multiple tables using union clause without any search criteria.

select xyz as Col1 from table1 join table2 ...(total 8 joins)

union

select abc as Col1 from table1 join table3...(total 8 joins)

union

select pqras Col1 from table1 join table4...(total 8 joins)

union

select pqras Col1 from table1 join table5...(total 8 joins)

As per database design Table1 represent supertype and Table3,4,5 represent sub types. Table1 has 1146090 rows. Not sure what to do with this. It showed up as leading in average reads at 14 million per execution and runs for 35 seconds. It is going to get a lot worse looking at the tables it's selecting from. It is basically doing a select across lots of tables, some large tables, without a select criteria. It's doing a bunch of in clustered index scans... appreciate your help here.