当我不断添加行时，SQL Server 2005 会变慢 [英] SQL Server 2005 slows down as I keep adding rows

问题描述

作为前言，我是使用 SQL Server 2005 的新手；我知道如何使用 SELECT、UPDATE、DELETE 和 INSERT 命令，仅此而已.我也在本地 PC 上使用 Express Edition(E8400 处理器，8GB DDR2-800，RAID 1 中的 2 个 640GB SATA-II 硬盘)

As a preface, I am brand new to using SQL Server 2005; I know how to use the SELECT, UPDATE, DELETE, and INSERT commands and that's about it. I am also using Express Edition on my local PC (E8400 processor, 8GB of DDR2-800, 2 x 640GB SATA-II HDD in RAID 1)

我有一个表，我设置了 8 列，所有列都是 NVARCHAR(Max) 并且我允许 Null.我在概念上知道主键是什么，但我没有(也不知道如何设置).

I have a table that I set up with 8 columns, all are NVARCHAR(Max) and I allow Null. I know in concept what a primary key is, but I don't have one (nor do I know how to set one up).

我正在开发的 VB.NET 程序正在从 Yahoo 下载历史股票价格图表，适用于现有的每个股票代码.我添加的前 50,000 行左右非常快.然后我上床睡觉，当我醒来时它仍在运行 - 但行添加的速度已经减慢了 waaaaaay;我在第 300,000 行左右注意到了这一点.我一直期望行添加的速度随着时间的推移保持不变，但显然事实并非如此！

My VB.NET program that I'm working on is downloading a historical stock price chart from Yahoo, for every single ticker symbol in existence. The first 50,000 rows or so I added was super fast. Then I went to bed, and when I woke up it was still running - but the rate of row additions has slowed waaaaaay down; I noticed this around row 300,000. I always expected the rate of row addition to be constant over time, but obviously this is not so!

通过浏览其他 Stack Overflow 问题，我怀疑我的速度变慢与我糟糕的桌子设置有关.如果是这种情况，我应该首先从哪里开始解决这个问题，是否有任何好的资源可以阅读以开始使用?我希望这是我可以解决的简单问题:)

From browsing other Stack Overflow questions, I suspect my slow down is related to my piss-poor table setup. If this is the case, where should I begin first to fix this, and are there any good resources I could read up on to get started? I'm hoping this is something simple I can fix :)

以防万一，这就是我添加行的方式:

In case it matters, this is how I'm adding rows:

cmdtext = "IF NOT EXISTS(SELECT DateStamp FROM DailyPrice WHERE (DateStamp = '" +     datestamp + "' AND Ticker = '" + ticker + "')) INSERT INTO DailyPrice (Ticker,OpenPrice,ClosePrice,HighPrice,LowPrice,AdjustedClose,Volume,DateStamp) VALUES('" + ticker + "','" + openprice + "','" + closeprice + "','" + highprice + "','" + lowprice + "','" + adjustedclose + "','" + volume + "','" + datestamp + "')"
                cmd = New SqlCommand(cmdtext, conn)
                howmanygotinserted = cmd.ExecuteNonQuery

我对 CSV 文件的每一行都进行了迭代，每个 CSV 文件大约有 30,000 行(我有超过 5000 行).

I iterate through that for every stinking row of the CSV file, which is around 30,000 rows per CSV file (and I have over 5000 of them).

推荐答案

8 列，都是 NVARCHAR(Max)

with 8 columns, all are NVARCHAR(Max)

这是你的第一个问题.如果您告诉他们您拥有什么类型的数据，并选择适用于您的数据的最小数据类型，则数据库效果最佳.NVARCHAR(Max) 是您可能做出的最低效的选择.

There's your first problem. Databases work best if you tell them what type of data you have, and choose the smallest datatype that works for your data. NVARCHAR(Max) is about the most inefficient choice you could have made.

我没有 [主键](我也不知道如何设置).

I don't have [a primary key] (nor do I know how to set one up).

这是你的第二个问题.在每次插入时，您都会检查是否已经插入了与某些列的另一行具有相同值的行.因为您还没有告诉数据库对这些列进行索引，所以每次都必须检查整个表，因此您的查询会随着表的增长而变得越来越慢.要将主键添加到现有表，您可以使用:

And that's your second problem. On every insert you are checking to see if you already have inserted a row that has the same values as another row for certain columns. Because you haven't told the database to index those columns, it has to check the entire table each time, so your query gets slower and slower as the table grows. To add a primary key to an existing table you can use this:

ALTER TABLE table1 ADD CONSTRAINT pk_table1 PRIMARY KEY (Ticker, DateStamp)

有关详细信息，请参阅此处.

See here for more info.

这篇关于当我不断添加行时，SQL Server 2005 会变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！