{"id":4864,"date":"2023-02-02T22:20:37","date_gmt":"2023-02-02T21:20:37","guid":{"rendered":"https:\/\/launix.de\/launix\/?p=4864"},"modified":"2023-03-02T10:26:44","modified_gmt":"2023-03-02T09:26:44","slug":"how-to-benchmark-a-sql-database","status":"publish","type":"post","link":"https:\/\/launix.de\/launix\/how-to-benchmark-a-sql-database\/","title":{"rendered":"How to benchmark a SQL database"},"content":{"rendered":"\n<p>To compare databases against each other, a benchmark like <a href=\"https:\/\/www.tpc.org\/\" target=\"_blank\" aria-label=\"TPC-H (opens in a new tab)\" rel=\"noreferrer noopener\" class=\"ek-link\">TPC-H<\/a> is used.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>At first, you have to download the TPC-H benchmark from tpc.org<\/p>\n\n\n\n<p>Then you will adjust the makefile (copied from makefile.suite)<\/p>\n\n\n\n<pre><font color=\"#33C7DE\">################<\/font>\n<font color=\"#33C7DE\">## CHANGE NAME OF ANSI COMPILER HERE<\/font>\n<font color=\"#33C7DE\">################<\/font>\n<font color=\"#33C7DE\"><b>CC      <\/b><\/font>= gcc\n<font color=\"#33C7DE\"># Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)<\/font>\n<font color=\"#33C7DE\">#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE<\/font>\n<font color=\"#33C7DE\"># Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS, <\/font>\n<font color=\"#33C7DE\">#                                  SGI, SUN, U2200, VMS, LINUX, WIN32 <\/font>\n<font color=\"#33C7DE\"># Current values for WORKLOAD are:  TPCH<\/font>\n<font color=\"#33C7DE\"><b>DATABASE<\/b><\/font>= ORACLE\n<font color=\"#33C7DE\"><b>MACHINE <\/b><\/font>= LINUX\n<font color=\"#33C7DE\"><b>WORKLOAD <\/b><\/font>= TPCH\n<\/pre>\n\n\n\n<p>After hitting make, you will run dbgen:<\/p>\n\n\n\n<pre><font color=\"#26A269\"><b>carli@launix-MS-7C51<\/b><\/font>:<font color=\"#12488B\"><b>~\/projekte\/memcp\/TPC-H V3.0.1\/dbgen<\/b><\/font>$ .\/dbgen -vf\nTPC-H Population Generator (Version 3.0.0)\nCopyright Transaction Processing Performance Council 1994 - 2010\nGenerating data for suppliers table\/\nPreloading text ... 100%\ndone.\nGenerating data for customers tabledone.\nGenerating data for orders\/lineitem tablesdone.\nGenerating data for part\/partsupplier tablesdone.\nGenerating data for nation tabledone.\nGenerating data for region tabledone.\n<\/pre>\n\n\n\n<p>Then create some test queries:<\/p>\n\n\n\n<pre><font color=\"#26A269\"><b>carli@launix-MS-7C51<\/b><\/font>:<font color=\"#12488B\"><b>~\/projekte\/memcp\/TPC-H V3.0.1\/dbgen<\/b><\/font>$ DSS_QUERY=queries .\/qgen &gt; queries.sql\n<\/pre>\n\n\n\n<p>Now you use the following files:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>dss.ddl to create the tables<\/li>\n\n\n\n<li>*.tbl to import the tables into your database<\/li>\n\n\n\n<li>queries.sql to run the queries (and benchmark them)<\/li>\n<\/ul>\n\n\n\n<p><strong>That&#8217;s it for today. Before I can start benchmarking memcp, I will have to complete the SQL parser first.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>To compare databases against each other, a benchmark like TPC-H is used.<\/p>\n","protected":false},"author":2,"featured_media":4867,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_editorskit_title_hidden":false,"_editorskit_reading_time":1,"_editorskit_is_block_options_detached":false,"_editorskit_block_options_position":"{}","_uag_custom_page_level_css":"","footnotes":""},"categories":[129,128],"tags":[],"class_list":["post-4864","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-memcp","category-programming","single-item"],"featured_image_urls_v2":{"full":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",1920,639,false],"thumbnail":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-150x150.jpg",150,150,true],"medium":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-300x100.jpg",300,100,true],"medium_large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-768x256.jpg",751,250,true],"large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-1024x341.jpg",751,250,true],"1536x1536":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-1536x511.jpg",1536,511,true],"2048x2048":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",1920,639,false],"trp-custom-language-flag":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",18,6,false],"xs-thumb":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-64x64.jpg",64,64,true],"appku-shop-single":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",620,206,false]},"post_excerpt_stackable_v2":"<p>To compare databases against each other, a benchmark like TPC-H is used. At first, you have to download the TPC-H benchmark from tpc.org Then you will adjust the makefile (copied from makefile.suite) ################ ## CHANGE NAME OF ANSI COMPILER HERE ################ CC = gcc # Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata) # SQLSERVER, SYBASE, ORACLE, VECTORWISE # Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, # SGI, SUN, U2200, VMS, LINUX, WIN32 # Current values for WORKLOAD are: TPCH DATABASE= ORACLE MACHINE = LINUX WORKLOAD = TPCH After hitting make, you will run dbgen:&hellip;<\/p>\n","category_list_v2":"<a href=\"https:\/\/launix.de\/launix\/category\/memcp\/\" rel=\"category tag\">MemCP<\/a>, <a href=\"https:\/\/launix.de\/launix\/category\/programming\/\" rel=\"category tag\">Programming<\/a>","author_info_v2":{"name":"Carl-Philip H\u00e4nsch","url":"https:\/\/launix.de\/launix\/author\/carli\/"},"comments_num_v2":"0 comments","uagb_featured_image_src":{"full":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",1920,639,false],"thumbnail":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-150x150.jpg",150,150,true],"medium":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-300x100.jpg",300,100,true],"medium_large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-768x256.jpg",751,250,true],"large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-1024x341.jpg",751,250,true],"1536x1536":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-1536x511.jpg",1536,511,true],"2048x2048":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",1920,639,false],"trp-custom-language-flag":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",18,6,false],"xs-thumb":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920-64x64.jpg",64,64,true],"appku-shop-single":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2023\/02\/database-2394312_1920.jpg",620,206,false]},"uagb_author_info":{"display_name":"Carl-Philip H\u00e4nsch","author_link":"https:\/\/launix.de\/launix\/author\/carli\/"},"uagb_comment_info":0,"uagb_excerpt":"To compare databases against each other, a benchmark like TPC-H is used.","_links":{"self":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts\/4864","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/comments?post=4864"}],"version-history":[{"count":3,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts\/4864\/revisions"}],"predecessor-version":[{"id":4868,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/posts\/4864\/revisions\/4868"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/media\/4867"}],"wp:attachment":[{"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/media?parent=4864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/categories?post=4864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/launix.de\/launix\/wp-json\/wp\/v2\/tags?post=4864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}