SQL Server转换 [英] SQL Server Conversion
问题描述
客户对他们的大型Access应用程序感到恐慌，
已经运行了很长时间，并且已经有数百条记录用于
。他们在明年有一个大项目将会导致大量使用数据库并添加相当多的新数据（尽管我可以他们认为他们增加了超过数百美元的记录，而这些记录根本不会改变目前的
性能概况。
如果有SQL Server转换，我的问题是：
1.只做一个ODBC升迁，或者
2.将整个该死的东西转换为ADO。
显然，＃1将变得更加容易。是的，我知道一些
的地方我需要大幅改变
应用程序的工作方式（尽管它无处加载任何大数字当然是
记录）。我可以很容易地想到几个领域，即服务器端处理将大大提高性能。
我的直觉说要尽可能少地改变，只是使用ODBC
链接表，然后修复
转换后效率低下的所有内容。这意味着很多存储过程。
一个未绑定的表单（数据输入量最大的地方需要
）现在都是用DAO完成的。也许它会从
转换为ADO中受益？实际上，它是具有绑定列表的UI的一部分
子表单和未绑定的详细信息子表单，因此我可以使用单个ADO
记录集作为两者的记录源（或是形式记录源
DAO默认情况下？），也许（我已经想到了那个时间很多
时间）。
关于如何为自己回答这个问题的任何建议？什么
我应该看一下特定的东西来帮助评估这个问题？
-
David W. Fenton http://www.bway.net/~dfenton
bway dot net的dfenton http://www.bway.net/~dfassoc
A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can''t conceive of them adding more than than
10s of thousands of records, which won''t change the current
performance profile at all).
If there is a SQL Server conversion, my question is this:
1. just do an ODBC upsizing, OR
2. convert the whole damned thing to ADO.
Obviously, #1 is going to be a lot easier. Yes, I''m aware of a
number of places where I''ll need to drastically alter the way the
application works (though it nowhere loads any large number of
records, of course). And I can easily think of several areas where
server-side processing will vastly improve performance.
My gut says to change as little as necessary, and just go with ODBC
linked tables and then fix all the things that are inefficient when
converted. This means a lot of stored procedures.
The one unbound form (where the highest volume of data entry takes
place) is now all done with DAO. Perhaps it would benefit from
conversion to ADO? Indeed, it is part of a UI with a bound list
subform and an unbound detail subform, so I could use a single ADO
recordset for the recordsource of both (or are form recordsources
DAO by default?), perhaps (I''ve thought of that one for quite some
time).
Any suggestions as to how to answer this question for myself? What
specific things should I look at to help evaluate the question?
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
推荐答案
" David W. Fenton" < DX ******** @ bway.net.invalid>在消息中写道
news：94 *************************** @ 24.168.128.74 ..。
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .
一个客户端对他们的大型Access应用程序感到恐慌，这个应用程序已经运行了很长时间，已经有数以万计的记录。他们在明年有一个大项目将导致大量使用数据库并添加相当多的新数据（尽管我无法想象他们会增加更多数据）而不是成千上万的记录，根本不会改变当前的性能概况。
如果有SQL Server转换，我的问题是这个：
1.只做一个ODBC升迁，或者
2.将整个该死的东西转换成ADO。
显然，＃1正在进行要容易得多。是的，我知道有很多地方我需要彻底改变
应用程序的工作方式（尽管它无法载入任何大量的
记录，课程）。我可以很容易地想到服务器端处理将大大提高性能的几个方面。
我的直觉说要根据需要进行更改，并且只需要使用ODBC链接
表格，然后修复转换时效率低下的所有内容。这意味着很多存储过程。
一个未绑定的表单（数据输入量最大的地方）现在都是用DAO完成的。也许它会从
转换为ADO中受益？实际上，它是具有绑定列表子窗体和未绑定详细信息子表单的UI的一部分，因此我可以使用单个ADO
记录集作为两者的记录源（或者是表单记录源
DAO默认情况下？），也许（我已经想过那个时间了很长一段时间）。
关于如何为自己回答这个问题的任何建议？我应该看看有哪些具体的东西来帮助评估这个问题？
A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can''t conceive of them adding more than than
10s of thousands of records, which won''t change the current
performance profile at all).
If there is a SQL Server conversion, my question is this:
1. just do an ODBC upsizing, OR
2. convert the whole damned thing to ADO.
Obviously, #1 is going to be a lot easier. Yes, I''m aware of a
number of places where I''ll need to drastically alter the way the
application works (though it nowhere loads any large number of
records, of course). And I can easily think of several areas where
server-side processing will vastly improve performance.
My gut says to change as little as necessary, and just go with ODBC
linked tables and then fix all the things that are inefficient when
converted. This means a lot of stored procedures.
The one unbound form (where the highest volume of data entry takes
place) is now all done with DAO. Perhaps it would benefit from
conversion to ADO? Indeed, it is part of a UI with a bound list
subform and an unbound detail subform, so I could use a single ADO
recordset for the recordsource of both (or are form recordsources
DAO by default?), perhaps (I''ve thought of that one for quite some
time).
Any suggestions as to how to answer this question for myself? What
specific things should I look at to help evaluate the question?
我只能告诉你我用SQL Server管理了一个大项目
使用ODBC / DAO好几年了，发现我没有任何问题
的性能。我主要使用ODBC链接表，但在我看到
性能优势时，我使用Pass-Throughs和Stored Procedures。你会感到非常惊讶，但是我不需要经常这么做。
我发现Jet / ODBC在传球方面表现非常出色SQL到服务器
，即使我不打算强迫它。当我进行转换
时，我完全按照你的描述做了。我从链接的Access表更改为
ODBC链接的SQL Server表，然后（没有更改任何其他内容）我
测试了应用程序。
我确实需要修改几件事来让它们工作。还有几个人可以获得高达标准杆的成绩，但同样，你可能想象的并不是那么多东西。一旦你达到这个位置，你就可以把应用程序
上线，并以更悠闲的速度仔细检查你在哪里可以通过额外的更改获得一些性能提升。
我的意见是你真的不需要所有的处理。在
服务器上，以及所有选择。只要服务器不是b / b
返回更多行/列而不是网络，
任何其他处理在客户端上的速度与在服务器。
如今，大多数客户端PC都拥有比所需更多的CPU。
这个策略允许你保留所有DAO代码（我从来没有写了一行
of ADO）并坚持使用熟悉的东西。对我来说，如果这是一个重要的应用程序
已经长期使用，那么通过引入一个新的后端，你将有更多的担心
。同时引入一系列其他
的变化（这可能是完全没必要的）只是在你需要追逐问题的时候就会出现问题。
大约一年前，我浏览了整个应用程序并查看了每一个
查询，看看哪些可以更改为Pass-Throughs或SP。我发现
三（100左右）作为PT表现得更好。在我的
体验中，SP更多的是解决安全问题，而不是性能
问题。如果你需要做一些有条件的
处理，使用临时表等等，它们很棒，但我只是看不到很多情况
我在哪里不能拉入所需的行并在VBA中完成所有相同的处理
同样快（尽管我写起来容易得多）。
在新应用程序中ADO可能实际上有一些优势（仍然等待一些已发布的数据），但对于已经成熟的应用程序已经满足其要求的b $ b使用DAO似乎是一种令人难以置信的浪费时间来尝试转换它。
-
我没有查看电子邮件帐户附上
到此消息。发送给... ...
在Hunter dot的RBrandt com
I can only tell you that I have managed a large project with SQL Server
using ODBC/DAO for several years and find that I have no problems at all
with performance. I mostly use ODBC linked tables, but where I see a
performance advantage, I use Pass-Throughs and Stored Procedures. You''d be
amazed just how infrequently I need to do either one though.
I find that Jet/ODBC does a very good job at passing the SQL to the server
even when I don''t go out of my way to force it. When I made the conversion
I did exactly as you described. I changed from linked Access tables to
ODBC linked SQL Server tables and then (without changing anything else) I
tested the app.
I did have to modify several things to "just make them work" and several
others to get performance up to par, but again, it was not so many things
as you might imagine. Once you achieve that position you can put the app
on-line and at a more leisurely pace make a closer examination of where you
might find some performance gains with additional changes.
My opinion is that you really don''t need "all of the processing" on the
server so much as "all of the selecting". As long as the server is not
returning more rows/columns than necessary and bogging down the network,
any other processing will be about as fast on the client as on the server.
Nowadays, most client PCs have way more CPU than required anyway.
This strategy allows you to retain all DAO code (I''ve never written a line
of ADO) and stay with what''s familiar. To me if this is an important app
that''s been in long term use, then you will have more than enough to worry
about by introducing a new back end. Introducing a whole host of other
changes at the same time (that might be completely unnecessary) just clouds
the waters when you do need to chase a problem down.
About a year ago I went through my entire app and looked at every single
query to see which could be changed to Pass-Throughs or SPs. I found about
three (of 100 or so) that performed any better as a PT. And in my
experience SPs are more of an answer to security problems than performance
issues. They are great if you need to do a bunch of conditional
processing, use temp tables, and the like, but I just don''t see many cases
where I can''t pull in the required rows and do all of that same processing
in VBA just as quickly (while being much easier for me to write).
There may in fact be some advantages to ADO in NEW applications (still
waiting for some published data on this), but for a mature app already
fulfilling its requirements using DAO it just seems like an incredible
waste of time to attempt converting it.
--
I don''t check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
在我看来，你可以将所有的Access表导入到Sql
服务器，在sql Server中建立你的关系，为数据输入添加一些触发器
，但我肯定会用ADO来获取数据
条目。并且对于错误处理，您将需要存储过程来在sql server端提交
或回滚内容（取决于用户密集的方式
您的项目）。但是sql server的健壮性和性能将比你回报你的努力更多。在我的地方的人让我用ODBC连接
用于adhoc的东西。这似乎工作正常。但是我用
不能用ODBC自动化任何东西。
在sql Server和Access之间为我工作的经验法则
是ODBC对于adhoc查询非常有效。但是对于通过Access或Web表单输入的严肃数据，以及数据处理和数据报告的自动化，我会坚持使用sp'和ADO。
Rich
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
It just seems to me that you could import all the Access tables to Sql
Server, establish your relationships in sql Server, add some triggers
for the data entry, but I would definitely go with ADO for the data
entry. And for error handling you will need stored procedures to commit
or rollback stuff on the sql server end (depending on how user intensive
your project is). But the robustness and performance of sql server will
more than repay you for your efforts. People at my place had me connect
them with ODBC for adhoc stuff. This seems to work fine. But I
wouldn''t automate anything with ODBC.
The rule of thumb that has worked for me between sql Server and Access
is that ODBC works pretty well for adhoc queries. But for serious data
entry through Access or Web forms, and data crunching and automation of
data reporting, I would stick with sp''s and ADO.
Rich
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
我们已经有了很好的经验，只需增加后端使用ODBC链接表来增加后端应用程序，然后使用ODBC链接表。
我们得到了最好的使用Access 2002进行升迁的结果，只需升级结构，然后使用SQL Server Data
转换服务来移植数据。
在一个应用程序中有一些部分未绑定的表单，我使用
ADO将数据移动到表单和
表之间，偶尔也会使用VBA布尔数据（复选框） ）
di d无法正确转换为SQL Server Bit数据类型。
我写了一些SQL Server存储过程来明确地进行翻译
，这似乎解决了这个问题。 />
Ragnar
" David W. Fenton" < DX ******** @ bway.net.invalid>在消息中写道
news：94 *************************** @ 24.168.128.74 ..。
We have had good experiences with just upsizing the back end
for Access 2000 applications then using ODBC linked tables.
We got the best results by using Access 2002 for the upsizing,
just upsizing the structure, then using the SQL Server Data
Transformation Service to port the data.
In one app there are some partially unbound forms where I used
ADO to move the data the data between the forms and the
tables, and occasionally VBA Boolean data (Checkboxes)
did not translate properly to SQL Server Bit data type.
I wrote some SQL Server Stored Procs to do the translation
explicitly and that appears to have fixed the problem.
Ragnar
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .
一个客户端对他们的大型Access应用程序感到恐慌，这个应用程序已经运行了很长时间，已经有数以万计的记录。他们在明年有一个大项目将导致大量使用数据库并添加相当多的新数据（尽管我无法想象他们会增加更多数据）而不是成千上万的记录，根本不会改变当前的性能概况。
如果有SQL Server转换，我的问题是这个：
1.只做一个ODBC升迁，或者
2.将整个该死的东西转换成ADO。
显然，＃1正在进行要容易得多。是的，我知道有很多地方我需要彻底改变
应用程序的工作方式（尽管它无法载入任何大量的
记录，课程）。我可以很容易地想到服务器端处理将大大提高性能的几个方面。
我的直觉说要根据需要进行更改，并且只需要使用ODBC链接
表格，然后修复转换时效率低下的所有内容。这意味着很多存储过程。
一个未绑定的表单（数据输入量最大的地方）现在都是用DAO完成的。也许它会从
转换为ADO中受益？实际上，它是具有绑定列表子窗体和未绑定详细信息子表单的UI的一部分，因此我可以使用单个ADO
记录集作为两者的记录源（或者是表单记录源
DAO默认情况下？），也许（我已经想过那个时间了很长一段时间）。
关于如何为自己回答这个问题的任何建议？我应该看一下具体的事情来帮助评估这个问题？
-
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can''t conceive of them adding more than than
10s of thousands of records, which won''t change the current
performance profile at all).
If there is a SQL Server conversion, my question is this:
1. just do an ODBC upsizing, OR
2. convert the whole damned thing to ADO.
Obviously, #1 is going to be a lot easier. Yes, I''m aware of a
number of places where I''ll need to drastically alter the way the
application works (though it nowhere loads any large number of
records, of course). And I can easily think of several areas where
server-side processing will vastly improve performance.
My gut says to change as little as necessary, and just go with ODBC
linked tables and then fix all the things that are inefficient when
converted. This means a lot of stored procedures.
The one unbound form (where the highest volume of data entry takes
place) is now all done with DAO. Perhaps it would benefit from
conversion to ADO? Indeed, it is part of a UI with a bound list
subform and an unbound detail subform, so I could use a single ADO
recordset for the recordsource of both (or are form recordsources
DAO by default?), perhaps (I''ve thought of that one for quite some
time).
Any suggestions as to how to answer this question for myself? What
specific things should I look at to help evaluate the question?
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
这篇关于SQL Server转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
