N
Neil
This was a good idea. I tried it. Same results.
Neil
This was a good idea. I tried it. Same results.
Neil
N
Neil
Well, I was premature in stating that this had been resolved. While
encrypting the view copy *did* in fact increase performance, there was a
price to pay. Apparently, when I encrypted the view copy and then refreshed
the original view's link, the original view's link lost its virtual primary
key. (Don't know why, but that's what happened. And I was able to reproduce
this phenomenon.) And, apparently, not having the virtual primary key made
the linked view scroll faster.
To test this I created two links to the same view: one with, and one without
virtual primary key. The one without the virtual primary key scrolled fast,
just like the ADP file; the one with the virtual primary key was slow.
I ran a trace on these two and on the ADP file when opening and scrolling
the view, and I think I see what's going on here.
When opening the view in the ADP file, profiler shows the following:
SELECT * FROM "vwWebMaintDuplicates"
However, when opening the linked view with the virtual primary key in place
from the MDB file, I get the following:
SELECT "dbo"."vwWebMaintDuplicates"."Index" FROM
"dbo"."vwWebMaintDuplicates"
sp_prepare @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7
int,@P8 int,@P9 int,@P10 int', N'SELECT
"Index","TITLE","AUTHILL1","attFirstEdition","attSigned","SignedCond","YRPUB","PRICE","Web","Status","WebStatusPending","ActivateDate","DeactivateDate","WebAddedBatchID","AllowDuplicate","WebAction","WebActionPending","DateModified","DateWebActionApplied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10', 1 select @P1
sp_execute 4, 1364, 1971, 1978, 2303, 3140, 3480, 3605, 4962, 6747, 6749
SELECT
Index","TITLE","AUTHILL1","attFirstEdition","attSigned","SignedCond","YRPUB","PRICE","Web","Status","WebStatusPending","ActivateDate","DeactivateDate","WebAddedBatchID","AllowDuplicate","WebAction","WebActionPending","DateModified","DateWebActionApplied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10
"Index" is the name of the primary key field in the main table in the view,
and it's the field that's used as the virtual primary key. The numbers 1364,
1971, etc., above, are the Index values for the first records that are
returned.
Thus, the MDB linked view with virtual primary key first gets a list of pk
values, then grabs the records ten at a time, specifying the pk values to
get.
This would explain why it's slow in scrolling -- it only gets the records in
sets of ten and has to keep grabbing them.
When I open the linked view in the MDB without the virtual primary key,
profiler shows:
SELECT "Index" ,"TITLE" ,"AUTHILL1" ,"attFirstEdition" ,"attSigned"
,"SignedCond" ,"YRPUB" ,"PRICE" ,"Web" ,"Status" ,"WebStatusPending"
,"ActivateDate" ,"DeactivateDate" ,"WebAddedBatchID" ,"AllowDuplicate"
,"WebAction" ,"WebActionPending" ,"DateModified" ,"DateWebActionApplied"
,"JIT" ,"MImage" ,"HImage" ,"AdCode" ,"OnWeb" FROM
"dbo"."vwWebMaintDuplicates"
This is essentially the same as what's going on in the ADP file, except that
it's specifying a field list, instead of "*".
Thus, without the virtual primary key, the MDB link is fast, like the ADP
file, because it's just getting a set of records. With the virtual primary
key, though, it's getting the list of pk's, and then grabbing records ten at
a time.
This appears to be an inherent property of the way the MDB file/ODBC driver
handles virtual primary keys, and doesn't appear to be anything I can
change. So the question still remains: why is it going slowly when it used
to not, and what can I do about it? Is there anyway to tell it to get all of
it's "sets of ten" at once, when the view is opened, rather than waiting
until the view is scrolled? Are there any other workarounds or settings that
can be tried here?
Anyway, we seem to be back at square one. But at least there's a bit more
knowledge now, and at least it explains the performance increase when the
encryption took place (though not why the virtual pk was lost in the first
place).
Thanks!
Neil
encrypting the view copy *did* in fact increase performance, there was a
price to pay. Apparently, when I encrypted the view copy and then refreshed
the original view's link, the original view's link lost its virtual primary
key. (Don't know why, but that's what happened. And I was able to reproduce
this phenomenon.) And, apparently, not having the virtual primary key made
the linked view scroll faster.
To test this I created two links to the same view: one with, and one without
virtual primary key. The one without the virtual primary key scrolled fast,
just like the ADP file; the one with the virtual primary key was slow.
I ran a trace on these two and on the ADP file when opening and scrolling
the view, and I think I see what's going on here.
When opening the view in the ADP file, profiler shows the following:
SELECT * FROM "vwWebMaintDuplicates"
However, when opening the linked view with the virtual primary key in place
from the MDB file, I get the following:
SELECT "dbo"."vwWebMaintDuplicates"."Index" FROM
"dbo"."vwWebMaintDuplicates"
sp_prepare @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7
int,@P8 int,@P9 int,@P10 int', N'SELECT
"Index","TITLE","AUTHILL1","attFirstEdition","attSigned","SignedCond","YRPUB","PRICE","Web","Status","WebStatusPending","ActivateDate","DeactivateDate","WebAddedBatchID","AllowDuplicate","WebAction","WebActionPending","DateModified","DateWebActionApplied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10', 1 select @P1
sp_execute 4, 1364, 1971, 1978, 2303, 3140, 3480, 3605, 4962, 6747, 6749
SELECT
Index","TITLE","AUTHILL1","attFirstEdition","attSigned","SignedCond","YRPUB","PRICE","Web","Status","WebStatusPending","ActivateDate","DeactivateDate","WebAddedBatchID","AllowDuplicate","WebAction","WebActionPending","DateModified","DateWebActionApplied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR "Index"
= @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10
"Index" is the name of the primary key field in the main table in the view,
and it's the field that's used as the virtual primary key. The numbers 1364,
1971, etc., above, are the Index values for the first records that are
returned.
Thus, the MDB linked view with virtual primary key first gets a list of pk
values, then grabs the records ten at a time, specifying the pk values to
get.
This would explain why it's slow in scrolling -- it only gets the records in
sets of ten and has to keep grabbing them.
When I open the linked view in the MDB without the virtual primary key,
profiler shows:
SELECT "Index" ,"TITLE" ,"AUTHILL1" ,"attFirstEdition" ,"attSigned"
,"SignedCond" ,"YRPUB" ,"PRICE" ,"Web" ,"Status" ,"WebStatusPending"
,"ActivateDate" ,"DeactivateDate" ,"WebAddedBatchID" ,"AllowDuplicate"
,"WebAction" ,"WebActionPending" ,"DateModified" ,"DateWebActionApplied"
,"JIT" ,"MImage" ,"HImage" ,"AdCode" ,"OnWeb" FROM
"dbo"."vwWebMaintDuplicates"
This is essentially the same as what's going on in the ADP file, except that
it's specifying a field list, instead of "*".
Thus, without the virtual primary key, the MDB link is fast, like the ADP
file, because it's just getting a set of records. With the virtual primary
key, though, it's getting the list of pk's, and then grabbing records ten at
a time.
This appears to be an inherent property of the way the MDB file/ODBC driver
handles virtual primary keys, and doesn't appear to be anything I can
change. So the question still remains: why is it going slowly when it used
to not, and what can I do about it? Is there anyway to tell it to get all of
it's "sets of ten" at once, when the view is opened, rather than waiting
until the view is scrolled? Are there any other workarounds or settings that
can be tried here?
Anyway, we seem to be back at square one. But at least there's a bit more
knowledge now, and at least it explains the performance increase when the
encryption took place (though not why the virtual pk was lost in the first
place).
Thanks!
Neil
Neil said:
B
Bri
Neil,
You said it was fast before you added the last three fields? Why don't
you remove those fields again, verify that it is indeed still speedy
with the virtual PK in place and see what the profiler says its doing then.
Perhaps there is something about these three fields that is triggering a
change in the execution plan. I know in Access itself (ie an Access BE)
that there are queries that Access will only grab the first so many
records and then continue to get more in the background as you scroll
through. IIRC, there may even be documentation on what triggers this.
Leaving the view without the virtual PK is not an option if you need
to be able to edit this view.
Oh, another thought, try changing the PK field name in the view to
another name. 'Index' might be a reserved word and that might add
overhead/confusion while it resolves that (I know it can figure it out,
but why make it do it if it doesn't need to).
Good and persistant work in tracking things down so far. This is a
strange one.
You said it was fast before you added the last three fields? Why don't
you remove those fields again, verify that it is indeed still speedy
with the virtual PK in place and see what the profiler says its doing then.
Perhaps there is something about these three fields that is triggering a
change in the execution plan. I know in Access itself (ie an Access BE)
that there are queries that Access will only grab the first so many
records and then continue to get more in the background as you scroll
through. IIRC, there may even be documentation on what triggers this.
Leaving the view without the virtual PK is not an option if you need
to be able to edit this view.
Oh, another thought, try changing the PK field name in the view to
another name. 'Index' might be a reserved word and that might add
overhead/confusion while it resolves that (I know it can figure it out,
but why make it do it if it doesn't need to).
Good and persistant work in tracking things down so far. This is a
strange one.
T
Trevor Best
Bri said:
Not necessarily, since the view contains more than 1 base table I doubt
that SQL Server will allow an update to it without use of an "instead
of" trigger.
N
Neil
Bri said:
I tried that before, and it seems the same without those three fields. So
either I was imagining that it was faster, or something else is going on.
The name that Access (automatically) gave the vpk is "__uniqueindex" with
"Index" as its only field.
Indeed. Raises the question: why am I staying with the MDB? Oh, right,
because others said that ADPs are much more problematic, even if better in
some ways.
Neil
N
Neil
FWIW, I've been able to update the view from the MDB file with a virtual pk.
L
Larry Linson
Neil said:
There's an old adage in the computer business: if it doesn't work, it
doesn't matter how fast it is. I think it is a telling point that a number
of respected, early adopters, advocates of ADPs have now abandoned their
use.
That said, in the very modest amount of modification/update work that I have
done with ADPs, I did not encounter any instances in which they just did not
work. Perhaps some of what appeared to be rather unusual design features
were, in fact, workarounds that the original author had to employ. However,
I suspect that most were because he did not realize that the SQL Server
tables had to have a Primary Key to be updateable when used with bound
Forms.
Larry Linson
Microsoft Access MVP
A
Albert D. Kallal
Based on [SQL Server 7.0 info]:
No, the concepts of "code" page in terms of assembler, is NOT related to the
above concept in a database. A "code" page in assembly is often just a
reference to a frame of data (in memory) used for some type of "mapping".
Often this type of mapping is referring to Localization (different languages
in windows for example have a code page, one is different for French,
English etc. You "load", or set a pointer to that page..and thus all
routines now transfer different characters etc correctly).).
I am not sure what the page size used, but I do believe it is 4k. As
computers get faster, and more ram available, the page size tends to go up a
bit. (too large, and you do waste disk i/o).
Remember, access97 (JET 3.x used page locking, and did not support record
locking). JET 4.0 (access 2000 and later) does in fact support record
locking. (what actually happens is the page lock occurs only during a
update...as each record can now be locked individual).
Remember, since records in ms-access now are VARIABLE length, then you need
a means to resolve to SINGLE record. What happens is the index routines
eventually translate WHERE/what BUCKET the record you are looking for. At
this point, when the bucket if found..then a SEQUENTIAL SEARCH occurs for
your record!!. Thus, with small records, you might easily fit 10 records
into a page (note that I am using the word, page, frame, bucket interchange
here...they all mean the same thing!).
This page based systems also explain why often running some update routines
can cause serous file bloat. IF the group of records you just modified now
expands beyond the size of the bucket where the records reside..then you got
split up the page..and re-arrange the records. If you go over the page size
by just ONE byte, you need a WHOLE NEW page, and the all of the "rest" of
the page is wasted. Of course, now that you got a extra almost empty
page..some records can be added without having to expand the file. (this is
why a file grows REAL fast RIGHT after a compact (any adding will cause
spillage out of the tightly packed frame. As the application use settles
down, then each time you modify a record, it is more likely to fit in its
page..and not spill over into another "needed" page).
So, note that each of those page frames are OFTEN referred to as a bucket in
database systems. And, normally, a hash-code scheme is used to resolve the
location of a stored record to that single bucket....then at which time a
sequential search occurs. These buckets are thus the SMALLEST size of data
that the JET engine can work with.
I don't know the answer to the above. However, memo fields can be quite
large in size...and thus really are just a "list" of linked frames. The
limit of the size of a memo field is thus just a question of how many bytes
in the linked list of frames is allowed. For example, if you only allow 1
byte, then you can have from 0 to 255 frames linked sequentially. I also
don't know if the frames have a forward link + backward link (some systems
do). Anyway, lets just assume a forward link byte in each frame. Thus, you
can have 0-255 linked frames x 4k would be your max object size....which is
of course not the case here. In fact, a memo field can be 1 gig in size.
(so, the link list data must be about 32 bits in size..as 16 would not be
enough). So, a large memo is just a sequential list of frames linked
together.
While the data in the disk file is organized in "pages", or so called
frames, the fact is that the operating system (windows) takes parts of the
file that JET is working on, and pulls it into ram anyway. Further, JET
maintains it own cache in ram of "frames". So, after you update, or read a
few records, then they are in ram anyway. In fact, JET can mark a frame for
update..but wait..and do something more important (like read a frame in the
pending list of frames to be read).
You certainly would see some performance improvements by placing a database
in a ram based drive, but those performance improves would be UN-related to
the fact of JET being a "frame", or page based database system.
And, for files of only a few megs, and smaller table sizes, the whole table
gets well cached in memory anyway..
No, the concepts of "code" page in terms of assembler, is NOT related to the
above concept in a database. A "code" page in assembly is often just a
reference to a frame of data (in memory) used for some type of "mapping".
Often this type of mapping is referring to Localization (different languages
in windows for example have a code page, one is different for French,
English etc. You "load", or set a pointer to that page..and thus all
routines now transfer different characters etc correctly).).
I am not sure what the page size used, but I do believe it is 4k. As
computers get faster, and more ram available, the page size tends to go up a
bit. (too large, and you do waste disk i/o).
Remember, access97 (JET 3.x used page locking, and did not support record
locking). JET 4.0 (access 2000 and later) does in fact support record
locking. (what actually happens is the page lock occurs only during a
update...as each record can now be locked individual).
Remember, since records in ms-access now are VARIABLE length, then you need
a means to resolve to SINGLE record. What happens is the index routines
eventually translate WHERE/what BUCKET the record you are looking for. At
this point, when the bucket if found..then a SEQUENTIAL SEARCH occurs for
your record!!. Thus, with small records, you might easily fit 10 records
into a page (note that I am using the word, page, frame, bucket interchange
here...they all mean the same thing!).
This page based systems also explain why often running some update routines
can cause serous file bloat. IF the group of records you just modified now
expands beyond the size of the bucket where the records reside..then you got
split up the page..and re-arrange the records. If you go over the page size
by just ONE byte, you need a WHOLE NEW page, and the all of the "rest" of
the page is wasted. Of course, now that you got a extra almost empty
page..some records can be added without having to expand the file. (this is
why a file grows REAL fast RIGHT after a compact (any adding will cause
spillage out of the tightly packed frame. As the application use settles
down, then each time you modify a record, it is more likely to fit in its
page..and not spill over into another "needed" page).
So, note that each of those page frames are OFTEN referred to as a bucket in
database systems. And, normally, a hash-code scheme is used to resolve the
location of a stored record to that single bucket....then at which time a
sequential search occurs. These buckets are thus the SMALLEST size of data
that the JET engine can work with.
I don't know the answer to the above. However, memo fields can be quite
large in size...and thus really are just a "list" of linked frames. The
limit of the size of a memo field is thus just a question of how many bytes
in the linked list of frames is allowed. For example, if you only allow 1
byte, then you can have from 0 to 255 frames linked sequentially. I also
don't know if the frames have a forward link + backward link (some systems
do). Anyway, lets just assume a forward link byte in each frame. Thus, you
can have 0-255 linked frames x 4k would be your max object size....which is
of course not the case here. In fact, a memo field can be 1 gig in size.
(so, the link list data must be about 32 bits in size..as 16 would not be
enough). So, a large memo is just a sequential list of frames linked
together.
While the data in the disk file is organized in "pages", or so called
frames, the fact is that the operating system (windows) takes parts of the
file that JET is working on, and pulls it into ram anyway. Further, JET
maintains it own cache in ram of "frames". So, after you update, or read a
few records, then they are in ram anyway. In fact, JET can mark a frame for
update..but wait..and do something more important (like read a frame in the
pending list of frames to be read).
You certainly would see some performance improvements by placing a database
in a ram based drive, but those performance improves would be UN-related to
the fact of JET being a "frame", or page based database system.
And, for files of only a few megs, and smaller table sizes, the whole table
gets well cached in memory anyway..
N
Neil
That said, in the very modest amount of modification/update work that I
Not sure I'm following you here. You mean the original author of the ODBC
mechanism for getting SQL records? Sorry for not following.
Neil
Not sure I'm following you here. You mean the original author of the ODBC
mechanism for getting SQL records? Sorry for not following.
Neil
L
Larry Linson
Neil said:
No, I am sorry that I was not clear -- I meant the author of the application
database I had to modify. Although ADP/ADO seemed a bit "quirky" to me, and
not as straightforward as DAO, it did work properly in the areas I had to
modify.
The client was primarily interested in features and function (and
controlling costs), so there was no opportunity to compare ADP/ADO versus
MDB/DAO.
In the one instance where a colleague did address a performance issue, it
was a "communication issue", not an Access issue.
Larry Linson
Microsoft Access MVP
D
david epsom dot com dot au
Access buffers "gets", so as to not lock up Windows.
If you open your linked view in the database window.
you will see the row pointer, and, eventually, the total
number of records, updated continuously as Access gets
the records.
If this is causing problems, you might be interested
in playing around with different methods of accessing
the linked view.
For a start, try using an ADO or DAO object instead
of the Access Application object (clicking in the
database window is equivalent to application.DoCmd
or application.OpenQuery)
"Set rs = application.codedb.openrecordset" won't
give you a visible data sheet, but it should run faster,
and you have a number of options for what kind of
recordset you want. Obviously, it won't solve your
problem, but it might be interesting.
Another thing you can try is 'record level locking'.
Turn that off if you can: I've noticed an obscure effect
on ODBC transaction handling, which indicates that
the connection is subtly different when this option
is selected.
Regarding ADP vs MDB: it is only fair to note that those
people who advocate MDB over ADB would not in general
countenance the opening of a view in datasheet view
at all. Accepted wisdom is that, as when using a bank
ATM, the user should request only one record, and only
the requested record should be displayed.
BTW, a classic cause of the kind of behaviour you are
seeing is ---- inadvertent SQL or ODBC logging ----
Check that you haven't accidentally left logging turned
on somewhere :~(. The Jet settings are TraceSQLMode
and TraceODBCAPI: there are more places to turn on
tracing in your ODBC DSN etc.
(david)
If you open your linked view in the database window.
you will see the row pointer, and, eventually, the total
number of records, updated continuously as Access gets
the records.
If this is causing problems, you might be interested
in playing around with different methods of accessing
the linked view.
For a start, try using an ADO or DAO object instead
of the Access Application object (clicking in the
database window is equivalent to application.DoCmd
or application.OpenQuery)
"Set rs = application.codedb.openrecordset" won't
give you a visible data sheet, but it should run faster,
and you have a number of options for what kind of
recordset you want. Obviously, it won't solve your
problem, but it might be interesting.
Another thing you can try is 'record level locking'.
Turn that off if you can: I've noticed an obscure effect
on ODBC transaction handling, which indicates that
the connection is subtly different when this option
is selected.
Regarding ADP vs MDB: it is only fair to note that those
people who advocate MDB over ADB would not in general
countenance the opening of a view in datasheet view
at all. Accepted wisdom is that, as when using a bank
ATM, the user should request only one record, and only
the requested record should be displayed.
BTW, a classic cause of the kind of behaviour you are
seeing is ---- inadvertent SQL or ODBC logging ----
Check that you haven't accidentally left logging turned
on somewhere :~(. The Jet settings are TraceSQLMode
and TraceODBCAPI: there are more places to turn on
tracing in your ODBC DSN etc.
(david)
Neil said:
J
jimfortune
Albert said:
Thanks for your insights. I used to think that each record was
indexed. I'll keep Access' scheme in the back of my mind. Hmm.. what
if I had a text field called 'Bank' filled with junk text? When
getting ready to add other data via code I could 'go to the Bank' and
throw away some junk. Would that prevent page splits from happening?
Just before compacting the db I could refill the Bank to make room for
more new data. BTW, I'm not seriously considering this idea.
James A. Fortune
G
Guest
Albert D. Kallal said:
We use Access MDBs primarily for report writing with ODBC linked tables back
to SQL Server. With a little thought you can get the same performance from
linked tables as you would with an ADP. Joins between local and linked
tables are obviously out (unless you don't mind the entire contents of the
linked table coming across the network) but pretty much all other joins will
translate correctly. A bit of monitoring with SQL Profiler lets you quickly
check whether or not Access can translate your query to something that runs
server side, and once you get to know what it likes you can write very
complex queries that always run server side.
Kind Regards,
Paul
N
Neil
If you open your linked view in the database window.
In this case, though, we're dealing with a very small number of records
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in. Yet
the form is hideously slow in scrolling. Even displaying the initial screen
of about 20 takes forever (about 5 seconds
).
All this could be attributed to the MDB blues. However, as noted, this form
has been in place for a long time without any slowness. All of a sudden, it
started acting this way, and I can't track it down.
Not sure what you mean by "clicking in the database window." In my case, the
form is opened or the user specifies an option in the form and the recordset
is reset. A dynamic SQL statement is built, and the form's recordsource is
set to the SQL.
I did try opening a DAO recordset and setting the form's Recordset property
instead of using the Recordsource, but the results were the same.
All locking in the form is off.
Well, that would be simpler, of course. But there are times when you need
continuous form view. And, as noted, it's been working fine until recently.
BTW, since you mention those who advocate MDB over ADP, does that mean that
you're an advocate of ADP?
I haven't seen any places to turn this on on an application level. And since
this is happening globally (both on the client's LAN, as well as on my local
PC), I can't see all of the machines having that turned on. Or is there a
global setting for the application itself, rather than the PC?
Thanks,
Neil
In this case, though, we're dealing with a very small number of records
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in. Yet
the form is hideously slow in scrolling. Even displaying the initial screen
of about 20 takes forever (about 5 seconds
).All this could be attributed to the MDB blues. However, as noted, this form
has been in place for a long time without any slowness. All of a sudden, it
started acting this way, and I can't track it down.
Not sure what you mean by "clicking in the database window." In my case, the
form is opened or the user specifies an option in the form and the recordset
is reset. A dynamic SQL statement is built, and the form's recordsource is
set to the SQL.
I did try opening a DAO recordset and setting the form's Recordset property
instead of using the Recordsource, but the results were the same.
All locking in the form is off.
Well, that would be simpler, of course. But there are times when you need
continuous form view. And, as noted, it's been working fine until recently.
BTW, since you mention those who advocate MDB over ADP, does that mean that
you're an advocate of ADP?
I haven't seen any places to turn this on on an application level. And since
this is happening globally (both on the client's LAN, as well as on my local
PC), I can't see all of the machines having that turned on. Or is there a
global setting for the application itself, rather than the PC?
Thanks,
Neil
D
david epsom dot com dot au
In this case, though, we're dealing with a very small number of records
I suggest that you start a new thread with this information.
Don't mention ADP's at all. Do mention that it is SQL Server,
and that it works differently if there is no primary key defined.
'--
I'm not an advocate on the mdb/adp issue: I just don't
use adp. We have a very large application with most
customers using an MDB backend: we do the best we can
for SQL Server clients.
Record-Level locking is a Engine-level option,
controlled by Access when a database is opened:
Tools | Options | Advanced
You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.
'---
(david)
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in. the
form is hideously slow in scrolling. Even displaying the initial of about
20 takes forever (about 5 seconds
I suggest that you start a new thread with this information.
Don't mention ADP's at all. Do mention that it is SQL Server,
and that it works differently if there is no primary key defined.
'--
I'm not an advocate on the mdb/adp issue: I just don't
use adp. We have a very large application with most
customers using an MDB backend: we do the best we can
for SQL Server clients.
Record-Level locking is a Engine-level option,
controlled by Access when a database is opened:
Tools | Options | Advanced
You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.
'---
(david)
N
Neil
Record-Level locking is a Engine-level option,
My understanding is that the option in Tools | Options | Advanced is only
the default record locking for forms, reports, and queries. That is, when a
new object is created, it is given the default record locking option
specified there.
I suppose if one edits data directly in a table, the default record locking
option comes into play. But when there is a form, report, or query, the
RecordLocks property of that object trumps the default record locking option
specified in Tools | Options | Advanced. That's my understanding, anyway.
Regardless, the point is somewhat moot, since, when dealing with a SQL
database, Jet can't lock the records anyway. From Access online help:
"Note When you edit data in a linked SQL database table by using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the record-locking
setting you choose for your database, Microsoft Access always acts as though
the No Locks setting has been selected."
That could be; but one wouldn't think that going from 21 fields displayed to
24 would make that much of a difference. Also, I tried removing the three
fields, and it's still slow -- which leads me to wonder if it's some sort of
optimization issue on the SQL end which will eventually work itself out.
Probably not; but it's a thought.
Neil
My understanding is that the option in Tools | Options | Advanced is only
the default record locking for forms, reports, and queries. That is, when a
new object is created, it is given the default record locking option
specified there.
I suppose if one edits data directly in a table, the default record locking
option comes into play. But when there is a form, report, or query, the
RecordLocks property of that object trumps the default record locking option
specified in Tools | Options | Advanced. That's my understanding, anyway.
Regardless, the point is somewhat moot, since, when dealing with a SQL
database, Jet can't lock the records anyway. From Access online help:
"Note When you edit data in a linked SQL database table by using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the record-locking
setting you choose for your database, Microsoft Access always acts as though
the No Locks setting has been selected."
That could be; but one wouldn't think that going from 21 fields displayed to
24 would make that much of a difference. Also, I tried removing the three
fields, and it's still slow -- which leads me to wonder if it's some sort of
optimization issue on the SQL end which will eventually work itself out.
Probably not; but it's a thought.
Neil
D
david epsom dot com dot au
The Option that is labelled "Open databases using record-level
locking" controls the way databases are opened. The options
are record-level locking and page-level locking. Another
'kind' of locking is table-level locking. At the engine level,
these are the kinds of locks that are used.
The decisions about WHEN to lock (early, late, never,
optimistically, pessimistically etc) are controlled
separately, for example by form properties.
Theoretically, there is no reason why the lock scope should
affect the data transfer strategy, but I have noticed some
odd effects of this setting, which is why I suggested that
you try changing it.
(david)
locking" controls the way databases are opened. The options
are record-level locking and page-level locking. Another
'kind' of locking is table-level locking. At the engine level,
these are the kinds of locks that are used.
The decisions about WHEN to lock (early, late, never,
optimistically, pessimistically etc) are controlled
separately, for example by form properties.
Theoretically, there is no reason why the lock scope should
affect the data transfer strategy, but I have noticed some
odd effects of this setting, which is why I suggested that
you try changing it.
(david)
N
Neil
OK, thanks for clarifying what you were referring to. Still, that would be
handled on a per-machine basis, and this is something that changed in the
database (either SQL back end or front end MDB), since it happened across
the board, both locally and across the LAN.
N
handled on a per-machine basis, and this is something that changed in the
database (either SQL back end or front end MDB), since it happened across
the board, both locally and across the LAN.
N
A
Alex - IntraLAN
Hi all,
great post, good debate, I just want to add my opinion to the post. I
have been using access for years since Access v2, I have used all
combinations of front-end/back-end technologies and the conclusion I
have drawn is this. If you want a database of < 100mb in size the MDB
method works very well, as soon as you are in a large database > 200mb
100+ tables 100+ forms then adp style projects seem to be better as the
maximum underlying connections to a DB is 2048 and linked MDB/ODBC
tables require 2 connections for every table/combo box/and other table
style connections and ADP/SQL tables only require 1. I have only hit
this limitation 3 times in my 10+ years developing access applications,
but when I hit it with the MDB I have only one choice convert to an ADP
project. From a performance perspective I would say that DAO is
slightly faster using similar code/loops etc to ADO, but once the move
to ADP has been made then it is a shame if the application is not
optimised using stored procedures to run the main code loops that don't
require user input or progress to the screen whilst running, I see
performance increases of 10x+ using stored procedure over client side
code loops. At the end of the day it is horses for courses, small apps
+ small data MDB every time, big app + big data ADP/SQL every time. I
keep hearing of problems with ADP projects I have not had any major
problems with these and I have one site running an ADP (access
2000)/SQL7 application on 150+ terminals I have not had to fix or get
involved in anything for 4 years. The most important thing to me is db
design with as much of the program logic held in the database and not
the front end. A couple of thing that I have done to improve query
performance is
never use 'Select * from' and to trim the field length of long fields
if the results are going to a list box 'SELECT
Convert(char(30),rsContacts.Name_Of_Solicitor)' and to add the
following to the end of those line 'FOR BROWSE' which seems to help
with locking on the server.
Just my thoughts.
Alex
great post, good debate, I just want to add my opinion to the post. I
have been using access for years since Access v2, I have used all
combinations of front-end/back-end technologies and the conclusion I
have drawn is this. If you want a database of < 100mb in size the MDB
method works very well, as soon as you are in a large database > 200mb
100+ tables 100+ forms then adp style projects seem to be better as the
maximum underlying connections to a DB is 2048 and linked MDB/ODBC
tables require 2 connections for every table/combo box/and other table
style connections and ADP/SQL tables only require 1. I have only hit
this limitation 3 times in my 10+ years developing access applications,
but when I hit it with the MDB I have only one choice convert to an ADP
project. From a performance perspective I would say that DAO is
slightly faster using similar code/loops etc to ADO, but once the move
to ADP has been made then it is a shame if the application is not
optimised using stored procedures to run the main code loops that don't
require user input or progress to the screen whilst running, I see
performance increases of 10x+ using stored procedure over client side
code loops. At the end of the day it is horses for courses, small apps
+ small data MDB every time, big app + big data ADP/SQL every time. I
keep hearing of problems with ADP projects I have not had any major
problems with these and I have one site running an ADP (access
2000)/SQL7 application on 150+ terminals I have not had to fix or get
involved in anything for 4 years. The most important thing to me is db
design with as much of the program logic held in the database and not
the front end. A couple of thing that I have done to improve query
performance is
never use 'Select * from' and to trim the field length of long fields
if the results are going to a list box 'SELECT
Convert(char(30),rsContacts.Name_Of_Solicitor)' and to add the
following to the end of those line 'FOR BROWSE' which seems to help
with locking on the server.
Just my thoughts.
Alex
D
david epsom dot com dot au
I don't see that you have reposted as a I suggested?
I don't think that the MDB subject is exhausted, but I
don't want to continue posting in an ADP thread if you
have start a newer, more relevant thread.
(david)
I don't think that the MDB subject is exhausted, but I
don't want to continue posting in an ADP thread if you
have start a newer, more relevant thread.
(david)