JDBC Query too slow

Can you show us an example of a slow query? If you have thousands of rows and your query contains things like joins (with large data sets) or sub queries, this could potentially be a problem - I'd need to see the queery to get an idea of what can be done.

@colr__:Below is a sample query which takes a long time to retreive results
select  a.rollupType, b.name as Type, a.distSrcID, c.dsCallLetters, c.dsCallLettersView,c.dsName,
a.distSrcIDPrimary, a.callLettersPrimary,d.dsCallLettersView as callLettersViewPrimary, d.dsName as dsNamePrimary, isActive
from  DistRollupTy b, DistRollup a
left outer join DistributionSrc c on a.distSrcID = c.distributionSourceStationID
left outer join  DistributionSrc d on a.distSrcIDPrimary = d.distributionSourceStationID
where a.rollupType = b.rollupType
In general what is the technique used to speed up these rettreivals???
Please help...

Setting the fetch size for the result set can improve performance

rs.setFetchSize(size);

Also check your indexes

If you can re-work your query to use UNION instead of left outer join, that would help.

@colr__::How do i do that in this case.
Please help...

I was trying to work that out, but my sql isn't all that strong!

@colr__:What's the diff of using a union incase of a join.How much imrovement in speed can be achieved.

I had a similar problem with a site several years ago, and I managed to change my huge joins to using unions, and the effect was a significant speed imporvement. When you do a join with A and B, this creates a large temp table in membery. Doing a join with this against table C produces an even larger result, then you do another join - so the in-memory data set is massive, and understandably cumbersome. Doing an interesection doesnt work like that, so the data sets in memory are a lot smaller and so faster. This article might be of use:

http://stackoverflow.com/questions/3279030/i-want-to-replace-the-left-outer-join-with-an-alternate-basic-query-how-can-i

@colr__:Thanks for replying.
But if if you ignore queries with joins my normal select  queries without any joins are taking a very long time.In that case how do i reduce the time.
Please help...

Ah, ok - that's probably not the issue then. Is your database server on the same machine that your web app is hosted?

If you have the following query in a jsp: SELECT one, two, three from ATable, try replacing it with this: select "one", "two", "three" from ATable. If this is still very slow, then its not a problem with your query or indexes or anything like that, it means its a problem either with the jsp or the communication from the web container and your database.

@colr__:My db server is on the same machine my web app is hosted.I didn't understand your suggestion.

Ok, if you have a query like this : SELECT one, two, three from ATable, then this selects values from the columns named one, two and three from the table named ATable.

If we replace this with select "one", "two", "three" from ATable, then this doesnt select values from the table named ATable., but it instead simply selects three constant string values - "one", "two" and "three".

If you run this sql in your jsp page and it is still very slow, then it points the finger at either the jsp or the communication between your web server and your database server (even if they are on the same physical machine). This is just a test to try adn figure out whether it is the queries you are running or something else that is the problem.

Try this test on one of the simplest select queries you have that are performing slowly

@colr__:I tried out your suggestion and its lightning quick and i even tried it wihout double quotes for a table which has few hundred records.And its quick again.
The speed is reduced exponentially when the table has a few thousand records.But modern day websites deliver huge data in an instant.
Please help...

This was posted in Sybase and Oracle zones.  Which database?

@slightwv:Its Sybase database.

sybase works with jTDS driver which is 10x faster than MS drivers against MS-SQL and a bit faster querying sybase.

"The queries are woking fine but are extremely slow.One of the pages just takes 25 to 30 seconds to ..."
Is it realy the query that is slow or could it be the 'loading into page' . When the query takes 10% off the time and 90% is the processing the result into your page then optimising the speed off the query is not the ultimate sollution.

Query:
1. are there good indexes the join's
2. there is no filtering -> allways everything as result  .... can you filter to limt the rows when you don't need everything? (less rows)
3. is every column necessary, what are datatype's  (less columns)
4. your double 'left outer join'  with table DistributionSrc  could be a reason to make it a union, but only if both join's (with c and d) don't come at the same time  -> see attached code (for 'empty'  I did'nt know the datatype so I just put 'xxx' shows nice in result that this record comes from part 1 or 2 off the union)
But you can loose time on the action off finding duplicates in the UNION -> UNION ALL is better
So there could be oportunities, but number off rows, datatype and functionallity play a role in knowing the best fit for your case.

I'm not familiar with sybase, but advice should be db-independent.

select  a.rollupType, b.name as Type, a.distSrcID, c.dsCallLetters, c.dsCallLettersView,c.dsName,
a.distSrcIDPrimary, a.callLettersPrimary
,'xxx' as callLettersViewPrimary, 'xxx' as dsNamePrimary, isActive 
from  DistRollupTy b, DistRollup a 
left outer join DistributionSrc c on a.distSrcID = c.distributionSourceStationID 
where a.rollupType = b.rollupType
union 
select  a.rollupType, b.name as Type, a.distSrcID
, 'xxx' as dsCallLetters, 'xxx' as dsCallLettersView,'xxx' as dsName,
a.distSrcIDPrimary, a.callLettersPrimary
,d.dsCallLettersView as callLettersViewPrimary, d.dsName as dsNamePrimary
, isActive 
from  DistRollupTy b, DistRollup a 
left outer join  DistributionSrc d on a.distSrcIDPrimary = d.distributionSourceStationID 
where a.rollupType = b.rollupType

Select allOpen in new window

A link that's showing what I mean with there are lot's of other possible directions to the slow loading other then the query
http://www.javaperformancetuning.com/tips/j2ee_srvlt.shtml#REF12

Can you monitor on the database when the query arrives there and how long it takse tor process? Or can you launch that query in a strictly sql-environment to see how long the query takes? If thats less than 2 seconds don't focus on optimizing the sql 'at this moment'.

Personaly I think chanses are far higher it is the processing off the query-result that takes most off the time. 'Few thousand records' ... all data over network to your webserver, processing them there ... over to browser (which browser) and render them there.  Lot's of places where it could go wrong and limiting the amount off data at the source (query) that prevents any possible bottleneck to become a huge problem.

In your case you have do you list a few thousand records?  How many types are there, because there's a , "b.name as Type"  consuming resources for each of your 'few thousand records" any step off the processing. Same for the 'DistributionSrc', can it be read/rendered only if you want to see the detail?