learning-it by example

#227 Data strategy 5.1 min

What Does One Query Cost? Cost Per Query From Timings

In a made-up traffic mix, one kind of query was 2 percent of the traffic and 60 percent of the compute cost. Here is how to find it.

1. In the DuckDB query cost demo, what is the method used to price a query in unit economics?
2. In the DuckDB query cost demo with a made-up traffic mix, which query type paid most of the compute bill, and how often did it run?
3. At a made-up rate of 20 cents an hour, what does a machine kept on cost per day, and what happens to the cost per query at only 1,000 queries a day?
4. In the DuckDB query cost demo, why does the code time each query once for the first run and then 9 more times, and keep the median?
5. In the DuckDB query cost demo, the timings came from one busy laptop. What does the video say about using them for a cloud machine?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Put in your own machine rate

    Copy the code folder first. This one needs DuckDB 2.0.0 from the video's requirements. In measure.py, change HOURLY_RATE from 0.20 to 0.40, then run python cost.py. Predict the dollars per 1,000 column for 1,000 queries a day before you run it. The machine costs 24 hours at the new rate, divided by 1,000 queries, times 1,000. Compare the busy column with the video's run: has it changed? The busy column will be close to the video's, not identical, because timings differ from run to run. Put HOURLY_RATE back to 0.20 afterwards.

    A hint

    The rate only prices time. The machine busy column comes from the timings, not the rate.

  2. Change who uses the system

    In measure.py, change the traffic share of customer_rank from 0.02 to 0.20 and the share of lookup from 0.40 to 0.22 so the shares still add up to 1. Predict the share of bill column for customer_rank before you run python measure.py. Will it go up or down, and by roughly how much? Then run it and compare. Put the shares back afterwards, or exercise 4 will not match the video.

    A hint

    The share of the bill is the share of traffic times the cost of one query, divided by the total.

  3. Time it once only

    In measure.py, in the main function, change rows = profile() to rows = profile(runs=1). Run python measure.py three times and write down the median column for customer_rank each time. Predict whether the three numbers will match exactly. Why does the video say timing only once is a mistake?

    A hint

    Compare your numbers with the first and median columns in the video's output. Timings move between runs, and the median ignores the odd slow run.

  4. See the gap shrink as traffic grows

    In cost.py, change the list of queries per day to 200,000, 500,000 and 1,000,000. Before running python cost.py, predict whether the machine busy column and the vs seconds only column will go up or down as traffic grows. Then run it and check. The video says the gap shrinks to a few times near a million, with the machine still mostly idle. Your busy and gap numbers will differ from the video's, because they come from your own timings.

    A hint

    Cost per query is the day's machine cost divided by the number of queries, so more queries share the same bill.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

The rate, the data and the traffic mix are made up. Timings come from one busy laptop with DuckDB on two threads and moved a lot between runs. Storage, network and people are not counted.

Up next in Data strategy

More in Data strategy

All Data strategy videos

Related videos