poor performance with JSP and SQL 3

[frag]

Hi everybody!

I try to select data from a Sybase-database with Java Server Pages (JDBC connection).
It's a simple &quot;select * from <table>&quot;. The <table> however has 400 entries. The JSP-page reads the resultset and writes the data in a html-table (which is dynamically build). It takes about 15 (!!) seconds which is a very poor performance!

Could some give me a hint why this is so slow??

cheers

frag

patrick.metz@epost.de

 

[frag]

hm... it seems that the jsp takes to long to build the html-table (or read from the result set). the query returns in less than a second. :-(

i don't think that i can make this faster...

frag patrick.metz@epost.de

 

[frag]

For building the html-table I use 'out.println(&quot;something&quot'.
Is there a faster way??

patrick.metz@epost.de

 

[harmmeijer]

You should have one out.println statement. I don't know jsp but in asp it works about the same.
I put all my content in an array (because in asp you can dynamicly set the length of buffers) and join the buffer in one response.write line.
I think the equivilent for jsp is StringBuffer?? and the .append??
At the end of the loop you can do one write
out.println(StringBuffer.toString());

 

[Rayz66]

Although that doesn't sound like a lot of entries from a database point of view, it's quite a lot for a web page.

Can't say that I've seen too many dynamically generated tables with that many entries in it.

What I would be inclined to do, is put the results on separate pages. For example, query the first 20 entries, then when the user presses next, then query the next 20 entries (you should also have a button so that they can go backwards).

If you do it like this, then your query will probably be faster, and your pages will load much more quickly.

 

[Sulaiman]

hi,
I am working with oracle and JSP with no problem and the speed is great even with too may records.

Any way I face the same problem with JSP or with java when I used to connect to the databse JDBC_ODBC driver (type 2 driver) and 15 second thats really fast, in my case it toke me 30 seconds. but I changed it to a type 4 driver called Thin driver specially for oracle.

I think it better if you find another Database engine, maybe by checking your vender website for such a driver.

Regards

smukahhal@hotmail.com

 

[iSeriesCodePoet]

The problem with with your speed is because it takes time to create a connection to the database. Using connection pooling should help performace some. Also, what kind of system are you running this one? One other thing to remember is that the initial load takes more time. iSeriesCodePoet
IBM iSeries (AS/400) Programmer

 

[engasi]

iSeriesCodePoet,

How to implement the connection pooling in JSP/JRun/SQL Server 2000 environment? Does it require alot of Java programming? If yes, from where I should start. Thanks.

 

[iSeriesCodePoet]

Search the web for connection pooling.

Apache has a project and there are many others out there. I have never done one. iSeriesCodePoet
IBM iSeries (AS/400) Programmer

 

[Rayz66]

Apache has a connection pool which you can find at:

http://jakarta.apache.org/commons/dbcp.html

I personally found it very tricky to use. But Proxool is a doddle.

http://proxool.sourceforge.net/

Well worth a try ...

 

[iSeriesCodePoet]

Hey thanks, I will try that one...

I have been looking for a good and easy connection pool program. iSeriesCodePoet
IBM iSeries (AS/400) Programmer

 

[frag]

Thank you to everybody for your replies.

The problem is not the JDBC-connection, that's for sure.
Connecting to the database-server, firing the sql-query
and retriving the resultset takes just about one (!) second.

The problem is generating the table like Rayz66 pointed out
correctly.

Rayz can you tell me how to do the trick with spreading the
resultset over several pages? I would just need a basic
howto or perhapse a little pice of code.

cu

frag patrick.metz@epost.de

 

[Rayz66]

Well, I can do you a quick description ...

The NEXT PAGE button on your mypage.jsp page calls:

[URL unfurl="true"]http://www.whatever.com/mypage.jsp?lowItem=100&highItem=110[/URL]

The lowItem and hightItem parameters are fed into the SQL statement:

SELECT * from table WHERE rownumber=> lowItem AND rownumber <= highItem

This will give you the limited resultset to display on your page

Now the NEXT PAGE button should now just add 11 to the current values of lowItem and highItem, so that when the page is displayed, the button will

[URL unfurl="true"]http://www.whatever.com/mypage.jsp?lowItem=111&highItem=121[/URL]

So when the user clicks on the button:

SELECT * from table WHERE rownumber=> lowItem AND rownumber <= highItem

is executed, the result set will contain the next page of values to display.

... and then you add 11 ... and so on ....

 

[harmmeijer]

I told you what the problem was 10 posts ago.
Printing out a large amount of information to a browser is not a big problem (depending on the connection speed of the client).
Printing it out line by line is the problem.

Again I don't know if a StringBuffer can be used in jsp but you should use something like that when looping through your records and print it out in one statemnt at the END of the loop.

String = String + &quot;anotherstring&quot;;//this is slow too, don't use thisone.
StringBuffer.append(&quot;anotherstring&quot;//could work for you.