| Performance of a query [message #308606] |
Tue, 25 March 2008 01:01  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Let say that there are four tables
i.e coabalances,coaratios,statement,subjective_prm_trans
If I query the count of these tables
e.g
SELECT COUNT(*) FROM STATEMENT
count(*)
------------
3805
SELECT COUNT(*) FROM COABALANCES
count(*)
-------------
613861
SELECT COUNT(*) FROM COARATIOS
count(*)
----------------------
26952
SELECT COUNT(*) FROM SUBJECTIVE_PRM_TRANS
count(*)
-----------------------
142788
Well These are the counts which are retrieved from these queries
Now There is a procedure which contains all the above sql statements embedded in it.Now For every year these many counts(*) are being generated.
Every year these counts are increased with the total number of counts this year.
The final output that I get in a total year is 212 which comes by firing this query
SELECT COUNT(*) FROM COAITEMS WHERE TR_ID < 5 AND COAID = 1
count(*)
--------------------
212
The conditions
If data is there in statement table there it is mandatory that the data has to be in COABALANCES Table
IF Data is there in Stataement table then it is mandatory that the data has to be in Subjective_prm_trans tABLE
If data is not there in statement table there the data has not to be in COABALANCES Table
If data is not there in statement table there the data has not to be in SUBJECTIVE_PRM_TRANS Table
But the count generated for every year is 212
Could you please tell me whether I wanted to optimise these queries.How I should optimise these queries.
For every table associated to it there are Composite B-TREE Indexes atttached to it
Suppose the time generated for one query is 1 second
I wanted the time generated to be in 1 milliseconds.
Can you please suggest me some ways....
[Updated on: Tue, 25 March 2008 01:03] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
