4

I attempted to run the following query to find posts that are "signed" (ending) with the poster's username:

select top 5 posts.Id as [Post Link] from posts join users on posts.OwnerUserId=users.id where posts.body like '%' + users.displayname

In Firefox 7.0.1, the query takes a couple minutes, then the page displays:

Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

In Chrome 14.0.835.202, the query also takes a couple minutes, then displays a JavaScript alert box reading:

Something is wrong with the server!

In both browsers, I never see any results displayed.

I was able to get the query to work by appending + '%' to the last line (see the modified query here). However, this isn't what I originally intended-- I wanted to search only for posts that end with the user's display name, not for posts that merely contain the user's display name.

Improve this question
1
  • 2
    The error message appears to tell you everything you need to know: there is a query timeout that you've reached. I guess SQL Server doesn't do "Ends With" very well at all. Commented Oct 17, 2011 at 14:33

1 Answer 1

Reset to default
3

You already found out that like '%last word' is slow and this post gives some hints on how these kind of queries can be improved.

I'm not sure if the body contained the same data it contains now but I expect your query would never returns any rows simply because no rows would match (which explains your timeouts). The body field contains the html markup and a lot of the content I inspected ended with an end tag for a paragraph: </p> and a not at first glance noticeable char(10).

where body like '%'+displayname+'</p>'+char(10) would still suffer from the same performance penalty (500 rows returned in 24157 ms) but the charindex variant was not much better...

This query would give you the questions that needs an edit to get the name of the user removed:

select top 500 posts.Id as [Post Link] , right(body,10) , ascii(right(body,1)) from posts join users on posts.OwnerUserId=users.id where body like '%'+ displayname+ N'</p>'+char(10) and posttypeid =1 -- questions

I left the debug columns in case you need to investigate cornercases that get skipped in error.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.