Multiset query select from (select statement)

[rake...@yahoo.com]

Hi All,

I am using IDS 9.4 . Using Multiset i have developed a query which is
generating the desired output but takes around 3 minutes to generate a
single record . I have updated the statistics but no improvement.
Posting the query on the user group . Please suggest me on
the same . Thanks & Regards.

select a.NAME , a.REF , a.RUNS , a.FOURS , a.SIXES
from TABLE (MULTISET
(select p.first_name NAME ,
bout.batsman_out_ref_id as REF,
bout.batsman_id as OUTBATSMAN_ID ,
bat.batsman_id as BATSMAN_ID ,
td.team_details_id as TID,
bat.team_details_id as BTID,
td.person_id as TPID,
p.person_id as PID,
bat.runs as RUNS,
bat.FOURS as FOURS,
bat.SIXES as SIXES
from person p, batsman_out bout , match m , reference_item r ,
team_details td , batsman bat )) a
where
a.BATSMAN_ID = a.OUTBATSMAN_ID and a.BTID = a.TID and a.TPID = a.PID
group by 1,2,3,4,5

[Richard Harnden]

rake...@yahoo.com wrote:
> Hi All,
>
> I am using IDS 9.4 . Using Multiset i have developed a query which is
> generating the desired output but takes around 3 minutes to generate a
> single record . I have updated the statistics but no improvement.
> Posting the query on the user group . Please suggest me on
> the same . Thanks & Regards.
>
> select a.NAME , a.REF , a.RUNS , a.FOURS , a.SIXES
> from TABLE (MULTISET
> (select p.first_name NAME ,
> bout.batsman_out_ref_id as REF,
> bout.batsman_id as OUTBATSMAN_ID ,
> bat.batsman_id as BATSMAN_ID ,
> td.team_details_id as TID,
> bat.team_details_id as BTID,
> td.person_id as TPID,
> p.person_id as PID,
> bat.runs as RUNS,
> bat.FOURS as FOURS,
> bat.SIXES as SIXES
> from person p, batsman_out bout , match m , reference_item r ,
> team_details td , batsman bat )) a

Now 'a' contains the cartesian product of person, batsman_out, match,
reference_item, team_details and batsman.

This likely contains thousands, if not millions, of rows.

> where
> a.BATSMAN_ID = a.OUTBATSMAN_ID and a.BTID = a.TID and a.TPID = a.PID

Joins are a good thing, but his should be part of the inline view.

You haven't joined all the tables together, either.

> group by 1,2,3,4,5
>

This is only to get rid of all the duplicates.

You're lucky it only takes three minutes. You either have hardly any
data, or you've got a super quick computer.

Aim for something more like this...

SELECT first_name,
batsman_out_ref_id,
runs,
fours,
sixes
FROM batsman,
batsman_out,
person
WHERE batsman.batsman_id = batsman_out.batsman_id
AND batsman.batsman_id = person.person_id

--
rh

[rake...@yahoo.com]

Hi Richard,

Thanks for your reply . I had debugged the query & resolved .
Now i am able to get the output of the query in 3 seconds.
Joins were not proper on the query.

Thanks for all your help.

Regards
Rakesh.

[macgillivary]

Thanks for the double posting rakesh. Keeping it to one thread would
be nice.

[rake...@yahoo.com]

Yep i will .

[bozon]

Even though you have solved this I just want to comment on this.

You should avoid using distinct unless you have to. (the goup by every
column is essentially a distinct by another name) This is because
distinct's hide f*ck ups like cross products, which your query cleary
had. So, don't do it because if you are getting many multiple records
then you probably don't have the correct query. Also, when you see
someone else do it assume they don't know what they are doing, until
proven wrong (I do this and I very seldom have to say "you know you are
right this query requires a distinct to work properly.) I have seen
more bad SQL hidden with a distinct. I'll get an email from a developer
with "I got the query to work but it returned multiple rows so I did a
distinct and it was fine. Can you help me make it run faster?"
Invariably it "works" but isn't correct because of the missing joins.
(sorry to be so tough here.)

Secondly, I can't see any real reason for the inline view so why
bother? I haven't looked at the sql in detail but it looks like a
simple join returning rows. And I am the biggest fan of views you may
ever meet.

Thirdly, you may be including tables that you don't need in the query.
Always, check to make sure you aren't including any tables that you
thought you needed but you don't actually use. Any table you don't have
to join by that you remove increases performance.

You are only returning columns from the following tables:

person
batsman_out
batsman

So you only need these tables and any tables that relate these tables
to the other tables. So I am not sure that you need the match,
reference_item, or team_details. But I can't tell because you didn't
include a schema and detail about what you would like the query to
accomplish, which you should do when posting a SQL.

Finally, I don't think that you want to do your joins outside of your
inline view, which you didn't need in the first place, but if you did
need it you would want to join as part of the view. I think you are
telling informix to cross product all of these tables and I'll tell you
what I want to do with it later. Informix is probably smart enough to
delay the cross product but it isn't really a correct view because the
view contains invalid entries, entries that don't really make sense
because you don't have any relationships define between the tables in
the view because it is a cross product of all of the information.

<rant off>

Sorry, I hope this is helpful. Also, there are many good books on SQL.
I like Joe Celko's SQL for smarties.

[bozon]

Nice Job, I hadn't read your post before I said basically the same
thing.