浅谈Linq To Sql集成数据库语言的优劣

看了好友介绍的 http://www.itus.cn/database/10/otherdb-112077.shtml 这篇博客，觉得将sql 和 linqToSql性能分析得很透彻，我这里只说一句，像分页这种大数据量处理，Linq To SQL的内置分页算法 性能更好,案例如下【IT168技术文档】经我们经理培训所得
  以Northwind为示例数据库，DLINQ(LINQ to SQL)之结合GridView控件和ObjectDataSource控件演示大数据量分页，同时介绍延迟执行和日志记录
  示例
  PagingAndLogging.aspx

<%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="PagingAndLogging.aspx.cs"    Inherits="LINQ_DLINQ_PagingAndLogging" Title="大数据量分页、延迟执行和日志记录" %><asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server"></asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server"><asp:GridView ID="gvProduct" runat="server" DataSourceID="odsProduct" AllowPaging="True" PageSize="5"></asp:GridView><asp:ObjectDataSource ID="odsProduct" runat="server" EnablePaging="True" SelectCountMethod="GetProductCount"SelectMethod="GetProduct" TypeName="PagingAndLogging"><SelectParameters><asp:Parameter Name="startRowIndex" Type="Int32" DefaultValue="0" /><asp:Parameter Name="maximumRows" Type="Int32" DefaultValue="10" /></SelectParameters></asp:ObjectDataSource></asp:Content>

PagingAndLogging.cs

using System;using System.Data;using System.Configuration;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Xml.Linq;using System.ComponentModel;using System.Collections.Generic;using System.IO;using DAL;/**//// <summary>/// PagingAndLogging 的摘要说明/// </summary>[DataObject]public class PagingAndLogging{[DataObjectMethod(DataObjectMethodType.Select, true)]public List<Products> GetProduct(int startRowIndex, int maximumRows){NorthwindDataContext ctx = new NorthwindDataContext();// System.Data.Linq.DataContext的记录日志的功能        StreamWriter sw = new StreamWriter(HttpContext.Current.Request.PhysicalApplicationPath + "Log.txt", true);ctx.Log = sw;var products = (from p in ctx.Productsselect p).Skip(startRowIndex).Take(maximumRows);// products实现了IQueryable<T>接口// 所以可以用如下方法从中获取DbCommand        System.Data.Common.DbCommand cmd = ctx.GetCommand(products);string commandText = cmd.CommandText;foreach (System.Data.Common.DbParameter param in cmd.Parameters){string parameterName = param.ParameterName;object value = param.Value;}// 延迟执行（Deferred Execution）// products实现了IEnumerable<T>接口// IEnumerable<T>接口的一个特性是，实现它的对象可以把实际的查询运算延迟到第一次对返回值进行迭代（yield）的时候// ToList()之前，如果是LINQ to SQL的话，那么就可以通过products.ToString()查看LINQ生成的T-SQL// ToList()后则执行运算        var listProducts = products.ToList();// 执行运算后System.Data.Linq.DataContext会记录日志，所以应该在执行运算后Close掉StreamWriter        sw.Flush();sw.Close();return listProducts;}public int GetProductCount(int startRowIndex, int maximumRows){NorthwindDataContext ctx = new NorthwindDataContext();StreamWriter sw = new StreamWriter(HttpContext.Current.Request.PhysicalApplicationPath + "Log.txt", true);ctx.Log = sw;// Count查询操作符（不延迟） - 返回集合中的元素个数        int c = (from p in ctx.Productsselect 0).Count();sw.Flush();sw.Close();return c;}}

 

通过查看日志可以发现，单击第1页时DLINQ生成的T-SQL语句如下

SELECT TOP 5 [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]FROM [dbo].[Products] AS [t0]-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1SELECT COUNT(*) AS [value]FROM [dbo].[Products] AS [t0]-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1通过查看日志可以发现，单击第10页时DLINQ生成的T-SQL语句如下SELECT TOP 5 [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued]FROM (SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]) AS [ROW_NUMBER], [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]FROM [dbo].[Products] AS [t0]) AS [t1]WHERE [t1].[ROW_NUMBER] > @p0-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [45]-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1SELECT COUNT(*) AS [value]FROM [dbo].[Products] AS [t0]