Improving slow queries. Slow query log attached

One area that stands out is the way you are accessing the BillDetails table. All queries appear to be using MONTH and YEAR operators on BillDate. For example:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;

This approach requires accessing every record in BillDetails. A better approach would be to index BillDate and query as follows:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE BILLDATE BETWEEN '2011-08-01' AND '2011-08-31';

Looks like you don't have much data in there at the moment (1373 rows) so the difference in the above will be minimal at the moment, more significant when the volume grows.

First off, most of these seem to be 'slow' because they're not using indexes. You need to analyze how you're accessing the data in your tables to determine proper indexes. But a few pointers from this file:

SELECT * FROM REMARKSETTINGS;

and

SELECT * FROM BILLINGSETTINGS;

These currently only have 1 row, but if they ever grow, you're asking for slowdown. A couple things you can do here is add a WHERE clause (I can't make a recommendation without knowing the DDL) or at the very least only select the columns you need instead of '*'.

For

SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE 
FROM ITEMMASTER A, STOCKENTRY B 
WHERE A.ITEMID = B.ITEMID 
  AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY 
                  WHERE ITEMID = A.ITEMID) 
  AND A.STOCKINHAND > 0 
  AND B.SALEPRICE > 0 
  AND B.INVOICEDATE IS NOT NULL 
ORDER BY A.ITEMNAME, B.INVOICEDATE;

From first glance, make sure you have an index on recordID.

May I make a suggestion and read up on indexing? Indexing is a great help, but if you over-index, it's as bad as under-indexing.