wood burning stoves 2.0*
The moose likes JDBC and the fly likes database query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "database query" Watch "database query" New topic
Author

database query

sagaya deni
Greenhorn

Joined: Jan 05, 2007
Posts: 2
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

Joined: Aug 03, 2002
Posts: 7729
Moved here from SCJP which certainly does not have such stuff in its objectives.


Ask a Meaningful Question and HowToAskQuestionsOnJavaRanch
Getting someone to think and try something out is much more useful than just telling them the answer.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Peter Chase
Ranch Hand

Joined: Oct 30, 2001
Posts: 1970
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.
Duke Astron
Greenhorn

Joined: Jan 26, 2007
Posts: 4
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: database query