Solved

ms access/sql performance question

Posted on 2014-03-27
10
394 Views
Last Modified: 2014-04-08
hi experts,

I just want to verify if when having a control in a form based on dcount(*) of a view, is it ok to use a view that already exists, although it has additional tables /fields that are not necessary in this case, or is better for performance to create a new view on the server just containing the tables/fields necessary for each occasion?

I am using Sql BE and MS Access ADP FE
0
Comment
Question by:bfuchs
10 Comments
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 84 total points
ID: 39960860
I think it will depend on the size of the view.  If the view results are huge and you only need a small portion of it for your purpose, you may want to create a new view.

Sometimes my project are huge and have tons of views and such.  As we build these things to use for specific purposes, then use them again for something else, and then later change the view forgetting that we used it in other places..... whoops?

Scott C
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 83 total points
ID: 39960895
This is one the questions where the answer is: why not just try and test the two options? It will take so little time that is not worth even considering.

The results may be very specific for your environment.

/gustav
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39961162
On a count operation, fields don't matter, but tables do.  You don't want any extra tables/joins being performed.

Jim.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 83 total points
ID: 39962628
In SQL Server, it will be more efficient to code a specific view that uses as few tables and columns as possible.  Query plans confirm this over and over.

I'd suspect that's true for Access, but I've never been an Access DBA and have never looked at its "query plans" (or whatever equivalent Access has for that).
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39965689
Hi Experts,

Thanks for the suggestion, I see you all sort of agree that is better to have a specific view, however just one more question,

In this case I want to know how many emails an employee got in the last week, should I create a view to be specific for that criteria, or is ok the view should be more general, and that can be part of the dcount() where clause, thereby gaining the option to reuse this view for different requirements in the future?
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39966353
<<In this case I want to know how many emails an employee got in the last week, should I create a view to be specific for that criteria, or is ok the view should be more general, and that can be part of the dcount() where clause, thereby gaining the option to reuse this view for different requirements in the future? >>

You can use a general view and apply the criteria, but again, if I'm using a "general view", that usually includes a lot of things I really don't need for a count.  

Personally, I always trend towards creating a new view for every operation I do.  There is some aggregation that's possible (i.e looking up a customer) but for the most part your better off being specific with the view for what your doing.

But I get your point, and yes you can do that.

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39966358
One other thing, on this:

<<I'd suspect that's true for Access, but I've never been an Access DBA and have never looked at its "query plans" (or whatever equivalent Access has for that). >>

The only tuning tools available to end users are JET SHOWPLAN and ISAMStats.

The first when turned on, gives you the costing plan that JET uses for a query.   Its use is limited however because it does not support sub-queries.

ISAMStats gives you numbers on page reads and what not when executing a query.

There are more extensive hooks built into JET for the monitoring of performance, but the tools to use those don't exist outside of Microsoft.

I'd also kick in that it's rare for anyone anymore to be looking to use these.  They take a considerable amount of time to use properly, and anyone that concerned with that level of performance should be looking to move to a SQL Server backend.

 Back in the day when SQL Server wasn't so close to the desktop, some JET DB's might get pushed into the millions of rows and you'd be looking to do some serious tuning, but those days are long gone.

Jim.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39967393
Thanks to all you experts,

@ JDettman ,
when you have a chance, please elaborate more on the SHOWPLAN and ISAMStats topics, in the sense of how to obtain them and how to use them properly.

Thanks,
Ben
0
 
LVL 57
ID: 39978006
Ben,

  Not too many references around any more on SHOWPLAN.    You can download a doc from here:

http://support.microsoft.com/kb/162701

 Which covers both SHOWPLAN and ISAM Stats and how their used.

That doc is a little out of date however, so here's a thread with the keys required to turn SHOWPLAN on:

http://www.experts-exchange.com/Database/MS_Access/Q_27951784.html

ISAM Stats is built-in and always available.

There's also the Microsoft Jet Engine Programmer's Guide, which covers all this, but is now out of print.  However it is still available on Amazon.

Jim.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39987057
Hi Jim,
Thanks for providing support to completion!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now