如何记录存储在文件中的运行sql语句的执行时间? [英] How Do I record the execution time of running sql statements that are stored in files?

问题描述

我正在做与TPC-H有关的事情.

我在几个文件中都有sql语句.我需要执行它们并记录执行时间，如下所示:

13 rows in set (0.00 sec)

我还需要结果集以查看是否获得正确的结果.
我的平台是linux(centOS).

如果我手动执行此操作，则将在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;

解决方案

要查找执行时间，您应该在程序开始时初始化一个日期对象，然后将其与另一个日期进行比较程序末尾的对象.这将为您提供执行所需时间的整数值.然后在需要的地方使用此int(例如，将其打印到控制台，文件等)

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

如果您不需要在Java程序中执行与查询结果相关的任何操作，则可以使用runtime.exec()使这个漂亮的代码简单一些，以使mysql执行查询.唯一的主要缺点是您无法打印出受影响的结果行数:

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();

如果您实际上需要对结果做些事情，那么runtime.exec()将不足以满足您的需求.继续阅读...

要读取SQL源，只需将其阅读为文本文件即可.如果将SQL的每一行都作为一个单独的SQL查询，这将是最简单的，因为否则，您将不得不进行一些解析和调整.这是示例，一次读取一行./p>

要运行SQL ，请使用JDBC.这是教程.第1到5项将详细介绍运行sql和使用结果所需的一切(从建立sql连接到运行查询再到处理返回的resultSet对象).如果这些步骤中的任何一个给您带来麻烦，那么最好的办法是问一个针对您在处理过程中遇到的特定问题而量身定制的单独问题.

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;

解决方案

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.

这篇关于如何记录存储在文件中的运行sql语句的执行时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！