On a large table partition, some partition data query time-out problems

A large table 50 partitions, partition data files in 150G

At this time the query efficiency is very low, some data partitioning is not great, but the query time will be in about 30 seconds

Some partitioned data with several G queries, but soon

How to optimize.?

Started by Rita at November 16, 2016 - 8:14 AM

The partition of latitude is very important, how to use zoning to see your query to the query, partitioning is favorable, the most important point is that the partition, decrease the range query to access. If your query often needs Association many areas, the performance is not high. This you should to some query analysis is given, the best execution plan, and then have a look the partition will not be very big difference, statistical information is not updated in a timely manner, and the partition number of lock. You'd better give a query execution plan two times very quickly and very slow to have a look

Posted by Greg at November 30, 2016 - 8:43 AM

What is the partition table structure, field? What is the query? Not these data can't analysis.

Generally speaking, the file data file partition corresponding, should be placed on a different disk drive, in order to improve query performance.
Also need to pay attention to the index fragmentation, regularly reforming.

Posted by Osborn at December 02, 2016 - 8:49 AM

1, In order to accelerate the speed of query by partition, then in your query, must take the district field queries to the where.

2, In addition, you mentioned above, relatively small partition, pass quickly, large partition query is very slow, this is normal, the,
Partition data imbalance, this is a question, then, that you need to partition table, build index.

Many people think that, as long as the partition table, speed will be faster, actually otherwise, if each partition your data on the billion, then you also need to build a partitioned index, which is in accordance with the (index field 1, district field) this combination to build index, it can quickly query.

Posted by Cathy at December 17, 2016 - 9:17 AM

The slow and fast partition partition on the same disk?

Posted by James at December 24, 2016 - 10:13 AM

Yes, a query condition is same, numerical boundary partition,
Problems with data, partition, partition data quickly, I would~~~


I have a look at where the execution analysis. . . . . .
And then look at the disk under different partition
The query time is long in service (win service) can also be,
But in the vweb application is prisoner. .

---------
Thanks for your

Posted by Rita at December 27, 2016 - 10:50 AM

The two query execution plan put up have a look, a slow to a fast

Posted by Greg at December 31, 2016 - 11:17 AM

Look at the execution plan.

Posted by James at January 10, 2017 - 11:27 AM