Limit of rows returned from a query = 300?

I recently signed up to DASP and am getting my database set up. However I was very dismayed when I ran a few queries and noticed that on one of them I got an error. The DASP server is saying that it refuses to run the query because the returned rowset will be over 300 (it says it estimates 576)? Why are these restrictions in place? I have several hosting accounts for other projects with other companies and I haven't seen such restrictions. Is there any way to get rid of this?

 

There is a limit in the time we allow queries to run. In shared SQL server, it's not feasible to let a query to run forever, hogging up all the CPU time and resources. You'll have to rewrite your query and make it more efficient.

Aristotle

DiscountASP.NET
www.DiscountASP.NET

 

On our local SQL Server database, running on Win Server 2003, the query is taking 0.4 seconds. This is too long of a time? Unless of course this query is taking longer on your servers for some reason. What can be done to determine this?

 

Sure, the error is:

{'Message':'The query has been canceled because the estimated cost of this query (587) exceeds the configured

threshold of 300. Contact the system administrator.','StackTrace':' at System.Data.SqlClient.SqlConnection

.OnError(SqlException exception, Boolean breakConnection)\r\n at System.Data.SqlClient.SqlInternalConnection

.OnError(SqlException exception, Boolean breakConnection)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning

(TdsParserStateObject stateObj)\r\n at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior

, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject

stateObj)\r\n at System.Data.SqlClient.SqlDataReader.HasMoreRows()\r\n at System.Data.SqlClient

.SqlDataReader.ReadInternal(Boolean setTimeout)\r\n at System.Data.SqlClient.SqlDataReader.Read()\r

\n at InvoiceServices.GetOptionValueTree(Int32 optionID) in d:\\Dev\\Projects\\RNH\\AccessLink\\App_Code

\\invoice_services.cs:line 219','ExceptionType':'System.Data.SqlClient.SqlException'}

 

Ok, the error message you are receiving is saying you are exceeding 300 seconds or 5 minutes for a query, not 300 records.

What does your query look like?


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

The query is fairly complex, but in no way would it ever take that long. As I said, we have a server set up at a remote location and sending a request to that server only takes 1s total roundtrip, including sending the request and receiving back a 50k data table. I just used the SQL Server Management Studio to run the query directly on your servers and the query took 5 seconds just to run, without internet roundtrip times. I'm not sure why it won't let me run the query through code or why it thinks it will take 587 seconds.

If you want, I can give you my details and you can check the query yourself. It is also interesting as to why your server is taking 5 seconds while our remote one is taking 1. If you want me to give you more specifics, please give me an email address I can send this to.

 

Can you please copy the exact error message you are receiving?


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Use Profiler, run that and take a look at the transactions, if you haven't already, it can help display oddities.
http://www.sql-server-performance.com/sql_server_profiler_tips.asp

 

The most common reason for this happening is the database tables not being indexed properly.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET