|
|
You're in the
sql server odbc
group:ADP vs. MDB: Speed
sql server odbc:
I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
7 back end. The view is scrolling very slowly. However, if I open the view in an ADP file, it scrolls quickly. I needed to use an ODBC link for the view because it needs to be editable. Otherwise, I would have used a pass-through query. In previous discussions about using an MDB file vs. an ADP file as a front end for SQL Server, the impression I got was that both were about the same, but that the MDB was a more mature technology and less problematic than the ADP technology. However, the speed difference I'm noticing with the ADP file in regards to this view is significant and is very disconcerting re. using an MDB file. Any thoughts/comments/suggestions would be appreciated. I've reproduced the view's SQL below for reference. Thanks, Neil SQL for view in question: SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, INVTRY.attFirstEdition, INVTRY.attSigned, ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, INVTRY.PRICE, INVTRY.Web, INVTRY.Status, INVTRY.WebStatusPending, INVTRY.ActivateDate, INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, INVTRY.AllowDuplicate, INVTRY.WebAction, INVTRY.WebActionPending, INVTRY.DateModified, INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, INVTRY.HImage, INVTRY.AdCode, CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL THEN - 1 ELSE 0 END AS OnWeb FROM vwInventory_Dupes INNER JOIN (WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.Web) ON (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND (vwInventory_Dupes.TITLE = INVTRY.TITLE) WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1)) SQL for vwInventory_Dupes, used as subquery: SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, Cast(attFirstEdition AS tinyint) FirstEd, Cast(attSigned AS tinyint) Signed, ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB YearPub FROM WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.Web WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1)) GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB HAVING (((COUNT(INVTRY.[INDEX])) > 1))
The ways ADPs and MDBs use recordsets are very different, so sometimes one or
the other will be faster. By default, the MDB opens a Dynaset which means it first opens a connection to grab the list of keys for the records, then reads the actual records as needed for display by doing individual selects of 10 records aby explicit key. This can help speed when each row has a lot of data because only the rows that are actually viewd will be read, not all of them. On the other hand, if the table has a very large number of rows, Access will spend a lot of time in the background reading the whole list of keys, and if the key lookup for each row is slow for some reason, everything will be sluggish. An ADP, a static recordset is normally used, and an absolute cap of 10,000 rows is applied, so if your recordset has more than that, you just won't get all the rows. Static means in loads all the data at once, then you browse through it in memeory. That's very fast once you load the data, but if each row has a lot of data, especially memo fields and such, it can take a long time to initially load. [quoted text, click to view] On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@pxdy.com> wrote:
>I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL >7 back end. The view is scrolling very slowly. However, if I open the view >in an ADP file, it scrolls quickly. > >I needed to use an ODBC link for the view because it needs to be editable. >Otherwise, I would have used a pass-through query. > >In previous discussions about using an MDB file vs. an ADP file as a front >end for SQL Server, the impression I got was that both were about the same, >but that the MDB was a more mature technology and less problematic than the >ADP technology. However, the speed difference I'm noticing with the ADP file >in regards to this view is significant and is very disconcerting re. using >an MDB file. > >Any thoughts/comments/suggestions would be appreciated. I've reproduced the >view's SQL below for reference. > >Thanks, > >Neil > >SQL for view in question: > >SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, > INVTRY.attFirstEdition, INVTRY.attSigned, > ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, > INVTRY.PRICE, INVTRY.Web, INVTRY.Status, > INVTRY.WebStatusPending, INVTRY.ActivateDate, > INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, > INVTRY.AllowDuplicate, INVTRY.WebAction, > INVTRY.WebActionPending, INVTRY.DateModified, > INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, > INVTRY.HImage, INVTRY.AdCode, > CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL > THEN - 1 ELSE 0 END AS OnWeb >FROM vwInventory_Dupes INNER JOIN > (WebStatus INNER JOIN > (INVTRY INNER JOIN > tabStatus ON INVTRY.Status = tabStatus.Status) ON > WebStatus.WebStatus = INVTRY.Web) ON > (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND > (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, > ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND > (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND > (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND > (vwInventory_Dupes.TITLE = INVTRY.TITLE) >WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) > = 1)) > >SQL for vwInventory_Dupes, used as subquery: > >SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, > Cast(attFirstEdition AS tinyint) FirstEd, > Cast(attSigned AS tinyint) Signed, > ISNULL(INVTRY.attSignedPD, ' ') SignedCond, > INVTRY.YRPUB YearPub >FROM WebStatus INNER JOIN > (INVTRY INNER JOIN > tabStatus ON INVTRY.Status = tabStatus.Status) ON > WebStatus.WebStatus = INVTRY.Web >WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) > = 1)) >GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, > Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), > ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >
On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
[quoted text, click to view] the blanks, no spam please)> wrote: >You may try to add a virtual index on your linked view: >http://support.microsoft.com/kb/q209123/ > >If this doesn't help, then you will have to use an unbound form coupled with >SQL pass-through queries (or ADO objects) to resolve your speed problem with >MDB. Another solution could be to go with TS to simulate a high-speed LAN. > >I don't know where you got the impression that MDB were about the same as >ADP in term of speed. It has been repeated a number of times that the use >of MDB' linked tables and views is only a workable solution for small >databases on a fast Lan. Frankly, I find that statement ludicrous. Many developers including myself have had excelent results using MDBs as front-ends to various kinds of SQL Server back-end for many years before there was such a thing as an ADP. When MDBs are slow, the workarounds to fix it are far less arduous than the workarounds required in ADPs to simply make them function in many cases.
You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/ If this doesn't help, then you will have to use an unbound form coupled with SQL pass-through queries (or ADO objects) to resolve your speed problem with MDB. Another solution could be to go with TS to simulate a high-speed LAN. I don't know where you got the impression that MDB were about the same as ADP in term of speed. It has been repeated a number of times that the use of MDB' linked tables and views is only a workable solution for small databases on a fast Lan. S. L. [quoted text, click to view] "Neil" <njones@pxdy.com> wrote in message news:Uxa%d.15606$cN6.15135@newsread1.news.pas.earthlink.net... >I have a situation with an ODBC linked view in an Access 2000 MDB with a >SQL 7 back end. The view is scrolling very slowly. However, if I open the >view in an ADP file, it scrolls quickly. > > I needed to use an ODBC link for the view because it needs to be editable. > Otherwise, I would have used a pass-through query. > > In previous discussions about using an MDB file vs. an ADP file as a front > end for SQL Server, the impression I got was that both were about the > same, but that the MDB was a more mature technology and less problematic > than the ADP technology. However, the speed difference I'm noticing with > the ADP file in regards to this view is significant and is very > disconcerting re. using an MDB file. > > Any thoughts/comments/suggestions would be appreciated. I've reproduced > the view's SQL below for reference. > > Thanks, > > Neil > > SQL for view in question: > > SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, > INVTRY.attFirstEdition, INVTRY.attSigned, > ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, > INVTRY.PRICE, INVTRY.Web, INVTRY.Status, > INVTRY.WebStatusPending, INVTRY.ActivateDate, > INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, > INVTRY.AllowDuplicate, INVTRY.WebAction, > INVTRY.WebActionPending, INVTRY.DateModified, > INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, > INVTRY.HImage, INVTRY.AdCode, > CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL > THEN - 1 ELSE 0 END AS OnWeb > FROM vwInventory_Dupes INNER JOIN > (WebStatus INNER JOIN > (INVTRY INNER JOIN > tabStatus ON INVTRY.Status = tabStatus.Status) ON > WebStatus.WebStatus = INVTRY.Web) ON > (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND > (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, > ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND > (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND > (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND > (vwInventory_Dupes.TITLE = INVTRY.TITLE) > WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) > = 1)) > > SQL for vwInventory_Dupes, used as subquery: > > SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, > Cast(attFirstEdition AS tinyint) FirstEd, > Cast(attSigned AS tinyint) Signed, > ISNULL(INVTRY.attSignedPD, ' ') SignedCond, > INVTRY.YRPUB YearPub > FROM WebStatus INNER JOIN > (INVTRY INNER JOIN > tabStatus ON INVTRY.Status = tabStatus.Status) ON > WebStatus.WebStatus = INVTRY.Web > WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) > = 1)) > GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, > Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), > ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB > HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >
Did you specify a field or set of fields to be used as the primary key for the
view when you created the link? If not, it will be slower, and will not be updateable. [quoted text, click to view] On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large >number here. And only returning 24 fields. > >There was a modification recently where three additional fields were added >to the view, and that seemed to slow things down significantly. I'm not sure >if there's some boundary at around 24 fields, or if something else is going >on. But the linked view is *very* slow, taking several seconds just to bring >up the first screen, and then, whenever the scroll bar goes down by one, >several more seconds just to refresh. The ADP view, on the other hand, is >fast, with no delay at all, either in bringing up the data or in scrolling. > >Neil > > >"Steve Jorgensen" <nospam@nospam.nospam> wrote in message >news:kq9r31tdjl3tv8v10kourkov1psurko5ug@4ax.com... >> The ways ADPs and MDBs use recordsets are very different, so sometimes one >> or >> the other will be faster. >> >> By default, the MDB opens a Dynaset which means it first opens a >> connection to >> grab the list of keys for the records, then reads the actual records as >> needed >> for display by doing individual selects of 10 records aby explicit key. >> This >> can help speed when each row has a lot of data because only the rows that >> are >> actually viewd will be read, not all of them. On the other hand, if the >> table >> has a very large number of rows, Access will spend a lot of time in the >> background reading the whole list of keys, and if the key lookup for each >> row >> is slow for some reason, everything will be sluggish. >> >> An ADP, a static recordset is normally used, and an absolute cap of 10,000 >> rows is applied, so if your recordset has more than that, you just won't >> get >> all the rows. Static means in loads all the data at once, then you browse >> through it in memeory. That's very fast once you load the data, but if >> each >> row has a lot of data, especially memo fields and such, it can take a long >> time to initially load. >> >> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@pxdy.com> wrote: >> >>>I have a situation with an ODBC linked view in an Access 2000 MDB with a >>>SQL >>>7 back end. The view is scrolling very slowly. However, if I open the view >>>in an ADP file, it scrolls quickly. >>> >>>I needed to use an ODBC link for the view because it needs to be editable. >>>Otherwise, I would have used a pass-through query. >>> >>>In previous discussions about using an MDB file vs. an ADP file as a front >>>end for SQL Server, the impression I got was that both were about the >>>same, >>>but that the MDB was a more mature technology and less problematic than >>>the >>>ADP technology. However, the speed difference I'm noticing with the ADP >>>file >>>in regards to this view is significant and is very disconcerting re. using >>>an MDB file. >>> >>>Any thoughts/comments/suggestions would be appreciated. I've reproduced >>>the >>>view's SQL below for reference. >>> >>>Thanks, >>> >>>Neil >>> >>>SQL for view in question: >>> >>>SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >>> INVTRY.attFirstEdition, INVTRY.attSigned, >>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >>> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >>> INVTRY.WebStatusPending, INVTRY.ActivateDate, >>> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >>> INVTRY.AllowDuplicate, INVTRY.WebAction, >>> INVTRY.WebActionPending, INVTRY.DateModified, >>> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >>> INVTRY.HImage, INVTRY.AdCode, >>> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >>> THEN - 1 ELSE 0 END AS OnWeb >>>FROM vwInventory_Dupes INNER JOIN >>> (WebStatus INNER JOIN >>> (INVTRY INNER JOIN >>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>> WebStatus.WebStatus = INVTRY.Web) ON >>> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >>> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >>> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >>> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >>> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >>> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >>>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>> = 1)) >>> >>>SQL for vwInventory_Dupes, used as subquery: >>> >>>SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, >>> Cast(attFirstEdition AS tinyint) FirstEd, >>> Cast(attSigned AS tinyint) Signed, >>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, >>> INVTRY.YRPUB YearPub >>>FROM WebStatus INNER JOIN >>> (INVTRY INNER JOIN >>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>> WebStatus.WebStatus = INVTRY.Web >>>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>> = 1)) >>>GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, >>> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), >>> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >>>HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >>> >> >
Well, perhaps the reason for our difference of opinion is that I never thought
it was particularly appropriate to connect directly to a database server across a WAN at all. I usually recommend using a terminal server or rewriting the app as a Web application or some other kind of 3-tier application. On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill [quoted text, click to view] the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN? > >In the case of the WAN, all tests that I have done in the past indicate that >MDB files are much slower to access a SQL-Server backend. The use of linked >views will greatly reduce the performance hit but even with them, I don't >see the benefice of creating hundred of views instead of creating hundred of >stored procedures. If you want to get some decent speed over the internet, >there are a lot more of work to do using MDB than using ADP and even then, >I'm not really sure if the use of linked views will achieve the same level >of performance. > >You are entitled to your opinion but all the tests that I have done in the >past clearly indicate that the use of MDB to access a SQL-Server over the >internet is only, at its best, a kludge. > >For a LAN, the problem is of course much less severe but at the condition >that the network is not already overcrowded. > >S. L. > >"Steve Jorgensen" <nospam@nospam.nospam> wrote in message >news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@4ax.com... >> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca >> (fill >> the blanks, no spam please)> wrote: >> >>>You may try to add a virtual index on your linked view: >>>http://support.microsoft.com/kb/q209123/ >>> >>>If this doesn't help, then you will have to use an unbound form coupled >>>with >>>SQL pass-through queries (or ADO objects) to resolve your speed problem >>>with >>>MDB. Another solution could be to go with TS to simulate a high-speed >>>LAN. >>> >>>I don't know where you got the impression that MDB were about the same as >>>ADP in term of speed. It has been repeated a number of times that the use >>>of MDB' linked tables and views is only a workable solution for small >>>databases on a fast Lan. >> >> Frankly, I find that statement ludicrous. Many developers including >> myself >> have had excelent results using MDBs as front-ends to various kinds of SQL >> Server back-end for many years before there was such a thing as an ADP. >> When >> MDBs are slow, the workarounds to fix it are far less arduous than the >> workarounds required in ADPs to simply make them function in many cases. >> >
TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to solve the possibility of corruption when you LAN network is not rock solid. However, this solution come with a price ($). With the use of unbound forms as the solution, you are responsible for sending the modifications to the data back to the database. This will give you the possibility to use the result of a read-only SQL pass-through in a form but, obviously, with the obligation of having more coding work to be done. Of course, some of this work can be partially automated. You will find more information on that subject on books dealing with Access and SQL-Server. (Personally, I prefer to use ADP but it has many bugs.) By using the profiler on the SQL-Server, it is also possible that you will see something that will give you the possibility of resolving the speed problem of you linked views by having a better understanding of what Access is doing when it communicates with the server. S. L. [quoted text, click to view] "Neil" <njones@pxdy.com> wrote in message news:FCk%d.633$H06.566@newsread3.news.pas.earthlink.net... >> You may try to add a virtual index on your linked view: >> http://support.microsoft.com/kb/q209123/ > > Yes, these are added when you first attach the view or table. When the > object doesn't have a primary key (as with views) Access prompts you for > which field(s) to use as pk. It then stores that information. You can't > update the data otherwise. So it's already there. > >> If this doesn't help, then you will have to use an unbound form coupled >> with SQL pass-through queries (or ADO objects) to resolve your speed >> problem with MDB. > > As noted, the reason for not using pass-through is because it needs to be > updatable. > >>Another solution could be to go with TS to simulate a high-speed LAN. > > What is "TS"? > >> I don't know where you got the impression that MDB were about the same as >> ADP in term of speed. It has been repeated a number of times that the >> use of MDB' linked tables and views is only a workable solution for small >> databases on a fast Lan. > > From discussions in this newsgroup. > > Neil > > >> >> S. L. >> >> "Neil" <njones@pxdy.com> wrote in message >> news:Uxa%d.15606$cN6.15135@newsread1.news.pas.earthlink.net... >>>I have a situation with an ODBC linked view in an Access 2000 MDB with a >>>SQL 7 back end. The view is scrolling very slowly. However, if I open the >>>view in an ADP file, it scrolls quickly. >>> >>> I needed to use an ODBC link for the view because it needs to be >>> editable. Otherwise, I would have used a pass-through query. >>> >>> In previous discussions about using an MDB file vs. an ADP file as a >>> front end for SQL Server, the impression I got was that both were about >>> the same, but that the MDB was a more mature technology and less >>> problematic than the ADP technology. However, the speed difference I'm >>> noticing with the ADP file in regards to this view is significant and is >>> very disconcerting re. using an MDB file. >>> >>> Any thoughts/comments/suggestions would be appreciated. I've reproduced >>> the view's SQL below for reference. >>> >>> Thanks, >>> >>> Neil >>> >>> SQL for view in question: >>> >>> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >>> INVTRY.attFirstEdition, INVTRY.attSigned, >>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >>> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >>> INVTRY.WebStatusPending, INVTRY.ActivateDate, >>> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >>> INVTRY.AllowDuplicate, INVTRY.WebAction, >>> INVTRY.WebActionPending, INVTRY.DateModified, >>> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >>> INVTRY.HImage, INVTRY.AdCode, >>> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >>> THEN - 1 ELSE 0 END AS OnWeb >>> FROM vwInventory_Dupes INNER JOIN >>> (WebStatus INNER JOIN >>> (INVTRY INNER JOIN >>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>> WebStatus.WebStatus = INVTRY.Web) ON >>> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >>> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >>> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >>> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >>> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >>> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >>> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>> = 1)) >>> >>> SQL for vwInventory_Dupes, used as subquery: >>> >>> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, >>> Cast(attFirstEdition AS tinyint) FirstEd, >>> Cast(attSigned AS tinyint) Signed, >>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, >>> INVTRY.YRPUB YearPub >>> FROM WebStatus INNER JOIN >>> (INVTRY INNER JOIN >>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>> WebStatus.WebStatus = INVTRY.Web >>> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>> = 1)) >>> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, >>> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), >>> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >>> HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >>> >> >> > >
Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that MDB files are much slower to access a SQL-Server backend. The use of linked views will greatly reduce the performance hit but even with them, I don't see the benefice of creating hundred of views instead of creating hundred of stored procedures. If you want to get some decent speed over the internet, there are a lot more of work to do using MDB than using ADP and even then, I'm not really sure if the use of linked views will achieve the same level of performance. You are entitled to your opinion but all the tests that I have done in the past clearly indicate that the use of MDB to access a SQL-Server over the internet is only, at its best, a kludge. For a LAN, the problem is of course much less severe but at the condition that the network is not already overcrowded. S. L. [quoted text, click to view] "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@4ax.com... > On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca > (fill > the blanks, no spam please)> wrote: > >>You may try to add a virtual index on your linked view: >>http://support.microsoft.com/kb/q209123/ >> >>If this doesn't help, then you will have to use an unbound form coupled >>with >>SQL pass-through queries (or ADO objects) to resolve your speed problem >>with >>MDB. Another solution could be to go with TS to simulate a high-speed >>LAN. >> >>I don't know where you got the impression that MDB were about the same as >>ADP in term of speed. It has been repeated a number of times that the use >>of MDB' linked tables and views is only a workable solution for small >>databases on a fast Lan. > > Frankly, I find that statement ludicrous. Many developers including > myself > have had excelent results using MDBs as front-ends to various kinds of SQL > Server back-end for many years before there was such a thing as an ADP. > When > MDBs are slow, the workarounds to fix it are far less arduous than the > workarounds required in ADPs to simply make them function in many cases. >
I don't really understand your statement about poor developers: you are
telling me that if I forget about 90% (or 50, 60 or 70%, pick your number) of the coding facilities of SQL-Server, I will be a better programmer? I know that the use of linked views will provide some relief about the lack of speed of linked ODBC tables; however, I don't see why this could be considered as an advantage: 1) It will take as much time creating all the necessary views for each form/sub-form/control using a join in their queries than it will take for creating the relevant stored procedures. 2) When I will really need to use a stored procedure - for example for having access to temporary tables, cursor, other stored procedures, etc. - because of the underlying complexity of the request, I will have a pretty leg if I've limited myself to the exclusive use of views because I'm using ODBC linked tables. (For obvious reasons, I won't comment on the possibility of using a sql pass-through query in these cases.) From what I can read from your post, it's like telling me that a car is as good and as fast as another one for the same price, but that you have the right to go in only half the city if you buy it. In my opinion, when you have to pay the same price in working time (at the minimum), you should be entitled to the same full capacity in programming capabilities and when the price is higher, there should be more, not less, capabilities. I don't see why limiting myself to the exclusive use of views and forgetting about stored procedures will make me a better programmer but I you want to go on this path, this is your right and your decision; not mine. S. L. [quoted text, click to view] "Albert D. Kallal" <kallal@msn.com> wrote in message news:FYn%d.745819$8l.276231@pd7tw1no... > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:uin5FbZLFHA.3340@TK2MSFTNGP10.phx.gbl... >> Are you talking about the WAN or a LAN? >> >> In the case of the WAN, all tests that I have done in the past indicate >> that MDB files are much slower to access a SQL-Server backend. > > The problem of slowness is not ms-access/jet, but that how the developer > uses sql server here. > > When ms-access is used correctly, and thoughtfully by a developer, then > the limits of the application become that of sql server, and not ms-access > at al. There are companies out there with 1000 simultaneous ms-access > users hitting the same sql server database. using ms-access is no worse > the using VB6, c++ or any other ide. The ONLY differences here are that of > poor developers. > >> The use of linked views will greatly reduce the performance hit but even >> with them, I don't see the benefice of creating hundred of views instead >> of creating hundred of stored procedures. > > HUH? You mean grabbing data from a link view vs a stored proc makes any > difference here? (it makes not ONE bit!!). > > If you talking about some code that needs to update some data..then, sure, > by all means move the code from ms-access into a stored proc, but we are > not talking about bad development practices here. > > Without question, execute sql on a JET based client through linked tables > needs caution. ANY time more then one table is involved, that sql should > be put on the server side, or pass through be used. If one follows this > rule, then 9 out 10 times, the odbc linked application will perform as > good as the ADP one. > > The fact of the matter is that a native ole-db connection sql server vs a > odbc one has be thrashed out MANY TIMES. If I hit a database and ask the > sql server to return one record, both the ole-db, and a simply linked > table in ms-access both return one record, and produce approximate the > same network traffic. > >> If you want to get some decent speed over the internet, there are a lot >> more of work to do using MDB than using ADP and even then, I'm not really >> sure if the use of linked views will achieve the same level of >> performance. > > Linked views is the solution in this poster case. If the poster does this, > then no difference will be noticed. The problem here is not ms-access, > but users of ms-access who don't take the time to utilize, and effect use > sql server here. > >> >> You are entitled to your opinion but all the tests that I have done in >> the past clearly indicate that the use of MDB to access a SQL-Server over >> the internet is only, at its best, a kludge. > > Compared to what? I know of people successfully using ms-access OVER DIAL > UP lines to sql server (you read that correctly!!). If you make the > effort, (and this is NO more effort then writing in VB6, or other tools, > then you get the same performance). > > So, sure, if a person just lazily links multiple tables..and then tries to > build a local query based on all those linked tables..then you will not > get decent performance. > > However, it is a myth that a ms-access odbc connection to sql server don't > work well. It will JUST as well as the other IDE's, and you get the same > performance.... > > Further, bound forms in ms-access can successful be used, but again, as > long as stupid things like opening a form to a table without some type of > "where" clause must be avoided. (I never do that in JET based > solutions...let alone sql server based ones anyway). > > Again, the fact that SO many ms-access applications open up forms to a > table, and THEN let the user browse/search for a record is a HORRIBLE > design, and one that should be avoided. > > So, it is not the fact of ms-access being bad, but the fact that it is SO > EASY to simply throw up a form with a large data set, and let the user > "have at it". > > So, even bound forms can quite well be used over a wan. > > The real problem here is bad practices...not ms-access... > > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal@msn.com > http://www.members.shaw.ca/AlbertKallal > >
The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields. There was a modification recently where three additional fields were added to the view, and that seemed to slow things down significantly. I'm not sure if there's some boundary at around 24 fields, or if something else is going on. But the linked view is *very* slow, taking several seconds just to bring up the first screen, and then, whenever the scroll bar goes down by one, several more seconds just to refresh. The ADP view, on the other hand, is fast, with no delay at all, either in bringing up the data or in scrolling. Neil [quoted text, click to view] "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:kq9r31tdjl3tv8v10kourkov1psurko5ug@4ax.com... > The ways ADPs and MDBs use recordsets are very different, so sometimes one > or > the other will be faster. > > By default, the MDB opens a Dynaset which means it first opens a > connection to > grab the list of keys for the records, then reads the actual records as > needed > for display by doing individual selects of 10 records aby explicit key. > This > can help speed when each row has a lot of data because only the rows that > are > actually viewd will be read, not all of them. On the other hand, if the > table > has a very large number of rows, Access will spend a lot of time in the > background reading the whole list of keys, and if the key lookup for each > row > is slow for some reason, everything will be sluggish. > > An ADP, a static recordset is normally used, and an absolute cap of 10,000 > rows is applied, so if your recordset has more than that, you just won't > get > all the rows. Static means in loads all the data at once, then you browse > through it in memeory. That's very fast once you load the data, but if > each > row has a lot of data, especially memo fields and such, it can take a long > time to initially load. > > On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@pxdy.com> wrote: > >>I have a situation with an ODBC linked view in an Access 2000 MDB with a >>SQL >>7 back end. The view is scrolling very slowly. However, if I open the view >>in an ADP file, it scrolls quickly. >> >>I needed to use an ODBC link for the view because it needs to be editable. >>Otherwise, I would have used a pass-through query. >> >>In previous discussions about using an MDB file vs. an ADP file as a front >>end for SQL Server, the impression I got was that both were about the >>same, >>but that the MDB was a more mature technology and less problematic than >>the >>ADP technology. However, the speed difference I'm noticing with the ADP >>file >>in regards to this view is significant and is very disconcerting re. using >>an MDB file. >> >>Any thoughts/comments/suggestions would be appreciated. I've reproduced >>the >>view's SQL below for reference. >> >>Thanks, >> >>Neil >> >>SQL for view in question: >> >>SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >> INVTRY.attFirstEdition, INVTRY.attSigned, >> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >> INVTRY.WebStatusPending, INVTRY.ActivateDate, >> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >> INVTRY.AllowDuplicate, INVTRY.WebAction, >> INVTRY.WebActionPending, INVTRY.DateModified, >> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >> INVTRY.HImage, INVTRY.AdCode, >> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >> THEN - 1 ELSE 0 END AS OnWeb >>FROM vwInventory_Dupes INNER JOIN >> (WebStatus INNER JOIN >> (INVTRY INNER JOIN >> tabStatus ON INVTRY.Status = tabStatus.Status) ON >> WebStatus.WebStatus = INVTRY.Web) ON >> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >> = 1)) >> >>SQL for vwInventory_Dupes, used as subquery: >> >>SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, >> Cast(attFirstEdition AS tinyint) FirstEd, >> Cast(attSigned AS tinyint) Signed, >> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, >> INVTRY.YRPUB YearPub >>FROM WebStatus INNER JOIN >> (INVTRY INNER JOIN >> tabStatus ON INVTRY.Status = tabStatus.Status) ON >> WebStatus.WebStatus = INVTRY.Web >>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >> = 1)) >>GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, >> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), >> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >>HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >> >
[quoted text, click to view]
> You may try to add a virtual index on your linked view: > http://support.microsoft.com/kb/q209123/ Yes, these are added when you first attach the view or table. When the object doesn't have a primary key (as with views) Access prompts you for which field(s) to use as pk. It then stores that information. You can't update the data otherwise. So it's already there. [quoted text, click to view] > If this doesn't help, then you will have to use an unbound form coupled > with SQL pass-through queries (or ADO objects) to resolve your speed > problem with MDB. As noted, the reason for not using pass-through is because it needs to be updatable. [quoted text, click to view] >Another solution could be to go with TS to simulate a high-speed LAN. What is "TS"? [quoted text, click to view] > I don't know where you got the impression that MDB were about the same as > ADP in term of speed. It has been repeated a number of times that the use > of MDB' linked tables and views is only a workable solution for small > databases on a fast Lan. From discussions in this newsgroup. Neil [quoted text, click to view] > > S. L. > > "Neil" <njones@pxdy.com> wrote in message > news:Uxa%d.15606$cN6.15135@newsread1.news.pas.earthlink.net... >>I have a situation with an ODBC linked view in an Access 2000 MDB with a >>SQL 7 back end. The view is scrolling very slowly. However, if I open the >>view in an ADP file, it scrolls quickly. >> >> I needed to use an ODBC link for the view because it needs to be >> editable. Otherwise, I would have used a pass-through query. >> >> In previous discussions about using an MDB file vs. an ADP file as a >> front end for SQL Server, the impression I got was that both were about >> the same, but that the MDB was a more mature technology and less >> problematic than the ADP technology. However, the speed difference I'm >> noticing with the ADP file in regards to this view is significant and is >> very disconcerting re. using an MDB file. >> >> Any thoughts/comments/suggestions would be appreciated. I've reproduced >> the view's SQL below for reference. >> >> Thanks, >> >> Neil >> >> SQL for view in question: >> >> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >> INVTRY.attFirstEdition, INVTRY.attSigned, >> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >> INVTRY.WebStatusPending, INVTRY.ActivateDate, >> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >> INVTRY.AllowDuplicate, INVTRY.WebAction, >> INVTRY.WebActionPending, INVTRY.DateModified, >> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >> INVTRY.HImage, INVTRY.AdCode, >> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >> THEN - 1 ELSE 0 END AS OnWeb >> FROM vwInventory_Dupes INNER JOIN >> (WebStatus INNER JOIN >> (INVTRY INNER JOIN >> tabStatus ON INVTRY.Status = tabStatus.Status) ON >> WebStatus.WebStatus = INVTRY.Web) ON >> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >> = 1)) >> >> SQL for vwInventory_Dupes, used as subquery: >> >> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, >> Cast(attFirstEdition AS tinyint) FirstEd, >> Cast(attSigned AS tinyint) Signed, >> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, >> INVTRY.YRPUB YearPub >> FROM WebStatus INNER JOIN >> (INVTRY INNER JOIN >> tabStatus ON INVTRY.Status = tabStatus.Status) ON >> WebStatus.WebStatus = INVTRY.Web >> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >> = 1)) >> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, >> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), >> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >> HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >> > >
[quoted text, click to view]
"Neil" <njones@pxdy.com> wrote in message news:Uxa%d.15606$cN6.15135@newsread1.news.pas.earthlink.net... > > In previous discussions about using an MDB file vs. an ADP file as a front > end for SQL Server, the impression I got was that both were about the > same, but that the MDB was a more mature technology and less problematic > than the ADP technology. However, the speed difference I'm noticing with > the ADP file in regards to this view is significant and is very > disconcerting re. using an MDB file. > > Any thoughts/comments/suggestions would be appreciated. I've reproduced > the view's SQL below for reference. Yes, a few things needs to cleared up here. First, using a ADP with sql server is kind of like using a automatic truck vs a truck with a standard shift. If you don't know trucks, and how to shift gears, then obviously the automatic truck is going to be better. On the other hand, an advanced driver will get the same performance out of the standard truck.... If you look at the sql you posted, there is a number of tables involved. If you just link a bunch of tables to sql server, and then tell ms-access to "join" them together..you will often get poor performance. I mean, obviously ms-access/JET has to pull data from multiple tables. With ODBC linked tables, each table is *often* thought as a separate data source. With a ADP, that sql is processed server side. Remember, ms-access/JET has a difficult time working with multiple ODBC DATA sources when you do a join on them. The reason of course is that one table might be from Oracle, and the other might be a local FoxPro table. When you join data through linked tables, you have to remem ber what the Robot in Lost in space used to say: "Danger ...Will Robison.....Danger"... When you use a ADP, that whole sql statement is sent to sql server to be processed. So, of course, if you used a pass-through query, then both the ADP, and the ODBC will performance the same. However, you mentioned that you can't use a pass through. However, you STILL are using a sql statement that have SEVERAL LINKED tables. ms-access is going to have to figure out those links for you!. This should not be a surprise if you think about what is going on here. However, the simple solution here is thus to simply create a view on sql server, and then link to that! Doing this, you will get the same performance again as the ADP. So, the lesson here is that you simply have to be MORE conscience of sql that joins tables together. You can often get away with at last one extra joined table via link tables..but as a general rule, you have to build a query anyway, so, just built a view on the sql side..and you eliminate the performance problem. So, our question is using a ADP going to be faster then a MDB with linked tables? answers: Yes, ADP will be faster if you don't pay attention to avoiding bottle necks, and thinking about what is going on.... So, yes, you do need a bit more caution when using a mdb file and odbc tables against sql server. Without question, a ADP allows you to be much more lazy, as ALL SQL is 100% executed on the sql server side, and thus it is MUCH harder to screw things up. With a mdb/odbc linked tables, you need extra caution, and extra effort to avoid bottle necks. In effect, the increased flexibility of a mdb/odbc setup means you have more rope and more room to hang yourself!! However, with a small effort, and some caution, you can EASLEY get the same performance with a mdb/odbc as you get with a ADP. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.members.shaw.ca/AlbertKallal
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
[quoted text, click to view] wrote in message news:uin5FbZLFHA.3340@TK2MSFTNGP10.phx.gbl... > Are you talking about the WAN or a LAN? > > In the case of the WAN, all tests that I have done in the past indicate > that MDB files are much slower to access a SQL-Server backend. The problem of slowness is not ms-access/jet, but that how the developer uses sql server here. When ms-access is used correctly, and thoughtfully by a developer, then the limits of the application become that of sql server, and not ms-access at al. There are companies out there with 1000 simultaneous ms-access users hitting the same sql server database. using ms-access is no worse the using VB6, c++ or any other ide. The ONLY differences here are that of poor developers. [quoted text, click to view] > The use of linked views will greatly reduce the performance hit but even > with them, I don't see the benefice of creating hundred of views instead > of creating hundred of stored procedures. HUH? You mean grabbing data from a link view vs a stored proc makes any difference here? (it makes not ONE bit!!). If you talking about some code that needs to update some data..then, sure, by all means move the code from ms-access into a stored proc, but we are not talking about bad development practices here. Without question, execute sql on a JET based client through linked tables needs caution. ANY time more then one table is involved, that sql should be put on the server side, or pass through be used. If one follows this rule, then 9 out 10 times, the odbc linked application will perform as good as the ADP one. The fact of the matter is that a native ole-db connection sql server vs a odbc one has be thrashed out MANY TIMES. If I hit a database and ask the sql server to return one record, both the ole-db, and a simply linked table in ms-access both return one record, and produce approximate the same network traffic. [quoted text, click to view] > If you want to get some decent speed over the internet, there are a lot > more of work to do using MDB than using ADP and even then, I'm not really > sure if the use of linked views will achieve the same level of > performance. Linked views is the solution in this poster case. If the poster does this, then no difference will be noticed. The problem here is not ms-access, but users of ms-access who don't take the time to utilize, and effect use sql server here. [quoted text, click to view] > > You are entitled to your opinion but all the tests that I have done in the > past clearly indicate that the use of MDB to access a SQL-Server over the > internet is only, at its best, a kludge. Compared to what? I know of people successfully using ms-access OVER DIAL UP lines to sql server (you read that correctly!!). If you make the effort, (and this is NO more effort then writing in VB6, or other tools, then you get the same performance). So, sure, if a person just lazily links multiple tables..and then tries to build a local query based on all those linked tables..then you will not get decent performance. However, it is a myth that a ms-access odbc connection to sql server don't work well. It will JUST as well as the other IDE's, and you get the same performance.... Further, bound forms in ms-access can successful be used, but again, as long as stupid things like opening a form to a table without some type of "where" clause must be avoided. (I never do that in JET based solutions...let alone sql server based ones anyway). Again, the fact that SO many ms-access applications open up forms to a table, and THEN let the user browse/search for a record is a HORRIBLE design, and one that should be avoided. So, it is not the fact of ms-access being bad, but the fact that it is SO EASY to simply throw up a form with a large data set, and let the user "have at it". So, even bound forms can quite well be used over a wan. The real problem here is bad practices...not ms-access... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.members.shaw.ca/AlbertKallal
First, what are these three fields that have slowed down your application
and are Invtry.MImage and Invtry.HImage image (binary) fields? Second, you should really take a look with the profiler. Don't forget that we don't have your database in front of us and that it is quite possible that only you will have the possibility of solving this problem. S. L. [quoted text, click to view] "Neil" <njones@pxdy.com> wrote in message news:w9s%d.1003$H06.485@newsread3.news.pas.earthlink.net... > > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:uNR1DNZLFHA.3500@TK2MSFTNGP14.phx.gbl... >> TS = Terminal Server; you can also use Citrix. This is a quick fix for >> getting a multi-user access to an Access' MDB file over the Internet or >> to solve the possibility of corruption when you LAN network is not rock >> solid. However, this solution come with a price ($). > > Not an issue with connection. I'm using the db locally for development > with MSDE and have the same situation with the linked view being slow. > Definitely not a network issue. > >> >> With the use of unbound forms as the solution, you are responsible for >> sending the modifications to the data back to the database. This will >> give you the possibility to use the result of a read-only SQL >> pass-through in a form but, obviously, with the obligation of having more >> coding work to be done. Of course, some of this work can be partially >> automated. You will find more information on that subject on books >> dealing with Access and SQL-Server. (Personally, I prefer to use ADP but >> it has many bugs.) > > Yes, I'm familiar with the approach. It would just be better to save the > development overhead. And, if one were to use unbound forms, then one > might as well just use VB. > >> >> By using the profiler on the SQL-Server, it is also possible that you >> will see something that will give you the possibility of resolving the >> speed problem of you linked views by having a better understanding of >> what Access is doing when it communicates with the server. > > Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is > that it was fine until I added three new fields. So I don't know if > something needs to be optimized, or going from 21 to 24 fields really > makes that much of a difference. > > Neil > > >> >> S. L. >> >> "Neil" <njones@pxdy.com> wrote in message >> news:FCk%d.633$H06.566@newsread3.news.pas.earthlink.net... >>>> You may try to add a virtual index on your linked view: >>>> http://support.microsoft.com/kb/q209123/ >>> >>> Yes, these are added when you first attach the view or table. When the >>> object doesn't have a primary key (as with views) Access prompts you for >>> which field(s) to use as pk. It then stores that information. You can't >>> update the data otherwise. So it's already there. >>> >>>> If this doesn't help, then you will have to use an unbound form coupled >>>> with SQL pass-through queries (or ADO objects) to resolve your speed >>>> problem with MDB. >>> >>> As noted, the reason for not using pass-through is because it needs to >>> be updatable. >>> >>>>Another solution could be to go with TS to simulate a high-speed LAN. >>> >>> What is "TS"? >>> >>>> I don't know where you got the impression that MDB were about the same >>>> as ADP in term of speed. It has been repeated a number of times that >>>> the use of MDB' linked tables and views is only a workable solution for >>>> small databases on a fast Lan. >>> >>> From discussions in this newsgroup. >>> >>> Neil >>> >>> >>>> >>>> S. L. >>>> >>>> "Neil" <njones@pxdy.com> wrote in message >>>> news:Uxa%d.15606$cN6.15135@newsread1.news.pas.earthlink.net... >>>>>I have a situation with an ODBC linked view in an Access 2000 MDB with >>>>>a SQL 7 back end. The view is scrolling very slowly. However, if I open >>>>>the view in an ADP file, it scrolls quickly. >>>>> >>>>> I needed to use an ODBC link for the view because it needs to be >>>>> editable. Otherwise, I would have used a pass-through query. >>>>> >>>>> In previous discussions about using an MDB file vs. an ADP file as a >>>>> front end for SQL Server, the impression I got was that both were >>>>> about the same, but that the MDB was a more mature technology and less >>>>> problematic than the ADP technology. However, the speed difference I'm >>>>> noticing with the ADP file in regards to this view is significant and >>>>> is very disconcerting re. using an MDB file. >>>>> >>>>> Any thoughts/comments/suggestions would be appreciated. I've >>>>> reproduced the view's SQL below for reference. >>>>> >>>>> Thanks, >>>>> >>>>> Neil >>>>> >>>>> SQL for view in question: >>>>> >>>>> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >>>>> INVTRY.attFirstEdition, INVTRY.attSigned, >>>>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >>>>> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >>>>> INVTRY.WebStatusPending, INVTRY.ActivateDate, >>>>> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >>>>> INVTRY.AllowDuplicate, INVTRY.WebAction, >>>>> INVTRY.WebActionPending, INVTRY.DateModified, >>>>> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >>>>> INVTRY.HImage, INVTRY.AdCode, >>>>> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >>>>> THEN - 1 ELSE 0 END AS OnWeb >>>>> FROM vwInventory_Dupes INNER JOIN >>>>> (WebStatus INNER JOIN >>>>> (INVTRY INNER JOIN >>>>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>>>> WebStatus.WebStatus = INVTRY.Web) ON >>>>> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >>>>> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >>>>> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >>>>> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >>>>> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >>>>> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >>>>> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>>>> = 1)) >>>>> >>>>> SQL for vwInventory_Dupes, used as subquery: >>>>> >>>>> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, >>>>> Cast(attFirstEdition AS tinyint) FirstEd, >>>>> Cast(attSigned AS tinyint) Signed, >>>>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, >>>>> INVTRY.YRPUB YearPub >>>>> FROM WebStatus INNER JOIN >>>>> (INVTRY INNER JOIN >>>>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>>>> WebStatus.WebStatus = INVTRY.Web >>>>> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>>>> = 1)) >>>>> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, >>>>> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), >>>>> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >>>>> HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >>>>> >>>> >>>> >>> >>> >> >> > >
Bit fields are often problematic with ADP; so maybe casting them to int will
give something. S. L. [quoted text, click to view] "Neil" <njones@pxdy.com> wrote in message
news:lFt%d.109$gI5.0@newsread1.news.pas.earthlink.net... > MImage and HImage are two of the new fields, and are bit. The other new > field was AdCode, which is varchar. > > > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:eS2v9bdLFHA.4028@tk2msftngp13.phx.gbl... >> First, what are these three fields that have slowed down your application >> and are Invtry.MImage and Invtry.HImage image (binary) fields? >> >> Second, you should really take a look with the profiler. Don't forget >> that we don't have your database in front of us and that it is quite >> possible that only you will have the possibility of solving this problem. >> >> S. L. >> >> "Neil" <njones@pxdy.com> wrote in message >> news:w9s%d.1003$H06.485@newsread3.news.pas.earthlink.net... >>> >>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> >>> wrote in message news:uNR1DNZLFHA.3500@TK2MSFTNGP14.phx.gbl... >>>> TS = Terminal Server; you can also use Citrix. This is a quick fix >>>> for getting a multi-user access to an Access' MDB file over the >>>> Internet or to solve the possibility of corruption when you LAN network >>>> is not rock solid. However, this solution come with a price ($). >>> >>> Not an issue with connection. I'm using the db locally for development >>> with MSDE and have the same situation with the linked view being slow. >>> Definitely not a network issue. >>> >>>> >>>> With the use of unbound forms as the solution, you are responsible for >>>> sending the modifications to the data back to the database. This will >>>> give you the possibility to use the result of a read-only SQL >>>> pass-through in a form but, obviously, with the obligation of having >>>> more coding work to be done. Of course, some of this work can be >>>> partially automated. You will find more information on that subject on >>>> books dealing with Access and SQL-Server. (Personally, I prefer to use >>>> ADP but it has many bugs.) >>> >>> Yes, I'm familiar with the approach. It would just be better to save the >>> development overhead. And, if one were to use unbound forms, then one >>> might as well just use VB. >>> >>>> >>>> By using the profiler on the SQL-Server, it is also possible that you >>>> will see something that will give you the possibility of resolving the >>>> speed problem of you linked views by having a better understanding of >>>> what Access is doing when it communicates with the server. >>> >>> Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is >>> that it was fine until I added three new fields. So I don't know if >>> something needs to be optimized, or going from 21 to 24 fields really >>> makes that much of a difference. >>> >>> Neil >>> >>> >>>> >>>> S. L. >>>> >>>> "Neil" <njones@pxdy.com> wrote in message >>>> news:FCk%d.633$H06.566@newsread3.news.pas.earthlink.net... >>>>>> You may try to add a virtual index on your linked view: >>>>>> http://support.microsoft.com/kb/q209123/ >>>>> >>>>> Yes, these are added when you first attach the view or table. When the >>>>> object doesn't have a primary key (as with views) Access prompts you >>>>> for which field(s) to use as pk. It then stores that information. You >>>>> can't update the data otherwise. So it's already there. >>>>> >>>>>> If this doesn't help, then you will have to use an unbound form >>>>>> coupled with SQL pass-through queries (or ADO objects) to resolve >>>>>> your speed problem with MDB. >>>>> >>>>> As noted, the reason for not using pass-through is because it needs to >>>>> be updatable. >>>>> >>>>>>Another solution could be to go with TS to simulate a high-speed LAN. >>>>> >>>>> What is "TS"? >>>>> >>>>>> I don't know where you got the impression that MDB were about the >>>>>> same as ADP in term of speed. It has been repeated a number of times >>>>>> that the use of MDB' linked tables and views is only a workable >>>>>> solution for small databases on a fast Lan. >>>>> >>>>> From discussions in this newsgroup. >>>>> >>>>> Neil >>>>> >>>>> >>>>>> >>>>>> S. L. >>>>>> >>>>>> "Neil" <njones@pxdy.com> wrote in message >>>>>> news:Uxa%d.15606$cN6.15135@newsread1.news.pas.earthlink.net... >>>>>>>I have a situation with an ODBC linked view in an Access 2000 MDB >>>>>>>with a SQL 7 back end. The view is scrolling very slowly. However, if >>>>>>>I open the view in an ADP file, it scrolls quickly. >>>>>>> >>>>>>> I needed to use an ODBC link for the view because it needs to be >>>>>>> editable. Otherwise, I would have used a pass-through query. >>>>>>> >>>>>>> In previous discussions about using an MDB file vs. an ADP file as a >>>>>>> front end for SQL Server, the impression I got was that both were >>>>>>> about the same, but that the MDB was a more mature technology and >>>>>>> less problematic than the ADP technology. However, the speed >>>>>>> difference I'm noticing with the ADP file in regards to this view is >>>>>>> significant and is very disconcerting re. using an MDB file. >>>>>>> >>>>>>> Any thoughts/comments/suggestions would be appreciated. I've >>>>>>> reproduced the view's SQL below for reference. >>>>>>> >>>>>>> Thanks, >>>>>>> >>>>>>> Neil >>>>>>> >>>>>>> SQL for view in question: >>>>>>> >>>>>>> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >>>>>>> INVTRY.attFirstEdition, INVTRY.attSigned, >>>>>>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >>>>>>> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >>>>>>> INVTRY.WebStatusPending, INVTRY.ActivateDate, >>>>>>> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >>>>>>> INVTRY.AllowDuplicate, INVTRY.WebAction, >>>>>>> INVTRY.WebActionPending, INVTRY.DateModified, >>>>>>> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >>>>>>> INVTRY.HImage, INVTRY.AdCode, >>>>>>> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >>>>>>> THEN - 1 ELSE 0 END AS OnWeb >>>>>>> FROM vwInventory_Dupes INNER JOIN >>>>>>> (WebStatus INNER JOIN >>>>>>> (INVTRY INNER JOIN >>>>>>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>>>>>> WebStatus.WebStatus = INVTRY.Web) ON >>>>>>> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >>>>>>> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >>>>>>> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >>>>>>> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >>>>>>> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >>>>>>> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >>>>>>> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>>>>>> = 1)) >>>>>>> >>>>>>> SQL for vwInventory_Dupes, used as subquery: >>>>>>>
Yes, there is a virtual primary key defined.
[quoted text, click to view] "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:fnvr3110nfld0damimndl2l9niv5tic6qp@4ax.com... > Did you specify a field or set of fields to be used as the primary key for > the > view when you created the link? If not, it will be slower, and will not > be > updateable. > > On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@pxdy.com> wrote: > >>The view in question only returns 1154 rows. So we're not talking a large >>number here. And only returning 24 fields. >> >>There was a modification recently where three additional fields were added >>to the view, and that seemed to slow things down significantly. I'm not >>sure >>if there's some boundary at around 24 fields, or if something else is >>going >>on. But the linked view is *very* slow, taking several seconds just to >>bring >>up the first screen, and then, whenever the scroll bar goes down by one, >>several more seconds just to refresh. The ADP view, on the other hand, is >>fast, with no delay at all, either in bringing up the data or in >>scrolling. >> >>Neil >> >> >>"Steve Jorgensen" <nospam@nospam.nospam> wrote in message >>news:kq9r31tdjl3tv8v10kourkov1psurko5ug@4ax.com... >>> The ways ADPs and MDBs use recordsets are very different, so sometimes >>> one >>> or >>> the other will be faster. >>> >>> By default, the MDB opens a Dynaset which means it first opens a >>> connection to >>> grab the list of keys for the records, then reads the actual records as >>> needed >>> for display by doing individual selects of 10 records aby explicit key. >>> This >>> can help speed when each row has a lot of data because only the rows >>> that >>> are >>> actually viewd will be read, not all of them. On the other hand, if the >>> table >>> has a very large number of rows, Access will spend a lot of time in the >>> background reading the whole list of keys, and if the key lookup for >>> each >>> row >>> is slow for some reason, everything will be sluggish. >>> >>> An ADP, a static recordset is normally used, and an absolute cap of >>> 10,000 >>> rows is applied, so if your recordset has more than that, you just won't >>> get >>> all the rows. Static means in loads all the data at once, then you >>> browse >>> through it in memeory. That's very fast once you load the data, but if >>> each >>> row has a lot of data, especially memo fields and such, it can take a >>> long >>> time to initially load. >>> >>> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@pxdy.com> wrote: >>> >>>>I have a situation with an ODBC linked view in an Access 2000 MDB with a >>>>SQL >>>>7 back end. The view is scrolling very slowly. However, if I open the >>>>view >>>>in an ADP file, it scrolls quickly. >>>> >>>>I needed to use an ODBC link for the view because it needs to be >>>>editable. >>>>Otherwise, I would have used a pass-through query. >>>> >>>>In previous discussions about using an MDB file vs. an ADP file as a >>>>front >>>>end for SQL Server, the impression I got was that both were about the >>>>same, >>>>but that the MDB was a more mature technology and less problematic than >>>>the >>>>ADP technology. However, the speed difference I'm noticing with the ADP >>>>file >>>>in regards to this view is significant and is very disconcerting re. >>>>using >>>>an MDB file. >>>> >>>>Any thoughts/comments/suggestions would be appreciated. I've reproduced >>>>the >>>>view's SQL below for reference. >>>> >>>>Thanks, >>>> >>>>Neil >>>> >>>>SQL for view in question: >>>> >>>>SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >>>> INVTRY.attFirstEdition, INVTRY.attSigned, >>>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >>>> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >>>> INVTRY.WebStatusPending, INVTRY.ActivateDate, >>>> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >>>> INVTRY.AllowDuplicate, INVTRY.WebAction, >>>> INVTRY.WebActionPending, INVTRY.DateModified, >>>> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >>>> INVTRY.HImage, INVTRY.AdCode, >>>> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >>>> THEN - 1 ELSE 0 END AS OnWeb >>>>FROM vwInventory_Dupes INNER JOIN >>>> (WebStatus INNER JOIN >>>> (INVTRY INNER JOIN >>>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>>> WebStatus.WebStatus = INVTRY.Web) ON >>>> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >>>> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >>>> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >>>> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >>>> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >>>> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >>>>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>>> = 1)) >>>> >>>>SQL for vwInventory_Dupes, used as subquery: >>>> >>>>SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, >>>> Cast(attFirstEdition AS tinyint) FirstEd, >>>> Cast(attSigned AS tinyint) Signed, >>>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, >>>> INVTRY.YRPUB YearPub >>>>FROM WebStatus INNER JOIN >>>> (INVTRY INNER JOIN >>>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>>> WebStatus.WebStatus = INVTRY.Web >>>>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>>> = 1)) >>>>GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, >>>> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), >>>> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >>>>HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >>>> >>> >> >
FWIW, in my case I'm testing the db on my laptop using MSDE, and the speed
difference is the same. So there isn't a WAN vs. LAN issue anyway. FWIW. Neil [quoted text, click to view] "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:1svr31tgrv6q82ilc4bou0oa2b3a06i9li@4ax.com... > Well, perhaps the reason for our difference of opinion is that I never > thought > it was particularly appropriate to connect directly to a database server > across a WAN at all. I usually recommend using a terminal server or > rewriting > the app as a Web application or some other kind of 3-tier application. > > On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca > (fill > the blanks, no spam please)> wrote: > >>Are you talking about the WAN or a LAN? >> >>In the case of the WAN, all tests that I have done in the past indicate >>that >>MDB files are much slower to access a SQL-Server backend. The use of >>linked >>views will greatly reduce the performance hit but even with them, I don't >>see the benefice of creating hundred of views instead of creating hundred >>of >>stored procedures. If you want to get some decent speed over the >>internet, >>there are a lot more of work to do using MDB than using ADP and even then, >>I'm not really sure if the use of linked views will achieve the same level >>of performance. >> >>You are entitled to your opinion but all the tests that I have done in the >>past clearly indicate that the use of MDB to access a SQL-Server over the >>internet is only, at its best, a kludge. >> >>For a LAN, the problem is of course much less severe but at the condition >>that the network is not already overcrowded. >> >>S. L. >> >>"Steve Jorgensen" <nospam@nospam.nospam> wrote in message >>news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@4ax.com... >>> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca >>> (fill >>> the blanks, no spam please)> wrote: >>> >>>>You may try to add a virtual index on your linked view: >>>>http://support.microsoft.com/kb/q209123/ >>>> >>>>If this doesn't help, then you will have to use an unbound form coupled >>>>with >>>>SQL pass-through queries (or ADO objects) to resolve your speed problem >>>>with >>>>MDB. Another solution could be to go with TS to simulate a high-speed >>>>LAN. >>>> >>>>I don't know where you got the impression that MDB were about the same >>>>as >>>>ADP in term of speed. It has been repeated a number of times that the >>>>use >>>>of MDB' linked tables and views is only a workable solution for small >>>>databases on a fast Lan. >>> >>> Frankly, I find that statement ludicrous. Many developers including >>> myself >>> have had excelent results using MDBs as front-ends to various kinds of >>> SQL >>> Server back-end for many years before there was such a thing as an ADP. >>> When >>> MDBs are slow, the workarounds to fix it are far less arduous than the >>> workarounds required in ADPs to simply make them function in many cases. >>> >> >
Mr. Kallal:
[quoted text, click to view] Perhaps you misread my original post, since you wrote: "The problem of slowness is not ms-access/jet, but that how the developer uses sql server here." And: "ANY time more then one table is involved, that sql should be put on the server side, or pass through be used. If one follows this rule, then 9 out 10 times, the odbc linked application will perform as good as the ADP one." And: "Linked views is the solution in this poster case. If the poster does this, then no difference will be noticed." I say that you must have misread my original post, since linked views are what are being used here; yet you offer them as a solution. From my original post: "I have a situation with an ODBC linked view in an Access 2000 MDB...." Neil [quoted text, click to view] "Albert D. Kallal" <kallal@msn.com> wrote in message news:FYn%d.745819$8l.276231@pd7tw1no... > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:uin5FbZLFHA.3340@TK2MSFTNGP10.phx.gbl... >> Are you talking about the WAN or a LAN? >> >> In the case of the WAN, all tests that I have done in the past indicate >> that MDB files are much slower to access a SQL-Server backend. > > The problem of slowness is not ms-access/jet, but that how the developer > uses sql server here. > > When ms-access is used correctly, and thoughtfully by a developer, then > the limits of the application become that of sql server, and not ms-access > at al. There are companies out there with 1000 simultaneous ms-access > users hitting the same sql server database. using ms-access is no worse > the using VB6, c++ or any other ide. The ONLY differences here are that of > poor developers. > >> The use of linked views will greatly reduce the performance hit but even >> with them, I don't see the benefice of creating hundred of views instead >> of creating hundred of stored procedures. > > HUH? You mean grabbing data from a link view vs a stored proc makes any > difference here? (it makes not ONE bit!!). > > If you talking about some code that needs to update some data..then, sure, > by all means move the code from ms-access into a stored proc, but we are > not talking about bad development practices here. > > Without question, execute sql on a JET based client through linked tables > needs caution. ANY time more then one table is involved, that sql should > be put on the server side, or pass through be used. If one follows this > rule, then 9 out 10 times, the odbc linked application will perform as > good as the ADP one. > > The fact of the matter is that a native ole-db connection sql server vs a > odbc one has be thrashed out MANY TIMES. If I hit a database and ask the > sql server to return one record, both the ole-db, and a simply linked > table in ms-access both return one record, and produce approximate the > same network traffic. > >> If you want to get some decent speed over the internet, there are a lot >> more of work to do using MDB than using ADP and even then, I'm not really >> sure if the use of linked views will achieve the same level of >> performance. > > Linked views is the solution in this poster case. If the poster does this, > then no difference will be noticed. The problem here is not ms-access, > but users of ms-access who don't take the time to utilize, and effect use > sql server here. > >> >> You are entitled to your opinion but all the tests that I have done in >> the past clearly indicate that the use of MDB to access a SQL-Server over >> the internet is only, at its best, a kludge. > > Compared to what? I know of people successfully using ms-access OVER DIAL > UP lines to sql server (you read that correctly!!). If you make the > effort, (and this is NO more effort then writing in VB6, or other tools, > then you get the same performance). > > So, sure, if a person just lazily links multiple tables..and then tries to > build a local query based on all those linked tables..then you will not > get decent performance. > > However, it is a myth that a ms-access odbc connection to sql server don't > work well. It will JUST as well as the other IDE's, and you get the same > performance.... > > Further, bound forms in ms-access can successful be used, but again, as > long as stupid things like opening a form to a table without some type of > "where" clause must be avoided. (I never do that in JET based > solutions...let alone sql server based ones anyway). > > Again, the fact that SO many ms-access applications open up forms to a > table, and THEN let the user browse/search for a record is a HORRIBLE > design, and one that should be avoided. > > So, it is not the fact of ms-access being bad, but the fact that it is SO > EASY to simply throw up a form with a large data set, and let the user > "have at it". > > So, even bound forms can quite well be used over a wan. > > The real problem here is bad practices...not ms-access... > > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal@msn.com > http://www.members.shaw.ca/AlbertKallal > >
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> [quoted text, click to view] wrote in message news:uNR1DNZLFHA.3500@TK2MSFTNGP14.phx.gbl... > TS = Terminal Server; you can also use Citrix. This is a quick fix for > getting a multi-user access to an Access' MDB file over the Internet or to > solve the possibility of corruption when you LAN network is not rock > solid. However, this solution come with a price ($). Not an issue with connection. I'm using the db locally for development with MSDE and have the same situation with the linked view being slow. Definitely not a network issue. [quoted text, click to view] > > With the use of unbound forms as the solution, you are responsible for > sending the modifications to the data back to the database. This will > give you the possibility to use the result of a read-only SQL pass-through > in a form but, obviously, with the obligation of having more coding work > to be done. Of course, some of this work can be partially automated. You > will find more information on that subject on books dealing with Access > and SQL-Server. (Personally, I prefer to use ADP but it has many bugs.) Yes, I'm familiar with the approach. It would just be better to save the development overhead. And, if one were to use unbound forms, then one might as well just use VB. [quoted text, click to view] > > By using the profiler on the SQL-Server, it is also possible that you will > see something that will give you the possibility of resolving the speed > problem of you linked views by having a better understanding of what > Access is doing when it communicates with the server. Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is that it was fine until I added three new fields. So I don't know if something needs to be optimized, or going from 21 to 24 fields really makes that much of a difference. Neil [quoted text, click to view] > > S. L. > > "Neil" <njones@pxdy.com> wrote in message > news:FCk%d.633$H06.566@newsread3.news.pas.earthlink.net... >>> You may try to add a virtual index on your linked view: >>> http://support.microsoft.com/kb/q209123/ >> >> Yes, these are added when you first attach the view or table. When the >> object doesn't have a primary key (as with views) Access prompts you for >> which field(s) to use as pk. It then stores that information. You can't >> update the data otherwise. So it's already there. >> >>> If this doesn't help, then you will have to use an unbound form coupled >>> with SQL pass-through queries (or ADO objects) to resolve your speed >>> problem with MDB. >> >> As noted, the reason for not using pass-through is because it needs to be >> updatable. >> >>>Another solution could be to go with TS to simulate a high-speed LAN. >> >> What is "TS"? >> >>> I don't know where you got the impression that MDB were about the same >>> as ADP in term of speed. It has been repeated a number of times that >>> the use of MDB' linked tables and views is only a workable solution for >>> small databases on a fast Lan. >> >> From discussions in this newsgroup. >> >> Neil >> >> >>> >>> S. L. >>> >>> "Neil" <njones@pxdy.com> wrote in message >>> news:Uxa%d.15606$cN6.15135@newsread1.news.pas.earthlink.net... >>>>I have a situation with an ODBC linked view in an Access 2000 MDB with a >>>>SQL 7 back end. The view is scrolling very slowly. However, if I open >>>>the view in an ADP file, it scrolls quickly. >>>> >>>> I needed to use an ODBC link for the view because it needs to be >>>> editable. Otherwise, I would have used a pass-through query. >>>> >>>> In previous discussions about using an MDB file vs. an ADP file as a >>>> front end for SQL Server, the impression I got was that both were about >>>> the same, but that the MDB was a more mature technology and less >>>> problematic than the ADP technology. However, the speed difference I'm >>>> noticing with the ADP file in regards to this view is significant and >>>> is very disconcerting re. using an MDB file. >>>> >>>> Any thoughts/comments/suggestions would be appreciated. I've reproduced >>>> the view's SQL below for reference. >>>> >>>> Thanks, >>>> >>>> Neil >>>> >>>> SQL for view in question: >>>> >>>> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, >>>> INVTRY.attFirstEdition, INVTRY.attSigned, >>>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB, >>>> INVTRY.PRICE, INVTRY.Web, INVTRY.Status, >>>> INVTRY.WebStatusPending, INVTRY.ActivateDate, >>>> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, >>>> INVTRY.AllowDuplicate, INVTRY.WebAction, >>>> INVTRY.WebActionPending, INVTRY.DateModified, >>>> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, >>>> INVTRY.HImage, INVTRY.AdCode, >>>> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL >>>> THEN - 1 ELSE 0 END AS OnWeb >>>> FROM vwInventory_Dupes INNER JOIN >>>> (WebStatus INNER JOIN >>>> (INVTRY INNER JOIN >>>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>>> WebStatus.WebStatus = INVTRY.Web) ON >>>> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND >>>> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, >>>> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND >>>> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND >>>> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND >>>> (vwInventory_Dupes.TITLE = INVTRY.TITLE) >>>> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>>> = 1)) >>>> >>>> SQL for vwInventory_Dupes, used as subquery: >>>> >>>> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, >>>> Cast(attFirstEdition AS tinyint) FirstEd, >>>> Cast(attSigned AS tinyint) Signed, >>>> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, >>>> INVTRY.YRPUB YearPub >>>> FROM WebStatus INNER JOIN >>>> (INVTRY INNER JOIN >>>> tabStatus ON INVTRY.Status = tabStatus.Status) ON >>>> WebStatus.WebStatus = INVTRY.Web >>>> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) >>>> = 1)) >>>> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, >>>> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), >>>> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB >>>> HAVING (((COUNT(INVTRY.[INDEX])) > 1)) >>>> >>> >>> >> >> > >
[quoted text, click to view]
> If you look at the sql you posted, there is a number of tables involved. > If you just link a bunch of tables to sql server, and then tell ms-access > to > "join" them together..you will often get poor performance. Again, as noted in my other post, you misread my original post. The first sentence in my post stated I was dealing with a linked view, and, before the SQL I posted I said it was the view's SQL. The SQL is from a SQL view -- pure server side -- nothing done in Access except to link to the view and display the data. [quoted text, click to view] > However, you mentioned that you can't use a pass through. However, you > STILL > are using a sql statement that have SEVERAL LINKED tables. ms-access is > going > to have to figure out those links for you!. This should not be a surprise > if > you think about what is going on here. However, the simple solution here > is > thus to simply create a view on sql server, and then link to that! Which is what was done. [quoted text, click to view] > > Doing this, you will get the same > performance again as the ADP. Well, this gets us back to the original post: that is what was done; yet the linked view in the MDB file is sluggish, taking a long time to scroll, and the same linked view in the ADP file is quick. Both the MDB and ADP are accessing a single view from SQL Server. [quoted text, click to view] > So, the lesson here is that you simply have to be MORE conscience of sql > that joins tables together. You can often get away with at last one extra > joined table via link tables..but as a general rule, you have to build a > query > anyway, so, just built a view on the sql side..and you eliminate the > performance > problem. Apparently not -- at least not in this case. In *theory* a view from SQL Server should perform the same in an ADP and MDB, since the processing is done on the server side. But, as this situation demonstrates, things are not always as simple as they appear in theory. Oh, and before we go there, please note that the large-recordset issue has already been addressed: there are only 1154 records returned by the view, with 24 fields displayed. [quoted text, click to view] > > So, our question is using a ADP going to be faster then a MDB with linked > tables? > > answers: Yes, ADP will be faster if you don't pay attention to avoiding > bottle necks, and thinking about what is going on.... > > So, yes, you do need a bit more caution when using a mdb file and odbc > tables against sql server. Without question, a ADP allows you to be much > more lazy, as ALL > SQL is 100% executed on the sql server side, and thus it is MUCH harder to > screw things up. With a mdb/odbc linked tables, you need extra caution, > and > extra effort to avoid bottle necks. In effect, the increased flexibility > of > a > mdb/odbc setup means you have more rope and more room to hang yourself!! > > However, with a small effort, and some caution, you can EASLEY get the > same > performance with a mdb/odbc as you get with a > ADP. Well, I'm open to suggestions! Neil [quoted text, click to view] > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal@msn.com > http://www.members.shaw.ca/AlbertKallal > > >
MImage and HImage are two of the new fields, and are bit. The other new field was AdCode, which is varchar. "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> [quoted text, click to view] wrote in message news:eS2v9bdLFHA.4028@tk2msftngp13.phx.gbl... > First, what are these three fields that have slowed down your application > and are Invtry.MImage and Invtry.HImage image (binary) fields? > > Second, you should really take a look with the profiler. Don't forget > that we don't have your database in front of us and that it is quite > possible that only you will have the possibility of solving this problem. > > S. L. > > "Neil" <njones@pxdy.com> wrote in message > news:w9s%d.1003$H06.485@newsread3.news.pas.earthlink.net... >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> >> wrote in message news:uNR1DNZLFHA.3500@TK2MSFTNGP14.phx.gbl... >>> TS = Terminal Server; you can also use Citrix. This is a quick fix for >>> getting a multi-user access to an Access' MDB file over the Internet or >>> to solve the possibility of corruption when you LAN network is not rock >>> solid. However, this solution come with a price ($). >> >> Not an issue with connection. I'm using the db locally for development >> with MSDE and have the same situation with the linked view being slow. >> Definitely not a network issue. >> >>> >>> With the use of unbound forms as the solution, you are responsible for >>> sending the modifications to the data back to the database. This will >>> give you the possibility to use the result of a read-only SQL >>> pass-through in a form but, obviously, with the obligation of having >>> more coding work to be done. Of course, some of this work can be >>> partially automated. You will find more information on that subject on >>> books dealing with Access and SQL-Server. (Personally, I prefer to use >>> ADP but it has many bugs.) >> >> Yes, I'm familiar with the approach. It would just be better to save the >> development overhead. And, if one were to use unbound forms, then one >> might as well just use VB. >> >>> >>> By using the profiler on the SQL-Server, it is also possible that you >>> will see something that will give you the possibility of resolving the >>> speed problem of you linked views by having a better understanding of >>> what Access is doing when it communicates with the server. >> >> Yeah, perhaps. The strange thing, as noted elsewhere in this thread, is >> that it was fine until I added three new fields. So I don't know if >> something needs to be optimized, or going from 21 to 24 fields really >> makes that much of a difference. >> >> Neil >> >> >>> >>> S. L. >>> >>> "Neil" <njones@pxdy.com> wrote in message >>> news:FCk%d.633$H06.566@newsread3.news.pas.earthlink.net... >>>>> You may try to add a virtual index on your linked view: >>>>> http://support.microsoft.com/kb/q209123/ |