StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Oded is correct, you should definitely start with the query plan. That said, there are a couple of things that you can already see at a high level, for example:</p> <blockquote> <p>CONVERT(VARCHAR(8000), CN.Note) LIKE '%T B9997%' </p> </blockquote> <p><code>LIKE</code> searches with a wildcard at the front are bad news in terms of performance, because of the way indexes work. If you think about it, it's easy to find all people in the phone book whose name starts with "Smi". If you try to find all people who have "mit" anywhere in their name, you will find that you need to read the entire phone book. SQL Server does the same thing - this is called a full table scan, and is typically quite slow. The other issue is that the left side of the condition uses a function to modify the column (specifically, converting it to a varchar). Essentially, this again means that SQL Server cannot use an index, even if there was one for the column CN.Note. </p> <p>My guess is that the column is a <code>text</code> column, and that you will not be allowed to change the filter logic to remove the wildcard at the beginning of the search. In this case, I would recommend looking into <a href="http://msdn.microsoft.com/en-us/library/ms142559.aspx" rel="nofollow">Full-Text Search / Indexing</a> functionality. By enabling full text indexing, and using specific keywords such as <code>CONTAINS</code>, you should get better performance.</p> <p>Again (as with all performance optimisation scenarios), you should still start with the query plan to see if this really is the biggest problem with the query.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload