SQL Queries takes long time for the first time

OpenEdge

New Member
Hi -

When we connect to the Progress OpenEdge DB with the 10.2 A driver, and execute a SQL statement having a simple inner join conditions ( the where clause are all indexed columns), it takes more that 4 - 5 minutes for the first time. However if we run the query next time, they return the result in seconds. The slowness in the query is again observed if the database is not used for couple of hours, and after the first execution the record retrieval is fast again.

The DB size is 9 GB and the update statistic have been run on the database.

Thanks for your help.

OE
 

RealHeavyDude

Well-Known Member
The reason for that behavior is plain simple: The buffer pool which caches the most used database blocks in memory. Chances are that, when you execute the same query within a short time frame that the records already reside in memory, whereas a long time period in between ( or the first time after the database server has been started ) this blocks have already been evicted in favor of other ones ( or after the start of the database have never been cached in the first place before ).

The reason for query slowness is mostly that they can't be satisfied with an appropriate index which means the database engine needs to do a full table scan on disk which is obviously very slow.

Heavy Regards, RealHeavyDude.
 
It seems it takes a lot of time to cache data to DB shared memory.
Second query uses data from shared memory.

It is expected.

It seems that query is not optimized. Check logical read statistics while executing query. I think DB server reads a lot of data and consume a lot of shared memory. You can try to increase -B parameter.
 

OpenEdge

New Member
Thanks for the reply.

Since SQL with Progress is new to me, where do I check the number of records that gets read when executing the SQL query? The Promon activity does not show any activity when executing the SQL query?

Thanks again.
 

unmeshgawade

New Member
It seems it takes a lot of time to cache data to DB shared memory.
Second query uses data from shared memory.

It is expected.

It seems that query is not optimized. Check logical read statistics while executing query. I think DB server reads a lot of data and consume a lot of shared memory. You can try to increase -B parameter.



Hi Maxim,

We are having number of Databases on one SQL Server, with only one database "PSim" simple "Select * from tbl_products" takes more 5 -15 mins...returns 1500 rows... where other databases same query finishes in 5-7 seconds.. returning even more rows like 2000+

We are having PSim index on....still.

in your update you mentioned about increase "-B Parameter"... can you explain how to and where to do that?... i am just a simple use.. .not expert in SQL 2005
 
Top