Three different queries have been written to get the count of same columns based on different conditions.But this results in hitting the database very often and thus performance comes down.So we want to write a query which combines all the three queries into a single one.We use three different methods to invoke these queries individually.This should be avoided and made into a single call and thereby hit the database only once.Here follows,the different queries........
Query1: SELECT COUNT(JOB_ID) JOBID FROM EOMS_JOB_QUEUE_V WHERE JOB_STATUS_ID != 12 AND JOB_TYPE = 18 AND AGENT_ID =4
Query2: SELECT COUNT(JOB_ID ) JOBID FROM EOMS_JOB WHERE JOB_STATUS_ID != 12 AND JOB_TYPE = 20 AND AGENT_ID = 4
Query3: SELECT COUNT(JOB_ID ) JOBID FROM "+ EOMS_JOB WHERE JOB_STATUS_ID != 12 AND JOB_TYPE = 19 AND AGENT_ID = 4
Note:In the above queries the job_type alone changes and the other conditions remains the same.
But this results in hitting the database very often and thus performance comes down.So we want to write a query which combines all the three queries into a single one
Why do you believe this is the source of your performance degredation? What makes you assume it is network bound, and not database bound? I'd imagine the bulk of the work is not the network traffic to the database (you return three single record ResultSets so there is not much being passed over the wire). Are you sure any lag you see is not the result of the queries themselves? If it is, covnerting these into one query is not going to fix the performance issue.
You could change the Job Type criterion from equality to inequalities to cover the range of Job Type values you are interested in, then use GROUP BY to get the count of each Job Type. That would do it in one pass, wouldn't it?
Your table probably should be indexed on all the criteria used in these queries, if they are indeed very frequent queries.
Betty Rubble? Well, I would go with Betty... but I'd be thinking of Wilma.
just focusing on the SQL side of things, your code would look like this:
SELECT JOB_TYPE, COUNT(JOB_ID) JOBID_COUNT FROM EOMS_JOB_QUEUE_V WHERE JOB_STATUS_ID != 12 AND JOB_TYPE in (18,19,20) AND AGENT_ID =4 GROUP BY JOB_TYPE
of course, make the necessary adjustments to your java code since you'll now have a "table" of values instead of just one value.
if EOMS_JOB_QUEUE_V(which i'll assume is a view) references a large table or group of tables, you should talk to your DBA about the ways of speeding up access to said table(s). he will know what to do. one of them is implementing indices on the appropriate columns and making sure your sql code is using them.