June 30, 2008 at 3:13 am
i have a complex view that will only return records if i use top. eg "select top 100000 * from ViewName". take about 12 seconds to return all 25k rows
when i use "select * from ViewName" it returns nothing.
have run it thru the index tuner many times, have updated statistics, have also tried query hint maxdop 1 to resolve an issue with intra-query parallelism when run inside a dts package.
only anomaly i can find is that the execution plan for "select top 100000 * from ViewName" has a task called parallelism/gather streams which claims to cost 1521% of the query cost.
the execution plan for "select * from ViewName" seems to be what i would expect for the complexity of the view.
any thoughts?
July 1, 2008 at 8:31 pm
SELECT TOP 100 PERCENT FROM viewname
July 2, 2008 at 12:06 am
thanks ivanna, alas we've already tried that. the execution plan looks identical to "select * from ViewName" and has the same problem when it runs. ie it hangs
July 3, 2008 at 8:07 am
Have you tried clearing your cache for the Query Optimizer? It could be that it is using a bad plan for the "SELECT *". You may need to clear the cache (and possibly update statistics on all involved tables) to get SQL to rebuild it's execution plan. Maybe you can post the XML for the execution plan?
July 6, 2008 at 9:06 pm
problem solved. turned out to be combination of the query design and bad data. specifically, a field that was joined to itself in a memory table had null values which were creating a cartesian product. data now populated correctly and view runs fine. thanks for the ideas
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply