Query Criteria, Part 1: Be specific!

re: Query Criteria, Part 1: Be specific!

Faisal — Fri, 09 Sep 2011 16:28:06 GMT

Hi Steven,

Thanks for your reply. I had thought about the server hardware not being suitable for running the queries fast enough, and had pursued this with the technical team.

To ensure that I was on the right track with this, I got a trial subscription from accesshosting today, my demo site for the Issues app is at faisalmajeed.accesshoster.com/Issues

I dont know how to grant you access on this, and all the app has is lorem ipsum kind of stuff, with a keyword pregnancy in 4 records. Again the app performs fine when run on local machine, but fails to give any results even on the accesshosting.com site.

I can pass the app to you, I dont know where to upload it for you. Its the issues template with the only difference of having 2000 records in the issues table filled with lorem ipsum.

This is strange that there is a similar performance issue between our Internal sharepoint Access web service and an externally hosted by AccessHosting.com which is even hosted on the cloud for better performance.

We kept on blaming ourselves and our technical team so we didn't test this in a real world environment but now that I did, I am surprised at the results. I will keep visiting the blog, to keep the conversation going if you need my email address or phone let me know or vice versa. I really appreciate your feedback on this.

re: Query Criteria, Part 1: Be specific!

Steven Thomas — Thu, 08 Sep 2011 16:06:26 GMT

Hello Faisal. The first thing that comes up is the possibility you should add some server capacity. Have a look at this Technet article about capacity planning for Access Services: technet.microsoft.com/.../gg440600.aspx

There may be other issues involved and I may need more information about your database. However, based on the content of that TechNet article I figured I should get it to you ASAP. I'm still looking at other possibilities and will roll those up in one more comment.

-Steven

re: Query Criteria, Part 1: Be specific!

Steven Thomas — Thu, 08 Sep 2011 15:50:52 GMT

Hi Faisal - this is a fine place for your question; thanks for asking it! Let me do a little research for you and I'll post back here.

-Steven

re: Query Criteria, Part 1: Be specific!

Faisal — Thu, 08 Sep 2011 13:03:16 GMT

I am not sure if this is the place for this question. I have started using the "Issues" webdatabase provided by MS as a template against the latest Enterprise Sharepoint edition.

I am faced with a very serious performance problem with queries. when I use the search function in the "Issues" database on the sharepoint GUI against oh about 1400 records, it takes 3-4 minutes to get a result back. The same query when I open in a local (client) copy of Issues that is online with sharepoint it runs in a couple of seconds, can someone help me in finding what is wrong with sharepoint and or access services that this query is taking that long to run.

The whole sharepoint web databases are unusable if the search is not working on the Sharepoint GUI as that is where the application gets used. Our IT staff is stumped as to how tof ix this performance problem with the query. I mean its awesome when it is on client but its real slow on sharepoint side.

re: Query Criteria, Part 1: Be specific!

Steven Thomas — Wed, 24 Aug 2011 15:41:49 GMT

Wow! Great feedback, Alan. We really appreciate the thoughtful consideration.

-Steven

re: Query Criteria, Part 1: Be specific!

Alan Cossey — Wed, 24 Aug 2011 06:59:30 GMT

Steven,

In case it will help the case for making Access reporting live in Office365, may I bring to your attention the following information (as I understand it):

• Access web databases widen Access’s reach considerably, but do have some weaknesses, e.g. a lack of action and union queries and, relevant here, the ability to group and summarize data unless in a report. The use of data macros to try to do anything other than the bare minimum of summation is not feasible. Access web databases need all the help they can get in this area. Office365’s emasculation of their functionality is a major hindrance, not a help

• If a user is using a web UI with a published Access web database it may well mean they do not have Access itself on their machine (why would they use a web UI if they have program itself?). Thus the suggestion at office.microsoft.com/.../build-and-publish-an-access-database-to-sharepoint-HA102435342.aspx to open a report from a database hosted in Office365 is not realistic. Users will not usually have access to Access itself.

• The EU has relatively strict controls on the transfer of personal data, e.g. Directive 95/46/EC, and thus if data is moved outside the EU it must either be into a country with appropriate legal data protection, e.g. Canada or Argentina (but NOT the USA) or the organisation involved must have obtained permission under the Safe Harbor rules. Microsoft have done this so Office365 is OK as far as I know. Currently I am aware of no hosted SharePoint 2010 options meeting these requirements other than Office365.

• Thus EU organisations wishing to use Access web databases seem to have no ability to use hosted Access web databases which are fully functional and which meet legal requirements. Thus such organisations will be reluctant to use Access web databases.

re: Query Criteria, Part 1: Be specific!

Steven Thomas — Tue, 23 Aug 2011 21:35:10 GMT

Alan I am happy to help you get the information you need to make a decision - I absolutely understand your position. You weren't harping, you were being persistent. I hope you continue to follow the Access blog - it takes commenters to make a community really work!

re: Query Criteria, Part 1: Be specific!

Alan Cossey — Tue, 23 Aug 2011 19:30:02 GMT

Steven,

Sorry to have harped on about this. There was no information coming from the Office365 people and I appreciate you answering the question (even if the answer was not the one wanted).

re: Query Criteria, Part 1: Be specific!

Alan Cossey — Tue, 23 Aug 2011 17:37:24 GMT

OK, thanks Steve. I had not realised "there are no current plans to support in-browser Access reports in Office 365". Thanks for clarifying this.

re: Query Criteria, Part 1: Be specific!

Steven Thomas — Tue, 23 Aug 2011 15:01:32 GMT

Hello Alan,

I have said what is going on - there are no current plans to support in-browser Access reports in Office 365, but the product team is considering whether to add it. I don't have any more detailed information. I can't speculate as I am an employee and it would be inappropriate, but I think that the reasons may be obvious if you think about it.

I've contacted a different person who may be able to get some movement on the forums. I wouldn't expect to hear anything different there unless something changes, but perhaps the unanswered questions could be handled. Thanks again for your help.

-Steven