How Do I record the execution time of running sql statements that are stored in files?

Question

I am doing something related to TPC-H. I've got sql statements in several files. I need to execute them and record the execution time like this: ``` 13 rows in set (0.00 sec) ``` I also need the result sets to see if I got the correct result. My platform is linux(centOS). If I do this manually I will type statements like this in mysql: ``` shell> mysql -u tpch -p mysql> use tpch; mysql> source /home/liova/download/tpch/queries/Q1.sql; mysql> source /home/liova/download/tpch/queries/Q2.sql; ```

Accepted Answer

To find the execution time, you should initialize a date object at the start of the program, and then compare that to another date object at the end of the program. This will give you an integer value of how long it took to execute. Then use this int wherever you need it (e.g. print it to the console, to a file, etc.)

```
Date startDate = new Date();
//Run the rest of the program
Date endDate = new Date();
int msElapsedTime = startDate.getTime() - endDate.getTime();
```

If you do not need to do anything in the Java program related to the results of your query, you can keep this pretty darn simple using `runtime.exec()` to have mysql execute the queries. The only major drawback here is that you can't print out the resulting number of rows affected:

```
Date startDate = new Date();
runtime.exec("mysql db_name < /home/liova/download/tpch/queries/Q1.sql");
Date endDate = new Date();
int msElapsedTime = startDate.getTime() - endDate.getTime();
```

If you actually need to do something with the results, then `runtime.exec()` will not suffice for you. Read on...

To read the SQL source, just read it as a text file. It will be easiest if you have each line of the SQL as a separate SQL query, since otherwise you will have to do some parsing and adjustment. Here is an example of reading a file one line at a time.

To run the SQL, use JDBC. Here's a tutorial on it. Items 1 through 5 will detail everything you should need for running the sql and using the results (from establishing your sql connection to running the query to processing the resultSet object that comes back). If any of these steps cause you trouble, your best bet is to ask a separate question tailored to the specific problem you are having in the process.

How Do I record the execution time of running sql statements that are stored in files?

Solution

Problem