Yes, INDEX is on Column products_ort. The problem is the index, when he is not set optimized, then the effekt like in the description is there.
Am Montag, 8. Oktober 2012 13:13:24 UTC+2 schrieb Kartweel: > > Have you tried running explain analyze on your query? That might shed > some light. > > Have you got an index on products_ort for the first query? > > http://www.h2database.com/html/grammar.html#explain > > > > On 4/10/2012 1:10 AM, snookerms wrote: > > > > Am Mittwoch, 3. Oktober 2012 15:06:19 UTC+2 schrieb Kartweel: >> >> That does sound a bit drastic!. Would be good if the JIT compiler made >> everything 41 times faster :). >> >> I'm really not sure sorry. I think if you can post a reproducible test >> case then someone will work it out! >> >> Perhaps it might be that the OS is swapping your data to disk, and on >> repeated querying it swaps it back and keeps it in memory so is faster. >> >> What I mean is, if you can post some more details then that should help >> us work it out. >> >> Thanks, Ryan >> >> >> >> On 3/10/2012 8:02 PM, snookerms wrote: >> >> >> >> Am Dienstag, 2. Oktober 2012 14:36:17 UTC+2 schrieb Kartweel: >>> >>> Maybe this is due to the JIT compiler optimising it? >>> >>> On 2/10/2012 6:31 PM, snookerms wrote: >>> > I'm running a SQL-Query the first time it takes 1540ms. The Second >>> > time it take 749ms and the third time 503ms and so on. >>> >>> Thank You. But a Difference of 41 times faster? >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/h2-database/-/YP3SrTKKfvIJ. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> >> >> Here a description of the test case: > > Hardware: Quad-Core AMD, 1.6GHz > 4GB RAM > 10 GB HDD > > Software: Ubuntu Server 12.04 > Openjdk-7-jdk > H2 v.2010.07.13 > > In the database are 24 tables. But only one table has 250.000 datasets > (rows of data). The table structure of this table is like this: > > I'm using MySQL-Mode: > > CREATE TABLE IF NOT EXISTS `products` ( > `products_id` int(11) NOT NULL AUTO_INCREMENT, > `products_model` varchar(64) DEFAULT NULL, > `products_str` varchar(64) NOT NULL, > `products_hausnr` varchar(50) NOT NULL, > `products_plz` varchar(32) NOT NULL, > `products_ort` varchar(64) NOT NULL, > `products_bndl` int(1) NOT NULL, > `products_refnr` varchar(32) NOT NULL, > `products_bj` varchar(32) NOT NULL, > `products_haus` int(11) NOT NULL, > `products_bhaus` int(11) NOT NULL, > `products_wohn` int(11) NOT NULL, > `products_einzelhandel` int(11) NOT NULL, > `products_gastgewerbe` int(11) NOT NULL, > `products_land_forst` int(11) NOT NULL, > `products_rooms` decimal(3,1) NOT NULL, > `products_bathrooms` int(3) NOT NULL, > `products_etagen` int(11) NOT NULL, > `products_wf` decimal(6,2) NOT NULL, > `products_nf` decimal(6,2) NOT NULL, > `products_gf` decimal(6,2) NOT NULL, > `products_beb` int(11) NOT NULL, > `products_ersch` int(11) NOT NULL, > `products_nutz` int(11) NOT NULL, > `products_verma` int(11) NOT NULL, > `products_heiz` int(11) NOT NULL, > `products_ensth` int(11) NOT NULL, > `products_enstw` int(11) NOT NULL, > `products_stat` int(11) NOT NULL, > `products_miet` int(11) NOT NULL, > `products_image` varchar(64) DEFAULT NULL, > `products_image_title` varchar(100) NOT NULL, > `products_price_anfra` varchar(5) NOT NULL DEFAULT 'false', > `products_price` decimal(15,4) NOT NULL, > `products_nebkost` decimal(15,4) NOT NULL, > `products_kaut` decimal(15,4) NOT NULL, > `products_provision` decimal(3,2) NOT NULL, > `products_provision_type` varchar(6) NOT NULL DEFAULT 'normal', > `products_discount_allowed` decimal(3,2) NOT NULL DEFAULT '0.00', > `products_date_added` datetime NOT NULL, > `products_last_modified` datetime DEFAULT NULL, > `products_date_available` datetime DEFAULT NULL, > `products_from` int(11) NOT NULL, > `products_from_id` varchar(32) NOT NULL, > `products_fertig` varchar(5) NOT NULL DEFAULT 'false', > `products_roll` varchar(5) NOT NULL DEFAULT 'false', > `products_gara` varchar(5) NOT NULL DEFAULT 'false', > `products_einl` varchar(5) NOT NULL DEFAULT 'false', > `products_ebk` varchar(5) NOT NULL DEFAULT 'false', > `products_bt` varchar(5) NOT NULL DEFAULT 'false', > `products_gmb` varchar(5) NOT NULL DEFAULT 'false', > `products_paz` varchar(5) NOT NULL DEFAULT 'false', > `products_bw` varchar(5) NOT NULL DEFAULT 'false', > `products_kell` varchar(5) NOT NULL DEFAULT 'false', > `products_wbs` varchar(5) NOT NULL DEFAULT 'false', > `products_kurzbeb` varchar(5) NOT NULL DEFAULT 'false', > `products_noprov` varchar(5) NOT NULL DEFAULT 'false', > `products_bgn` varchar(5) NOT NULL DEFAULT 'false', > `products_share` varchar(5) NOT NULL DEFAULT 'false', > `products_afa` varchar(5) NOT NULL DEFAULT 'false', > `products_bezfrei` varchar(9) NOT NULL DEFAULT 'value', > `products_bezfrei_value` varchar(255) NOT NULL, > `pay_ad` varchar(5) NOT NULL DEFAULT 'false', > `payed` varchar(5) NOT NULL DEFAULT 'false', > `ad_type` varchar(7) NOT NULL DEFAULT 'partner', > `valid_date` datetime DEFAULT NULL, > `extended` varchar(5) NOT NULL DEFAULT 'false', > `extension_date` datetime DEFAULT NULL, > `openimmo_id` varchar(200) NOT NULL, > PRIMARY KEY (`products_id`), > KEY `idx_products_date_added` (`products_date_added`) > ) ; > > And I run the following SQL-Queries: > > First one: SELECT * FROM products WHERE products_ort = 'Berlin' > > second: SELECT * FROM products AS a, products_wohn AS b WHERE > a.products_ort = 'Dresden' > > AND a.products_wohn = b.products_wohn_id > > AND a.products_rooms = 2.0 > > AND b.products_wohn_name ='Erdgeschoss' > > First query they need first time = 1543 ms, second = 630, third= 497, and > so on > > Second Query first run =173 ms, second = 122 ms, third= 121 > > I use Apache Jmeter for Benchmarks with JDBC. The queries run in a row, > without a break. And five times in a rows without a break. > > If I run only the second Query without the first one, it begins with 123 > ms and after that ist needs only 6 ms, 7 ms and so on. > > > > > > > > > > > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/sT7eBsUoyIoJ. > To post to this group, send email to [email protected]<javascript:> > . > To unsubscribe from this group, send email to > [email protected] <javascript:>. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/UDp72-lbRA4J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
