What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

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.

share|improve this question
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. –  user7116 Oct 17 '11 at 14:33

1 Answer 1

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.

share|improve this answer

You must log in to answer this question.

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