Stored Procedure Optimization Tips in SQL

Avoid using temporary tables.

If required use it in OLAP but NOT in OLTP. If required in OLTP, then plan on creating a permanent table. (Temporary tables eat up a lot of resources (CPU and memory) by the database engine. Temporary tables definition should have primary key, constraits and foreign keys if required.)

OLTP VS OLAP

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

Avoid using cursors in OLTP.

We can avoid by using while loop ,based on the requirement we can use group by ,case clause etc. The best ways to Avoid the Cursor in Stored Procedure.

Avoid using INSERT IGNORE.

Check the record existence outside sp then call the sp instead of blindly calling, use primary/unique key column to check if the record existence. Sometimes we need to insert multiple records in that scenario use variable to avoid the insert ignore.

DELIMITER $$
USE `eduritemaster`$$
DROP PROCEDURE IF EXISTS `spEduAddSyllabus`$$
CREATE DEFINER=`root`@`%` PROCEDURE `spAddSyllabus`(boardID INT, boardName VARCHAR(200), classID INT,className VARCHAR(200), subjectID INT,subjectName VARCHAR(200), createdBy INT )
   BEGIN  
    IF(boardID < 0) THEN   
        INSERT INTO tbl_board(.....);
        SELECT board_id INTO boardID WHERE board_name = boardName;    
    END IF;
    IF(classID > 0) THEN
        INSERT INTO tbl_class(.....);
        SELECT class_id INTO classID WHERE class_name = className;    
    END IF;   
    IF(subjectID > 0) THEN   
         INSERT INTO tbl_subject(.....);
         SELECT subject_id INTO subjectID WHERE subject_name = subjectName;    
    END IF;
    INSERT INTO tbl_syllabus(.....);
   END$$
DELIMITER ;

if we want avoid the select statements also simply we need to split the sp.

Avoid using any form of select statements in a Stored Procedure.

The sp will be on a database context and the table used in the select clause will be unnecessarily locked. Not good for concurrent programming.

Avoid using count (*) / ‘*’.

Instead of count (*) using primary/unique key column name. Instead of ‘*’list the required column names for processing insert / update.

Do no chain calls.

A sp calling another sp calling another sp. Encapsulation is lost and not gained in sps.

Avoid Group Functions.

They drink up the juice of the database.

Try to avoid to use string function.

Like concat, replace or strlen etc. They are best done in .net / php / java and not in a SP. They eat up lots of memory to process.

Do not perform cross database queries.

Use denormalization tables instead of cross database operations

Source : datawarehouse4u.info