 |
|
| Computers Forum Index » Computer - Databases - Ingres » [Info-Ingres] char() + char() AND NOT IN vs outer... |
|
Page 1 of 1 |
|
| Author |
Message |
| Martin Bowes... |
 Posted: Thu Nov 05, 2009 7:16 pm |
|
|
|
Guest
|
Hi All,
The following query runs in 7 hours:
insert into lab_result(
sample_id, test_id, result, when_analysed, status, participant_id,
visit_id
)
select sample_id, test_id, result, when_analysed, status,
participant_id, visit_id
from session.temp_lab_result
where char(sample_id) +'-' +char(test_id) not in(select char(sample_id)
+'-' +char(test_id)from lab_result)
And this runs in 7 seconds...
insert into lab_result(
sample_id, test_id, result, when_analysed, status,
participant_id, visit_id
)
select t.sample_id, t.test_id, t.result, t.when_analysed, t.status,
t.participant_id, t.visit_id
from session.temp_lab_result t left outer join lab_result l on
t.sample_id=l.sample_id and t.test_id=l.test_id
where l.sample_id is null and l.test_id is null
I'm sure the resulting rows are the same or have I done something
massively bone headed?
Martin Bowes |
|
|
| Back to top |
|
|
|
| Karl Schendel... |
| Posted: Thu Nov 05, 2009 7:27 pm |
|
|
|
Guest
|
On Nov 5, 2009, at 9:16 AM, Martin Bowes wrote:
Quote: Hi All,
The following query runs in 7 hours:
insert into lab_result(
sample_id, test_id, result, when_analysed, status,
participant_id, visit_id
)
select sample_id, test_id, result, when_analysed, status,
participant_id, visit_id
from session.temp_lab_result
where char(sample_id) +'-' +char(test_id) not in(select char
(sample_id) +'-' +char(test_id)from lab_result)
Are any of these columns nullable? Sounds like you're getting an SE-
join,
and the most likely explanation is a nullable column gumming up the
works.
I don't know if the char+char expression would inhibit the notin to
outer-join
transform, but at least I would expect the old style quel-like
flattening
with ANY. It can't even do that if the inner is nullable, though.
Karl |
|
|
| Back to top |
|
|
|
| Martin Bowes... |
| Posted: Thu Nov 05, 2009 7:54 pm |
|
|
|
Guest
|
Hi Karl,
Bingo on nulls and SE join!
The session temporary was created with nullable columns, the main table
uses non-nullable columns.
Once I corrected the session temp table to use non nullable columns the
original query with char() and not in was morpehed into right join/Hash
join and completed in much the same time as the specific ouer join case.
Marty
-----Original Message-----
From: info-ingres-bounces at (no spam) kettleriverconsulting.com
[mailto:info-ingres-bounces at (no spam) kettleriverconsulting.com] On Behalf Of Karl
Schendel
Sent: 05 November 2009 14:28
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] char() + char() AND NOT IN vs outer join
On Nov 5, 2009, at 9:16 AM, Martin Bowes wrote:
Quote: Hi All,
The following query runs in 7 hours:
insert into lab_result(
sample_id, test_id, result, when_analysed, status,
participant_id, visit_id
)
select sample_id, test_id, result, when_analysed, status,
participant_id, visit_id
from session.temp_lab_result
where char(sample_id) +'-' +char(test_id) not in(select char
(sample_id) +'-' +char(test_id)from lab_result)
Are any of these columns nullable? Sounds like you're getting an SE-
join,
and the most likely explanation is a nullable column gumming up the
works.
I don't know if the char+char expression would inhibit the notin to
outer-join
transform, but at least I would expect the old style quel-like
flattening
with ANY. It can't even do that if the inner is nullable, though.
Karl
_______________________________________________
Info-Ingres mailing list
Info-Ingres at (no spam) kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Tue Dec 01, 2009 6:55 pm
|
|