small table takes more time as compared to bigger table

From: MadhavC <>
Date: Tue, 17 May 2011 08:56:38 -0700 (PDT)
Message-ID: <>

Hello Experts,

I am working on an enterprise application and facing some issue while working on DB queries.

I have a table - TABLE1 (having total 5 million rows) col1, col2, col3, col4, col5, col6, col7, col8, col9, col10

I have another table - TABLE2 (having total only 1000 rows) col1, col4, col6

TABLE2 contains redundent information for col1, col4, and col6 from TABLE1 I run the query as below for 16000 times for different values of col4 in where clauseselect
 col1, col4, col6 from TABLE1 where col4='SOMEVALUE' This finish in few milliseconds

I run the following query for 16000 times for different values of col4 in where clause
select col1, col4, col6 from TABLE2 where col4='SOMEVALUE' Even though TABLE2 is a very small table as compared to TABLE1, this time these 16000 queries takes around 2 seconds. Almost 5-6 times more than that of the query for TABLE1.

TABLE1 and TABLE2 are indexed on col1.

Can you please suggest what parameters I should be looking for to understand and fix this.

Thanks in advance for your help. Received on Tue May 17 2011 - 10:56:38 CDT

Original text of this message