Here are some general methods to improve performance.
Are you using stored procedures or direct queries ? You can use stored procedures to improve the speed very much.
You have to define appropriate indexes for tables. An index defines the order in which records are stored (either physically or logically). How to determine which column to index ? This will depend on the query you are using to fetch (especially the order by fields) and the way you want to show data.
Let a table contain very large number of records. But 90% of the time, users will be using only 10% of these records. Then it is better to split the table into two (one live table and one archive table). You can retain the 10% frequently accessed records in the live table and the 90% infrequently accessed records in an archive table. However, this will involve significant amount of script changes.
If the table is having large number of fields (more than 20), it is better to split the table into two or more. The most frequently used fields may be kept on one table and less frequently used fields can be pushed to other tables. For e.g. It may be collecting and storing about 50 details of an employee. But only 10 of them may be used frequently. The less frequently used fields (such as housename, home postal address etc. etc) can be put into another table and then relate these two tables.
Better query formation
You can use efficient queries. For e.g.
Select <field list> from <table clause> Where <where clause>
is more efficient than
select * from <table clause> Where <where clause>
You can use join queries instead of sub queries to fetch field names corresponding to foreign keys. For e.g. consider the query.
Select employees.employee_name, (select countries.country_name from countries where country_id=employees.country_id) as employee_country from employees
The following query is much more efficient than this query to fetch a list of employees and their countries.
select employees.employee_name, countries.country_name as employee_country FROM (employees left join countries on employees.country_id=countries.country_id)
Use of triggers
You can use triggers to do many business rules. For e.g. if you want to delete records from many tables in a cascaded manner, this can be very well done with the help of triggers. The normal method of first deleting records from the bottom-most table in the hierarchy and then from the table just above and finally from the top-most table is not very efficient.
Whenever you are in need to display hierarchical records, it is better to use data shape object. You can search and findout what it is.