Database question

[Philip Freeman]

For example i need to insert 1000 rows into a table, and the time it needs to insert these rows is measured with timer T.
I decide to do this for MySQL OLEDB/ODBC and SQL OLEDB/ODBC and sql provider, so i can compare times different providers need. Can this be done in java?

[Paul Clapham]

You can certainly use Java to time how long it takes to perform some piece of code. The System.currentTimeMillis() method can be used for that. (I don't know what you mean by "timer T" so I can't say anything about that specifically.)

[Philip Freeman]

Thanks for your reply.
I do not know much about databases and java. Basically i wish to know if i can measure the time MySQL and SQL providers (OLEDB/ODBC and sql) need for inserting some arbitrary number of rows in a table.
The lack of knowledge not being about timers but providers.

[Paul Clapham]

Probably I don't understand what you're trying to do, then. For me if I want to measure the time taken to do X, I don't need to know anything at all about X. I just look at the times before and after I run X and then subtract the two. But apparently you do need to know about X. So there's something not being understood.

[Philip Freeman]

Again thanks for reply. I think that it is my fault, it seems that i did not explain what my trouble actually is. I am sorry for this and hope you have patience.

As far as i understand there is an JDBC driver that enables a java program to interact with databases. And if i want to connect to an ODBC database there is an JDBC-ODBC bridge if i am not wrong. And because of this JDBC-ODBC bridge i can use the ODBC provider and measure the time it needs to do some arbitrary number of queries.(at this point i need to say again that i know very little about databases and how stuff works, so maybe i made a mistake since i still do not know much and maybe the JDBC-ODBC bridge has nothing to do with ODBC provider. I am walking in the dark here and if i hit my head against the wall it seems stupid but i have to keep walking).

TL;DR I presume that i need a JDBC-ODBC bridge so i can use an ODBC provider to measure the time it needs to do some number of queries, and if it is so then i also need a bridge for OLEDB and sql provider. And if there are no bridges for OLEDB/sql provider then i cant measure the time these providers. Am i wrong, is the conclusion that i came to with my limited knowledge utter stupidity?

[Campbell Ritchie]

You may find the System class method which measures nanoseconds more accurate. I would expect to be able to insert 1000 rows so quickly the millisecond method might not be able to keep up.

There is a nice section in the Java™ Tutorials about databases. Here it is.

[Winston Gutkowski]

Campbell Ritchie wrote:You may find the System class method which measures nanoseconds more accurate. I would expect to be able to insert 1000 rows so quickly the millisecond method might not be able to keep up.

@Philip: You also need to be very sure of what you're timing. Database connections, for example, quite often take a disproportionate amount of time compared to an actual retrieval or query - especially the first time you connect. So iif you include them in your measurement, you may be skewing your result in favour of a db that connects quickly, as opposed to one that processes quickly.

HIH

Winston

[Paul Clapham]

Philip Freeman wrote:And if i want to connect to an ODBC database there is an JDBC-ODBC bridge if i am not wrong.

Not in Java 8 there isn't. It was never a good product and when Oracle released Java 8 they decided to stop including it.

But I'm still a bit confused about one point -- if you don't know much about databases then why is this your first project? The question "How long does it take to insert 1000 rows" doesn't strike me as the first question one would investigate while learning about databases.

Anyway I'm going to move this question to the JDBC forum, now that it turns out to be about databases.

[Brian Tkatch]

Philip Freeman wrote:For example i need to insert 1000 rows into a table, and the time it needs to insert these rows is measured with timer T.
I decide to do this for MySQL OLEDB/ODBC and SQL OLEDB/ODBC and sql provider, so i can compare times different providers need. Can this be done in java?

In general, a statement is prepared and executed. Due to my lack of experience with java, i will explain with (pseudo)code.

//connect to database

//prepare a statement with a placeholder, as it will be executed many times
statement.prepare("INSERT INTO tablename(columnname) VALUES(?));

//begin timer here

for(int i = 0; i <data.length(); i++)
{
 statement.execute(data[i]); // this executes the statement with the current value
 // do timer stuff here
}

There's a bunch of overhead in preparing a statement, which is usually negligible, but can add up over numerous statements. By preparing once and executing many times, the calls to the database are both (more) secure and faster.