Hello,
I want to generate triangles from points and optional relations between them. Not all points form triangles, but many of them do.
In the initial structure, I've got a database with the following tables:
Nodes(id, value)
Relations(id, nodeA, nodeB, value)
Triangles(id, relation1_id, relation2_id, relation3_id)
In order to generate triangles from both nodes and relations table, I've used the following query:
INSERT INTO Triangles
SELECT t1.id, t2.id , t3.id,
FROM Relations t1, Relations t2, Relations t3
WHERE t1.id < t2.id AND t3.id > t1.id AND
(
t1.nodeA = t2.nodeA
AND (t3.nodeA = t1.nodeB AND t3.nodeB = t2.nodeB
OR t3.nodeA = t2.nodeB AND t3.nodeB = t1.nodeB)
OR
t1.nodeA = t2.nodeB
AND (t3.nodeA = t1.nodeB AND t3.nodeB = t2.nodeA
OR t3.nodeA = t2.nodeA AND t3.nodeB = t1.nodeB)
)
It's working perfectly on small sized data. (~< 50 points)
In some cases however, I've got around 100 points all related to each other which leads to thousands of relations. So when the expected number of triangles is in the hundreds of thousands, or even in the millions, the query might take several hours.
My main problem is not in the select query, while I see it execute in Management Studio, the returned results slow. I received around 2000 rows per minute, which is not acceptable for my case.
As a matter of fact, the size of operations is being added up exponentionally and that is terribly affecting the performance.
I've tried doing it as a LINQ to object from my code, but the performance was even worse.
I've also tried using SqlBulkCopy on a reader from C# on the result, also with no luck.
So the question is... Any ideas or workarounds?
