Here at Windward Studios, we’ve seen numerous examples of how structuring customer data first leads to huge time savings in report design later. Along the way, we’ve also seen quite a few common errors.
We’re here to help you avoid these mistakes. We’ve put together a white paper that features eleven useful tips to help you organize your data in a way that will save you time in the long run. Here’s a sneak preview.
Tip #1: Always index your columns.
Indexing is a delicate balance of doing just enough without overdoing it.
Indexing improves the query response time of a select by creating a system-managed table that allows the data to be directly referenced instead of searching for it.
But since each modification of data in a user table potentially involves updating the indexes, adding or removing data rapidly can noticeably slow down performance. In addition, not enough indexing will also decrease the performance of SQL selects when querying data.
To achieve this delicate balance, we recommend you:
- Build your queries with an index order. If the data being returned is usually ordered by a certain column (e.g., dates) every time, then it makes sense to index the order of that column.
- Make use of covering indexes. A covering index consists of all the columns a query needs. This optimizes your query for only the columns contained within it.
- Rebuild fragmented indexes regularly. Indexes become fragmented through the modification of table information activity splitting the physical and logical locations, thus creating mismatches.
Tip #2: Separate data into logical pieces and types.
Applications that collect data frequently do not store that data in a logical manner. This most often occurs with text fields. Prime examples are names, addresses, dates and numerical values.
If you store a name as a single string, e.g., “First Middle Last,” you will encounter problems later when you need to sort by first name only, last name only or a mixture of any of the three. Placing this data into separate columns ensures that you can sort and access your data in an optimized manner.
A trick that DBAs use is to create a query that will assemble and return the full name based on these individual parts using the COALESCE function. This prevents the need for duplicate data by creating an additional “full name” column.
Get All 11 Tips
To read the full white paper, head to Your Data: Friend or Foe? Eleven Tips for Creating a Reporting-Friendly Database.
Author: Ryan Fligg
Ryan, Windward's Sales Engineer, has been with Windward since 2006 in many roles as a sales engineer, IT specialist and account executive. Ryan's background fuels his desire to guide Windward's product development. He now works on the future vision of Windward offerings through creating the product roadmap, responding to customer requests, and communicating what Windward is doing and where it’s headed.
Other posts by Ryan Fligg