Hi All,

I've moved an INSERT / SELECT / UNION query from DBISAM which worked
very snappy and fast into EDB.

I got it working, but couldn't work out why it was so slow (a few secs
in DBISAM to 15 + secs in EDB).

I tried removing the CAST TO DATE statements and it worked fast, of
course since I was CASTING from a TIMSTAMP to a DATE field, I really
need to keep the CASTING otherwise the results are not right.

Does anyone have any ideas?

INSERT INTO forecast (forecastdate, stay_overs, room_arrivals,
room_departures, rooms_occupied, guests)

Select F.forecastdate, 0 stay_overs, 0 room_arrivals, 0 room_departures,
Coalesce(Count(R.guestID),0) rooms_occupied, SUM(adults + children) guests
From forecastdates F
inner join reservation R on CAST(R.ar_time AS DATE) = F.forecastdate OR
(ar_time < F.forecastdate AND CAST(R.dep_time AS DATE) >  F.forecastdate)
Group by forecastdate

union all

Select F.forecastdate, Coalesce(Count(R.guestID),0) stay_overs, 0
room_arrivals, 0 room_departures, 0 rooms_occupied, 0 guests
From forecastdates F
left outer join reservation R on CAST(R.ar_time AS DATE) <
F.forecastdate AND CAST(R.dep_time AS DATE) > F.forecastdate
Group by forecastdate

union all

Select F.forecastdate, 0 stay_overs, Coalesce(Count(R.guestID),0)
room_arrivals, 0 room_departures, 0 rooms_occupied, 0 guests
From forecastdates F
inner join reservation R on CAST(R.ar_time AS DATE) = F.forecastdate
Group by forecastdate

union all

Select F.forecastdate, 0 stay_overs, 0 room_arrivals,
Coalesce(Count(R.guestID),0) room_departures, 0 rooms_occupied, 0 guests
From forecastdates F
inner join reservation R on CAST(R.dep_time AS DATE) = F.forecastdate
Group by forecastdate


Thanks,

Phil.

OK I found there are MORE records in the newly moved dataset and along
with the CAST this was slowing it down...

To get around it inserted just the records I needed to manipulate into a
temp table then ran the INSERT SELECT / UNION and its nice and fast.

Out of curiosity, if there is an index on r.ar_time, will EDB still be able to use the index if you use a Cast() function on it, as shown below?

>>inner join reservation R on CAST(R.ar_time AS DATE) = F.forecastdate

I personally would break ar_time into two fields, date and time, or have a generated field to create ar_date from ar_time and have an index on ar_date.

Just my 2 cents worth.

Barry

Hi there,

EDB wont use an index on R.ar_time if CAST is used, however that might be irrelevant, depending on the table being used as the 'driver table' for the join operation.
If the driver table is R then no index on R is going to be used for the join, but existing indexes on F would be used if appropriate; if not, and the driver table is F, then it would be the other way round.
To know what table has been chosen as the driver table, look into the Execution Plan of the query.

--
Fernando Dias
[Team Elevate]

If in fact the driver table is F, Barry is right and adding a Calculated or Computed column and an index on the date might speed up the query.

--
Fernando Dias
[Team Elevate]

Thanks Barry and Fernando...

Its useful to know the cast effects the index, I will keep that in mind
for future, I guess its obvious thinking about it.

The ar_time is a timestamp and its used more often to check exact
date/time positions than it is a date, this is one of the few reports
where I need to cast, and so for this report I found I could create a
temp table of the reservation records only required for the query as
oppose to having it query on the full 100,000 records and it works in a
split second now.

>Phil wrote:
>Thanks Barry and Fernando...

I'm glad you got it working in under a second.

Just as tip from my MySQL days (it probably applies to EDB as well).

If a function is used on an Indexed column in a Where clause or a Join clause, then that index cannot be used.  So if you have a slow table join or a slow Where Clause, then look at the Execution Plan to make sure the function isn't the culprit.

As Fernando Dias pointed out, the Execution Plan will tell you if it is using an index or not. Sometimes you can get away with it if the function is on the column of driving table. But with MySQL we had no idea how it was going to optimize the table join so any of the tables in the join could be the driving table. I suspect this may be the case with EDB as well.

Barry

Barry,

<< As Fernando Dias pointed out, the Execution Plan will tell you if it is
using an index or not. Sometimes you can get away with it if the function is
on the column of driving table. But with MySQL we had no idea how it was
going to optimize the table join so any of the tables in the join could be
the driving table. I suspect this may be the case with EDB as well. >>

By default, yes.  However, you can use the NOJOINOPTIMIZE clause to tell EDB
to "leave my joins alone".

Tim Young
Elevate Software
www.elevatesoft.com

>"Tim Young [Elevate Software]" wrote:
>By default, yes.  However, you can use the NOJOINOPTIMIZE clause to tell EDB
>to "leave my joins alone".

You've given me a great idea for a bumper sticker: "Leave My Joins Alone".
The only ones that would understand it are SQL'ers.

But I'm sure my car would peak the cops' interest too - like a magnet! LOL

Barry

Barry wrote:

>>You've given me a great idea for a bumper sticker: "Leave My Joins Alone".

I see no one got the joke.
Cops would misread the bumper sticker as "Leave My Joints Alone".

Oh, well. It's a tough crowd out there.

Barry