sql, query optimisation with and inner join?

Question

I'm trying to optimise my query, it has an inner join and coalesce.

The join table, is simple a table with one field of integer, I've added a unique key.

For my where clause I've created a key for the three fields.

But when I look at the plan it still says it's using a table scan.

Where am I going wrong ?

Here's my query

select date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) as due

from billsndeposits a 

inner join util_nums b on date(a.startdate, '+'||(b.n*a.interval)||' 
     '||a.intervaltype) <= coalesce(a.enddate, date('2013-02-26')) 

where not (intervaltype = 'once' or interval = 0) and factid = 1 

order by due, pid;

Please supply the name of the DB your are using. There might be other options you can consider. — Philip Fourie
– Philip Fourie, Commented Feb 26, 2012 at 11:47
date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) does this work? — Isaac
– Isaac, Commented Feb 26, 2012 at 11:55

Mark Rotteveel · Accepted Answer · 2012-02-26 11:37:52Z

3

Most likely your JOIN expression cannot use any index and it is calculated by doing a NATURAL scan and calculate date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) for every row.

BTW: That is a really weird join condition in itself. I suggest you find a better way to join billsndeposits to util_nums (if that is actually needed).

answered Feb 26, 2012 at 11:37

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jules Over a year ago

Well, I'm storing days, months etc in the intervaltype field and adding a number produced by interval and evaluating that to produce a custom date recurrence. The util_nums is purely to provide recurrences in the future. e.g. '+ 7 months'

Mark Rotteveel Over a year ago

@Jules I can't really wrap my head around what you are trying to achieve and what the structue and content of both tables is, maybe you can update your question by including the table structure, and example content plus a description of what you want to achieve.

Isaac · Accepted Answer · 2012-02-26 13:02:47Z

I think I understand what you are trying to achieve. But this kind of join is a recipe for slow performance. Even if you remove date computations and the coalesce (i.e. compare one date against another), it will still be slow (compared to integer joins) even with an index. And because you are creating new dates on the fly you cannot index them.

I suggest creating a temp table with 2 columns (1) pid (or whatever id you use in billsndeposits) and (2) recurrence_dt

populate the new table using this query:

INSERT INTO TEMP
SELECT PID, date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype)
FROM billsndeposits a, util_numbs b;

Then create an index on recurrence_dt columns and runstats. Now your select statement can look like this:

SELECT recurrence_dt
FROM temp t, billsndeposits a
WHERE t.pid = a.pid
  AND recurrence_dt <= coalesce(a.enddate, date('2013-02-26'))

you can add a exp_ts on this new table, and expire temporary data afterwards.

I know this adds more work to your original query, but this is a guaranteed performance improvement, and should fit naturally in a script that runs frequently.

Regards,

Edit

Another thing I would do, is make enddate default value = date('2013-02-26'), unless it will affect other code and/or does not make business sense. This way you don't have to work with coalesce.

Collectives™ on Stack Overflow

sql, query optimisation with and inner join?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related