sql-query中的Java Hibernate常量

Below is the original content of a hibernate sql query I have:

下面是我所拥有的hibernate sql查询的原始内容:

<sql-query name="countryOfOrigin-limit-country-city-location">
<return alias="rb" class="RecentBooking"/>
    SELECT 
        bb.reserv_num as {rb.reservNum}, 
        bb.origin as {rb.countryOfOrigin}, 
        bb.pick_up_loc as {rb.locationId}, 
        bb.first_date as {rb.bookingDate}, 
        bb.pick_up_time as {rb.pickUpDate}, 
        bb.drop_off_time as {rb.dropOffDate},  
        bb.car_price as {rb.carPrice}, 
        bb.discount as {rb.discount}, 
        bb.exchange_rate as {rb.exchangeRate},
        SUBSTRING(a.internal_class,1,1)as {rb.carClass},
        a.car_type as {rb.carType}, 
        bb.vehicle_type as {rb.vehicleType}, 
        s.name as {rb.supplier}, 
        vv.country as {rb.country}, 
        vv.city as {rb.city}, 
        vv.location as {rb.location}, 
        bb.cur as {rb.currency}, 
        a.pics as {rb.carImage}
    FROM (SELECT * FROM b  WHERE first_date > DATE(NOW()-1)AS bb  
    JOIN a a ON a.id = bb.car_id
    JOIN d  d ON d.id = bb.pick_up_loc
    JOIN supplier s ON s.id = d.supplier_id
    JOIN v_location_trans vv ON vv.location_id = d.location_id
    AND vv.lang=33
    AND vv.country = :country
    AND vv.city = :city
    AND vv.location = :location
    AND bb.origin = :countryOfOrigin
    ORDER BY bb.id DESC
    LIMIT :limit
</sql-query>

The sql runs fast enough, but as you might have noticed there is a big, the NOW()-1 should be NOW() - INTERVAL 1 DAY.

sql运行得足够快,但你可能已经注意到有一个很大的,NOW() - 1应该是NOW() - INTERVAL 1 DAY。

I modified the nested select statement as follows:

我修改了嵌套的select语句,如下所示:

FROM (SELECT * FROM b WHERE first_date > DATE(NOW() - INTERVAL 1 DAY)) AS bb

The fix works fine in terms of results, however the query slows down massively (pretty much almost instant up to a few seconds).

修复程序在结果方面工作正常,但是查询速度大幅减慢(几乎是瞬间几秒钟)。

I believe this is because the DATE(NOW() - INTERVAL 1 DAY) is being evaluated a large number of times (we have a very large data set). How would I go about seperating this calculation so that it only occurs once, without having to pass it in from java code like the country, city, limit, etc?

我相信这是因为DATE(NOW() - INTERVAL 1 DAY)被评估了很多次(我们有一个非常大的数据集)。我将如何分离这个计算,使它只发生一次,而不必从国家,城市,限制等Java代码中传递它?

I have very little experience with hibernate or SQL, and I have tried seperating the calculation out into a variable before the select statement, which isnt allowed. I have also tried making small changes to the SQL query but I keep getting runtime errors, presumably due to invalid SQL.

我对hibernate或SQL的经验很少,我尝试在select语句之前将计算分成一个变量,这是不允许的。我也尝试对SQL查询进行小的更改,但我不断收到运行时错误,可能是由于SQL无效。

How would I go about doing this?

我该怎么做呢?

Thanks.

1 个解决方案

#1

You are creating a new temp table bb in your query on the whole record.

您正在查询中在整个记录上创建一个新的临时表bb。

I would suggest that you should filter out the result in creating the temp table.

我建议您在创建临时表时过滤掉结果。

for example if you put the AND clause while creating temp table

例如,如果在创建临时表时放置AND子句

AND bb.origin = :countryOfOrigin

AND bb.origin =:countryOfOrigin

Like:

FROM (SELECT * FROM b  WHERE first_date > (NOW() - INTERVAL 1 DAY) AND origin = :countryOfOrigin)AS bb

Also you can put the limit it it as well.

你也可以限制它。

FROM (SELECT * FROM b  WHERE first_date > (NOW() - INTERVAL 1 DAY) AND origin = :countryOfOrigin LIMIT :limit)AS bb

EDITED -- New Answer.

编辑 - 新答案。

I thing you do not need to change the INTERVAL 1 DAY, you stick to the previous query but replace the 1 with 86400000 which is equal to INTERVAL 1 DAY (24*60*60*1000)

我不需要更改INTERVAL 1天,你坚持上一个查询,但用86400000替换1,等于INTERVAL 1天(24 * 60 * 60 * 1000)

new query would look like

新查询看起来像

FROM (SELECT * FROM b WHERE first_date > (NOW() - 86400000) 
               AND origin = :countryOfOrigin LIMIT :limit) AS bb

#1