An experience with Azure Sql Server

Question

Hello,
I want to tell you about an experience made with a db Azure Sql Server having as a standard pricing plan S0 10 dtu, in the hope of getting from you clarifications or considerations.

It basically happened that by connecting to the db from a WPF desktop application through the direct use of the System.Data.SqlClient (no EF) classes, running on a SqlCommand with CommandType set to Text a select query that would have to return about 40k of rows by filling a DataTable using the Load method, I got after a 'long wait' a TimeOut error.

Not knowing what to do, I decided to modify the SqlCommand using a StoredProcedure instead of the text. In doing so I 'solved' in the sense that I received the 40k rows without errors.

As time went on and the amount of rows returned by the query increased, this approach also failed because I always received the usual timeout error.

However, if I ran the same stored procedure from Management Studio, I would get all the rows without any problems.

Who knows why?

Hello, thank you.

translated by Bing translator

Answer 1

@Mario Formosa Welcome to Microsoft Q&A.

It appears that you are running into execution timeouts while executing and returning large datasets.
By default, the CommandTimeout is set to 30 seconds in the .Net provider. You can override this and set it to 0 to indicate a "no limit".

SqlCommand cmd = new SqlCommand();  
cmd.CommandTimeout = 300;  //Override  
cmd.Connection = conn;  

However, it is somewhat concerning that you are consuming >40K records in a client WPF application on an S0 database.
Please check the DTU utilization to make sure that you are not hitting the resource limits and getting throttled. You should tune your logic and queries accordingly if this is the case.

You might see all the records getting returned from SSMS because it runs the commands via an SQL provider (without the constraints of timeouts etc.) and doesn't have to go through various layers (application logic, business logic etc.) in a typical client application.

Please let us know if you have any further questions.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.