Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ms access/sql performance question

Posted on 2014-03-27
10
Medium Priority
?
408 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 336 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 332 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 332 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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 58
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

971 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