• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

ms access/sql performance question

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
bfuchs
Asked:
bfuchs
6 Solutions
 
clarkscottCommented:
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
 
Gustav BrockCIOCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
On a count operation, fields don't matter, but tables do.  You don't want any extra tables/joins being performed.

Jim.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott PletcherSenior DBACommented:
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
 
bfuchsAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
bfuchsAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
bfuchsAuthor Commented:
Hi Jim,
Thanks for providing support to completion!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now