Edit Question - KnowledgeHub

Title Body

I have a table which is a link table from objects in my SQL Server 2012 database, ( annonsid, annonsid2 ). This table is used to create chains of triangle or even rectangles to see who can swap with who. This is the query I use on the table Matching_IDs which has 1,5 million rows in it, producing 14 million possible chains using this query: SELECT COUNT(*) FROM Matching_IDs AS m INNER JOIN Matching_IDs AS m2 ON m.annonsid2 = m2.annonsid INNER JOIN Matching_IDs AS m3 ON m2.annonsid2 = m3.annonsid AND m.annonsid = m3.annonsid2 I must improve performance to take maybe 1 second or less, Is there a faster way to do this? The query takes about 1 minute on my computer. I normally use a WHERE m.annonsid=x , but it takes just the same amount of time, cause it has to go through all possible combinations anyway. Update: the latest query plan |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[globalagg1011],0))) |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010]))) |--Parallelism(Gather Streams) |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*))) |--Hash Match(Inner Join, HASH:([m2].[annonsid2], [m2].[annonsid])=([m3].[annonsid], [m].[annonsid2]), RESIDUAL:([MyDatabase].[dbo].[Matching_IDs].[annonsid2] as [m2].[annonsid2]=[MyDatabase].[dbo].[Matching_IDs].[annonsid] as [m3].[annonsid] AND [MyDatabase].[dbo].[Matching_IDs].[annonsid2] as [m].[annonsid2]=[MyDatabase].[dbo].[Matching_IDs].[annonsid] as [m2].[annonsid])) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([m2].[annonsid2], [m2].[annonsid])) | |--Index Scan(OBJECT:([MyDatabase].[dbo].[Matching_IDs].[NonClusteredIndex-20121229-133207] AS [m2])) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([m3].[annonsid],

Tags (comma-separated) Cancel