Hive, time taken for partitioned vs unpartitioned database
So, I have 2 tables for a dataset which are unpartitioned table and partitioned table.
When I see the report for partitioned data, the cumulative CPU time decreased dramatically but the total time taken are the same compared to unpartitioned data.
Why is this ?
mysql database hive
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
add a comment |
So, I have 2 tables for a dataset which are unpartitioned table and partitioned table.
When I see the report for partitioned data, the cumulative CPU time decreased dramatically but the total time taken are the same compared to unpartitioned data.
Why is this ?
mysql database hive
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
What query? What is table content, format and partition column
– Gaurang Shah
Nov 26 '18 at 1:31
add a comment |
So, I have 2 tables for a dataset which are unpartitioned table and partitioned table.
When I see the report for partitioned data, the cumulative CPU time decreased dramatically but the total time taken are the same compared to unpartitioned data.
Why is this ?
mysql database hive
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
So, I have 2 tables for a dataset which are unpartitioned table and partitioned table.
When I see the report for partitioned data, the cumulative CPU time decreased dramatically but the total time taken are the same compared to unpartitioned data.
Why is this ?
mysql database hive
mysql database hive
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
asked Nov 25 '18 at 23:13
Geongu Aiden ParkGeongu Aiden Park
1716
1716
What query? What is table content, format and partition column
– Gaurang Shah
Nov 26 '18 at 1:31
add a comment |
What query? What is table content, format and partition column
– Gaurang Shah
Nov 26 '18 at 1:31
What query? What is table content, format and partition column
– Gaurang Shah
Nov 26 '18 at 1:31
What query? What is table content, format and partition column
– Gaurang Shah
Nov 26 '18 at 1:31
add a comment |
1 Answer
1
active
oldest
votes
As this is a strictly theoretical question you will get a strictly theoretical answer. Partitioning causes a table to be split into smaller tables with same structure. This makes your SELECT queries to execute on different tables (effectively SELECT * FROM table_part1 UNION ALL table_part2 vs SELECT * FROM table).
The overall time will be same because you are reading same amounts of data. You can put table_part1 and table_part2 on different physical disks, which probably will make reading faster as you will have less IO wait. But in general, for tables partitioned within same tablespace you will see comparable time for both queries (partitioned and unpartitioned).
As for CPU, we can speculate that there is some optimization in place that makes operating smaller tables easier. It is possible that the partitioned tables simply fit better into memory (including CPU caches). In this case it is possible that results will depend on the size of the initial and partitioned tables - for super-large tables on both sides you may end up with same CPU load either way.
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472962%2fhive-time-taken-for-partitioned-vs-unpartitioned-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
As this is a strictly theoretical question you will get a strictly theoretical answer. Partitioning causes a table to be split into smaller tables with same structure. This makes your SELECT queries to execute on different tables (effectively SELECT * FROM table_part1 UNION ALL table_part2 vs SELECT * FROM table).
The overall time will be same because you are reading same amounts of data. You can put table_part1 and table_part2 on different physical disks, which probably will make reading faster as you will have less IO wait. But in general, for tables partitioned within same tablespace you will see comparable time for both queries (partitioned and unpartitioned).
As for CPU, we can speculate that there is some optimization in place that makes operating smaller tables easier. It is possible that the partitioned tables simply fit better into memory (including CPU caches). In this case it is possible that results will depend on the size of the initial and partitioned tables - for super-large tables on both sides you may end up with same CPU load either way.
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
add a comment |
As this is a strictly theoretical question you will get a strictly theoretical answer. Partitioning causes a table to be split into smaller tables with same structure. This makes your SELECT queries to execute on different tables (effectively SELECT * FROM table_part1 UNION ALL table_part2 vs SELECT * FROM table).
The overall time will be same because you are reading same amounts of data. You can put table_part1 and table_part2 on different physical disks, which probably will make reading faster as you will have less IO wait. But in general, for tables partitioned within same tablespace you will see comparable time for both queries (partitioned and unpartitioned).
As for CPU, we can speculate that there is some optimization in place that makes operating smaller tables easier. It is possible that the partitioned tables simply fit better into memory (including CPU caches). In this case it is possible that results will depend on the size of the initial and partitioned tables - for super-large tables on both sides you may end up with same CPU load either way.
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
add a comment |
As this is a strictly theoretical question you will get a strictly theoretical answer. Partitioning causes a table to be split into smaller tables with same structure. This makes your SELECT queries to execute on different tables (effectively SELECT * FROM table_part1 UNION ALL table_part2 vs SELECT * FROM table).
The overall time will be same because you are reading same amounts of data. You can put table_part1 and table_part2 on different physical disks, which probably will make reading faster as you will have less IO wait. But in general, for tables partitioned within same tablespace you will see comparable time for both queries (partitioned and unpartitioned).
As for CPU, we can speculate that there is some optimization in place that makes operating smaller tables easier. It is possible that the partitioned tables simply fit better into memory (including CPU caches). In this case it is possible that results will depend on the size of the initial and partitioned tables - for super-large tables on both sides you may end up with same CPU load either way.
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
As this is a strictly theoretical question you will get a strictly theoretical answer. Partitioning causes a table to be split into smaller tables with same structure. This makes your SELECT queries to execute on different tables (effectively SELECT * FROM table_part1 UNION ALL table_part2 vs SELECT * FROM table).
The overall time will be same because you are reading same amounts of data. You can put table_part1 and table_part2 on different physical disks, which probably will make reading faster as you will have less IO wait. But in general, for tables partitioned within same tablespace you will see comparable time for both queries (partitioned and unpartitioned).
As for CPU, we can speculate that there is some optimization in place that makes operating smaller tables easier. It is possible that the partitioned tables simply fit better into memory (including CPU caches). In this case it is possible that results will depend on the size of the initial and partitioned tables - for super-large tables on both sides you may end up with same CPU load either way.
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
answered Nov 26 '18 at 11:31
Boris SchegolevBoris Schegolev
3,19651529
3,19651529
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472962%2fhive-time-taken-for-partitioned-vs-unpartitioned-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What query? What is table content, format and partition column
– Gaurang Shah
Nov 26 '18 at 1:31