Talis Developer Network - Trying to make sense of some sql - Comments

running SQL

Brian Crampton — Fri, 10 Nov 2006 11:41:42 +0000

It may require a scripted approach to get what you want.

I have published an article How to analyse SQL statement performance that gives an introduction to analysing Sybase performance. It is obviously an area where care needs to be taken.

I checked briefly and it looks like when you don't have the LOAN.CREATE_DATE then an index on the loan table (LOAN_INDA) is used. But when you do use the condition then as the attribute is not indexed it does a table scan. Since the LOAN table will be one of the biggest tables you want to avoid the table scans if possible especially as it may have to do multiple scans.

An approach may be the following SQL

select L.CREATE_LOCATION, count(L.LOAN_ID)
from LOAN L (1), ITEM I, CLASSIFICATION C
where C.CLASS_NUMBER BETWEEN "$1" AND "$2"
and L.STATE in (0, 1)
and C.CLASS_ID = I.CLASS_ID
and I.ITEM_ID = L.ITEM_ID
group by L.CREATE_LOCATION

This will list the count for each site which I realise may not be what you are looking for. The SQL will use the LOAN_INDA index but now requires a work table to do the group by section. This may mean that is is not quicker (it could take longer) or still not quick enough. In my tests here it made little difference to the time but then I wasn't seeing a real difference in the first place between the two origianl pieces of SQL.

You could give it a go but be very careful if running on the live system. This is a standard warning whenever you run a piece of SQL.

The only other option is to run for smaller sections of class numbers and then add together the values. Again not ideal and may still take too long but a possibility.

Thanks

Brian Crampton
Developer, Talis

running sql

vicky — Fri, 3 Nov 2006 15:43:23 +0000

Thank you very much for that comment, Brian - I appreciate your comment that there is nothing inherently wrong with the sql because it did seem such an odd thing to happen.

The response time is not acceptable because this is to be used as a checking tool by staff for stock, and ideally they would want to get answers to all the aspects of current provision and previous use within a particular Dewey area within a minute - so for just one part of it to take more than two is no use. If anyone has got any ideas as to how I could otherwise get this information, I will be very grateful.

Why do you say that care needs to be taken in the use of indexes rather than full table scan? - and I have not heard of the LOAN_INDA index. I will also be interested in learning more about the query plans - sound a useful tool.

running SQL

Brian Crampton — Wed, 1 Nov 2006 14:59:02 +0000

There is nothing inherently wrong with the SQL. It is likely to be the way Sybase has decided to run the two queries on your database.

Whenever you run a query the Sybase database decides on a query plan on how to retrieve the data. This plan includes what indexes to use on each table. The plan can potentially vary from customer to customer because the size of tables, the distribution of data etc is different.

As the database changes it is possible that one day an index is used and then the next day Sybase may decide to table scan rather than use an index. A table scan is more intensive.

The database is configured e.g. indexes etc so that Alto and other applications are not usually affected by this.

When I run this SQL here the time is about the same for both queries and both queries use LOAN_INDA index. It is possible that in your case that this index is used for the first query but for the second query Sybase has decided that it might as well do a table scan of the LOAN table which does more work.

Without investigation I'm not sure whether this is the exact cause but it could be.

As you will have noted I was able to check what the query plan was doing for a peice of SQL. I will put together some details about how this can be checked.

It is possible to force the use of indexes but this needs to be done carefully. You don't mention why the response time is not acceptable and what the context in which the SQL is being used. There may be other solutions.

Thanks

Brian Crampton
Developer, Talis

Trying to make sense of some sql

vicky — Wed, 1 Nov 2006 12:25:53 +0000

I am trying to get some information out, but I fear my sql ability is failing. I have test run

select count(L.LOAN_ID) from LOAN L, ITEM I, CLASSIFICATION C
where C.CLASS_NUMBER BETWEEN "$1" AND "$2"
and L.STATE in (0, 1)
and C.CLASS_ID = I.CLASS_ID
and I.ITEM_ID = L.ITEM_ID

and this gives a result in 13 seconds. However, if I add in a line to include the location as a criterion:

select count(L.LOAN_ID) from LOAN L, ITEM I, CLASSIFICATION C
where C.CLASS_NUMBER BETWEEN "$1" AND "$2"
and L.STATE in (0, 1)
and L.CREATE_LOCATION = "ML"
and C.CLASS_ID = I.CLASS_ID
and I.ITEM_ID = L.ITEM_ID