Category: Database
Improve SQL query performance
Tips to improve SQL query performance 1. Avoid usage of multiple joins in a single query. Unnecessary joins degrade your query performance. 2. Creation of indexes on the table. Indexes are used for improving your queries. We need to create
DAO with JPA (and service layer/EJB3-Session Beans) or not
There is a lot of discussion on internet about whether to have DAOs or not with JPA and service layer or with session beans (which is the service layer). My personal opinion is more favored towards DAO layer and some
MySQL | Communications link failure
MySql Communication link failure while connecting MySql server remotely I’m getting the following error when I tried to connect MySql server remotely: ERROR: —————————————— Cannot connect to database server Communications link failure The last packet sent successfully to the server
MySql | Show only columns as rows which have specific value
Sometimes, we want to show only those columns which have specific value. Let’s assume we have following table : In the above table, value of 1 in a column represents that corresponding service is provided by organization and 0 represents
SQL Transaction in Cakephp 3
SQL transaction in Cakephp 3 is a series of statements working in a logical order to handle database errors and ensure data integrity. There are four main methods being used in Sql transaction in Cakephp 3. 1. Initialize the statement
Azure | Consumer Web application to crerate copy of SQL Database
Azure provides various rest apis to perform different actions and operations that are possible in the azure server like to create web applications, databases and many more. User can design their own simplified UI to perform actions, instead of logging
MySql | Display Latest entries of two columns of two rows into single row
Suppose we have two tables : articles and article_comments CREATE TABLE articles( `id` INT( 11 ) NOT NULL AUTO_INCREMENT , `title` VARCHAR( 200 ) NOT NULL , `content` VARCHAR( 500 ) NOT NULL , `author_name` VARCHAR( 20 ) DEFAULT 1
MySQL | How to convert row values into column names
Sometimes requirement arises to display a field value as column name in output and adjacent values in the same row below it as its values. Suppose I have a table “students” with marks of each student in different subjects. Each
Download CSV of table Records using OUTFILE sql command and PHP
Create and Download CSV of table Records using OUTFILE Sql command and PHP 1. Create temporary CSV file using OUTFILE SQL command: OUTFILE command is used to write your sql command output to the specified location. You can write the output