Geek Answers Handbook

SQL query takes much longer in code than db direct query

I have an SQL query that takes less than a second to execute when I use SQL Management Studio, but when my code runs it, it takes more than 30 seconds to get the result from the database server. The result contains 1700 lines. Another similar request, which returns 900 rows, takes several ms to execute. What could be the reason for this odd behavior?

public SqlDataReader ExecuteReader(string strSQL, ArrayList arParams) { OpenConnection(); SqlCommand myCommand = new SqlCommand(strSQL, myConnection); myCommand.CommandTimeout = intTimeout; foreach (SqlParameter myParameter in arParams) myCommand.Parameters.Add(myParameter); return myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); }

strSQL:

 SELECT [Group].[Id] ,[Group].[intCustomerId] ,[Group].[strName] ,[Permission].[dtmCreated] ,[Permission].[intPermissionTypeId] ,[Permission].[intObjectTypeId] ,[Permission].[intObjectId] ,[Permission].[blnActive] ,[Permission].[blnHaveAccess] ,[Permission].[intLevelTypeId] FROM [Group] LEFT JOIN Permission ON [Group].[Id] = intGroupId AND intObjectId = @ObjectId AND intObjectTypeId = @ObjectTypeId AND intLevelTypeId = @LevelType AND intPermissionTypeId = @PermissionTypeId AND blnActive = 1 WHERE [Group].[intCustomerId] = @CustomerId AND [Group].[blnDeleted] = 0 ORDER BY strName, blnActive DESC

arParams:

 arParams.Add(DatabaseHandler.MakeSqlParameter("@CustomerId", customer.Id)); arParams.Add(DatabaseHandler.MakeSqlParameter("@ObjectId", masterprocess.Id)); arParams.Add(DatabaseHandler.MakeSqlParameter("@ObjectTypeId", Convert.ToInt32(ObjectType.MasterProcess))); arParams.Add(DatabaseHandler.MakeSqlParameter("@PermissionTypeId", Convert.ToInt32(permissiontype))); arParams.Add(DatabaseHandler.MakeSqlParameter("@LevelType", Convert.ToInt32(leveltype)));

DatabaseHandler.MakeSqlParameter:

 public static SqlParameter MakeSqlParameter(String strName, int intInput) { return new SqlParameter(strName, intInput); }
+4
source share
2 answers

Based on your response to the comments, I would say that the right solution is indexes.

The simplest way would be to start SQL logging for bits when you run regular queries and then run sql profiler.

Based on its recommendations, it may contain spaces in the indices.

+1
source

I had the same problem, but I thought of a solution, considering this issue.

When you run a query using SQLCommand with parameters, the query is NOT executed the same way. It uses a stored procedure keeper to package parameters. Something like Exec sp_Execute.

If you are performing a parameter change, you should notice an improvement (I know there is a risk of SQL injection then).

You can also go even further and specify WITH (INDEX) next to your table to indicate which index you want to use. (Because he can try and use the PK index).

0
source

More articles:


All Articles