Thread: query execution time
Hi,
I am wondering if someone can help me understand the following query execution behavior:
I have two similar tables and table2 consists of 5000000 records.
Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: A ms.
Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: B ms.
Sometimes B >> A (B is very larger than A). Both queries are same and run twice. What could be the reason that the same query is taking very long sometime?
Thanks a lot!
On Mar 21, 2011, at 10:42 AM, preetika tyagi wrote: > Query: INSERT INTO table1 SELECT * FROM table2. > Execution Time: A ms. > > Query: INSERT INTO table1 SELECT * FROM table2. > Execution Time: B ms If session is same, then B < A if the data is cached due to execution of first statement. B > A, if the there is any change happened Or Any session has evicted the cached data. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
Thank you, Vibhor!
What could be other factors in case database server is separated from clients (on different computer systems)?
Preetika
On Mon, Mar 21, 2011 at 2:48 AM, Vibhor Kumar <vibhor.kumar@enterprisedb.com> wrote:
If session is same, then
On Mar 21, 2011, at 10:42 AM, preetika tyagi wrote:
> Query: INSERT INTO table1 SELECT * FROM table2.
> Execution Time: A ms.
>
> Query: INSERT INTO table1 SELECT * FROM table2.
> Execution Time: B ms
B < A if the data is cached due to execution of first statement.
B > A, if the there is any change happened Or Any session has evicted the cached data.
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
On Mar 21, 2011, at 9:55 AM, preetika tyagi wrote: > For example, if A is 15 minutes, then B is 1.5 hrs. Well, considering that random disk access is on the order of 10,000 times slower than RAM... But you can answer the question yourself by comparing the query run against cold caches (after a reboot, or various command-linetricks to purge cache) vs against warm caches (twice back-to-back). -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Thank you, Scott!
I tried running the same query after reboot and back-to-back, it was taking less time in both the cases. It means the problem is something else.
Can there be a reason which is more hardware/operating system specific and due to which the behavior is not uniform?
Preetika
I tried running the same query after reboot and back-to-back, it was taking less time in both the cases. It means the problem is something else.
Can there be a reason which is more hardware/operating system specific and due to which the behavior is not uniform?
Preetika
On Mon, Mar 21, 2011 at 9:06 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
On Mar 21, 2011, at 9:55 AM, preetika tyagi wrote:Well, considering that random disk access is on the order of 10,000 times slower than RAM...
> For example, if A is 15 minutes, then B is 1.5 hrs.
But you can answer the question yourself by comparing the query run against cold caches (after a reboot, or various command-line tricks to purge cache) vs against warm caches (twice back-to-back).
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
On Mar 21, 2011, at 12:03 PM, preetika tyagi wrote: > I tried running the same query after reboot and back-to-back, it was taking less time in both the cases. It means the problemis something else. > > Can there be a reason which is more hardware/operating system specific and due to which the behavior is not uniform? While I do have a couple of ideas, you're probably better served by letting those here with more optimization experiencehelp you, as their answers will be more complete. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice