May 17, 2013 at 9:05 am
Situation, I do not have the power to change the situation, but want to understand the situation.
Orders normaly belong to single clients, the database is partioned on clients, so most orders also follow the same partitioning.
Except for orders which belong to multiple clients, they can be spread over multiple partitions.
So a general template for accessing orders is:
Create a temptable with the orders and their partioning in the table. With any access now the table is used in the where clauses.
This code is used for all sitations, also for the situations when there is only a single partition for the order (far over 98 percent of the time), so there is always a temptable which holds that order for that single partition (a single row in the temptable).
This also works similar for orderlines, ordercomments, orderstates etc.
How does partitioning handle this situation?
(The code works, that is not the problem).
Is there an issue with this code?
Can the optimizer handle this kind of code efficiently?
Thanks for remarks, suggestions and your time,
Ben Brugman
May 17, 2013 at 9:32 am
ben.brugman (5/17/2013)
Can the optimizer handle this kind of code efficiently?
May be.
It depends on exact code and other details - all details - table design, structure, indexes etc.
May 17, 2013 at 10:07 am
partitioning the data, just by itself, does not mean the queries hitting it will be faster;
only on the queries where the optimizer determines that all the data required is all in a specific partition would the optimizer use a plan that shortcuts it to use the partition in question.
the rest of the rules as far as indexing all apply...good indexes for the query, sarg-able arguments, whether the indexes INCLUDE the columns needed in the query, or if it has to go to the clustered index to get other columns to return all have substantially more to do with performance and optimization than just partitioning the data in general.
Lowell
May 17, 2013 at 10:46 am
Lowell (5/17/2013)
partitioning the data, just by itself, does not mean the queries hitting it will be faster;
I am not concerned that the queries will be hitting it faster.
I am concerned that the majority of the calls which should only be processed by one partition, is going to be processed by more or all partitions. Because there is potentially a table which could potentially have something for each partition.
Although I know that this will never happen, can the optimiser see that. I am worried that if the selection for the partition is held in a (temp) table, that this get's passed on to each partition which then determines that there is nothing fitting the query, but still uses some CPU to determine that or wordt does some indexsearches which deliver no rows at all.
(For the large majority of all calls, but not for all calls).
When in a table is the partition key hidden for the optimizer of does the optimizer check for the amount of partitions in this table.
(My reasoning 🙂
If only one plan is used it has to visit all partitions all the time, because potentially the table could hold a row for each partition.
Ben
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply