SQL (sybase) query using TOP N performs very badly when inserted into table

Question

I have a performance issue using SYBASE ASE when you try to insert the following code into a table or temp table:

    INSERT INTO #temp (Id)
    SELECT TOP 100 a.Id
    FROM TableA a
    INNER JOIN TableB b ON a.Id = b.Id
    WHERE a.SomeColumn = 'blah' and b.SomeColumn = 'Blah'
    ORDER BY a.Id

The WHERE clause isn't that important...the important thing is that the SELECT query runs in a split second on it's own, but as soon as you try to insert it into a table, it take 2 mintutes!!!!

Looking at the query plan, the optimiser does not seem to take into account that the estimated rows should be 100 and does a table scan of TableB. The select statement on it's own seems to render a sensible plan where the TOP 100 is taken into account, but the insert seems to make the optimiser take a very inefficient route. Have tried many permutations to this query and to no avail. Tables A and B are very large and the TOP N is a must. Have also tried set rowcount 100 and same result.

Can anyone suggest a work around for this?

Thanks

Answer 1

Have you tried:

INSERT INTO #temp (Id)
SELECT * FROM
(
  SELECT TOP 100 a.Id
  FROM TableA a
  INNER JOIN TableB b ON a.Id = b.Id
  WHERE a.SomeColumn = 'blah' and b.SomeColumn = 'Blah'
  ORDER BY a.Id
)

It may produce a slightly different execution plan.

Answer 2

Why dont you try this and see if you still have issues. Technically, the insert into also should have been quite fast if the select statement itself is fast.

--INSERT INTO #temp (Id)
SELECT TOP 100 a.Id
INTO #temp
FROM TableA a
INNER JOIN TableB b ON a.Id = b.Id
WHERE a.SomeColumn = 'blah' and b.SomeColumn = 'Blah'
ORDER BY a.Id