Comparison of optimized and unoptimized queries 

Context in source publication

Context 1

... EXISTS. It is clear that the first step of implementing NOT EXISTS is to parse a query and recognize the NOT EXISTS clauses. Finding the clauses is straightforward since it is a standard traversal of a tree generated to represent the input query. But once these locations are found, we cannot immediately begin the genera- tion of queries because there are two main issues that must be resolved. First, the naming of table aliases could conflict between the outer and inner queries. Second, we need to generate the queries in the correct order to produce the alternating in signs that we desire for the probabilities. The first problem requires a renaming that is done at the moment an input query is parsed. For every table alias that is given, we will append on a suffix to make the name unique from all others. The suffix chosen for MystiQ was X where X is a non-negative integer. An example of this renaming is shown below: The second problem is much more simple to solve. In order to generate the new queries in the correct order, we first make a pass over the input query and extract/remove all the inner queries. At the end of this process, we have a list of all the nested queries and the outer query we need. For the first of the nested queries, we merge it with the outer query, then we take another nested query, if it exists, and merge it with the first two generated queries in reverse order (just as Fig 3 demonstrated). We proceed in this fashion for all nested queries. After this process is done, we are ready to begin calculating probabilities. Calculation of probabilities has two pathways that it can take. The first is to compute the probabilities explicitly, taking advantage of the safe plans optimization. The second is to use the multi-simulation method. Clearly, explicitly calculating the probability is much more desirable, so unless a user specifies that simulations must be run, MystiQ will check if all of the generated queries, which are all monotone, can be computed exactly. If so, we can compute the NOT EXISTS query by substituting the probability values directly into the formulas discussed in Sections 3 and 4 to get an exact solution. In a simulation, we no longer have exact probabilities to work with, but rather entire sets of confidence intervals, one set for each of the generated queries. Recall from Section 2, an interval represents a range in which the true probability lies for a particular result of the query. As mentioned in Section 4, the possible tuples of the original query are precisely those of the outer query. However, there is the additional complication of the dependency between the intervals for the original query and those of the generated queries. Since we never actually ran the original query we have no DNF formula to simulate, so we must use the other intervals. The endpoints for a tuple of the original query is equivalent to just finding the extreme points of the probability formula. For example, if one was computing the lower endpoint of an interval, they would be computing the smallest possible probability value that could result from substituting the probabilities of the equivalent tuples of the generated queries. Similarly, the upper endpoint would be the largest possible probability value. Figure 4 provides a demonstration of the actual calculation and equations. With this key issue resolved, the simulation process can proceed in much the same way as it did in the original implementation of MystiQ, with only a slight modification. The process begins with initializing a set of intervals (to represent the original query), S , to the intervals of the outer query. Then, one interval, I ∈ S , is chosen (exactly in the same way an interval would have been chosen before). However, this interval itself is not simulated. Rather, all intervals representing the same tuple from the generated queries are simulated. These associated intervals are then aggregated together using the method just described before to come up with the new interval for our original query. This process is repeated until a certain number of intervals are isolated from each other giving us the top-k tuples. Experiments were run in order to judge how well the implementation performed when adjusting various parameters. For starters, we adjusted database table sizes and toggled using safe plans. Next, we varied the number of Monte Carlo simulations run every step in the multisimulation algorithm. Finally, we experimented with how the query system handles more complex queries. First, experiments were run to test how the implementation performed over different data sizes. For this, we used a synthetic database (rows are generated with ’random’ probabilities) consisting of Products and Orders , and ran the following query: We varied the data set size (split almost equally between Products and Orders ): ∼ 4000, ∼ 9500, ∼ 14000, ∼ 19000, ∼ 28500 rows. We also ran this query, both, with the safe plan optimizations and without. Fig. 5 shows the results of those runs. From this graph, we see that the running times of NOT EXISTS queries exponentially rise as the data set size increases. However, as the size increases, the slopes do seem to grow shallower. We also see in the graph that safe plan optimizations make a significant improvement in performance. From the tests, there was generally a 94% improvement in time when optimizations could be found. Another experiment examined how changing the number of simulations run every step in the simulating process of MystiQ affected the running time. The graph in Fig. 6 charts the change in times as we varied the number of simulations from 10000 to 70000 on a fixed data set of about 10000 rows, about 5000 products and 5000 orders (we used the same query as before). We see that for both small and large values, the running times dramatically increase; however, the cause is very different. For the smaller values, the increase in running time is mostly due to the fact that many more steps have to run to reach enough simulations on the intervals to isolate the top k. For the larger values, the increase in time is due to the fact that, though we reduce the number of steps run, we are over simulating the intervals. The extra simulations are simply taking up time and not adding any new information. The middle numbers, particularly at around 30000, balance the two problems well. They run enough simulations to reduce the number of steps taken, but they do not over simulate the intervals. Although Fig. 6 showed 30000 to be the optimal value for the Number of Simulations per Step , this is only for this particular case. Different queries will have different results. However, even in those cases, the graph of times ought to follow the same curve since these queries face the same problems, only it will be translated along the x-axis. Lastly, experiments were performed on gathered data closely related to the RFID data (times when a person entered and left a room). A complex query was written that searched for consecutive events of entering and leaving a room for a particular person, but the data set size was only 660 tuples (split between entering and leaving rooms). The query, at first, used two NOT EXISTS queries. Here is an example of the query: Then the query was translated into a single NOT EXISTS query to see how timings are affected. This new query is found below (where the AllSightings table is simply the union of EnteredRoom/LeftRoom events). The results of the test showed that it is about 25 times slower when adding one additional level of complexity. With only one NOT EXISTS sub query, results were found in 51.062 seconds where as by adding one more level of complexity, it took 1260.389 seconds. This increase comes from executing twice the number queries and running simulations on twice the number of sets of intervals. In addition to the immediate results of the experiment, another observation was made. An issue arises when there are many probabilities that lie very close together, particularly when they are all close to zero (which was the case for this experiment). While executions were run, there was a clear slow down when all the intervals narrowed and were centered about zero. At this point, the simulator makes only small changes to an interval’s endpoints leading to longer execution to distinguish a top k set of intervals. This is more prevalent in NOT EXISTS queries since using the NOT EXISTS clause, we can more easily send more probabilities closer to zero, but if certain monotone queries were chosen carefully over data sets with very small probabilities, it would also be possible for this case to arise. Systems for managing uncertain data need to support queries with negated subgoals, such as SQL queries with NOT EXISTS predicates. We have described in this paper an approach for supporting such queries, while leveraging much of the infrastructure of an existing probabilistic database management system. We have described optimizations, which in our preliminary experiments show improvement of performance by about 94%. However, the execution time still is fairly slow and can be improved upon. As our experiments show, the running times are seemingly exponential with respect to the data size. Also, as the complexity of the query increases, there is also a significant increase in running time. Further improvements in any of these areas would be extremely beneficial to making the system more practical to use in many different environments. Possible directions for future work would be to improve the simulation process or improving the interpretation of a NOT EXISTS query. Some specific areas that can be improved include implementing a faster way of running Monte Carlo steps, or understanding how to better choose intervals to simulate, or even figuring out how to choose the number of steps to take each time we simulate an interval. Other work could be to find better ways to break apart a ...