Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms access/sql performance question

Posted on 2014-03-27
10
Medium Priority
?
403 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 51

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

704 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