Register on the forum now to remove ALL ads + popups + get access to tons of hidden content for members only!

Go Back   Site Owners Forums - Webmaster Forums > Web Programming > Programming General > PHP / mySQL

Notices


Reply
 
Thread Tools Rate Thread Display Modes
Old 09-16-2018, 09:21 AM   #1
ajaxdns
Registered User
 
Join Date: Apr 2018
Posts: 1,360
What are the advantages of stored procedures, triggers, indexes?

A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don’t need to keep re-issuing the entire query but can refer to the stored procedure.

This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side. Triggers will also be implemented. A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs.

For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted. Indexes are used to find rows with specific column values quickly.

Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question,

MySQL 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 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ajaxdns is offline   Reply With Quote

Old 10-01-2018, 06:17 AM   #2
Daisyallie
Registered User
 
Join Date: Nov 2017
Posts: 453
Stored Procedure: A stored procedure is a set of Sql commands that can be compiled and stored in the server.

Once this has been done, client don’t need to keep re-issuing the entire query but can refer to the stored procedures.

This provide better overall performance because the query has to be passed only once and less information need to be sent between the server and client.

Stored procedures of course do increase the load on the database server system, as more of the work is done on server side and less on client (application) side.

Trigger: A trigger is effectively a type of stored procedure, and that is invoke when an particular event occours.

For example: We can install a stored procedure that is triggered each time a record is deleted from a transaction table and that invoke stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

Indexes: Indexes are used to find rows with specific column values quickly.

Without an index, mysql must begin with the first row and then read through the entire table to find the relavant rows.

The larger the table, the more the cost. If table has an index for the column in question, mysql 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 1000 rows, this is the least 100 times faster than reading sequentially.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Daisyallie is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What is the difference between execution of triggers and stored procedures? ajaxdns PHP / mySQL 0 08-17-2018 08:35 AM
What are the advantages of stored procedures, triggers, indexes? ajaxdns PHP / mySQL 0 08-11-2018 08:44 AM
What are the advantages of stored procedures, triggers, indexes? ajaxdns PHP / mySQL 0 05-17-2018 02:40 AM
Differences among batches, stored procedures & triggers sunled PHP / mySQL 0 04-27-2018 09:52 PM
What is the difference between execution of triggers and stored procedures? sunled PHP / mySQL 0 04-18-2018 04:41 AM


All times are GMT -7. The time now is 09:40 PM.


Powered by vBulletin Copyright © 2013 vBulletin Solutions, Inc.