I have SQL 2005. This test is run on Developers Edition, 2GB memory. Indexed View has about 500,000 records. Production system runs on Workgroup edition (this is why I am...

Craig,

I tried to play with this query a bit more and you are absolutely right

that first, I have to subquery outside of With, and second, that I have to bring...

I will gather all the information. Should I post it here or provide it in some other way?

If I bring products_pid into Select part, it will require to add it to the Group By. Then I am getting very strange results.

I am sorry, in the previous reply I posted the wrong JOIN. Here is correct oneI am trying to do. Only by adding 2 JOINs, execution time increases from 45ms...

My test environment has SQL 2005 developer's edition, but my production one has Workgroup edition. So I have to use (NOEXPAND) for production environment. Where do I add JOIN another...

If I join other tables and add filters after the View it significantly slows down performance of this query.

Thank you.

The response is the same right after the REBUILD and at the end of the week (we don't get too many updates at this time). As far as memory configuration,...

We REBUILD catalog every week.