Indexes
Introduction:
An index is a data structure that improves the speed of operations in a table.
We can create an index using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
Without an index, SQL must begin with the first row and then read the entire table to find the relevant rows.
The larger the table, the more the cost. If a table has an index for the column in question, SQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data.
If a table has 10000 rows, this is the least 1000 times faster than reading sequentially.
Usage of Indexing:
Let us understand this with an example:
If you had a book on databases and indexed the word “database”, you would see that it is mentioned on pages 1-70, 70-190, and 191 to 300. In such a case, the index is not much help and it might be faster to go through the pages one by one (in a database, this is “poor selectivity”).
For a 10-page book, it makes no sense to make an index, as you may end up with a 10-page book prefixed by a 5-page index.
The index also needs to be useful – there is generally no point to index e.g. the frequency of the letter “L” per page.
Advantages:
Use of Indexing usually results in much better performance.
They make it possible to quickly fetch data.
They can be used for sorting. A post-fetch-sort operation can be eliminated.
Unique indexes guarantee uniquely identifiable records in the database.
Disadvantages:
They decrease performance on inserts, updates, and deletes.
They take up space (these increase with the number of fields used and the length of the fields).
You should only create indexes when they are actually needed.
Take care not to add an index on something that has already been indexed. If you need a more detailed index, you can add fields to an existing index as long as it is not a unique index.
Stored Procedure Introduction:
A stored procedure is a bunch of SQL commands that can compile and stored in the server.
It includes all the statements of SQL, but we can also use if, then, else blocks and while loops.
SQL Server, My SQL etc. have many built-in stored procedures, called System Stored Procedures, which are, used for maintenance and management activities, they start with ps_suffix.
They can accept input parameters.
They can return various output types.
Syntax:
CREATE [OR REPLACE] PROCEDURE <ProcedureName> (<ParamList>)
<localDeclarations>
Begin
<procedureBody>;
End;
/
ParamList: A parameter in the paramList is specified below
<name> <mode> <type>
Mode:
IN => Input Parameter (Default)
OUT => Output Parameter
INOUT => Input and Output Parameter
Stored Procedure Structure in MYSQL:
DELIMITER // ##declare the character end the procedure CREATE PROCEDURE mytestprocedure () BEGIN SELECT * FROM actor; END // CALL mytestprocedure (); DROP PROCEDURE mytestprocedure; Stored Procedure Example in MYSQL:
Write a stored procedure that receives as input the id of an employee and displays his name and his salary. Then call this procedure for the employee id=2 Employee (id, FNAME, LNAME, SALARY) CREATE PROCEDURE find_emp_salary @ide int AS BEGIN SELECT FNAME , LNAME , SALARY FROM EMPLOYEE WHERE ID = @ide END CALL find_emp_salary (2); Value Assignments
We can set a value into a variable
Its value last for a specific session
The name should start with a “@”
The value assignment is done using the SET command
SET @x=4;
SET @y=7;
SET @z=@x-@y;
We can print the value of a variable using the select command, e.g:
Select @x;
Declarations in a SP
We can create a variable in a procedure.
Each variable lasts for the specific session
We can use “DECLARE” command to declare a variable
We have to declare its data type e.g:
DECLARE id INT;
DECLARE name VARCHAR(20);
DECLARE birthday DATETIME;
The declarations should be made at the beginning of the stored procedure
Advantages
Code Reusability
SQL Server compiles and stores these in memory for future use
Enhanced Security
Requires valid permissions to execute
Users can have permission to execute a stored procedure, without having permission to read from the underlying tables or views
You can specify the security context
Owner, Caller or User
Speed / Optimization
The stored procedures are cached on the server
Disadvantages
Limited Coding Functionality
The stored procedure code is not as robust as app code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)
Testing
Any data errors in handling Stored Procedures are not generated until runtime
Portability
Complex Stored Procedures that utilize complex, the core functionality of the RDBMS used for their creation will not always port to upgraded versions of the same database. This is especially true if moving from one database type (Oracle) to another (MS SQL Server).
Original Source: Advanced SQL: Indexing, Stored Procedures and Triggers