How to check your Query performance?

Once you have written a query, you will need to know if its the most optimised query or not. One place you would want to check is the Query plan. Now what is this query plan?
Its the steps the ASE follows to retrive data from the tables.

What does it give you?
1. In case of a query involving multiple tables, it gives you the order in which the
tables are taken up for data retrival.
2. It will give you information if the ASE is using the indexes or not.
3. It will tell you if any temporary tables are created while executing the query.
4. It will also tell you how the aggregate cluses are being processed.

How do you see the query plane in Sybase?Use the following:

1> set showplan on
2> go
1> set noexec on
2> go

'showplan' puts the query plan on and 'noexec' ensures that the query doesn't get executed if you run it. When you run the query, you should see something like:

1> select title from titles where title > "Cooking"
2> go

STEP 1
The type of query is SELECT
FROM TABLE
titles
Nested iteration
Index: titlein

Things to look out for:
1. If there is a something like "Tablescan", it means the ASE is scanning the entire
contents fo the table to retrieve results. Its dangerous if you have huge table. If its
inevitable make sure ASE does it onlyfor samll tables.
2. Temporary tables: If you see ASE is creating too many temporary tables, its bound to
take a lot of time and reflets bad querying technique. Temporary table are inevitable
for ordery by/ aggregate clauses but proper querying might mitigate them if not avoid
them alltogether.

Differentiating between 'Joins' and Where clauses in SQL queries

Simply speaking, a select statement is used to select data from tables depending on a particular criteria. Simple. Isn't it? Now how best to do it? I follow the following simple steps:

1. Determine what fields you want and from which table each of these fields are coming from.
2. Determine the join criteria with which you would join these tables to get the common data.
3. Detemine whether you need to do an inner join or an outer join.
4. Determine what goes into the "where" clause to purify the retrieved rows.
5. If there are more than 4 tables to be joined, Sybase is usually clueless about the order of joins.

So determine the order of joins.

Most of the developers have a habit of mixing up the where clause with the join. This makes a query complex and hard to debug.When you differentiate, you specify
1. What goes into a join, .i.e the criteria of commonility between two tables based on which the
combined rows are retrieved &
2. What goes into a 'where' clause .i.e Criteria on which the retrieved rows are filtered.

This make the query clean, more readable and inturn faster to debug. Let me eloborate further. When there is a join in a query, the ASE gets ready to retrieve the cartesian product of the tables. For example, if there are two tables EmpAddress(5 Cols, 10 Rows) & EmpName(4 Cols 8Rows). When you say join, the ASE tries to combine each of the 50 elements in the table EmpAddress with each of the 32 elements in table EmpName using all permutations and combinations. When you specify a join, the ASE selects only those elements with a commonality. For example both tables have a common column EmpID, when you write a join statement like

SELECT
EmpAddress.*,
EmpName.*
FROM EmpAddress A
INNER JOIN EmpName N ON N.EmpID = A.EmpID

You are specifying that the ASE should bring only those rows which have the same EmpID in both the tables. eg, if EmpID= 233,234,235 are common in both the tables, it will get the Address row from EmpAddress(A) and the Name row from EmpName(B) for EmpIds 233,234,235. All the other combinations are ignored.

About the where clause, if you want to select only the details of employee with EmpId 233, then you specify,

SELECT

EmpAddress.*,

EmpName.*
FROM

EmpAddress A INNER JOIN EmpName N ON N.EmpID = A.EmpID

Where

A.EmpId = 233

Compare this with

SELECT

EmpAddress.*,

EmpName.*

FROM

EmpAddress A,

EmpName N

WHERE

A.EmpID=N.EmpID AND

A.EmpId=233.

Since the query here is simple, you might not get the entire picture. Imagine having to join 5 tables, with 10 filter criteria?The query gets patch, muddled and complex.

One quick point about differnce between 'where' clause and 'Having' clause. Having is used to specify filter criteria on aggregate functions.Say suppose you write a query to calulate the average salary of all the empolyees and filter those with <50000.>

SELECT

AVG(Sal)

FROM

EmpSal S

INNER JOIN EmpDetails D on D.EmpID = S.EmpID

WHERE

S.EmpId in (233, 234,235)

HAVING

AVG(Sal) > 50000

Purpose of this Blog

I read a book called Impatient Perl. True to its Annam, it's book for people who know nothing about Perl and are forced to program in it with very less time to pick up the threads. Well, this blog is on the similar line for people new to SQL's, Querying and Database usage in general and Sybase in particular.