Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

database query

 
sagaya deni
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Barry Gaunt
Ranch Hand
Posts: 7729
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moved here from SCJP which certainly does not have such stuff in its objectives.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Peter Chase
Ranch Hand
Posts: 1970
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Duke Astron
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic