SQL Server Query Optimization Statistics

What are Query Optimization Statistics?

The term statistics refers specifically to distribution statistics. Distribution statistics describe index key values, specifically:

• Selectivity – Refers to how many rows are identified by a key value. A unique index has high selectivity. A key with many duplicates has low selectivity. 
• Distribution – Used when estimating how efficient an index will be in retrieving data associated with a key value or range. 

Example query

The query optimization statistics are simply a form of dynamic metadata that contain statistical information about the distribution of values in one or more columns of a table or indexed view.

Full article - Understanding SQL Server Query Optimization Statistics

Impala: Open Source Real-Time SQL Queries on Hadoop

Cloudera Impala is an open source system which provides real-time SQL querying functionality on top of Hadoop.

Impala High-level Architectural View

Cloudera, which created Impala, said they had been technically inspired by Google's Dremel paper, which made them think that it would be possible to perform real-time, ad-hoc queries in Apache Hadoop.

In October 2012, when announcing Impala, Cloudera introduced it as follows:

“Real-Time Queries in Apache Hadoop, For Real”

Impala adopted Hive-SQL as an interface. As mentioned above, Hive-SQL is similar in terms of syntax to SQL, a popularly used query language. For this reason, users can access data stored in HDFS through a very familiar method.

As Hive-SQL uses Hive, you can access the same data through the same method. However, not all Hive-SQLs are supported by Impala. For this reason, you had better understand that Hive-SQLs that are used in Impala can also be used in Hive.

Continue to Meet Impala: Open Source Real-Time SQL Queries on Hadoop

Finding the most recent purchase by each customer with SQL

If you have transaction history such as purchases in a database (or really any list of items with timestamps), you frequently want to pick out recent items. SQL makes it easy to find the most recent items in the table.

The most recent items in the table

Finding the most recent purchase by each customer with SQL | Metrica

Groupdate.sql - Better SQL group by date

Groupdate.sql provides the simplest way to group by:

• day
• week
• month
• day of the week
• hour of the day
• and more

Timezone support is included and you can use Groupdate functions with any programming language. Supported SQL DBMS: MySQL and PostgreSQL.

Groupdate.sql creates SQL functions, which are stored by the database. You can use these functions with any ORM (or without one).

Example query with Groupdate functions

Groupdate.sql project page

Large Scale Data Analytics with XtremeData Parallel SQL Database Engine

XtremeData dbX is a full SQL and ACID compliant database engine based on shared-nothing, massive parallel query execution. The underlying technology relies on PostgreSQL (similarly to AWS Redshift). In essence, the key architecture components are the head node and multiple data nodes – this is a fairly common patterns in massive parallel execution scenarios.

The head node manages the client connections, parses and plans the queries and sends the result back to the clients. The data nodes manage data storage and execute queries.

Continue to the article

Sparse matrix multiplication using SQL

a and b are two sparse matrices. We want to perform a * b as shown below:

Sparse matrix multiplication using SQL

We can represent a sparse matrix in a relational database as a table matrix_name(row_num, col_num, value). Each non-zero cell in the matrix is represnted as a record (i, j, value) in the table.

Sparse matrix multiplication using SQL | Rohit Agarwal`s Notes

Fluent SQL - a minimalistic SQL builder for Java

Github user ivanceras has published a new SQL API library for Java called Fluent SQL. A minimalistic Fluent SQL API for Java built with one compilation unit aimed to resemble the code to your original SQL code.

Fluent SQL

Fluent SQL | GitHub.com