?
Solved

ms access/sql performance question

Posted on 2014-03-27
10
Medium Priority
?
411 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 59

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 59

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 59

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 59
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Implementing simple internal controls in the Microsoft Access application.
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…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 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