I am updating a piece of legacy code in one of our web apps. The app allows the user to upload a spreadsheet, which we will process as a background job.
我正在更新我们的一个网络应用程序中的遗留代码。该应用程序允许用户上传电子表格,我们将处理该电子表格作为后台作业。
Each of these user uploads creates a new table to store the spreadsheet data, so the number of tables in my SQL Server 2000 database will grow quickly - thousands of tables in the near term. I'm worried that this might not be something that SQL Server is optimized for.
这些用户上传中的每一个都创建了一个新表来存储电子表格数据,因此我的SQL Server 2000数据库中的表数量将快速增长 - 近期内有数千个表。我担心这可能不是SQL Server优化的东西。
It would be easiest to leave this mechanism as-is, but I don't want to leave a time-bomb that is going to blow up later. Better to fix it now if it needs fixing (the obvious alternative is one large table with a key associating records with user batches).
按原样保留这种机制是最简单的,但我不想留下一个随后爆炸的定时炸弹。如果需要修复,最好立即修复它(显而易见的替代方案是一个大表,其中一个键将记录与用户批处理相关联)。
Is this architecture likely to create a performance problem as the number of tables grows? And if so, could the problem be mitigated by upgrading to a later version of SQL Server ?
随着表数量的增长,这种架构是否会产生性能问题?如果是这样,可以通过升级到更高版本的SQL Server来缓解问题吗?
Edit: Some more information in response to questions:
编辑:回答问题的更多信息:
- Each of these tables has the same schema. There is no reason that it couldn't have been implemented as one large table; it just wasn't.
- Deleting old tables is also an option. They might be needed for a month or two, no longer than that.
这些表中的每一个都具有相同的模式。没有理由不能将它作为一个大表来实现;它只是没有。
删除旧表也是一种选择。他们可能需要一两个月,不会超过这个。
4 个解决方案
#1
3
Having many tables is not an issue for the engine. The catalog metadata is optimized for very large sizes. There are also some advantages on having each user own its table, like ability to have separate security ACLs per table, separate table statistics for each user content and not least improve query performance for the 'accidental' table scan.
拥有多个表不是引擎的问题。目录元数据针对非常大的尺寸进行了优化。让每个用户拥有自己的表也有一些优势,例如每个表具有单独的安全ACL的能力,每个用户内容的单独表统计信息,以及“意外”表扫描的改进查询性能。
What is a problem though is maintenance. If you leave this in place you must absolutely set up task for automated maintenance, you cannot let this as a manual task for your admins.
但问题是维护。如果将其保留在原位,则必须完全设置自动维护任务,不能将其作为管理员的手动任务。
#2
2
Having all of these tables isn't ideal for any database. After the upload, does the web app use the newly created table? Maybe it gives some feedback to the user on what was uploaded?
拥有所有这些表并不适合任何数据库。上传后,Web应用程序是否使用新创建的表?也许它会向用户提供有关上传内容的一些反馈?
Does your application utilize all of these tables for any reporting etc? You mentioned keeping them around for a few months - not sure why. If not move the contents to a central table and drop the individual table.
您的应用程序是否将所有这些表用于任何报告等?你提到让它们保持几个月 - 不知道为什么。如果没有将内容移动到*表并删除单个表。
Once the backend is taken care of, recode the website to save uploads to a central table. You may need two tables. An UploadHeader table to track the upload batch: who uploaded, when, etc. and link to a detail table with the individual records from the excel upload.
一旦后端得到处理,重新编码网站以将上传保存到*表。您可能需要两张桌子。一个UploadHeader表,用于跟踪上传批次:谁上传,何时等,并链接到详细信息表,其中包含来自Excel上传的各个记录。
#3
1
I think this is definitely a problem that will be a pain later. Why would you need to create a new table every time? Unless there is a really good reason to do so, I would not do it.
我认为这绝对是一个后来会很痛苦的问题。为什么每次都需要创建一个新表?除非有充分的理由这样做,否则我不会这样做。
The best way would be to simply create an ID and associate all uploaded data with an ID, all in the same table. This will require some work on your part, but it's much safer and more manageable to boot.
最好的方法是简单地创建一个ID并将所有上传的数据与ID相关联,所有这些都在同一个表中。这将需要您的一些工作,但它更安全,更易于管理。
#4
0
I will suggest you to store these data in a single table. At the server side you can create a console from where user/operator could manually start the task of freeing up the table entries. You can ask them for range of dates whose data is no longer needed and the same will be deleted from the db.
我建议您将这些数据存储在一个表中。在服务器端,您可以创建一个控制台,用户/操作员可以从中手动启动释放表条目的任务。您可以询问他们不再需要数据的日期范围,并且将从数据库中删除相同的日期。
You can take a step ahead and set a database trigger to wipe the entries/records after a specified time period. You can again add the UI from where the User/Operator/Admin could set these data validity limit
您可以提前一步并设置数据库触发器以在指定的时间段后擦除条目/记录。您可以再次添加用户/操作员/管理员可以设置这些数据有效性限制的UI
Thus you could create the system such that the junk data will be auto deleted after specified time which could again be set by the Admin, as well as provide them with a console using which they can manually delete additional unwanted data.
因此,您可以创建系统,以便在指定时间后自动删除垃圾数据,管理员可以再次设置垃圾数据,并为他们提供一个控制台,使用该控制台可以手动删除其他不需要的数据。
I guess I am way too late for the party though..!! :P Anyways Hope it helps some time later...!! :)
我想我现在为派对来得太晚了!! !! :P无论如何希望它能帮助一段时间后...... !! :)
All the best..!! :)
祝一切顺利..!! :)
#1
3
Having many tables is not an issue for the engine. The catalog metadata is optimized for very large sizes. There are also some advantages on having each user own its table, like ability to have separate security ACLs per table, separate table statistics for each user content and not least improve query performance for the 'accidental' table scan.
拥有多个表不是引擎的问题。目录元数据针对非常大的尺寸进行了优化。让每个用户拥有自己的表也有一些优势,例如每个表具有单独的安全ACL的能力,每个用户内容的单独表统计信息,以及“意外”表扫描的改进查询性能。
What is a problem though is maintenance. If you leave this in place you must absolutely set up task for automated maintenance, you cannot let this as a manual task for your admins.
但问题是维护。如果将其保留在原位,则必须完全设置自动维护任务,不能将其作为管理员的手动任务。
#2
2
Having all of these tables isn't ideal for any database. After the upload, does the web app use the newly created table? Maybe it gives some feedback to the user on what was uploaded?
拥有所有这些表并不适合任何数据库。上传后,Web应用程序是否使用新创建的表?也许它会向用户提供有关上传内容的一些反馈?
Does your application utilize all of these tables for any reporting etc? You mentioned keeping them around for a few months - not sure why. If not move the contents to a central table and drop the individual table.
您的应用程序是否将所有这些表用于任何报告等?你提到让它们保持几个月 - 不知道为什么。如果没有将内容移动到*表并删除单个表。
Once the backend is taken care of, recode the website to save uploads to a central table. You may need two tables. An UploadHeader table to track the upload batch: who uploaded, when, etc. and link to a detail table with the individual records from the excel upload.
一旦后端得到处理,重新编码网站以将上传保存到*表。您可能需要两张桌子。一个UploadHeader表,用于跟踪上传批次:谁上传,何时等,并链接到详细信息表,其中包含来自Excel上传的各个记录。
#3
1
I think this is definitely a problem that will be a pain later. Why would you need to create a new table every time? Unless there is a really good reason to do so, I would not do it.
我认为这绝对是一个后来会很痛苦的问题。为什么每次都需要创建一个新表?除非有充分的理由这样做,否则我不会这样做。
The best way would be to simply create an ID and associate all uploaded data with an ID, all in the same table. This will require some work on your part, but it's much safer and more manageable to boot.
最好的方法是简单地创建一个ID并将所有上传的数据与ID相关联,所有这些都在同一个表中。这将需要您的一些工作,但它更安全,更易于管理。
#4
0
I will suggest you to store these data in a single table. At the server side you can create a console from where user/operator could manually start the task of freeing up the table entries. You can ask them for range of dates whose data is no longer needed and the same will be deleted from the db.
我建议您将这些数据存储在一个表中。在服务器端,您可以创建一个控制台,用户/操作员可以从中手动启动释放表条目的任务。您可以询问他们不再需要数据的日期范围,并且将从数据库中删除相同的日期。
You can take a step ahead and set a database trigger to wipe the entries/records after a specified time period. You can again add the UI from where the User/Operator/Admin could set these data validity limit
您可以提前一步并设置数据库触发器以在指定的时间段后擦除条目/记录。您可以再次添加用户/操作员/管理员可以设置这些数据有效性限制的UI
Thus you could create the system such that the junk data will be auto deleted after specified time which could again be set by the Admin, as well as provide them with a console using which they can manually delete additional unwanted data.
因此,您可以创建系统,以便在指定时间后自动删除垃圾数据,管理员可以再次设置垃圾数据,并为他们提供一个控制台,使用该控制台可以手动删除其他不需要的数据。
I guess I am way too late for the party though..!! :P Anyways Hope it helps some time later...!! :)
我想我现在为派对来得太晚了!! !! :P无论如何希望它能帮助一段时间后...... !! :)
All the best..!! :)
祝一切顺利..!! :)