SQL查询需要花费大量时间较长的code比查询数据库直接 [英] SQL-query takes a lot longer time in code than query db direct

问题描述

我有一个SQL查询需要不到一秒钟，当我使用SQL Management Studio来执行的，但是当我的code执行它，它需要在30秒内获得从数据库服务器的结果。该结果包含1700行。另一个类似的查询，返回900行，需要几毫秒执行。有什么可以为这种奇怪的行为的原因是什么？

 公共SqlDataReader对象的ExecuteReader（字符串STRSQL，ArrayList的arParams）
    {
        OpenConnection的（）;

        SqlCommand的myCommand =新的SqlCommand（STRSQL，MyConnection的）;
        myCommand.CommandTimeout = intTimeout;


        的foreach（SqlParameter的myParameter在arParams）
            myCommand.Parameters.Add（myParameter）;

        返回myCommand.ExecuteReader（System.Data.CommandBehavior.CloseConnection）;
    }
 

STRSQL：

  SELECT [组]。[ID]
       ，[组]。[intCustomerId]
       ，[组]。[则strName]
       [许可]。[dtmCreated]
       [许可]。[intPermissionTypeId]
       [许可]。[intObjectTypeId]
       [许可]。[intObjectId]
       [许可]。[blnActive]
       [许可]。[blnHaveAccess]
       [许可]。[intLevelTypeId]
 从[组]
 LEFT JOIN权限[组]。[ID] = intGroupId和
                         intObjectId = @ObjectId和
                         intObjectTypeId = @ObjectTypeId和
                         intLevelTypeId = @LevelType和
                         intPermissionTypeId = @PermissionTypeId和
                         blnActive = 1
 WHERE [组]。[intCustomerId] = @CustomerID和
       [组]。[blnDeleted] = 0
 ORDER BY则strName，blnActive DESC
 

arParams：

  arParams.Add（DatabaseHandler.MakeSqlParameter（@客户ID，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：

 公共静态的SqlParameter MakeSqlParameter（字符串则strName，诠释intInput）
{
    返回新的SqlParameter（则strName，intInput）;
}
 

解决方案

根据您的意见我会说，正确的办法是索引的答复。

简单的方法是在运行正常的查询，运行SQL日志记录了一下，然后再运行之后运行SQL事件探查器。

根据其建议，它可能spottet丢失索引

I have a SQL query that takes less than a second to execute when I'm using SQL Management Studio, but when my code executes it, it takes over 30 seconds to get the result from the database server. The result contains 1700 rows. Another similar query, that returns 900 rows, takes a few ms to execute. What can be the reason for this odd behaviour?

    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);
}

解决方案

Based on your reply on comments i would say the correct solution is indexes.

Simplest way would be to run the sql logging for a bit when you run the normal queries, and then afterward run the run the sql profiler.

Based on its recommendations it could have spottet a missing indexes.

这篇关于SQL查询需要花费大量时间较长的code比查询数据库直接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！