JDBC Result Closing Takes Ists Time
i am reading 1000 Elements from a database (500.000 all together).
The problem is reading the data takes only a few miliseconds but
closing the ResultSet takes another 22 seconds. My Hard Disk tells me
that the ResultSet is probably running to the end of my table row by
row. Is there a way to tell mysql not to do this ?
Statement tempStmnt = aConnection.createStatement();
tempStmnt = aConnection.createStatement(
java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
tempStmnt.setFetchSize(Integer.MIN_VALUE);
Date tempStart = new Date();
ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM
ARTIKELSTAMMDATEN");
int tempXx = 0;
while (tempRs.next() && tempXx++ < aCount) {
tempRs.getString("artikelBezeichnung");
}
View Complete Forum Thread with Replies
Related Forum Messages:
JDBC Result Closing Takes Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Query Takes A Long Time
On my site I have a query that searches through 1,7 million. The php-file with the query takes a bit of time to load. Is it possible to show some sort of progress-bar during this time?
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
View Replies !
Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table BOOKS for the TITLE 'java' is very fast. We have a fulltext index on the column TITLE. However, if we want to do a exact same query and include the rule that the book with ISBN '0131016210' always should be included - then the query take several seconds to finish: SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE '0%' OR ISBN LIKE '1%') OR ISBN = '0131016210' ISBN is the PRIMARY KEY. Is there anything I can do about this?
View Replies !
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
JDBC Error - Javax.servlet.ServletException: Com.mysql.jdbc.driver
I get the following error when trying access my MySQL database from a jsp page. I have copied the proper jar into the WEB-INF/lib and the /ext path of my CLASSPATH. Am I misisng something? +++++++++++++++++++++++++++++++++++++++++++++ type Exception report message description The server encountered an internal error () that prevented it from fulfilling this request. exception javax.servlet.ServletException: com.mysql.jdbc.driver org.apache.struts.action.RequestProcessor.processException(RequestProcessor.ja va:535) org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcesso r.java:433) org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) javax.servlet.http.HttpServlet.service(HttpServlet.java:689) javax.servlet.http.HttpServlet.service(HttpServlet.java:802) root cause java.lang.ClassNotFoundException: com.mysql.jdbc.driver org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java: 1352) org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java: 1198) java.lang.ClassLoader.loadClassInternal(Unknown Source) java.lang.Class.forName0(Native Method) java.lang.Class.forName(Unknown Source) com.mullen.cac.struts.action.TestDB.execute(TestDB.java:30) org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcesso r.java:431) org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) javax.servlet.http.HttpServlet.service(HttpServlet.java:689) javax.servlet.http.HttpServlet.service(HttpServlet.java:802) note The full stack trace of the root cause is available in the Apache Tomcat/5.5.17 logs.
View Replies !
Result Time
is there a function in either php or sql that tells you how long a query took to process? i'm trying to find the best sql statment, and i need benchmarks.
View Replies !
Closing The Connection
When i am closing the browser window all the active open connections to the db must be closed. Is it possible to do this, how?? I am using Java Servlets and Mysql.
View Replies !
Tables Not Closing
I’ve got a database that seems to be misbehaving and I’m hoping that some one might be able to point me in right direction for a solution. The details are as follows: When I run a Check table and quiet often get a warning connection not closed or other users have the table open with MySql claiming that up to three connections are open. I now that no one else has a connection open as at this stage I have taken the server off line. The server details are as follows: OS X 10.2 (also happening on an OS X 10.1 server). The database is been accessed through PHP (PEAR DB library).
View Replies !
Closing MySQL Connections
Here's a question for you, my hosts have told me that that one my pages, php, was causing their server to reboot because there were too many open connections and that they should be closed. Now, correct me if I am wrong but I thought that the connections were closed after a minute of inactivity. Does this sound right? Could open connections cause their server to reboot a lot, say 3 times in 15 minutes? If so, what can I do about it?
View Replies !
DB Connection Not Closing In A Subroutine
I have created a connectDB and closeDB subroutine in perl. I connect to the DB and return the $dbh from connectDB subroutine and perform the database operation. I call the subroutines like this: $dbh = connectDB(); closeDB($dbh) closeDB routine is like this: my ($dbh) = @_; print "<BR>dbh in disconnect:$dbh <BR>"; $dbh->disconnect(); print "<BR>dbh after disconnect:$dbh"; The strange thing is even after I call the disconnect method, it seems it isn't disconnected because the last statement of the subroutine still prints the $dbh value. And sometimes I am getting an error in Apache logs as 'can't call disconnect on an undefined variable'.
View Replies !
ORDER BY Takes Forever
I'm having problems with ORDER BY. When I run a select targeting just one of my tables, it runs quickly and nicely. When doing the following: select * from rubbet, kommun where ortnamn like '%Johannes%' and rubbet.harad=kommun.harad and rubbet.socken=kommun.socken order by rubbet.ortnamn; it screws up bad. The query takes about 2 minutes to run before showing the result: "74 rows fetched in 0.0032s (112.5129s)" obviously the query takes no longer than 0.0032s but the ORDER BY clause makes it take forever to bring back the result. without the order by the whole thing runs very smoothly.
View Replies !
SELECT Takes 20 Seconds
I am having trouble speeding up a SELECT statement from a table with 1,764 records. I tried myisamchk --sort-index --sort-records=1 but this did not help (is a Primary key enough?). Things were fine at around 1200 records but went downhill after 1700. I am using MySQL 3.23.42 on HP/UX 11.11 and PHP 4.3.4. Any tips on how to speed things up?
View Replies !
Update Takes Too Long, I Need Help
here is my update SQL string: update prices, legend set prices.legend_id=legend.id where ( (prices.id between 1 and 10006) ) and prices.a_legend=legend.description running time: 63.97960 as you see prices has 10006 records and legend table arround 349 records please advice how to speed up this?
View Replies !
Subquery Takes Forever
I have two problems where I'm trying to retrieve data using a query. Part 1: I am doing a simple sub query which is on a two data sets with no more than 3000 rows in total. The query is taking 28 seconds to execute? Why I don't have a clue? Quote: 'Select Title FROM products WHERE Product_ID IN (SELECT Product_ID FROM order_items WHERE Order_ID="'.$id.'")' Part 2: When I have solved the above I'm looking to do a join, but don't know how to approach it ? Basically I'm trying to get a complete data set for a product/order like the following: SELECT * (which includes product_id) from items AND SELECT Title from products where product_id =(the product_id is retrieved from items); // using a join
View Replies !
Create Index Takes More Than 5 Hours
I'm running MYSQL on a windows xp laptop with an intel centrino 1.60 GHz Processor and 512MB RAM. I've created a table and imported 27 million rows of data. I wanted to create an index on one char(55) field, as this field is a key against which many selects will be made. The information in the field is not conducive to having an integer key as any queries would require an extra join to the look-up (reference) table. Creating the index ran for more than 5 hours and finally gave up when there was no more space on the drive (creating the index apparently consumed more space (11GB) than the actual table (8GB). I'm wondering if this is normal time and space consumption for 27 million rows. Finally, doing an un-indexed query on this table : SELECT * from historical where reference = 'abcd'; Takes more than 1/2 hour. Is this also a normal amount of time? I understand that people are using MYSQL for data warehousing and major transactional applications.
View Replies !
MySQL Takes FOREVER To Start Back Up
Need some help here. I am running MySQL 4.1 on a windows 2003 server. We've had the server for about a year and right now the data file for the database is up to 345mb. Its growing week by week. Lately if I had to restart my server once windows comes back up it takes 20min for MySQL to start back up.
View Replies !
Backup With Mysqldump Takes Hours, Database Is Not That Big
Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb). My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all. My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk. The database is around 350mb, with around 1.1 million rows. To backup I use mysqldump -uroot -p database > backup.sql As I said, the above takes hours. I guess it should take minutes?
View Replies !
Delete All Records In A Table Takes Forever -- Anyone Know Why?
I have a table called table_a that has 1 record in it. I delete that record, which because of foreign keys (the tables are type InnoDB) will cause the records in 8 other tables to be deleted. The 8 other tables have a maximum of 200,000 records in them. 2 of them have that many while the remaining have < 5,000 records. Currently, deleting that 1 record has taken hours upon hours with no end in site. Does anyone know why in the world this would take so long? Is there any way to speed it up (maybe a config setting I am missing or something)?
View Replies !
How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set? I would like to pass the calculated offset into the limit half of and ordered select statement. E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23. To do that I need to find the offset of photo with id=23 in select id, datetime, caption from photos order by datetime; Then I could get the result I want by doing.... select id, datetime, caption from photos order by datetime limit $offset, 5; I've spent several hours scouring around and found some people with similar problems, but no solutions yet.
View Replies !
Lost Connection To MySQL Server During Query After Script Takes 60 Secs
I get the error "Lost connection to MySQL server during query" at the mysql_select_db when reaching this part of the code after 60 seconds of script execuation has already passed. Works fine if the script execution time when reaching this is under 60. Tested with Sleep() and I'm 100% sure that the 60 second barrier is causing it. I just don't know what the barrier is.////
View Replies !
About JDBC
Well i work with intellution´s Proficy line of products and my clientes are using various database manager systems, now MySQL is very popular and i found a lot of clients using it, for progrmas like iFIX there is no problem we just use MyODBC and it is like a walk in the park, but trying to connect it with JDBC to other program (to be exact Proficy portal wich is made totally in Java) at least for me has been impossible. would anyone please tell me how to connect, install and manage the .jar files under Windows XP? what step am i missing? where do i have to put install or copy this files? is there an application i am not using?
View Replies !
JDBC To MySQL
I wrote a Java program to connect to MySQL using JDBC ...and it works fine...it connects and it does what is supposed to do.....however when I use the same program to connect to another MySQL server (in another machine) then I get an exception: "Server Configuration Denies access to data source"
View Replies !
Setting The First Row In JDBC
Consider a table with 100 entries in it. Say, I execute the following query from a JAVA application using JDBC: - SELECT * FROM foo_table LIMIT 10 ORDER BY start_date DESC; Due to 'LIMIT 10 ORDER BY start_date DESC' it will return me top 10 results. How can I retrieve the next 10 in some later query, totally independent of any current query? I wish to do something like the Hibernate function Query.setFirstResult(int index); but using JDBC.
View Replies !
JDBC And MySQL
I want to create a Java-Applet, which communicates with a MySQL-Server. My question: Is it possible to use a Java-Applet without installing a Driver on the client? What do I need?
View Replies !
MySQL & JDBC
We currently have mySQL version 4.1.21 on our server. Does a version of JDBC come bundled with mySQL? If so what version is it?
View Replies !
JDBC Install
i have recently installed MySQL 5.0.67 on my Mac OS X 10.5.5. I want to connect my web application to a database. I was hoping that someone might point me in the correct direction to get this sorted.
View Replies !
Comment With JDBC
I'm just experiencing this new database, it's great. I have one question. Is it possible to get the column comment (set by the insert command) with JDBC somewhere in the metadefinitions? ... that would be great because I want to make a generator and could use this for several settings.
View Replies !
Map JDBC Types
I'm setting up a server-application on a tomcat that is developed for SQL Server or Oracle but I got the thankless task of trying to set up the application on a MySQL instead. One part of setting up this is to port some properties-files and I cannot get the hang of it. Is there some friendly soul out there that could give me a hand? I've included the content of properties-files below for SQL Server and I believe you'll get how it should be ported to MySQL. Most part of the content is the same in all three files but I included them all anyway
View Replies !
Mysal / Jdbc
why this code leads to SQLException? with SELECT * from FUNDS all is well. SELECT FUNDS.ISIN, NAME, COUNTRY_NAME, CURRENCY_ID, UL_1.LOGIN AS CLOGIN, UL_2.LOGIN AS MLOGIN FROM FUNDS, COUNTRIES, USER_LIST ALIAS UL_1, USER_LIST ALIAS UL_2 WHERE FUNDS.COUNTRY_ID = COUNTRIES.COUNTRY_ID AND CREATOR = UL_1.USER_ID AND MODIFICATOR = UL_2.USER_ID UNION SELECT FUNDS.ISIN, NAME, COUNTRY_NAME, CURRENCY_ID, UL_1.LOGIN AS CLOGIN, NULL FROM FUNDS, COUNTRIES, USER_LIST ALIAS UL_1, USER_LIST ALIAS UL_2 WHERE FUNDS.COUNTRY_ID = COUNTRIES.COUNTRY_ID AND CREATOR = UL_1.USER_ID AND MODIFICATOR IS NULL ORDER BY ISIN;
View Replies !
Classpath For JDBC
Do I need to set a classpath to get a connection between a java program and a database? If I have to set a classpath in the command prompt in Windows - what must this classpath direct to - the .exe file where mysql is installed on my system?
View Replies !
Com.mysql.jdbc.MysqlDataTruncation:
I just upgraded to MySQL to 4.1.9 and JDBC mysql-connector-java-3.1.6-bin.jar and now getting the following Exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'usaCard' at row 1 What causes this exception? I've set jdbcCompliantTruncation=false for the time being but would like to understand what causes the Exception.
View Replies !
Com.mysql.jdbc.CommunicationsException
I have the web Application in Spring and Hibernate which is connected to the MySql database through the Jboss Server. I am getting the following error. com.mysql.jdbc.CommunicationsException: Communications link failure Do any one has the solution?
View Replies !
Can't Establish JDBC Connection
I'm developing a small java web-app for a client using JSP, Servlets and a MySQL db. The app-server and database are leased from a third party vendor, so all my work is done remotely. I've uploaded my app and am trying to establish a connection but can't. The exception is this: Access denied for user: 'username@localhost.localdomain' (Using password: YES) and occurs at line: Code: Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","username","pwd");
View Replies !
How To Use Jdbc To Connect To Database?
I have installed MySQL4.1.9 and installed JDBC driver connect J/3.1 I installed MySQL in C:MySQL and I have copied the "mysql-connector-java-3.1.6-bin.jar" file into <java home>jrelibext , <java runtime environment home>libext and <Tomcat Home> commonlib. I started up MySQL from the commandline client and created a 'books ' database using "CREATE DATABASE books;". I have also added the statement: GRANT ALL PRIVILEGES ON *.* TO '<user>' IDENTIFIED BY '<password>' WITH GRANT OPTION; The problem is when I try to use java to access the database via jdbc. I typed the code: Connection con = null; Class.forName("org.gjt.mm.mysql.Drive").newInstance(); con = DriverManager.getConnection("jdbc:mysql://localhost/books ?user=<user>&password=<password>"); It compiles correctly but when i execute the code,it displays: "SQLException: java.sql.SQLException: Incorrect database name 'books ' " I changed the statement to: con = DriverManager.getConnection("jdbc:mysql:books ?user=<user>&password=<password>"); but when executing,the message now is: SQLException: java.sql.SQLException: No suitable driver I have tried checked the jdbc manuel online but the con statement given is about the same. It seems that somehow jdbc cannot find the database and the driver is not recognised also.
View Replies !
JDBC Number Rows
After you have done a .executeQuery() and returned that to a ResultSet how do you get the number of rows returned? I have tried using getFetchSize() but that does not help me.
View Replies !
Remote Access Using JDBC
I've set up a User account called 'test' for my MySQL database (for use from my Uni). The host value for 'test' is '%' but for some reason I can't connect when accessing the Database from a dynamic dns name. Test can connect when the JDBC is run using 'localhost' but the connection attempt simply times out when I change the URL to 'bailz.dyndns.org'. The PC is running behind a router but I can access the web server no problem from outside... it's just MySQL via JDBC that I'm having trouble with.
View Replies !
Jdbc / Odbc Connection
I am running Suse 10.1 and i have MySQL working fine but Im having problems with connecting my Java Application with my Database. I got the recent J Connector drivers but i keep getting the exception about it not being able to find my device driver. The only other thing I was un-able to figre is where is "%Java_home% /jre/lib" is that only for windows?
View Replies !
Jdbc Over SSL, SAP Business Connector 4.6
I have set up a connection between SAP Business Connector 4.6 and MYSQL. I'm currently using jdbc driver com.mysql.jdbc.Driver (mysql-connector-java-3.0.16-ga-bin.jar). Using DB URL jdbc:mysql://<ip>/<database> the connection is working fine. So far so good. Now my problem: I need to set up a SSL connection but in MySQL Connector/J Documentation is stated: 'For SSL Support to work, you must have the following: A JDK that includes JSSE (Java Secure Sockets Extension), like JDK-1.4.1 or newer. SSL does not currently work with a JDK that you can add JSSE to, like JDK-1.2.x or JDK-1.3.x due to the following JSSE bug: http://developer.java.sun.com/developer/bugParade/bugs/4273544.html' My SAP business connector is running under jdk 1.3.0 How do I get SSL running?
View Replies !
[java] Jdbc, More Than One Sql Command
I use Connector/J ver. 3.0 (downloaded from mysql.com). Does anybody know why I cannot execute something like that in my Java program: stat.execute("CREATE TABELE a(b int);INSERT INTO a(b) VALUES(1);"); ??? something like this works fine: stat.execute("CREATE TABELE a(b int);"); stat.execute("INSERT INTO a(b) VALUES(1);"); i was trying to change the string: "CREATE TABELE a(b int); INSERT INTO a(b) VALUES(1);" "CREATE TABELE a(b int); INSERT INTO a(b) VALUES(1);" and nothing works. Can anyone tell me why this jdbc driver cannot take this??? If I put the string: "CREATE TABELE a(b int);INSERT INTO a(b) VALUES(1);" into mysql console everthing works fine.... I don't get it. I was trying to run it in pg, and evertyhing worked fine.
View Replies !
Configuring MySQL For JDBC ?
I'm having a MySQL DB running on one machine and a Java-Tool which accesses this DB via JDBC. With this tool, I can add a second DB data source in form of a mySQL DB and I've installed one more MySQL DB on a third machine, which runs perfect, but the access from the java-tool via JDBC-connection doesn't work. Command line "mysql" tool work perfect from the machine where the java tool is running. The company which coded the java-tool told me that I have to install a package for the mysql db which enables jdbc. Is that right and what do I have to configure ?
View Replies !
JDBC And Mysql Errors
I developed the java code to create data object's that is created from the class mpcData, the main database connectivity is mpcConnect now when i initially developed the code it compiled error free, soon as i added the classpath to the directory containing the Mysql drivers ( "c:jdbcDriver") in my windows system environment i'm getting error's in the compiling that i cannot find the data object mpcData anymore.
View Replies !
|
TRACKER
