Increase SELECT Query performance without using indexing
I have a SELECT query which is fetching data from approx 4 tables with data in range of 90,000 to 1lac records, it is a long query with multiple inner joins between tables.The problem is taking damn long time to fetch the data.Now the problem is in 2 parts:
1---> Out of 4, 3 tables used are not indexed there is no primary key, i know by indexing performance will increase but i cannot index the tables esp. cant add a new column with unique values in each table of such huge volumes of data so to make it primary key.I tried using non-unique(coz i cant use unique),non-clustered indexing which was not of much help.
Now i need some resolution to increase the query performance without indexing in some other way.
2.---> Other problem is, after i get data after a looooong patience of atleast 30 to 45 mins through running query in TOAD or SQL Developer.It is taking hell longer to get its record count or export data in excel.Its already been 4 hrs now since i have put in for exporting into excel but still i could not get it, i m sure that the database or system has not hung till now becoz the displayed count of no. of rows fetched is increasing with time.It is now 1500 rows after 4 hrs and expect the total records to be between 25000 to 30000 approx.Please tell me if there is any way to export data in excel or other format faster.
Will be reall grateful for any assistance provided on above 2 queries.
I told already...non-unique,non-clustered indexing i have tried was not of much help...unique indexing is the last option...
Either indexing strategy is wrong or business specs are asking to process a high percentaje of the rows in each table.
Have you traced the query? do you know what is doing?
On the other hand ...
1- the only option to increase performance in such an scenario is by resorting to parallel hint.
2- 1 lac is like 100,000 isn't it? ... a couple of tables in our shop have a population of in excess of 2 billion rows and we have no issues with indexes, not sure why it is not doable for in your case.
Last edited by PAVB; 04-30-2010 at 08:06 AM.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.