3

I can't run f.e. this query against SO. SU, SF and Meta are working fine, but SO just tells me to hold tight while my results are fetched...but no matter how tight I hold my PC and Modem, it doesn't return with my results. :(

Also, I can't use OData during that time, it's just not responding, it loads forever. After one or two minutes it just stops but I still can't use OData again (takes some more minutes before it comes back to me)...but still no results.

So, am I too dumb to write proper SQL, are the results just to big or is there something else going on?

1 Answer 1

3

The query does complete, but the Ajax stuff times out before it does, and doesn't return the result set. That's why it only appears to not work for Stack Overflow data, because the data set is so much larger than the other sites.

I strongly suspect the performance issue is a lack of a full text index on the Posts.Body column. If you change the LIKE expression to 'http:%', the query returns very quickly. (Unfortunately, that doesn't give any results.)

As a side note, in this kind of application, it's next to impossible to get covered indexes for an arbitrary query... but I think the post bodies do deserve to be indexed.

4
  • I was just going to answer this and you beat me to it. Sorry no fulltext indexes are supported in sql azure ... there is nothing I can do about it short of build a Lucene indexer which would take a long time ...
    – waffles
    Aug 16, 2010 at 22:59
  • 1
    @waffles: Ugh.. that's really unfortunate.
    – Jon Seigel
    Aug 17, 2010 at 2:11
  • I thought query results were cached? if so, if the query does complete, shouldn't the result be cached by azure so that re-running the same query just reads from cache? (i guess i misunderstood the existence of a such a feature...)
    – Kip
    Aug 17, 2010 at 3:07
  • @Kip: I think they are, but the caching being available in this situation may depend on the architecture of the system, since part of it failed during the process. I don't know enough about MVC to trace through the code to find out.
    – Jon Seigel
    Aug 17, 2010 at 3:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .