查询SQL语句成本

　在有些大系统中,客户端查询非常复杂,需要显示很多字段和过滤很多条件,例如TFS中的条件过滤,或则用户希望客户端查询能像EXCEL过滤那样方便

这样可能导致查询语句非常复杂,此时的效率也比较低.

 

TFS查询条件界面:

 

 

如果可以获取查询语句的成本,当SQL语句执行成本很大(可能执行时间会很长时),系统自动提示用户是否继续或则大概需要多长时间,这样会提高用户体验.

 

下举一例:

查询Sql Server中的所有列信息表:sys.all_columns

在Sql server 2k8 management studio 中先后执行下面每行语句即可得到Sql语句的成本, 即为第二个语句执行结果中第一行中TotalSubtreeCost字段值.

set showplan_all on
select * from sys.all_columns

 

 第二个语句(select * from sys.all_columns)执行结果:

 

StmtText	TotalSubtreeCost	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize
select * from sys.all_columns	0.1736313	1	1	0	NULL	NULL	5270	NULL	NULL	NULL
|--Concatenation	0.1736313	1	2	1	Concatenation	Concatenation	5270	0	0.000527	303
	0.01454595	1	3	2	Filter	Filter	659	0	8.44E-04	303
	0.01370243	1	4	3	Compute Scalar	Compute Scalar	659	0	6.59E-05	305
	0.01363653	1	5	4	Clustered Index Scan	Clustered Index Scan	659	0.01275463	0.0008819	179
	0.1585583	1	36	2	Compute Scalar	Compute Scalar	4611	0	0.0004611	303
	0.1523828	1	37	36	Compute Scalar	Compute Scalar	4611	0	0.0011905	164
	0.1511923	1	38	37	Clustered Index Scan	Clustered Index Scan	4611	0.1379398	0.0132525	167

 

再执行 set showplan_all off结束查询计划分析结果.

 

此时可以应用showplan_all来获取Sql查询的成本.

如下为C#简单实现上述应用,在VS2k8中编译通过,只需正确更改Data Source即可运行:

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder();
            connectionBuilder.Add("Data Source", "your server name or ip");
            connectionBuilder.Add("Initial Catalog", "master");
            connectionBuilder.Add("Integrated Security", "True");

            string sqlStr = "select * from sys.all_columns";
            Console.WriteLine(GetSqlCost(connectionBuilder.ToString(), sqlStr).ToString());
            Console.Read();
        }

        static double GetSqlCost(string connectStr, string strSQL)
        {
            double sqlCost;
            int timeOut;
            SqlDataAdapter adapter;
            SqlConnection connection = new SqlConnection(connectStr);
            SqlCommand command;
            DataTable table;
            timeOut = 180;
            adapter = null;

            try
            {
                connection.Open();
                command = new SqlCommand("set showplan_all on ", connection);
                command.ExecuteNonQuery();
                command.CommandText = strSQL;
                adapter = new SqlDataAdapter(strSQL, connection);
                adapter.SelectCommand.CommandTimeout = timeOut;
                table = new DataTable();
                adapter.Fill(table);
                command.CommandText = "set showplan_all off";
                command.ExecuteNonQuery();
                sqlCost = System.Convert.ToDouble(table.Rows[0]["TotalSubtreeCost"]);
            }
            finally
            {
                command = null;
                connection.Close();
            }
            return sqlCost;
        }
    }

 

}