How to write this query for better performance in Sql Server? Removing SubString rows

Question

How to write this query for better performance?

SELECT T1.flow
FROM #allflows T1 INNER JOIN
     #allflows T2
     ON t2.flow like t1.flow + '%'

Answer 1

I made my own representative '#allflows' by querying a sample customer table, 30,000 rows

here's what I got

Declare @s as datetime = getdate();


CREATE NONCLUSTERED INDEX ix_tempNCIndexAft ON #allflows (flow);

SELECT 'make index', DATEDIff(second,@s, getdate())

SET @s = getdate();

SELECT t1.flow,COUNT(DISTINCT t2.flow) cnt INTO #realflows FROM #allflows t1 
                INNER JOIN #allflows t2 ON t2.flow like t1.flow+ '%' GROUP BY t1.flow HAVING COUNT(DISTINCT t2.flow) = 1;


SELECT 'your query', DATEDIff(second,@s, getdate())

    select * from #realflows ;