Database query - slow the first couple of times.

Question

Hi All, 
 
 we have field on the interface, when user enters a values and tabs out, we query a table to get information and load the rest of the page. 
 Issue is when a user logs in for the first time and enters the value, the query takes lot of time (7-8 seconds to load, soemtimes 10+seconds and the screen errors out). 
 But if he tries subsequently 2nd or 3rd time, the querying becomes very fast (100-200 ms). 
 
 The logs show that the execute times is what takes the maximum time.

Operation Type 
 Operation Name 
 Operation Detail 
 Prepare Time (ms) 
 Execute Time (ms) 
 Transform Time (ms)

QUERY 
 queryentity expression 
 "[FAILED] Query[Selection[sl as sl (show), price as price (show), quantity as quantity (show), enter_date as enter_date (show), plant as plant (show), matnr as matnr (show), knumh as knumh (show)], criteria[(matnr = TypedValue[it=3,v=713-312126-002])], PagingInfo[startIndex=0, batchSize=-1, sort=[Sort[sl asc]]]]" 
 0 
 10013 
 0

QUERY 
 queryentity expression 
 "[FAILED] Query[Selection[sl as sl (show), price as price (show), quantity as quantity (show), enter_date as enter_date (show), plant as plant (show), matnr as matnr (show), knumh as knumh (show)], criteria[(matnr = TypedValue[it=3,v=713-335743-002])], PagingInfo[startIndex=0, batchSize=-1, sort=[Sort[sl asc]]]]" 
 0 
 10016 
 0

The table has 1.5 million rows and the column that we are searching on is properly indexed. So the table is properly optimized.

What could the possible reasons for it being really slow the first couple of times (1-2 times)

catalinp0001 · Answer

@ siddharths why you don't use a view table and make the DB do the "heavy lifting" and after that just show the output through a simple query?

Database query - slow the first couple of times.

Top Replies