We have two queries
1. A join query involves 32 fields from 4 tables.
2. Query from a single table involving retrieval of the same 32 fields.(Table contains a total of 650 columns)
There is a large variation in times taken for traversing through the database(JDBC resultset)
Both the queries will retrieve around 1 Lakh Records.
The time taken in case of Join Query is:
Time to execute the query –> 11-12 secs
Time to traverse through the dataset(resultset) –> 6 minutes
The timetaken in case of Query from single table is:
Time to execute the query –> 100 millisecs
Time to traverse through the dataset(resultset) –> 12-13 minutes
We would like to know the reason for this variation in time taken for traversal throughthe datasets(resultsets).
and also like to know is this because of the the total number of columns present in the single table.
In case, if it is yes, why does the join query perform well during traversing.