DB Bloat：怀疑与I / O有什么关系？ [英] DB Bloat: Suspect Something To Do With I/O?

问题描述

我知道*我在几年前来过这里，但不能为我的生活

召回细节。


我的应用程序在6-8小时批量运行的

课程中从大约5 megs增长到超过100 megs，执行大约1.2 $

的顺序百万计算。


后端也在增长（从大约50 megs到半gig），但是我希望这可以用来计算压缩它这个过程。


困扰我的是应用程序的增长。


- 所有表都是后端的链接和/或工作数据库。

- 我已经完成了I / O代码（并且没有太多内容）它看起来好像

喜欢每次我打开一个记录集，我。关闭它并设置它= Nothing

退出例程。

- 同上查询对象。无论什么时候设置myQuery =，它都会被一个集合平衡

myQuery = Nothing。


我发现唯一可疑的事情就是一个例行程序我把记录集弄清楚，然后

..关闭它并设置它=退出例程中没有任何东西（没有错误捕获 - 所以

它不会抛出错误没有什么是开放的......但是从不在常规中使用它。

它是死的对象可以这么说。

在我过去几年模仿的情况下，每次我做*某事*时，数据库正在接受每桶4美元的价格。但是我不记得是什么......


任何人？

-

PeteCresswell

解决方案

"（Pete Cresswell）" < [e-mail]>写在

新闻：dn ******************************** @ 4ax.com：

- 我已经逐步完成了I / O代码（并且没有太多内容）
看起来每次打开记录集时，我。关闭它并在退出例程之前设置它= Nothing。
- 同上查询对象。无论什么时候设置myQuery =，它都是通过Set myQuery = Nothing来平衡的。

您使用的是QueryDefs吗？如果是这样，我知道有人说如果你用b $ b b使用临时的（没有名字），他们不会膨胀数据库，但是我的

经验说是不是这样的。


我可能想知道的另一件事是你的记录集是否正在使用

保存的查询 - 也许他们在这个过程中重新编译？ br />

-

David W. Fenton http://www.bway.net/~dfenton

dfenton at bway dot net http://www.bway.net/~dfassoc

2005年3月2日星期三02 ：17：27 GMT，David W. Fenton

< dX ******** @ bway.net.invalid>写道：

您使用的是QueryDefs吗？如果是这样，我知道人们会说如果你使用临时的（没有名字），他们就不会臃肿，但我的经验表明情况并非如此。


我正在使用预定义的查询，实例化它们，设置参数，

然后打开基于相同的记录集

我可能想知道的另一件事是你的记录集是否正在使用
保存的查询 - 也许他们在这个过程中重新编译？

你突然想到那一个。如上所述，我基于预定义/保存/命名查询的记录集

。听起来好像应用程序可能会立即编译查询？

PeteCresswell< [e-mail]>写在

新闻：sc ******************************** @ 4ax.com：

2005年3月2日星期三02:17:27 GMT，David W. Fenton
< dX ******** @ bway。 net.invalid>写道：

您使用的是QueryDefs吗？如果是这样，我知道人们会说，如果你使用临时的（没有名字），他们不会臃肿db，但我的经验说不是这样。

我正在使用预定义的查询，实例化它们，设置
参数，然后打开基于相同的记录集

然后它们是你打开的临时querydef，因为那是'/ b
是为参数赋值的唯一方法。根据我的经验，

导致膨胀。

我可能想知道的另一件事是你的记录集是否<使用保存的查询 - 也许他们在
过程中重新编译？

你对那个问题进行了重新审视。如上所述，我基于预定义/保存/命名查询的记录集。听起来好像
应用程序正在动态编译查询？

我不知道什么时候重新编译保存的查询。我认为只有当一个契约后第一次保存查询时，它才是

。


我更倾向于怀疑querydef参数赋值。


我不会在只在代码中打开的查询中使用参数。

的确，我通常不会使用已保存的查询来打开记录集

。您认为通过使用参数查询获得的结果是什么？b
你打算使用WHERE

子句在SQL上打开记录集是不会得到的飞？


我有一种模糊的感觉，参数可以加快速度（因为输入的

数据类型是已知的），但我可以''得出任何支持它的
。


-

David W. Fenton http://www.bway.net/~dfenton

dfenton at bway dot net http://www.bway.net/~dfassoc

I *know* I''ve been here before some years back, but can''t for the life of me
recall the details.

My application is growing from about five megs to over a hundred megs in the
course of a 6-8 hour batch run that performs something on the order of 1.2
million computations.

The back end also grows (from about 50 megs to a half gig), but I would expect
this and plan to compress it after the process.

What''s bothering me is the app''s growth.

- All tables are links to the back end and/or a work DB.
- I''ve stepped through the I/O code (and there isn''t much of it) and it looks
like every time I open a recordset, I .Close it and set it = Nothing before
exiting the routine.
- Ditto query objects. Whenever do a Set myQuery=, it''s balanced out by a Set
myQuery=Nothing.

The only suspicious thing I found is one routine where I Dim a recordset, then
..Close it and set it = Nothing in the exit routine (with no error trapping - so
it doesn''t throw an error bc nothing''s open)...but never use it in the routine.
It''s a "Dead" object so-to-speak.
In the situation I dimly remember from years past, the DB was picking up about
4k every time I did *something*....but I can''t remember what...

Anybody?
--
PeteCresswell

解决方案

"(Pete Cresswell)" <[e-mail]> wrote in
news:dn********************************@4ax.com:

- I''ve stepped through the I/O code (and there isn''t much of it)
and it looks like every time I open a recordset, I .Close it and
set it = Nothing before exiting the routine.
- Ditto query objects. Whenever do a Set myQuery=, it''s balanced
out by a Set myQuery=Nothing.

Are you using QueryDefs? If so, I know that people say that if you
use temporary ones (no name), they don''t bloat the db, but my
experience says that is not the case.

The other thing I might wonder about is if your recordsets are using
saved queries -- perhaps they are recompiling during the process?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

On Wed, 02 Mar 2005 02:17:27 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Are you using QueryDefs? If so, I know that people say that if you
use temporary ones (no name), they don''t bloat the db, but my
experience says that is not the case.
I am using pre-defined queries, instantiating them, setting the parms,
and then opening a recordset based on same

The other thing I might wonder about is if your recordsets are using
saved queries -- perhaps they are recompiling during the process?

You went over my head on that one. As above, I''m basing recordsets
on pre-defined/saved/named queries. Sounds like maybe the app is
compiling the query on-the-fly?

PeteCresswell <[e-mail]> wrote in
news:sc********************************@4ax.com:

On Wed, 02 Mar 2005 02:17:27 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Are you using QueryDefs? If so, I know that people say that if you
use temporary ones (no name), they don''t bloat the db, but my
experience says that is not the case.

I am using pre-defined queries, instantiating them, setting the
parms, and then opening a recordset based on same

Then they are temporary querydefs that you''re opening, since that''s
the only way to assign values to parameters. In my experience, that
leads to bloat.

The other thing I might wonder about is if your recordsets are
using saved queries -- perhaps they are recompiling during the
process?

You went over my head on that one. As above, I''m basing
recordsets on pre-defined/saved/named queries. Sounds like maybe
the app is compiling the query on-the-fly?

I don''t know when a saved query gets recompiled. I thought it was
only when the query was saved the first time after a compact.

I''m more inclined to suspect the querydef parameter assignment.

I don''t use parameters in queries that are opened only in code.
Indeed, I generally don''t use saved queries for opening recordsets
at all. What do you think you''re getting by using parameter queries
that you wouldn''t get with opening recordsets from SQL with a WHERE
clause constructed on-the-fly?

I have a vague feeling that parameters can speed things up (as the
data type of the input is known), but I can''t come up with anything
that supports that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

这篇关于DB Bloat：怀疑与I / O有什么关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！