Linq2Sql过了一会儿就慢了下来

在一个更大的项目中，我们决定在web服务（WCF）中使用Linq2Sql。我们"很快"发现，在添加了一些数据后（通过一个导入客户数据的小型控制台应用程序），网络服务的速度明显减慢了——在该应用程序的使用寿命内。

每次我们重新启动应用程序时，它一开始似乎导入数据很快，导入10-15分钟后速度就会减慢。

我决定尝试创建一个示例应用程序，模仿我们主应用程序的小部分。

• 我必须补充一点，我们决定在web服务中省略Linq2Sql，用reqularsqlcommand/SqlConnection等替换它们

有人知道应用程序速度减慢可能是什么原因吗？

以下是模拟一些数据库查询的"最小化"应用程序的代码：

包括SQL语句的应用程序来创建表：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Linq2SqlTest
{
    class Program
    {
        /*
         * USE [Linq2SqlTest]
            GO
            SET ANSI_NULLS ON
            GO
            SET QUOTED_IDENTIFIER ON
            GO
            CREATE TABLE [dbo].[Adresse](
                [id] [int] IDENTITY(1,1) NOT NULL,
                [person_id] [int] NOT NULL,
                [Gate] [nvarchar](50) NOT NULL,
                [Poststed] [nvarchar](50) NOT NULL,
                [By] [nvarchar](50) NOT NULL,
             CONSTRAINT [PK_Adresse] PRIMARY KEY CLUSTERED 
            (
                [id] ASC
            )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
            ) ON [PRIMARY]
            GO
            ALTER TABLE [dbo].[Adresse]  WITH CHECK ADD  CONSTRAINT [FK_Adresse_Person1] FOREIGN KEY([person_id])
            REFERENCES [dbo].[Person] ([id])
            GO
            ALTER TABLE [dbo].[Adresse] CHECK CONSTRAINT [FK_Adresse_Person1]
         * 
         * 
         * USE [Linq2SqlTest]
            GO
            SET ANSI_NULLS ON
            GO
            SET QUOTED_IDENTIFIER ON
            GO
            CREATE TABLE [dbo].[Items](
                [ItemID] [int] IDENTITY(1,1) NOT NULL,
                [ItemNumber] [nchar](10) NOT NULL,
                [PersonID] [int] NOT NULL,
                [name] [nvarchar](50) NULL,
             CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
            (
                [ItemID] ASC
            )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
            ) ON [PRIMARY]
            GO
            ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Person] FOREIGN KEY([PersonID])
            REFERENCES [dbo].[Person] ([id])
            GO
            ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Person]
         *
         * USE [Linq2SqlTest]
                GO
                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO
                CREATE TABLE [dbo].[Person](
                    [id] [int] IDENTITY(1,1) NOT NULL,
                    [name] [nvarchar](50) NOT NULL,
                    [AddedDate] [datetime] NOT NULL,
                    [ssn] [nvarchar](50) NOT NULL,
                 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
                (
                    [id] ASC
                )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                ) ON [PRIMARY]
         */
        public static Random randomizer = new Random();
        public static String GenerateName()
        {
            int nLetterCount = (randomizer.Next() % 5) + 3;
            String s = "";
            for (int i = 0; i < nLetterCount;i++)
            {
                int r = (randomizer.Next() % 26)+97;
                s += Convert.ToChar(r);
            }
            return s;
        }
        public static String GenerateFullname()
        {
            String Fullname = "";
            int nNameCount = randomizer.Next() % 3;
            nNameCount++;
            while (nNameCount>0)
            {
                if (Fullname.Length == 0)
                    Fullname = GenerateName();
                else
                    Fullname += " " + GenerateName();
                nNameCount--;
            }
            return Fullname;
        }
        static void Main(string[] args)
        {
            DatabaseDataContext db = new DatabaseDataContext();
            for (int i = 0; i < 300000;i++ )
            {
                int personid = randomizer.Next() % 100;
                Person p = db.Persons.FirstOrDefault(_p=> _p.ssn == personid.ToString());
                if (p == null)
                {
                    p = new Person();
                    p.name = GenerateFullname();
                    p.ssn = personid.ToString();
                    p.AddedDate = DateTime.Now;
                    db.Persons.InsertOnSubmit(p);
                    db.SubmitChanges();
                    Adresse a = new Adresse();
                    a.person_id = p.id;
                    a.Gate = GenerateFullname();
                    a.Poststed = GenerateName();
                    a.By = GenerateName();
                    db.Adresses.InsertOnSubmit(a);
                    db.SubmitChanges();
                    int jj = randomizer.Next() % 10 + 1;
                    for (int j = 0; j < jj; j++)
                    {
                        Item item = new Item();
                        item.ItemNumber = randomizer.Next().ToString();
                        item.PersonID = p.id;
                        db.Items.InsertOnSubmit(item);
                        db.SubmitChanges();
                    }
                }
                else
                {
                    int jj = randomizer.Next() % 10 + 1;
                    for (int j = 0; j < jj; j++)
                    {
                        int number = randomizer.Next();
                        Item item = db.Items.FirstOrDefault(_i => _i.ItemNumber == number.ToString());
                        if (item == null)
                        {
                            item = new Item();
                            item.ItemNumber = number.ToString();
                            item.PersonID = p.id;
                            db.Items.InsertOnSubmit(item);
                        }
                        else
                        {
                            item.ItemNumber = randomizer.Next().ToString();
                        }
                        db.SubmitChanges();
                    }
                }
                Console.WriteLine("'r" + i + "   ");
            }
        }
    }
}

DatabaseContext.DeferredLoadingEnabled = false;