Complex Database Queries with PostgreSQL

Complex Database Queries with PostgreSQL

Dr.B.Hemalatha Deputy Chief System Manager

IIT Kharagpur-INDIA

PGConf India 2019, Bengaluru, 14 Feb 2019

1

SQL Queries

? Vital part of all applications ? Creating, retrieving and manipulating data in an

efficient way.--CRUD

Read Update

Create

Delete

SQL

PGConf India 2019, Bengaluru, 14 Feb 2019

2

SQL Queries--Broad Classification

Queries

Bound to Application

Adhoc Queries

Well Defined Tested for Performance

Repeated Execution

Always on Production Server

Loosely Bound

One Time , Specific Requirement Performance can be compromised

Mostly run on Replication Server

PGConf India 2019, Bengaluru, 14 Feb 2019

3

Adhoc Queries

? Adhoc queries are needed

? To cater to the Data Requirements for Analytics ? RTI requirements that are diverse and different ? Specific requirements from National and International bodies ? To test the output of a batch program

? More often the data requirement is in a particular format (pivot/un pivot)

? In most cases these need complex queries to generate the data

? Adhoc queries work on data that will not normally change in that time span. Not currently transactional.

? Adhoc queries are run on a Streaming Replication Server using

? pgAdmin Client ? Microsoft Excel through ODBC drivers ? Customized Application Interface

? Features available in PostGre 9.x and upwards make adhoc data retrieval easier

PGConf India 2019, Bengaluru, 14 Feb 2019

4

SQL Queries--Degree of Difficulty

Query

Complex

Simple

Perform more specific and flexible

searches of a database

Several Tables Complex Joins Sub queries

Queries are long

Needs Time to understand and debug the query

PGConf India 2019, Bengaluru, 14 Feb 2019

Straightforward Easy to understand One or two tables

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download