If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MS Access 2007 & SharePoint Performance
Access 2007 does fine connecting as a client to SharePoint linked lists
(WSS3) remotely in certain situations but perhaps someone could help improve my understanding on performance issues. There seems to be a performance degradation (to say the least) the larger the linked list. For example, if I use a Delete all query in Access on a SharePoint linked list, it takes FOREVER, even if the list is about 100 rows. Does anyone know what goes on underneath the processing of a list on SharePoint? How does Access and Sharepoint actually communicate with each other? Is it true that SharePoint operates on one row at a time? Is the processing of a SharePoint list somehow done locally on the client machine, despite the data residing remotely on a SharePoint list, i.e. does it download a copy of the table, perform the operation, and re-sync with SharePoint? Would a VB.Net application with a remote web reference to SharePoint encounter the same performance problems on larger lists? Is there a more efficient method for list/table processing between Access and Sharepoint? It seems that if Access displays a larger list the performance is acceptable - just the delete function is my biggest problem. Anyway, if anyone has some time I would appreciate any help or guidance. -- Thanks in advance, Glynn |
#2
|
|||
|
|||
MS Access 2007 & SharePoint Performance
If you have the permissions, setup a Trace on the SharePoint SQL Server
machine, and you can watch what happens. Report back what you find. Alternatively, try using a PassThrough query? (untested by me.) "Glynn" wrote: Access 2007 does fine connecting as a client to SharePoint linked lists (WSS3) remotely in certain situations but perhaps someone could help improve my understanding on performance issues. There seems to be a performance degradation (to say the least) the larger the linked list. For example, if I use a Delete all query in Access on a SharePoint linked list, it takes FOREVER, even if the list is about 100 rows. Does anyone know what goes on underneath the processing of a list on SharePoint? How does Access and Sharepoint actually communicate with each other? Is it true that SharePoint operates on one row at a time? Is the processing of a SharePoint list somehow done locally on the client machine, despite the data residing remotely on a SharePoint list, i.e. does it download a copy of the table, perform the operation, and re-sync with SharePoint? Would a VB.Net application with a remote web reference to SharePoint encounter the same performance problems on larger lists? Is there a more efficient method for list/table processing between Access and Sharepoint? It seems that if Access displays a larger list the performance is acceptable - just the delete function is my biggest problem. Anyway, if anyone has some time I would appreciate any help or guidance. -- Thanks in advance, Glynn |
#3
|
|||
|
|||
MS Access 2007 & SharePoint Performance
Hi,
I do not have the ability to setup a trace on the Sharepoint SQL Server. My enviornment is the following: An Access 2007 client linked (via broadband cable, Roadrunner) to a remote SharePoint site hosted on Microsoft SharePoint Online. I looked at performance tips to speed up Access performance and one suggestion was to have a persistent connection to the linked database by opening an Access database variable in VBA using the DAO OpenDatabase method and to keep this variable open as long as my application is running. However, this approach describes opening a back-end .mdb database. Is there an equivalent for a remote SharePoint link? The delete query I am using is a delete all query on a table that yields the SQL: "DELETE ReportOpenInvoices.* FROM ReportOpenInvoices;" where ReportOpenInvoices is a table with an autonumber primary key (ID). Is there a more efficient method to write the query? There are about 100 rows and it takes about 45 seconds. It is my understanding that SharePoint is built on top of SQL Server. Is that true? If so, it would seem that a bulk delete would not encounter a performance problem unless there was single row processing/acknowledgement, but I am at a loss here. The pass-through query seems to want a DSN name. If the SharePoint site is hosted by Microsoft Sharepoint Online, how is the SQL Server name determined? (That question may be a totally inappropriate considering my expertise, or lack thereof). Again, I so appreciate your help and guidance on this subject, as I am trying to use Access as a front-end connection to Microsoft SharePoint Online. If Access 2007 is not an appropriate tool for this strategy, then please let me know. -- Thanks in advance, Glynn "S.Clark" wrote: If you have the permissions, setup a Trace on the SharePoint SQL Server machine, and you can watch what happens. Report back what you find. Alternatively, try using a PassThrough query? (untested by me.) "Glynn" wrote: Access 2007 does fine connecting as a client to SharePoint linked lists (WSS3) remotely in certain situations but perhaps someone could help improve my understanding on performance issues. There seems to be a performance degradation (to say the least) the larger the linked list. For example, if I use a Delete all query in Access on a SharePoint linked list, it takes FOREVER, even if the list is about 100 rows. Does anyone know what goes on underneath the processing of a list on SharePoint? How does Access and Sharepoint actually communicate with each other? Is it true that SharePoint operates on one row at a time? Is the processing of a SharePoint list somehow done locally on the client machine, despite the data residing remotely on a SharePoint list, i.e. does it download a copy of the table, perform the operation, and re-sync with SharePoint? Would a VB.Net application with a remote web reference to SharePoint encounter the same performance problems on larger lists? Is there a more efficient method for list/table processing between Access and Sharepoint? It seems that if Access displays a larger list the performance is acceptable - just the delete function is my biggest problem. Anyway, if anyone has some time I would appreciate any help or guidance. -- Thanks in advance, Glynn |
#4
|
|||
|
|||
MS Access 2007 & SharePoint Performance
Access should be the right tool for the job, at least that's what they've
strived to do with it. To maintain the open connection, open a global variable to one of the linked tables. [Put this in a public dim area] dim grs as dao.recordset [Put within some code when the database starts] set grs = currentdb.openrecordset("tablename") I assume a DSN can be made to hit the SHPTOL site. Contact their help directly to see what they have to offer. "Glynn" wrote: Hi, I do not have the ability to setup a trace on the Sharepoint SQL Server. My enviornment is the following: An Access 2007 client linked (via broadband cable, Roadrunner) to a remote SharePoint site hosted on Microsoft SharePoint Online. I looked at performance tips to speed up Access performance and one suggestion was to have a persistent connection to the linked database by opening an Access database variable in VBA using the DAO OpenDatabase method and to keep this variable open as long as my application is running. However, this approach describes opening a back-end .mdb database. Is there an equivalent for a remote SharePoint link? The delete query I am using is a delete all query on a table that yields the SQL: "DELETE ReportOpenInvoices.* FROM ReportOpenInvoices;" where ReportOpenInvoices is a table with an autonumber primary key (ID). Is there a more efficient method to write the query? There are about 100 rows and it takes about 45 seconds. It is my understanding that SharePoint is built on top of SQL Server. Is that true? If so, it would seem that a bulk delete would not encounter a performance problem unless there was single row processing/acknowledgement, but I am at a loss here. The pass-through query seems to want a DSN name. If the SharePoint site is hosted by Microsoft Sharepoint Online, how is the SQL Server name determined? (That question may be a totally inappropriate considering my expertise, or lack thereof). Again, I so appreciate your help and guidance on this subject, as I am trying to use Access as a front-end connection to Microsoft SharePoint Online. If Access 2007 is not an appropriate tool for this strategy, then please let me know. -- Thanks in advance, Glynn "S.Clark" wrote: If you have the permissions, setup a Trace on the SharePoint SQL Server machine, and you can watch what happens. Report back what you find. Alternatively, try using a PassThrough query? (untested by me.) "Glynn" wrote: Access 2007 does fine connecting as a client to SharePoint linked lists (WSS3) remotely in certain situations but perhaps someone could help improve my understanding on performance issues. There seems to be a performance degradation (to say the least) the larger the linked list. For example, if I use a Delete all query in Access on a SharePoint linked list, it takes FOREVER, even if the list is about 100 rows. Does anyone know what goes on underneath the processing of a list on SharePoint? How does Access and Sharepoint actually communicate with each other? Is it true that SharePoint operates on one row at a time? Is the processing of a SharePoint list somehow done locally on the client machine, despite the data residing remotely on a SharePoint list, i.e. does it download a copy of the table, perform the operation, and re-sync with SharePoint? Would a VB.Net application with a remote web reference to SharePoint encounter the same performance problems on larger lists? Is there a more efficient method for list/table processing between Access and Sharepoint? It seems that if Access displays a larger list the performance is acceptable - just the delete function is my biggest problem. Anyway, if anyone has some time I would appreciate any help or guidance. -- Thanks in advance, Glynn |
Thread Tools | |
Display Modes | |
|
|