The Pitfalls of Data Queries — and How Qlik Can Help

Secondly, using asterisks degrades performance — the fewer the number of columns that need to be returned, the faster the query is processed.

A way to deal with this is to specify the desired column(s) to be returned:Asking For Data Without BoundsDatabases can hold huge amounts of data — thousands, if not millions of bytes in some cases.

Querying such a database will yield an equally large amount of data, leading to slow response.

Clearly, it’s inefficient.

And of course, no one enjoys waiting, even for a couple of seconds, for a query to return results.

Whenever possible, the returned data should be limited.

The LIMIT command comes in handy: It restricts the number of results from a query.

In this example, the result will be arranged according to the top 100 scores.

You can already see the benefits of limiting the returned result if the database contains a huge amount of data.

Spaghetti QueryThere’s no one way of asking a particular question.

In the same vein, a data query can be written in several ways, although the concise version is better in almost any case.

This can lead to some problematic queries that are very hard to debug.

Let’s take this example by Bill Karwin (good luck trying to understand):NULL TrapNULL is one major source of confusion in SQL.

NULL signifies an absence of value.

This is because NULL in SQL is not the same as zero, an empty string, or false, like in some programming languages.

Note that there are some exceptions to this distinction.

It is important to know how NULL is interpreted as some queries may yield unexpected results.

Careful with SubqueriesA subquery (nested or inner query) is a query inside of another query.

Sometimes you may need the result of a query as input for another query.

You write a subquery to deal with that.

However, when a subquery returns the wrong number of rows, you run into problems.

Think about what happens if the subquery in the following example yields more than one record.

The query fails!There are several other challenges that come with writing data queries.

Nonetheless, data queries are sometimes necessary.

Following the KISS principle whenever possible helps you eliminate some of the challenges of data queries.

So instead of writing long and sophisticated queries, you should write concise queries that perform a specific function efficiently.

The Qlik Approach to Avoid Writing Data QueriesThe most experienced developers sometimes run into issues with data queries.

This is where Qlik comes in handy.

QlikView is a powerful business discovery platform that makes it easy to work with data (analysis, visualization, etc.

) The fun part is that QlikView helps developers avoid writing data queries, and as a result, avoid the pitfalls associated with writing data queries.

Earlier, we looked at some pitfalls of data queries.

Now, we’ll see how to use QlikView to prevent these and other data query pitfalls.

Query Without Writing a QueryQlikView provides a script editor that makes it possible to query data without writing the queries yourself.

You connect to your data source, select the fields in the data you want to work on, and the script editor then generates the data query automatically for you.

The great news is that you get to preview the data and you’ll have a basic idea about the kind of data each column contains.

This way, you avoid exposing sensitive information in a database — the subject of our first data query pitfall.

In QlikView, click on File > Edit Script > Select Connect At the Bottom Pane > Choose Data Source.

Right away, the data load command is written for you.

The Select button on the bottom pane allows you to create SELECT statements by choosing the data fields to work with.

Sample SELECT Query generated by script editorLimit Your ResultSometimes, you may want to limit the number of records you get from a query.

This involves preceding the LOAD statement directly with FIRST, followed by the limit of the result.

If you’re interested in the first 10 items, you write FIRST 10 before LOAD.

Reloading the query only loads the first 10 records.

Limiting the number of returned records can also be done in debug mode Debug.

Break Complex Queries into Smaller QueriesBreaking long, complex queries into concise ones makes it easy to spot bugs.

In the script editor, you can include several query statements.

When you reload the editor, you can select the fields you’re interested in to appear in the sheet.

From the sheet, you are able to delve deeper into each field.

For instance, you may decide to view the table structure of the document.

Although Qlik eliminates the need for developers to write data queries to some extent, you cannot completely do away with data queries in some situations.

Qlik script statements are quite easy to pick up.

Check out the Help Section for details on all available statements and their usage.

Closing ThoughtsIn this post, we’ve seen some of the dangers associated with writing data queries.

Some pitfalls are easier to avoid, while others require proper planning.

Yet we cannot totally do away with data queries, as they are essential in some use cases.

But when possible, data queries should be kept simple.

QlikView helps developers eliminate the need to write data queries and focus on productivity.

Qlik also provides several other tools that help developers work with data effectively — from data visualization to analytics.

*****About the AuthorBruno Edoh is an external paid contributor via Fixate.

io and a junior at Ashesi University College studying Computer Science.

He is interested in leveraging the power of technology to increase productivity.

As a big fan of open source technology, he is currently exploring the possibility of using Bitcoin Blockchain to fight corruption in government.

.

. More details

Leave a Reply