0

I am designing a database based off of the NBA stats website (http://stats.nba.com/player/#!/201939/?p=stephen-curry)

If you go to the link posted on the player's stat, you will notice that the player's stats are organized based on different factors (such as the season, month, location, home or away, etc), which you can set based on your filter setting.

The question is, how did the person who designed the site database allow for efficient querying of the each players stats. Each player will have a series of game logs, which will record their stats based on each quarter.

Did they use materialized view to calculate the monthly and seasonal stats for efficient querying?

I try to design the database below:

enter image description here

Improve this question
0

2 Answers 2

Reset to default
5

Unless you work on that database, that's not really something we can answer. However, we can speculate.

It could be a materialized view, but it could also be that a job runs on a schedule to calculate the stats. Maybe computed columns. Maybe just really fast hardware with efficient queries and the perfect indexes. So many possibilities.

My bet would be on the job though.

Improve this answer
3
  • Are jobs like scripts that run periodically? what advantage does it have over materialized views?
    – Tyler N
    Aug 9, 2016 at 20:43
  • Yes a job is a script that runs on a schedule. In Oracle, it's called a cron job. In MS SQL Server, we simply say job, Agent job or SQL Agent job. As for advantage over materialized view, it is only having to calculate the data when the script runs rather than for every single insert/update/delete to the tables involved in the materialized view.
    – Tara Kizer
    Aug 9, 2016 at 20:45
  • To add to my answer/comment, the stats would be in a table that gets updated by a job. So the web page would just query the table without having to do any calculations. The job would be periodically refresh/calculate the stats.
    – Tara Kizer
    Aug 9, 2016 at 20:55
0

I love opportunities where I can fit in an indexed view. I love to see the performance of an specific query to go sky-high.

However, before I put one in place these days I consider at least the following factors:

1- can't I improve the query code

2 - how many indexes are there in each of the tables involved

3 - how often insert, deletes and updates happen on each of the tables involved in the indexed view

4- what indexes are already there in each of the tables involved, can't they be modified so that they cater for the needs of this particular query??

5 - If the current indexes do not cater and can't be modified, would not new appropriate index(es) help the query in question

6 - after trying to modify the query, and running it a few times why not run a performance tuning advisor and see what suggestions it comes out with? you might be surprised the gains you might get by adding some multi-column statistics to your database. Use with care and have a test system for this.

7 - are you monitoring the number of deadlocks, disk reads, disk writes, which objects are causing most locking and blocking, and for how long?

8 - are you monitoring WAIT STATS?

9 - from previous experience, calculated columns and indexed views together can slow down writes, and increase the number of deadlocks to an unacceptable level

10 - basically indexed views improve reading but must be done in a way that do not hurt the writing too much.

the more you know your database and how it is used, the best decision you can make. also each of your objects in your database, the more you know how they are used, the best use you can make of filegroups, disks, indexed views, and now even new features like in memory objects.

In this situation I will probably have a parallel process to keep up the stats on separate tables, either a job(s) or triggers (after update, insert, delete) so that the data does not need too much calculation when I needed to be shown.

calculated fields might be an alternative as well.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.