即时创建表格 [英] Creating Tables on the Fly

问题描述

我有一个带有ODBC链接表的Access 2000 MDB到SQL Server 7返回

end。我目前在前端文件中有一个选择表，

用户用来选择记录。该表有两个字段 - primary

key（匹配main，SQL Server表的主键）和boolean

字段。该表链接到异构内连接中的主表。


我想将表移动到后端，同时仍然给每个

机器一组独特的选择。使用一个机器名称为

的大型表作为主键的一部分实际上会降低速度。所以我正在考虑使用一系列表格来确定

，其中每台机器在后面都有自己的表格

结束选择。机器名称将包含在特定的

选择表名称中，当数据库打开以指向该机器时，前端链接将被动态修改

后端选择

表。


这需要在

中有大约50-100个个人选择表结束数据库。此外，如果在该机器上打开数据库

时机器没有表格，那么该表格将在运行中创建，

填充，并且通过ODBC链接指向。


任何人都会看到这种方法有任何问题，特别是创建表格

即时，然后立即使用它，以及因为有很多小桌子在跑来跑去？感谢您的任何意见！


Neil

I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back
end. I currently have a selections table in the front end file which the
users use to make selections of records. The table has two fields -- primary
key (which matches primary key of main, SQL Server table), and a boolean
field. The table is linked to the main table in a heterogeneous inner join.

I''m looking to move the table to the back end, while still giving each
machine a unique set of selections. Using one large table with machine name
as part of the primary key actually slows things down. So I''m considering
using a series of tables, where each machine has its own table in the back
end for selections. The machine name would be incorporated in the particular
selections table name, and the front end link would be modified on the fly
when the database is opened to point to that machine''s back end selections
table.

This would require having about 50-100 individual selections tables in the
back end database. Also, if a machine doesn''t have a table when the database
is opened on that machine, then that table would be created on the fly,
populated, and pointed to via the ODBC link.

Anyone see any problems with this approach, specifically creating the table
on the fly and then immediately using it, as well as having that many little
tables running around? Thanks for any input!

Neil

推荐答案

>加工一套独特的选择。使用一个带有机器

> machine a unique set of selections. Using one large table with machine

名称的大型表作为主键的一部分实际上减慢了速度。所以我在考虑
...

这需要在
后端数据库中有大约50-100个单独的选择表。

name as part of the primary key actually slows things down. So I''m
considering
...

This would require having about 50-100 individual selections tables in the
back end database.

你在表中有50-100行，你认为这会减慢它的速度吗？即使没有聚集索引或任何索引，我也很难相信

，你可以根据
三位数范围。牺牲可管理性似乎很荒谬，至少对我来说是个b $ b。

You have 50-100 rows in the table and you think that slows it down? Even
without a clustered index or any index at all, I find it hard to believe
that you can perceive any slowness whatsoever based on a scan of rows in the
three figure range. And to sacrifice manageability for that seems absurd,
at least to me.

Neil（no****@nospam.net）写道：

Neil (no****@nospam.net) writes:

我有一个Access 2000 MDB与ODBC链接表到SQL Server 7返回结束。我目前在前端文件中有一个选择表，
用户可以使用它来选择记录。该表有两个字段 -
主键（与main，SQL Server表的主键匹配）和一个
布尔字段。该表链接到异构
内连接中的主表。

我正在寻找将表移动到后端，同时仍然给每台
机器独特的选择。使用一个带有机器名称的大表作为主键的一部分实际上减慢了速度。所以我在考虑使用一系列表格，每台机器在后端都有自己的表格供选择。机器名称将被合并到特定的选择表名称中，当数据库打开以指向该机器的后面时，前端链接将被动态修改。结束选择表。
...
任何人都会看到这种方法有任何问题，特别是动态创建
表然后立即使用它，以及拥有那些
许多小桌子跑来跑去？感谢您的任何意见！

I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back
end. I currently have a selections table in the front end file which the
users use to make selections of records. The table has two fields --
primary key (which matches primary key of main, SQL Server table), and a
boolean field. The table is linked to the main table in a heterogeneous
inner join.

I''m looking to move the table to the back end, while still giving each
machine a unique set of selections. Using one large table with machine
name as part of the primary key actually slows things down. So I''m
considering using a series of tables, where each machine has its own
table in the back end for selections. The machine name would be
incorporated in the particular selections table name, and the front end
link would be modified on the fly when the database is opened to point
to that machine''s back end selections table.
...
Anyone see any problems with this approach, specifically creating the
table on the fly and then immediately using it, as well as having that
many little tables running around? Thanks for any input!

是的，我看到了问题。根本不要去那里。这不是你使用

关系数据库的方式。在单个

表中将该机器名称作为PK的一部分。如果只有一张桌子，放慢速度，调查原因

而不是诉诸于kludges。


-

Erland Sommarskog ，SQL Server MVP， es****@sommarskog.se


联机丛书适用于SQL Server SP3
http：/ /www.microsoft.com/sql/techinf...2000/books.asp

Yes, I see problems. Simply don''t go there. This is not the way you use a
relational database. Make that machine name part of the PK in the single
table. If having one single table, slows things down, investigate why
instead of resorting to kludges.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

您误读了我的帖子。我说50-100个表，而不是50-100行。


Aaron Bertrand [SQL Server MVP]" < TE ***** @ dnartreb.noraa>在消息中写道

news：ew ************** @ TK2MSFTNGP09.phx.gbl ...

You misread my post. I said 50-100 tables, not 50-100 rows.

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:ew**************@TK2MSFTNGP09.phx.gbl...

加工一组独特的选择。使用一个带有机器名称的大表作为主键的一部分实际上减慢了速度。所以我在考虑
...

这需要在后端数据库中有大约50-100个单独的选择表。

machine a unique set of selections. Using one large table with machine
name as part of the primary key actually slows things down. So I''m
considering
...

This would require having about 50-100 individual selections tables in
the back end database.

表格中有50-100行，您认为这会减慢它的速度吗？即使没有聚集索引或任何索引，我也很难相信，你可以根据三个数字范围内的行扫描来感知任何缓慢。为此牺牲可管理性似乎很荒谬，至少对我而言。

You have 50-100 rows in the table and you think that slows it down? Even
without a clustered index or any index at all, I find it hard to believe
that you can perceive any slowness whatsoever based on a scan of rows in
the three figure range. And to sacrifice manageability for that seems
absurd, at least to me.

这篇关于即时创建表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！