Query Fundamentals
Query fundamentals
What makes a good query?
It has a specific purpose
A good query has a specific purpose. It answers a particular question.
Examples:
- What were the top 5 products sold last month?
- Which developer made the most commits today?
- What items need to be ordered?
Specific queries can often be better optimized.
It is perceived as being fast
Users expect applications to appear to work as fast as possible.
No one likes to wait for applications to load information.
In the past we used techniques like animated spinner gifs to communicate to user’s that
the application is busy doing something.
The best case scenario is that applications return some amount of data
within a few hundered milliseconds so that that it appears to be fast.
It returns only the data fields that are requested.
In some cases returning more data than needed can increase the response size
and in turn reduce the response time of the query.
In such a case that filtering specific data elements in a resopnse
reduces the speed at which the result would be returned favor speed instead.
It returns a useful amount of data.
If the execution of a query is expected to return multiple results how many results
are likely to be enough to be useful. There is a constant need to balance speed,
the network IO, and the amount of data being returned.
If the results of the query are being displayed to a user they probably
don’t need to view more than 10-100 records at a time.
If there caller of the query is a system it may prefer to gather a larger number of records at a time.
Given the scenarios that you are aware consider what restrictions you may want
to apply to make the amount of data returned useful.
The results are fresh enough to be useful
Like many food products, query results often have an expiration date,
or a point in time in which they are no longer valuable.
If you are shopping online and the website displays last weeks sales prices,
you would probably find that information useless. That data has expired like week old donuts.
There isn’t a specific timeframe in which all data is percieved to be expired,
it depends on the context in which its being used to make a decision.
Manager’s trying to determine this weeks sale prices may be interested in the sale prices from the previous week,
but customers probably don’t care.
Query Styles
Fetch
Fetching means to look up data by its identifier.
Assuming a SQL database the query would be similar to
SELECT * FROM SomeTable WHERE ID = @ID
A lot of queries in systems that we use every day are simply fetches.
When you are looking at a specific piece of data, like a YouTube video, a facebook post,
or a product that you are considering purchasing,
one or more fetch style queries are executed to retreive the information used to be displayed on the page.
Predefined
As mentioned previously good queries answer specific questions.
This information often feeds into preestablished decision making processes or workflows.
As someone who keeps a monthly budget I frequently want to know how much money have I spent in a certain category,
and how much is remaining, so that I can adjust my plans accordingly.
The more questions that you can anticipate will be asked in your system
the better that you can optimize your system to answer them.
In some cases these queries can even be turned into fetch queries with some adjustments to your system design.
Exploratory / Dynamic
Exploratory queries answer previously unanticipated questions, or multi-dimensional, multi-facted queries.
These types of queries are often used when searching for something. You may not know exactly what you are looking for but you are trying search around and narrow down your options.
You might be searching for a new pair of shoes, and then discover there are men’s, women’s, kids,
and with in those there are various categories like dress, athletic, dance, house,
and various facets with in those like color, material, and more specific purposes.
You are essentially exploring the data to see what options that you have
to eventually narrow it down to a few selections that you have interest in.
These queries are also thought of as dynamic becuase they compostion of the query changes as you and and remove search parameters.
In some contexts and systems these types of queries can “graduate” to Predefined queries.
This might happen if user does some data exploration and determines this query is something that
they’d like to execute on a recurring basis.
Query Aspects
Limit or Size
The maximum number of results returned from a query. Sometimes clients
need to be able to specify how many results they think will be useful at a time
this is typically combined with paging.
Paging
If the query results have a limit and the number of possible results exceeds that
limit that paging is used to allow the caller to request the next set of data.
Sorting
Query results are can sometimes be more useful when they are sorted on a particular
data field. Sometimes this is a time aspect or a string field, like a name.
Filtering
In dynamic queries filtering is often used to narrow a large result set down to a more
useful size. This can also be used for fetching a specific record, but this can be less performant use of filtering.
Aggregates
Aggrgates are things like Count, Sum, and Average this is often used in reporting scearnios where rollups of information are most useful.
These can be baked into predefined queries or used in Dynamic queries.
Various pieces of meta data may be added to a query result like the time in which the result set was captured,
what page of results the data represents, how to get the next page, how many items there are in total in response to the query,
and any other piece of data that might be useful for the caller.