Optimising SQL Data Access Through Appropriate Query Input

Servers

If you have ever coded a website which interacts with the database and is used to fetch data from the database, it’s not only how intelligently the site is designed and loaded with features, it is also important for the app to be called successful that the database also provide instant access to the data.  The real test of a site and its ability to interact with a database seamlessly is done as the number of users increases on the site.  Some of the sites which performs exceptionally well with a small number of users; surprisingly fail to perform as the number of users increases.  The best practice is to optimize the data access routines beforehand. Following is an example with various techniques to optimize data access.

For example there is a Customer_details table with following contents

CREATE TABLE Customer_details
(
Customerid int NOT NULL,
Productid varchar(255) NOT NULL,

Customer_name varchar(255) NOT NULL,

Product_name varchar(255) NOT NULL,
DOP DATE,
Amount int NOT NULL,
CONSTRAINT Cust_CustomerId PRIMARY KEY (Customerid));

Customerid Productid Customer_name  Product_name DOP  Amount
1209 A348 Smith Wifi router 12/10/15 $40
1210 B867 Jacob AC 12/10/15 $200
1211 C569 Tim Fridge 12/10/15 $300

 

Another table with the name of Product_details is below

CREATE TABLE Product_details
(
Productid varchar(255) NOT NULL,
Product_name varchar(255) NOT NULL,
Location varchar(255) NOT NULL,

DOD DATE,
Number int NOT NULL,
CONSTRAINT Prod_productid PRIMARY KEY (Productid)

FOREIGN KEY (Productid) REFERENCES Customer_details(Customerid));

 

Prouctid Product_name  location DOD  Number
A348 Wif Router Chicago 10/10/15 10
B867 AC San Jose 10/10/15 18
C569 Fridge Los Angelas 10/10/15 12

1 Indexing

Indexing helps fast retrieval of data as the indexes help in locating a specific data in a database easily and rapidly. Indexing coupled with creating a primary key can reduce data access time considerably. Whenever you create a primary key a clustered index is created automatically.  The data retrieval operation using primary key is very fast.

Create Index Indx1 on Customer_details(Customer_name);

In the above example an index is created with name Indx1 on the table customer_details on the coloumn Customer_name.

 

2 Function based Indexing

In function based indexing we can create an index based on a condition which will retrieve the lesser number of rows than a normal index thereby saving time. In the same table as above, we create a function based index as below -

Create Index Indx2  on Customer_details(Customer_name);

SELECT * FROM Customer_details WHERE AMOUNT > 200;

3 ORDER BY clause

ORDER BY clause helps in keeping the database sorted based on a column which will ensure faster retrieval of data. Retrieving data from a sorted database will yield results faster than fetching data from unsorted database.

SELECT * FROM Customerid

ORDER BY Amount DESC;

4 JOIN

An SQL JOIN clause is used to combiner from two or more tables, based on a common field between them. This is a good stratergy to retrieve data from two or more tables based on a common column as it saves a lot of time in traversing these tables individually.

SELECT Customer_details.Customerid, Products_details.Productid,
FROM Customer_details
INNER JOIN Product_details
ON Customer_details.Productid=Products_details.ProductId;

These are some basic stratergies which work well to optimize data access to ensure that data is kept sorted and can be reterived at a faster pace.

AUTHOR:

Vaishnavi Agrawal loves pursuing excellence through writing and have a passion for technology. She has successfully managed and run personal technology magazines and websites. She currently writes for Intellipaat, a global training company that provides e-learning and professional certification training.

The courses offered by Intellipaat address the unique needs of working professionals. She is based out of Bangalore and has an experience of 5 years in the field of content writing and blogging. Her work has been published on various sites related to Hadoop Training, Big Data, Business Intelligence, Cloud Computing, IT, SAP, Project Management and more.

IMAGE CREDITS:     http://www.datarecoveryguide.net/ 

Your Turn To Talk

Your email address will not be published.