0

I am using the following query as a base line on some unstructured data (no indexes, no clusters etc) and the query is performing better on the unstrutured data than when I add indexes to the join columns or add the tables to a cluster or hash-cluster. I feel my baseline query isn't ideal, my task is to take a baseline query and find a structure that performs the best with it but all the structures I have tried perform worse than the unstructured. Is there anything I can do to my baseline query that will at least find one structure that has a noticeable improvement over the unstructured?

Query:

SELECT Cust_name, price
FROM Customer, Sales
WHERE price > 1000
AND num_sold > 10
AND Sales.Cust_id = Customer.Cust_id;

This query is returning 108k+ rows out of 150k.

Here are the traces from the testing:

Unstructured: enter image description here

With index added on the join columns Sales.Cust_id and Customer.Cust_id: enter image description here

With both tables added to a cluster where the cluster index is the Cust_id: enter image description here

With Both tables added to a hash cluster with 10000 keys:

enter image description here

5
  • Full scans + hash join is a pretty efficient plan for your query as long as it all holds in memory. You have much less disk I/O for your first query than the last two, make sure you're running your tests multiple times to avoid cache effects. Also try queries that select <5% of the data and compare plans & efficiency then - the optimal structure often depends on what sort of query are expected (OLTP vs OLAP type thing). – Mat Sep 29 '15 at 5:02
  • What kind of queries would I need to be able to get a decent performance bump from either indexes, clusters or hash-clusters? – joe Sep 29 '15 at 5:31
  • You need to make the index useful, i.e. restrict on a small set or range of cust_ids. I'm not sure how your last structure could be helpful for this query though (but I'm not too familiar with clusters). – Mat Sep 29 '15 at 5:45
  • I tried to restrict the number of rows with the price and num_sold conditions, but you think I should make the condition on the index? i.e cust_id < 100 etc. And would searching for a single cust_id work or would I not see much difference? – joe Sep 29 '15 at 5:51
  • Try it out, best way to learn. (But think about it: you're not indexing the price or amounts, so the only way for the DB to get all rows that match these conditions is to scan all rows of the relevant table(s). If you want to avoid that, you need some sort of structure to allow the DB to narrow down your selection without that full scan.) – Mat Sep 29 '15 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.