Feature Requests item #2816059, was opened at 2009-07-03 00:48
Message generated for change (Settings changed) made by nielsnes
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=2816059&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
>Category: None
>Group: None
Status: Open
Priority: 3
Private: Yes
Submitted By: Stefan de Konink (skinkie)
Assigned to: Niels Nes (nielsnes)
Summary: SQL: Performance slowdown on or
Initial Comment:
I received a take down notice of the KVK, until that is resolved I have to keep
this academic...
With a new data provider aka real partner without legal battles; new bugs arise:
CREATE TABLE "sys"."faillissementen" (
"kvk" bigint,
"bedrijfsnaam" varchar(255),
"plaats" varchar(32),
"rechtbank" varchar(64),
"rolnummer" varchar(16),
"status" varchar(16),
"datum" date
);
CREATE TABLE "sys"."kvk" (
"kvk" bigint,
"bedrijfsnaam" varchar(255),
"adres" varchar(64),
"postcode" varchar(6),
"plaats" varchar(32),
"type" varchar(16)
);
select * from faillissementen, kvk where faillissementen.kvk = kvk.kvk;
0 tuples
Timer 25.549 msec 0 rows
sql>select * from faillissementen, kvk where (faillissementen.kvk*10000) =
kvk.kvk;
22 tuples
Timer 44.483 msec 22 rows
So far fine; now... for some reason nobody really cares to normalise their
numbers so I want to join the two.
select * from faillissementen, kvk where (faillissementen.kvk*10000) = kvk.kvk
or faillissementen.kvk = kvk.kvk;
Query gets totally out of bounds.
Now you may ask; so what if you do it like first year students?
select * from faillissementen, kvk where (faillissementen.kvk*10000) = kvk.kvk
union select * from faillissementen, kvk where faillissementen.kvk = kvk.kvk;
20 tuples
Timer 57.804 msec 20 rows
Now you see that there are 20 results instead of 22. Basically what misses are
the multiple results of the kvk side matching the number; The most trivial
example:
sql>select kvk, adres from kvk where kvk = 10926910000;
+-------------+-------------------------+
| kvk | adres |
+=============+=========================+
| 10926910000 | Anthonie Fokkerstraat 5 |
| 10926910000 | Anthonie Fokkerstraat 5 |
+-------------+-------------------------+
sql>select kvk.kvk, kvk.adres from faillissementen, kvk where
(faillissementen.kvk*10000) = kvk.kvk union select kvk.kvk, kvk.adres from
faillissementen, kvk where faillissementen.kvk = kvk.kvk;
+--------------+---------------------------+
| kvk | adres |
+==============+===========================+
| 10926910000 | Anthonie Fokkerstraat 5 |
| 60680140000 | Spanjaardslaan 25 -1 |
sql>select kvk.kvk, kvk.adres from faillissementen, kvk where
(faillissementen.kvk*10000) = kvk.kvk;
+--------------+---------------------------+
| kvk | adres |
+==============+===========================+
| 10926910000 | Anthonie Fokkerstraat 5 |
| 10926910000 | Anthonie Fokkerstraat 5 |
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-08-14 11:58
Message:
tagged subject
----------------------------------------------------------------------
Comment By: Martin Kersten (mlkersten)
Date: 2009-08-06 13:45
Message:
stacked for possible future improvements of the query optimizer
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2009-07-03 11:40
Message:
As Niels suggested, union all solves the last part; didn't know union had a
build in distinct.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=2816059&group_id=56967
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
