Solved

Access - FE/BE ran Analyze Performance - It suggested an Index????

Posted on 2014-12-02
6
331 Views
Last Modified: 2014-12-02
Hello Experts....
I have a split DB FE/BE and I ran the Performance Analyzer and the Results suggested adding an Index for a Field in one of my tables.

I was in the Front End when I ran it.....  I Clicked Optimize and I realized that I was in the Front End and not the Back End....

I then Opened the Back End and Ran the same Performance Analyzer... (the one in Access itself)  In the Back End It gave NO Suggestions....  However, I looked at the table of the one field I had clicked "Optimize" when I was in the Front End and there wasn't any change to that Field....  I looked at the Settings in the table and for that field the "Indexed" was still set to NO...

So that begs a couple questions...

A) Should you allow Access to Optimize a Table from the Front End or does it even work....
B) Should you just take the suggestion from the FE and go do it in the BE...

OR
Did I just screw up that table when I click on Optimize from the FE???
0
Comment
Question by:wlwebb
[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
  • 3
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 450 total points
ID: 40477388
The analyzer would need to see your queries to make optimisation suggestions, so it makes sense to run it from the front-end database and then manually apply those changes to the tables in the backend. With a split FE/BE architecture, the optimiser cannot modify the table index in the linked table, so you'd have to do it manually.

I think that answers both your A) and B) questions. I am  sure that clicking optimize in the FE cannot screw up your linked table in the BE.
0
 

Author Comment

by:wlwebb
ID: 40477396
Thanks!  I'll see if anyone else comments as well
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 450 total points
ID: 40477435
That's fine. In the meantime... Does the analyzer-suggested index make sense to you, given the queries, forms and reports that you have defined in the FE? Does the table in question already have a primary key defined? Do you join that table to others in any queries or lookup values from the column that it is suggested you create an index on?

Just bear in mind that the analyzer wasn't created to deal with FE/BE databases. It would be able to apply the suggestions it made if you had the whole database in a single file. If you pushed the FE/BE thing a little further and put the BE on MSSQL, you could use the SQL Server Database Engine Tuning Advisor on the backend. The difference is that in that case the backend is an active server rather than a passive file.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 50 total points
ID: 40477465
I think Simon has you squared away.
The optimizer isn't going to hose anything up.
Indexing can be a bit of a black art.

Good table design (autonumber as Primary Key, foreign keys are autonumbers from other tables) takes care of most of it.
An index is almost always recommended on fields used in JOINS (see above :)
An index on fields used in criteria (Between ThisDate and ThatDate) can also help performance.
Indexes on text fields used in criteria or heaven-forbid JOINS can REALLY boost query performance

BUT

Indexes have overhead.
The system has to create, maintain and update them.
So useless indexes create overhead for little gain.

ALSO

Access has an upper limit of 32 indexes per table
And Access insists that each foreign key has an index.
32 seems like a lot -- and depending on your application it might be.
I've hit it, though, and it was one of the drivers behind moving the backend to SQL Server to move beyond that limit.
So, on your main table, you may wish to considered the Analyzers advice -- and disregard it.
0
 

Author Comment

by:wlwebb
ID: 40477542
Simon....

 In the meantime... Does the analyzer-suggested index make sense to you, given the queries, forms and reports that you have defined in the FE? ....

They did make sense.  Both were on Yes/No fields that I used to filter (ie: ClosedShift and VoidShift)  

I am relatively new to DB so SQLServers are beyond me at this point but both you and Nick have given some good info, just not sure I'm ready to tackle that yet.....

I'm going to close this an award the points
0
 

Author Closing Comment

by:wlwebb
ID: 40477545
Thank you for the input..........!!!!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

751 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