Archive for the ‘SQL’ Category

If you want to improve your Web performance in your project, then you need to think/Answer on below points:
● How many database connections do I have in my code?
● How many request are going in my application?
● How much time does every select (db) statement spend?
● How many select statements do you have in your code?
● Are they inside loops?
● Do I really need them? Can I cache them?
● How many functions are there in my application? Can we replace with inbuilt functions?
(more…)

Advertisements

Tips for SQL Query Optimization

Posted: October 24, 2012 in General, SQL, Technology

1.    Use UNION ALL statement instead of UNION, wherever possible.

UNION ALL statement is quite faster than UNION, because UNION ALL statement does not check duplicate rows, while the UNION statement always check for duplicate rows, whether duplicate record is exists or not.

2.     Include SET NOCOUNT ON statement into your stored procedures

Usually T-SQL will show message regarding affected row but with the help of SET NOCOUNT ON statement we can stop these message. This can reduce network traffic, as your client will not receive the message indicating the number of rows affected by a T-SQL statement. (more…)

Why we use stored procedures in application

Applications that use stored procedures have the following advantages:

1.       Procedural Language Capability: – 

PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops). Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

2.        Stored Procedures are pre-compiled:-

Once created, that can be used again and again without doing compilation. That will save compilation time and get result in less time.

(more…)

MySQL – Speed of UPDATE Queries ?
Update queries are optimized as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated.

Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table. (more…)

Some MySQL Management Tools

Posted: February 4, 2010 in SQL

A large percentage of small to medium sized websites depend on Mysql server to support their db infrastructure. Working with it is as easy is saying it and for some reason there are numerous web and non-web administration software written specifically to manage a Mysql server and sites running on it. This article lists quite a few of them which you may find useful. (more…)

Database Normalization Rules

Posted: November 17, 2009 in SQL

SQL-based English Query applications work best with normalized databases. In general, it is easiest to create English Query applications against normalized SQL databases. In addition, the resulting applications are more flexible and powerful than those developed against databases that are not normalized.

This topic describes normalization rules as they pertain to English Query SQL applications. It describes problematic database structures that break these rules and how to solve these problems by creating views in Microsoft® SQL Server™, which can be used in English Query just like any other table.

Rule 1: There should be a one-to-one relationship between the instances of an entity and the rows of the table.

For every table that represents an entity, each and every row in that table should represent one and only one instance of that entity. Conversely, each and every instance of that entity should be represented by one and only one row in the table.

In this situation, this rule is not met:

Table:    Employees
Fields:    Emp_id, Emp_name, Status, Position, Salary
Keys:    Emp_id, Status

This table stores information about employees. It contains their names, positions, and salaries. But sometimes employees move around from position to position, and when they do, their salaries change. So for some employees, this table also stores information about their projected position and salary. If the value of the Status field is C, the row contains the current information for the employee. If the value is P, it contains the projected information. Thus, an individual employee may appear twice in this table.

Because an employee can appear twice, you cannot use this table to represent the employees entity. If you were to associate the employees entity with this table, even simple requests, such as “Count the employees”, would give the wrong answer. The solution to this problem is to create a view in the database that contains a single row for each employee and to tell English Query about this view.

Here is what the view would look like:

CREATE VIEW Emps AS
SELECT Emp_id, Emp_name, Position, Salary
FROM employees
WHERE status = 'C'

You now have a view that contains exactly one row per employee. The employees entity can now be represented by this view.  (more…)

SQL Performance Tips

Posted: November 17, 2009 in SQL

SQL Performance Tips :-

  1. Use EXPLAIN to profile the query execution plan
  2. Use Slow Query Log (always have it on!)
  3. Don’t use DISTINCT when you have or could use GROUP BY
  4. Insert performance
    1. Batch INSERT and REPLACE
    2. Use LOAD DATA instead of INSERT
  5. LIMIT m,n may not be as fast as it sounds
  6. Don’t use ORDER BY RAND() if you have > ~2K records
  7. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
  8. Avoid wildcards at the start of LIKE queries
  9. Avoid correlated subqueries and in select and where clause (try to avoid in)
  10. No calculated comparisons — isolate indexed columns (more…)