用NBear的时候,如果列表显示一组数据,此数据中只是返回少量字段信息,如:id,title等,点击详细后,再出现获取完整记录,如包括的content信息,往往content包含了比较大的数据量,如果返回整个实体,SQL的Reads自然会增加,服务器变做了许多无用的工作,性能自然也不好,我没有找到NBear中有解决相关问题的方法。所以我这里稍微改动了一下NBear,来实现列表数据的查询优化。步骤如下:
1、打开NBear的源代码,
![](/images/cnblogs_com/litongtong/111.JPG)
我们将红线标示的两个文件。
1、修改FromClip.cs中的ToString()方法如下:
public override string ToString()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
StringBuilder sb = new StringBuilder();
![](/Images/OutliningIndicators/InBlock.gif)
sb.Append('[');
string _tableOrViewName = tableOrViewName.TrimStart('[').TrimEnd(']');
if (_tableOrViewName.Substring(0, 2) == "__")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_tableOrViewName = tableOrViewName.Substring(2, tableOrViewName.Length-2);
}
sb.Append(_tableOrViewName);
sb.Append(']');
if (aliasName != tableOrViewName)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(' ');
sb.Append('[');
sb.Append(aliasName.TrimStart('[').TrimEnd(']'));
sb.Append(']');
}
![](/Images/OutliningIndicators/InBlock.gif)
foreach (string joinAliasName in joins.Keys)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (sb.ToString().Contains("INNER JOIN"))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb = new StringBuilder('(' + sb.ToString() + ')');
}
![](/Images/OutliningIndicators/InBlock.gif)
KeyValuePair<string, WhereClip> keyWhere = joins[joinAliasName];
sb.Append(" INNER JOIN ");
sb.Append('[');
sb.Append(keyWhere.Key.TrimStart('[').TrimEnd(']'));
sb.Append(']');
if (joinAliasName != keyWhere.Key)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(' ');
sb.Append('[');
sb.Append(joinAliasName.TrimStart('[').TrimEnd(']'));
sb.Append(']');
}
sb.Append(" ON ");
sb.Append(keyWhere.Value.ToString());
}
![](/Images/OutliningIndicators/InBlock.gif)
return sb.ToString();
}
2、修改SqlQueryUtils.cs中AppendColumnName方法如下:
public static void AppendColumnName(StringBuilder sb, string columnName)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
Check.Require(sb != null, "sb could not be null!");
Check.Require(columnName != null, "columnName could not be null!");
![](/Images/OutliningIndicators/InBlock.gif)
if (columnName.Contains("."))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string[] splittedColumnSections = columnName.Split('.');
for (int i = 0; i < splittedColumnSections.Length; ++i)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (splittedColumnSections[i] == "*" || splittedColumnSections[i].Contains("(") || splittedColumnSections[i].Contains("["))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(splittedColumnSections[i]);
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append('[');
string s = splittedColumnSections[i];
if (0 == i)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (s.Substring(0, 2) == "__")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
s = s.Substring(2, s.Length - 2);
}
}
sb.Append(s);
sb.Append(']');
}
![](/Images/OutliningIndicators/InBlock.gif)
if (i < splittedColumnSections.Length - 1)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append('.');
}
}
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (columnName == "*" || columnName.Contains("(") || columnName.Contains("["))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(columnName);
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append('[');
sb.Append(columnName);
sb.Append(']');
}
}
}
这里面做了简单的限定:如果实体类的开头以两条下划线"__"开头的话,那么就认为是承载未带下划线类的部分数据,承载字段根据承载类的属性确定
完成之后,编译项目。
2、项目中使用刚才我们编译出的NBear.Common.dll
如:EntityDesigns中
public interface HtmlTable : Entity
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
[PrimaryKey]
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
int id
{ get; }
[SqlType("varchar(255)")]
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string title
{ get; set; }
[SqlType("text")]
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string context
{ get; set; }
}
![](/Images/OutliningIndicators/None.gif)
public interface __HtmlTable : Entity
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
[PrimaryKey]
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
int id
{ get; }
[SqlType("varchar(255)")]
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string title
{ get; set; }
}
项目中
Entities.__HtmlTable[] tables = ServiceFactory.Create().GetService<ICommonService>().GetAllOfEntity<Entities.__HtmlTable>();
foreach (Entities.__HtmlTable table in tables)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
Response.Write( table.id + ":" + table.title);
}
查看数据检索的SQL语句如下:
![](/images/cnblogs_com/litongtong/222.JPG)
为:SELECT [HtmlTable].[id],[HtmlTable].[title] From [HtmlTable]
而不是:SELECT [HtmlTable].[id],[HtmlTable].[title],[HtmlTable].[context] From [HtmlTable]
这样对性能应该会好些吧。