asked
Query timeout with nvarchar(max) fields
I am having a problem with a "Microsoft SQL Server Native Client 10.0 error '80040e31' Query timeout expired" error running from an ASP web page against a SQL Server 2008 db. The problem only occurs when two search terms are being used as alternatives against an nvarchar(max) field. For example, the following structures work: SELECT field1 FROM table1 WHERE field1 LIKE '%string1%' SELECT field1 FROM table1 WHERE field1 LIKE '%string1%' AND field1 LIKE '%string2%' SELECT field1 FROM table1 WHERE field1 LIKE '%string1%' AND NOT field1 LIKE '%string2%' But the following sometimes causes a timeout error after about 60 seconds (even though timeout is set at 300 seconds), depending on the size of the data (number of characters - Small fields (just over 255 characters) work, larger fields fail): SELECT field1 FROM table1 WHERE field1 LIKE '%string1%' OR field1 LIKE '%string2%' The following ALWAYS causes a timeout error: SELECT field1, field2 FROM table1 WHERE field1 LIKE '%string1%' OR field2 LIKE '%string1%' I thought at first it might be some sort of memory problem, but there is no correlation with the number of records that could potentially be returned. It happens even if only one record fits the criteria. Also, all queries work as Stored Procedures if run within SQL Server itself, but calling the stored procedure from the web page causes the same error. Has anyone ever come across this problem? Is it a setting that I have missed? Any help for possible alternative approaches would be appreciated.
Thanks for all your answers. I will try out indexing and server settings. Although, to clarify, all timeouts are set at 5 minutes. One thing I didn't say is that this query works fine for the MS Access database version of this data, which is what leads me to believe it must be a SQL Server issue. I will get back shortly with my findings, and votes.
If any answers were helpful, please indicate that by clicking on the thumbs up next to them. If any one answer solved the problem, please indicate that by clicking on the check box next to it.
It has to do with your search criteria more than likely. A search with '%string%' will always result in a scan. You will not see any kind of index use. Combining multiple criteria, especially in an OR situation will require an even more intense scan across multiple fields. It has nothing to do with the number of records that match the criteria. It will check all records, every time. If you need to search for strings like that you'll be better off looking into [Full Text indexing][1] rather than relying on straight T-SQL. Otherwise, you need to change to equals statements or even LIKE 'string%' which can use indexes appropriately to speed your queries up. [1]:
http://msdn.microsoft.com/en-us/library/ms142571.aspx
Querying like that won't make use of any indexes, so I not surprised it's slow. Have you considered Full Text Search (
http://msdn.microsoft.com/en-us/library/ms142571(v=sql.100).aspx) ? A side note about timeouts - not sure what you mean it timeout at 60s even though it is set at 300s - something somewhere must be set at 60s - SQL Server itself doesn't timeout, its the client waiting for the query to finish that does. That's why when you run the query in SQL (i.e. SSMS) it will work - it will just keep running. The client timeout also has to handle the time it takes to pump the data back to the client too - a query can run very fast, but if that generates lots of wide output (nvarchar(max)), it takes time to go 'over the wire'
Make sure you are setting the command timeout to 300 not the connection timeout. It's possible that a query governor value has been set on the server, although unlikely as I have never seen anyone use it. As other people have said this type of string match is not ideal as it requires a considerable amount of IO to perform as it scans for record matches. I am sure that if you add an index to the fields the query engine will make use of it albeit via an Index Scan.
15 People are following this question.
