• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

database query

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 7729
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moved here from SCJP which certainly does not have such stuff in its objectives.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1970
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
She's out of the country right now, toppling an unauthorized dictatorship. Please leave a message with this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic