Abhijit More (5/14/2013)
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)
select abc as Col1 from table1 join table3...(total 8 joins)
select pqras Col1 from table1 join table4...(total 8 joins)
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.
How do you want us to help?
Can you drop this proc and never use?
If not, what are the real requirements?
It's impossible to advise on something like that - too little information.
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help