Index-based adaptive connection size estimation

Abstract

Systems, methods, and computer media are described for index-based join size estimation. For a join operation between two tables, a filter is applied to the first table, resulting in a filter output. The filter output is then sampled. For each sample, an index of the second table is accessed and a count of records in the second table that match the sample is retrieved. Using the sample size and the retrieved count from the index of the second table, the data size of the join can be efficiently and accurately estimated. Variance-based calculations can also be used to assess the statistical confidence of the estimate.

Description

Technical Field

[0001] This disclosure relates to database management systems, and more specifically, to methods for estimating the join size for queries within a database management system. Background Technology

[0002] For a given query, there are usually multiple possible execution plans that achieve the same result. However, different execution plans can use vastly different amounts of computational resources. For example, join operations can be ordered in the execution plan to avoid inefficiencies such as creating large intermediate results. Summary of the Invention

[0003] A method for estimating the size of a join is disclosed, the method comprising: applying a filter to the first table in a join operation between the first table and the second table, and generating a filter output; sampling the filter output to generate a filter sample, the filter sample comprising a plurality of records from the filter output; for a corresponding plurality of records in the filter sample, accessing an index of the second table and retrieving a count in the second table of records corresponding to the records in the filter sample; and estimating the data size of the join operation based on the count of the corresponding records in the filter sample.

[0004] A computational system is disclosed, configured to execute computer-readable instructions for performing a join size estimation operation, the operation comprising: for a join operation between a first table and a second table to which a filter has been applied, performing iterations until a statistical confidence level of a join factor for the join operation is reached, the iteration comprising: sampling the filter output of the filter using a sample size to generate a filtered sample, the filtered sample comprising a number of records from the sample size number of the filter output; for a corresponding record in the filtered sample, accessing an index of the second table and retrieving a count in the second table of records corresponding to records in the filtered sample; calculating the join factor based on the count of the corresponding record in the filtered sample and the sample size of the filtered sample; and, if it is determined that the statistical confidence level of the join factor has not yet been reached, determining a new value for the sample size for the next iteration; and estimating the data size of the join operation using the join factor.

[0005] Disclosed is a computer storage device storing one or more computer-executable instructions for estimating a join size, the estimation comprising: for a join operation between a first table and a second table, the join operation being associated with executing a received query, applying a filter to the first table, and generating filter output; performing index-based sampling, comprising: sampling the filter output to generate a filter sample, the filter sample comprising a plurality of records from the filter output; and for a corresponding plurality of records in the filter sample, accessing an index of the second table and retrieving a count of records in the second table corresponding to records in the filter sample; calculating a join factor for the join operation based on the count of the corresponding records in the filter sample; performing one or more iterations until the target statistical confidence level of the join factor is reached, the iteration comprising: re-performing index-based sampling; recalculating the join factor; and evaluating the statistical confidence level of the join factor; estimating the data size of the join operation using the join factor; and sorting the join operation in a query execution plan of a received query based on the estimated data size. Attached Figure Description

[0006] Figure 1 An example method for estimating connection size is shown.

[0007] Figure 2 An example system configured to estimate connection size using an index-based method is shown.

[0008] Figure 3 An example iterative method for estimating connection size is shown.

[0009] Figure 4 This is a flowchart illustrating an example iterative join size estimation method in which an index lookup is used.

[0010] Figure 5 An example method for estimating join size is shown, where join operations are ordered in the query execution plan.

[0011] Figure 6 This is a diagram illustrating a general implementation environment in which some of the described examples can be implemented. Detailed Implementation

[0012] The examples described in this article typically estimate connection size using an index-based approach. Some examples also use variance-based adaptive sampling methods to provide statistical confidence in the connection size estimate.

[0013] When a query (e.g., a Structured Query Language (SQL) query) is received in a Database Management System (DBMS), an execution plan is formulated and / or followed to perform various operations required to retrieve the query results. The order in which operations are executed and the types of operations performed can significantly impact the amount of data processed, computation time, or other resources required to retrieve results.

[0014] As an example, consider a join of three tables, T1, T2, and T3. Only two tables can be joined at a time, so the two tables must be joined first to create an intermediate result, which is then joined with the remaining tables. For example, if the intermediate result formed by joining T1 and T2 is small, it might be more efficient to join T1 and T2 first, and then join T3 to the intermediate result. Conversely, if the intermediate result formed by joining T1 and T2 is large (e.g., a many-to-many join), joining T1 and T2 first might be more resource-intensive. Similarly, different physical algorithms (e.g., hash pushdown join, sort-merge join, etc.) can be chosen based on the data size. As an example, hash pushdown join is generally more efficient than other physical algorithms (e.g., compared to sort-merge join). However, it has higher memory requirements, which may make it a poor choice for large amounts of data.

[0015] Accurately estimating join sizes allows for the efficient generation of query execution plans through query optimization. Query optimization is the process of determining the order in which operations should be performed, which physical algorithms should be used to perform the operations, and other aspects of the query execution plan. In this context, "optimization" does not necessarily mean determining the "best" execution plan, but rather the process of making the query execution plan resource-efficient.

[0016] Connection size (also known as "cardinality") estimation can be performed in several ways. Some methods are machine learning-based and involve the characterization of intermediate results (creating feature vectors) and model training. Other methods include online (dynamically performed) or offline (samples are stored in memory) sampling, or using histograms / statistics.

[0017] The example described in this article uses an index-based approach for join size estimation. Consider an example of joining table A with table B, having a filter associated with table A but no filter associated with table B. The filter is applied to table A to create a filter output (records in table A that match the filter criteria). Sampling is then performed on the filter output (instead of sampling the unfiltered base table as in other methods) to create a filtered sample. Table B has an associated index. The example described in this article retrieves from the index of table B and uses the match count (the count of matching records), instead of sampling table B and performing a join between the filtered sample and the sample from table B.

[0018] In this approach, for each record in the filtered sample, an index of table B is accessed to determine the count of matching records in table B. Index lookups are fast, efficient, and do not incur significant additional storage costs. The sample size of the filtered sample and the match count (e.g., the sum of match counts for all records in the filtered sample) can be used to estimate the data size for the join operation between tables A and B.

[0019] In some of the described examples, a variance-based approach is used to evaluate the statistical confidence of the join size estimate. Multiple iterations can be performed to sample the filter output and retrieve match counts from the index of Table B, and variance calculations can be used to evaluate the sample size and match count for each iteration. If the desired statistical confidence is not achieved, another iteration can be performed. In some examples, a new sample size is determined after each iteration. The number of iterations can be limited by specifying a budget for the maximum number of index lookups to limit the resources spent estimating the join size.

[0020] The described example effectively provides accurate join size estimates with statistical confidence. The index-based approach reduces the computational resources required for additional sampling and improves accuracy by applying filters before sampling and variance calculation. The improved join size estimation is used to generate more efficient query execution plans, resulting in faster, less resource-intensive query execution. See below for reference. Figure 1-6 Example description.

[0021] Cost-based query optimizers rely on cardinality estimates of intermediate results to avoid suboptimal query execution plans. However, when faced with ad hoc queries on large datasets, these optimizers can produce large estimation errors, leading to a sharp decline in overall performance. This error occurs because many join estimation algorithms use strong independence and consistency assumptions. Moreover, equijoins on skewed data with filtered predicates tend to cause these assumptions to fail. Since cardinality estimates of results with multiple joins depend on estimates of lower-level joins, improving the accuracy of join size estimates in a bottom-up manner can significantly improve performance.

[0022] Some join size estimation methods using offline samples perform poorly in filtering and may suffer from insufficient sample size. Some query optimizers use persistent histograms. The associated storage space of these methods is a significant deterrent, especially with persistent offline samples. The described example estimates join size by: 1) sampling the filtered base table, and 2) utilizing persistent counts in the index (e.g., a B+ tree index). The method can also provide statistical confidence in the estimate.

[0023] The described examples can be easily incorporated into query optimizers that leverage bottom-up enumeration and evaluate filter predicates before optimization. Compared to existing machine learning-based methods, the described examples are easier to implement or obtain from existing sampling support. The join size estimate is generated using persistent counts from index-based sampling. The amount of sampling to be performed can be limited by achieving a parameterized budget or sufficient statistical confidence in the estimate. On a subset of industry-standard benchmark queries involving joins on skewed data, the described examples improve overall execution time by 16%. In queries where the execution plan is altered by the improved estimates from the described examples, the average percentage improvement in individual execution time is 34%.

[0024] Figure 1 A method 100 for estimating join size is illustrated. In process block 102, a filter is applied to the first table in the join operation between the first and second tables, and a filter output is generated. A filter (also called a filter predicate) specifies a value or characteristic, and records in the filter output match that value or characteristic. In the case of such a "one-to-many" join, the first table can also be called the "primary key source," and the second table can also be called the "foreign key source." Each value in the latter is guaranteed to have exactly one matching value in the (unfiltered) former. In some examples, the filter can be applied to both the first and second tables.

[0025] In process block 104, the filter output is sampled to generate a filter sample. The filter sample is a subset of records from the filter output. Sampling can be performed based on a predefined sample size (such as 500 records, 1000 records, 1500 records, etc.) of the total number of records in the filter output. In some examples, a percentage of the first table or the filter output (e.g., 1%, 5%, 0.1%, etc.) can be used as the sample size.

[0026] For each corresponding record in the filtered sample, the index of the second table is accessed in procedure block 106, and the count of the records in the second table corresponding to the records in the filtered sample is retrieved. In procedure block 108, the data size of the join operation is estimated based on the count of the corresponding records in the filtered sample. Table 1 below illustrates a detailed example estimation method.

[0027] As a concrete example, the first sample might have a match count of 5, the second sample might have a match count of 3, and the third sample might have a match count of 11, and so on. The match counts of the samples can be summed (sample size n). i =3, Match count X i =19). Connection factor μ i By X, the number of matches retrieved can be increased. i Divide by sample size n iThis is used for calculation. Then, the connection factor can be used to estimate the connection size, as shown in Table 1.

[0028] In some examples, sampling, index access, and record retrieval are performed iteratively until a target statistical confidence level or resource consumption threshold is reached. The resource consumption threshold can be the budget for index lookups (e.g., a limit of 5000 lookups), a time threshold (e.g., 3 seconds), or another threshold. In examples where the resource consumption threshold is the available index lookup count (budget), the budget can be decreased by the sample size per iteration (because there is one index lookup per sample). In examples that calculate the join factor, the join factor can be recalculated for each iteration. After each iteration, a new sample size can also be determined for sampling in the next iteration.

[0029] A statistical confidence level can be determined using a variance-based approach that assesses the counts of the sampled and retrieved records. A quality test can then be used to evaluate the confidence level. For example, an upper bound on the population variance of the connection factor can be calculated. In other examples, the sample variance, an unbiased estimator of the population variance between the matched counts, can be calculated. Typically, a quality test checks whether the variance measure is small enough. For example, a confidence interval around the average matched connection factor estimate can be derived using the sample variance, and the width of that interval can be compared to a threshold.

[0030] In some examples, both resource consumption thresholds and statistical confidence levels are used simultaneously. In this approach, iteration continues to pursue the desired statistical confidence level as long as there is budget remaining. If the budget is exhausted before reaching the desired confidence level, the latest join size estimate or join factor can be used, or the default rule-based join size estimation method can be used instead of index-based sampling estimation. The default method might be the one used by SAP IQ database analysis software, where various heuristics are used to improve naive estimates based on the independence assumption by leveraging available pre-computed information about the distribution of join predicate column data, known or inferred relationships between join tables based on the database schema or query join topology, or other sources of statistical information about the joined data. Other default methods are also possible.

[0031] In some examples, method 100 is executed after determining that the join operation has one or more characteristics that indicate the default size estimation method may be inaccurate. For example, in cases where the join operation is a many-to-many or one-to-many join that filters primary key sources, the default size estimation method may be inaccurate. If the default method is likely accurate because the join operation does not have such characteristics, then the default method can be used. This classification allows the use of the default method (which is often simpler and faster) where appropriate and accurate, and allows the use of index-based sampling methods in other cases.

[0032] The join operation corresponds to the received query. Method 100 may also include determining or modifying the query execution plan based on the estimated data size of the join operation. For example, the join operation may be ordered relative to other operations in the query execution plan, or a physical algorithm may be selected for the join operation.

[0033] Method 100 estimates the join size between two tables, but it can also be used to estimate the join size of more than two tables. In this case, records in the second table that match the filtered sample can be sampled to create intermediate samples (also known as intermediate results), and the join size between the intermediate samples and the third table can be estimated using the method of Method 100.

[0034] Figure 2 A system 200 configured to estimate connection size using an index-based method is shown. System 200 can be implemented separately. Figure 1 Method 100 and Figure 3 , 4 Methods 300, 400, and 500 of 5 are also applicable. For example, system 200 may be used with or as part of an online analytical processing (OLAP) or other system. Query 202 is received from application 204 at query optimizer 206. For cases where query 202 is parsed before optimization, query optimizer 206 may also include parsing functionality, or a separate query parser (not shown) may be used prior to query optimizer 206. Query optimizer 206 is part of a database management system (DBMS) implemented on computing devices 208.

[0035] Multiple query execution engines 210 execute optimized queries on multiple databases 212 and return query results 214. A join size estimator 216 uses methods 100, 300, 400, or 500 to estimate the size of join operations so that the query optimizer 206 can sort the join operations, select a physical algorithm, and otherwise organize an efficient query execution plan for execution by the multiple query execution engines 210.

[0036] The connection size estimator 216 can access multiple databases 212, which are configured to store tables 218 and 220. Tables 218 and 220 can be... Figure 1 Method 100 references the first and second tables. Index 222 is associated with and stored with table 220. Match counts of filtered samples can be retrieved from index 222 based on table 218. In some examples, each table stored in database 212 has an associated index.

[0037] Figure 3An iterative join size estimation method 300 is illustrated for a join operation between a first table and a second table with filters applied. Iterations are performed until the statistical confidence level in the join factor of the join operation has been reached. The iterations include process blocks 302, 304, 306, and 308. Similar to method 100, method 300 can also be performed using intermediate samples and with these intermediate samples as the first table. The second table is a base table with an index that has persistent match counts.

[0038] Intermediate samples can be collected for joins so that sampled tuples from filtered samples originating from the first table are joined with randomly sampled tuples from the second table. If the size estimate of an intermediate sample passes a quality test, such an intermediate sample is considered "valid." In higher-level join size estimation, a valid sample can be considered the base table, as it contains tuples that are statistically sufficient to capture the relationships between join attributes / columns in the lower-level base table. The data structure can be used as a "cache" to store these intermediate samples when the query optimizer performs a "bottom-up" enumeration. As a result, statistically confident intermediate samples for two-table joins can be used for size estimation of three-table joins, and so on. If no intermediate samples are available for joins between the base tables and some intermediate results, their respective size estimates can be performed using conventional methods.

[0039] In procedure block 302, the filter output of the filter is sampled using the sample size to generate a filtered sample, which includes the number of records from the filter output that corresponds to the sample size. In procedure block 304, for the corresponding record in the filtered sample, the index of the second table is accessed, and the count of the record corresponding to the record in the second table in the filtered sample is retrieved. In procedure block 304, the join factor is calculated based on the count of the corresponding record in the filtered sample and the sample size of the filtered sample. When it is determined that the statistical confidence level of the join factor has not yet been reached, a new value for the sample size is determined for the next iteration in procedure block 308. An example of sample size determination is discussed below with reference to Table 1. In some examples, a predetermined number of iterations with a fixed sample size may be performed before determining a new value for the sample size used for successive iterations. In other examples, the sample size remains unchanged for each iteration (i.e., the "new value" of the sample size is the same for each iteration). After the iteration is complete, the data size of the join operation is estimated using the join factor. The query execution plan can then be modified or determined based on the join size estimate.

[0040] Figure 4Example method 400 for estimating the join size between two tables is shown. Method 400 begins in process block 402. In decision block 404, it is determined whether the index lookup budget has been exhausted. If so, method 400 ends in process block 406, and the estimate is determined using either the most recent join size estimate or the default method. For the first iteration, the budget is not exhausted. If the budget is not exhausted, the first base table to which the filter has been applied is sampled to determine the filtered samples, and the budget is updated based on the number of samples (e.g., decreasing or increasing). In process block 410, the match count is retrieved by accessing the index of the second table. In process block 412, the global sample size N and the global match count sum X are updated. With each iteration, the new sample size is added to the global sample size, and the new sum of match counts is added to the global match count sum.

[0041] In procedure block 414, the connection factor is calculated. For example, the connection factor can be calculated as X / N. In procedure block 416, the population variance in the connection factor is calculated. In decision block 418, the calculated variance is compared with the expected variance (confidence level). If the expected variance has not yet been reached, method 400 returns to decision block 404 and begins another iteration. In some examples, a new sample size will be calculated for the next iteration. If the expected confidence level has been reached, the connection size estimate is calculated using the connection factor in procedure block 420. Method 400 ends in procedure block 422.

[0042] Figure 5 An example method 500 for estimating the join size is illustrated. In process block 502, for a join operation between a first table and a second table, the join operation is associated with executing a received query, applying a filter to the first table, and generating a filter output. In process block 504, index-based sampling is performed. Index-based sampling includes sampling the filter output in process block 506 to generate a filter sample, the filter sample including multiple records from the filter output, and in process block 508, for the corresponding multiple records in the filter sample, accessing the index of the second table and retrieving the count of records in the second table corresponding to the records in the filter sample.

[0043] In procedure block 510, the join factor of the join operation is calculated based on the count of the corresponding records in the filtered samples. In procedure block 512, if it is determined that the target statistical confidence level of the join factor has not yet been reached, one or more iterations are performed until the target statistical confidence level is reached. The iterations include procedure blocks 514, 516, and 518. In procedure block 514, index-based sampling is performed again. In procedure block 516, the join factor is recalculated. In procedure block 518, the statistical confidence level of the join factor is evaluated.

[0044] In procedure block 520, a join factor is used to estimate the data size of the join operations, and in procedure block 522, the join operations in the query execution plan of the received query are sorted based on the estimated data size.

[0045] Detailed example

[0046] Consider equi-joins It has a many-to-many relationship with a number of parameters or filters the primary key source, where σ p σ is the filter predicate applied to table A, and where σ q This is the filter predicate applied to table B. In the first case, sampling from A is typically chosen, defined as the ratio of cardinality to distinct counts being closer to 1 compared to B. Otherwise, A is defined as the primary key source for filtering, i.e., p is not a tautological proposition. Assumptions:

[0047]

[0048]

[0049] The connection factor μ is defined as σ p (A) The ratio of the true size of the connection between itself and its unfiltered connection partner B (J', shown in Equation 2) to the size of the sampled filtering base table. That is,

[0050]

[0051] Now, applying the assumptions of consistency and independence, it is as follows:

[0052]

[0053] Note the use of estimated connection factors The equivalent join can be derived as follows: Size estimation

[0054]

[0055] Under certain conditions, SAP IQ can calculate |σ| before the connection order is selected. p (A)|,|σ q (B)| exact value and result set σ p (A), σ q (B). Therefore, the goal is to generate A precise estimate of σ. Basically, for σ... p The sample in (A) is used to find the number of tuples in B that match the join attribute values ​​and are estimated to pass the filter predicate q. This number is referred to as the filter match count because the count of matches or join tuples in B is scaled selectively by q. Given σp Sample Sσ on (A) p (A) We use a B+ tree index to access the count of join tuples in B, but we never materialize the join between the two relations. Therefore, Not sampled. Instead, based on σ p For samples (A), the sum of filtered match counts is calculated at low cost.

[0056] The consistency assumption is then applied, which states that the ratio of connected tuples satisfying q across all match counts is equal to the selectivity of q. Various methods can be used to build statistical confidence, including those using adaptive statistics. Specifically, sampling can be performed in rounds, where after a fixed number of rounds, variance-based calculations can be used to perform a quality test to determine if more sampling is needed. If so, the minimum sample size for the next round is calculated. However, block-level sampling is not performed, and connections are not fully computed. Instead, the calculations are performed separately. Conversely, from σ p (A) Extract samples and use filtering to match counts and sums.

[0057] Assume that an action was performed on σ p (A) K rounds of sampling. In the i-th round of sampling,

[0058]

[0059] Where Xi is the sum of the filtered match counts. For each round, we obtain

[0060]

[0061] Using the central limit theorem, we obtain

[0062]

[0063] Where σ 2 This is the population variance of the matching counts. Based on this result, variance-based quality checks can be applied to σ. 2 The upper bound of the estimate can be determined. For example, the quality test used in the paper can be used. Furthermore, if the quality test fails, the minimum sample size required to pass in the next round can be inferred using the above calculations.

[0064] In these examples, the estimate is only injected into the query optimizer's cost model if the quality test shows that the chosen variance metric is low enough. If the quality test fails and another round of adaptive sampling will exhaust the budget, the default method using constant-time estimation can be used.

[0065] Table 1 below illustrates the relationship with equations 1-8 and Figure 4Method 400 is a consistent method for estimating the specific join size: EstimateJoinSize(A, B, budget). This method takes the budget and base tables A and B as input. The budget is the number of allowed index lookups. It returns an estimated join size.

[0066]

[0067]

[0068] Table 1 – Example Connection Size Estimation

[0069] In Table 1, the index lookup budget is decremented in operation 4 and checked in operation 5 to ensure the budget is not exhausted. In operation 8, the filter output of Table A is sampled, and in operation 9, the match count is retrieved from the index of Table B. In operation 10, the current iteration sample size n is used. i Update the global sample size N, and use the matching count X of the current iteration. i Update the global match count X. X / N is the connection factor, μ. Variance is calculated using the connection factor in operation 11, and operation 12 determines whether the desired statistical confidence level has been achieved through a quality test that checks if the variance metric is sufficiently small (e.g., meets a threshold). If so, the connection size is estimated using the connection factor in operation 13 according to Equation 5. If the desired confidence level has not been achieved, the sample size for the next iteration is determined in operation 15. The next sample size can be determined using a variance metric, a fixed value, or a formula related to the population size (the filter output of A).

[0070] To evaluate the described examples, a subset of queries was selected from open-source, industry-standard benchmarks based on the existence of joins with characteristics suitable for the default estimation method. The accompanying synthetic dataset was generated using open-source software with a moderate skew frequency. Three metrics were considered: overall workload execution time, Q-error in cardinality estimation of two table joins, and individual query execution time. Regarding overall workload time, the examples above showed a 16% improvement compared to SAP IQ's estimation algorithm. For 31% of the evaluated queries, introducing adaptive join size estimation improved the execution time of individual queries. Conversely, only two queries experienced a performance degradation despite better estimation.

[0071] For individual query execution time, the average, maximum, and minimum percentage improvements were 14%, 88%, and -16%, respectively. In queries with improved execution plans, the average improvement in execution time was 55%. The significant improvements in query execution plans are attributed to changes in both the join order and the physical algorithm. Furthermore, in 75% of the queries improved through the example described, the dimension tables were sampled entirely within budget due to their relatively small size. One such query showed an 88% improvement in query execution time.

[0072] The observed improvements in execution time and minimum overhead suggest that SAP IQ and other OLAP systems can benefit from index-based adaptive sampling.

[0073] Example computing system

[0074] Figure 6 A general example of a suitable computing system 600 is depicted, in which the described innovations can be implemented. The computing system 600 is not intended to impose any limitations on its scope of use or functionality, as the innovations can be implemented in various general-purpose or special-purpose computing systems.

[0075] refer to Figure 6 The computing system 600 includes one or more processing units 610, 615 and memories 620, 625. Figure 6 In the diagram, the basic configuration 630 is included within the dashed lines. Processing units 610 and 615 execute computer-executable instructions. The processing units can be general-purpose central processing units (CPUs), processors in application-specific integrated circuits (ASICs), or any other type of processor. In a multiprocessor system, multiple processing units execute computer-executable instructions to increase processing power. For example, Figure 6 A central processing unit 610 and a graphics processing unit or coprocessor 615 are shown. Physical memories 620 and 625 may be volatile memories (e.g., registers, caches, RAM), non-volatile memories (e.g., ROM, EEPROM, flash memory, etc.), or some combination thereof, accessible by the processing unit(s). Memories 620 and 625 store one or more innovative software 680 described herein, implemented in the form of computer-executable instructions suitable for execution by the processing unit(s). For example, memories 620 and 625 may store… Figure 2 Connection size estimator 216.

[0076] The computing system may have additional functionalities. For example, computing system 600 includes storage 640, one or more input devices 650, one or more output devices 660, and one or more communication connections 670. Interconnection mechanisms (not shown), such as buses, controllers, or networks, interconnect the components of computing system 600. Typically, operating system software (not shown) provides an operating environment for other software executing in computing system 600 and coordinates the activities of the components of computing system 600.

[0077] Physical storage 640 may be removable or non-removable and includes disks, magnetic tapes or tape cartridges, CD-ROMs, DVDs, or any other medium that can be used to store information and is accessible within computing system 600. Memory 640 stores instructions for implementing one or more of the innovative software 680 described herein. For example, memory 640 may store… Figure 2 Connection size estimator 216.

[0078] Multiple input devices 650 may be touch input devices, such as a keyboard, mouse, pen or trackball, voice input device, scanning device, or another device that provides input to the computing system 600. For video encoding, multiple input devices 650 may be a camera, video card, TV tuner card, or similar device that accepts video input in analog or digital form, or a CD-ROM or CD-RW that reads video samples into the computing system 600. Multiple output devices 660 may be a monitor, printer, speaker, CD writer, or another device that provides output from the computing system 600.

[0079] Multiple communication connections 670 enable communication with another computing entity via a communication medium. The communication medium transmits information such as computer-executable instructions, audio or video inputs or outputs, or other data in modulated data signals. A modulated data signal is a signal whose one or more characteristics are set or altered in a manner that encodes information in the signal. By way of example and not limitation, the communication medium may be electrical, optical, RF, or other carriers.

[0080] These innovations can be described in the general context of computer-executable instructions (such as instructions included in program modules, instructions that execute on a computing system targeting a real or virtual processor). Typically, program modules include routines, programs, libraries, objects, classes, components, data structures, etc., that perform specific tasks or implement specific abstract data types. In various embodiments, the functionality of program modules can be combined or divided among program modules as needed. The computer-executable instructions for program modules can execute within a local or distributed computing system.

[0081] The terms “system” and “device” are used interchangeably herein. Unless the context clearly indicates otherwise, neither term implies any limitation on a type of computing system or computing device. Generally, a computing system or computing device can be local or distributed and can comprise any combination of dedicated hardware and / or general-purpose hardware with software that implements the functions described herein.

[0082] For ease of demonstration, the terms "determine" and "use" are used in detail to describe computer operations in a computing system. These terms represent high-level abstractions of operations performed by the computer and should not be confused with human actions. The actual computer operations corresponding to these terms vary depending on the implementation method.

[0083] Example Implementation

[0084] Although some operations in the disclosed methods are described in a specific, sequential manner for ease of presentation, it should be understood that this descriptive style includes rearrangement unless the specific language described below requires a particular order. For example, in some cases, sequentially described operations can be rearranged or performed simultaneously. Furthermore, for simplicity, the accompanying drawings may not show the various ways in which the disclosed methods can be combined with other methods.

[0085] Any of the disclosed methods can be implemented as computer-executable instructions or a computer program product stored on one or more computer-readable storage media and executed on a computing device (e.g., any available computing device, including smartphones or other mobile devices that include computing hardware). A computer-readable storage medium is any available tangible medium accessible in a computing environment (e.g., one or more optical discs, such as DVDs or CDs, volatile memory components such as DRAM or SRAM, or non-volatile memory components such as flash memory or hard disk drives)). See also, as an example. Figure 6 Computer-readable storage media include memories 620 and 625 and memory 640. The term "computer-readable storage medium" excludes signals and carrier waves. Furthermore, the term "computer-readable storage medium" excludes communication connections (e.g., 670).

[0086] Any computer-executable instructions used to implement the disclosed technology, and any data created and used during the implementation of the disclosed embodiments, may be stored on one or more computer-readable storage media. The computer-executable instructions may be, for example, a dedicated software application or software application or other software application (such as a remote computing application) accessed or downloaded via a web browser. For example, such software may be executed using one or more networked computers on a single local computer (e.g., any suitable commercial computer) or in a networked environment (e.g., via the Internet, a wide area network, a local area network, a client-server network (such as a cloud computing network), or other such networks.

[0087] For clarity, only selected aspects of the software-based implementation are described. Other details well-known in the art are omitted. For example, it should be understood that the disclosed techniques are not limited to any particular computer language or program. For instance, the disclosed techniques can be implemented using software written in C++, Java, Perl, JavaScript, Adobe Flash, or any other suitable programming language. Similarly, the disclosed techniques are not limited to any particular computer or hardware type. Certain details of suitable computers and hardware are well-known and do not need to be elaborated in this disclosure.

[0088] Furthermore, any software-based implementation (including, for example, computer-executable instructions for causing a computer to perform any of the disclosed methods) can be uploaded, downloaded, or remotely accessed via suitable communication devices. Such suitable communication devices include, for example, the Internet, the World Wide Web, intranets, software applications, cable (including fiber optic cables), magnetic communication, electromagnetic communication (including RF, microwave, and infrared communication), electronic communication, or other such communication devices.

[0089] The disclosed methods, apparatuses, and systems should not be construed as limiting in any way. Rather, the invention addresses all novel and non-obvious features and aspects of the various disclosed embodiments, individually and in various combinations and sub-combinations of each other. The disclosed methods, apparatuses, and systems are not limited to any particular aspect or feature or combination thereof, nor are the disclosed embodiments required to address any one or more particular advantages or problems solved.

[0090] The techniques from any example can be combined with the techniques described in any one or more other examples. Given that the principles of the disclosed techniques can be applied to many possible embodiments, it should be understood that the illustrated embodiments are examples of the disclosed techniques and should not be considered as limiting the scope of the disclosed techniques.

Claims

1. A method for estimating connection size, the method comprising: (a) Apply a filter to the first table in the join operation between the first and second tables, and generate the filter output; (b) Based on the sample size, the filter output is sampled to generate a filter sample, the filter sample comprising multiple records from the filter output; (c) For multiple records in the filtered sample, access the index of the second table and retrieve the count of records in the second table corresponding to the multiple records in the filtered sample; When the statistical confidence level of the link factor based on record counts and sample size has not yet been reached, a new value for the sample size is determined for the next iteration, and (b)–(c) are iterated with the new value of the sample size until the statistical confidence level of the link factor is reached; and The data size of the join operation is estimated based on the count of records in the second table that correspond to multiple records in the filtered sample.

2. The method according to claim 1, wherein, The iteration stops when the resource consumption threshold is reached.

3. The method of claim 2, wherein the resource consumption threshold is an available index lookup count, and the method further comprises subtracting the sample size used for the iteration from the available index lookup count before or after each iteration.

4. The method according to claim 3 further includes estimating the data size of the connection operation using a default estimation method when the resource consumption threshold is reached.

5. The method of claim 1, wherein the statistical confidence level is determined using a variance-based method, the variance-based method evaluating the count of sampled and retrieved records.

6. The method of claim 1, further comprising determining, before sampling the filter output, that the connection operation has one or more characteristics indicating that the default size estimation method may be inaccurate.

7. The method of claim 6, wherein one or more features include the join operation being a many-to-many join or a one-to-many join that filters the first table.

8. The method of claim 1, wherein the join operation corresponds to a received query, and the method further comprises determining or modifying a query execution plan for the query based on an estimated data size of the join operation.

9. The method according to claim 8, wherein, Determining or modifying the query execution plan includes at least one of the physical algorithms for sorting or selecting the join operations relative to other operations in the query execution plan.

10. The method of claim 1, further comprising sampling records in the second table corresponding to the records in the filtered samples to create intermediate samples.

11. The method of claim 10, further comprising estimating the data size of the join operation between the intermediate sample and the third table.

12. A computing system configured to execute computer-readable instructions for performing a connection size estimation operation, the operation comprising: For a join operation between the first and second tables where filters have already been applied, iterate until the statistical confidence level of the join factor for the join operation is reached. The iteration includes: Using a sample size, the filter output of the filter is sampled to generate a filter sample, which includes records from the filter output having a sample size number; For each record in the filtered sample, access the index of the second table and retrieve the count of records in the second table that correspond to the records in the filtered sample; The connection factor is calculated based on the count of records corresponding to records in the filtered sample in the second table and the sample size of the filtered sample; and When it is determined that the statistical confidence level of the connectivity factor has not yet been reached, a new value for the sample size to be used in the next iteration is determined; and The connection factor is used to estimate the data size of the connection operation.

13. The system of claim 12, wherein the statistical confidence level is determined by a variance-based method.

14. The system of claim 12, wherein the iteration further includes terminating the iteration when it is determined that a resource consumption threshold has been reached but the statistical confidence level has not yet been reached.

15. The system of claim 12, wherein the operation is performed after determining that the connection operation has one or more characteristics indicating that the default size estimation method may be inaccurate.

16. The system of claim 12, wherein the operation further comprises: When it is determined that the statistical confidence level of the data size estimate has been reached, the iteration ends, and the query execution plan for the query corresponding to the join operation is determined or modified based on the data size estimate.

17. One or more computer storage devices storing computer-executable instructions for estimating connection size, the estimation comprising: For the join operation between the first table and the second table, a filter is applied to the first table and a filter output is generated, the join operation being associated with the execution of the received query; Performing index-based sampling includes: The filter output is sampled to generate a filter sample, the filter sample comprising multiple records from the filter output; and For multiple records in the filtered sample, access the index of the second table and retrieve the count of records in the second table corresponding to the multiple records in the filtered sample; The connection factor of the join operation is calculated based on the count of records corresponding to multiple records in the filtered sample in the second table. If the target statistical confidence level of the connectivity factor has not yet been reached, one or more iterations are performed until the target statistical confidence level is reached, wherein the iterations include: Perform index-based sampling again; Recalculate the connection factor; and Evaluate the statistical confidence level of the recalculated connectivity factors; The data size of the join operation is estimated using the connection factor; and Based on the estimated data size, the join operations are sorted in the query execution plan of the received query.

18. The computer storage device of claim 17, wherein the iteration further comprises determining a new sample size for re-performing the index-based sampling.

19. The computer storage device of claim 17, wherein, for iteration, the connection factor is calculated by dividing the count of retrieved records by the sample size.

Images