If you are a DBA who is very hands off on the client side, you could just say its not the database, and therefore its not my problem. If I've just described you, feel free to stop reading now. If you want to be able to identify common problems introduced by the application configuration and how to remedy them, please keep reading.
When most of the time is being spent in the database the root of the problem is often a missing index,stale statistics (bad execution plan), or a ill-maintained state tree for versioned environments. It could also be a poorly written definition query for a layer. The cause of slow refresh times outside of the database can be many different things and are usually harder to identify: network issues, resource contention on a heavily loaded citrix machine, etc, etc. For these cases in which database time is far less than user experienced draw time, I'd like to focus on what I see most - a poorly configured map document.
Based your map document configuration for any given layer, the bulk of the processing can be weighted towards the db or the client (or heavy on both). The trick is knowing which changes affect which side of the equation.
Unlike some applications, ArcMap is very flexible. It allows end users to:
- Add whatever they want to the map (as many times as they want)
Users tend to keep layers in the map that that they might need. Often times, they are even left on to draw always. This means useless database calls and client side processing to render the layer.
Also, users can an add new (ad hoc) datasets to the map document that could be contributing to draw performance.
- Draw data at whatever scales they desire
Users can add something very large like streets or elevation contours to the map, and choose to draw it at a scale of 1:1,000,000. Visually there would be no added value but such a configuration may incur massive amounts of IO on the database for every refresh of the map. If you have scale dependent rendering configured for a layer to prevent this, users still have the flexibility to turn it off.
- Use very complex symbology
The more complex your symbology, the more time to render on the client side. This link describes this in detail far better than I can and comes directly from ESRI.
- Symbolize the data based on data attributes
In cases where users are symbolizing by column values where there are hundreds of distinct values, this can be a problem as the client takes additional time and cpu to render the data based on this filter.
- Label the map features based on map attributes
Labeling incurs additional overhead on the client. Rendering and placement of labels requires cpu on the client, and can perform terribly at scales in which many features are drawn and have to be labeled.
Users have many options when it comes to labeling. It can be as simple as labeling map features based on the raw values in a column, or setting up multiple labeling classes for a given layer, and/or writing custom post processing logic via vbscript or jscript if they wish to manipulate/filter the data before labeling occurs. Based on the configurations they choose, additional client processing may be necessary, as well as additional calls to the database during every refresh.
- Join map layers to other sources of information
Arcmap joins are a great way to label, symbolize, and filter based on a column gained from the join, however, it will add to the time required to do the labeling,symbolizing and filtering based on a column that is native to the layer. Also, users have the option to make the join an inner (keep only matching records) or outer join (keep all records), which are handled very differently. An inner join takes place in the db, where the outer join method does the join on the client after retrieving all the necessary rows.
See the "performance tips for joining data" section under this link for more information.
- Filter the results with a definition query
I love this capability for reducing the number of rows to be retrieved from the database. Unfortunately, it also provides the ability for users to submit their own where clause, no matter how poorly written. This is where I usually see SQL written that suppresses index use. Also, the column(s) they are referencing in the definition query may not be indexed, which could result in high database IO and poor draw performance.
- Reproject data on the fly.
A user can pull data from many sources into a map document. Those sources may not all have the same spatial reference information; coordinate systems and/or projections could be different. ArcMap is robust enough to reproject them all on the fly to match the spatial reference defined for your data frame - at a rendering cost to the client.
MXDPERFSTAT is available via ESRI's Arcscripts site. You can get to the download page here. At the time of this posting two builds of MXDPERFSTAT are available: One for ArcGIS 92/93, and another for ArcGIS 931 and 10.
In a nutshell, MXDPERFSTAT is a stand alone executable that takes a map document as input, along with the scales you want to test, and outputs a report of draw times per scale and per layer. An ArcEngine app runs during execution, capturing metrics at each scale for each layer by turning them on and off one at a time. For each layer, the times spent within different "phases" are listed allowing you to see where most of the refresh time is coming from. You'll also be able to see the number of features and vertices drawn per layer per scale and if a definition query was being supplied for that layer as the sql will be listed. Additionally, if you have the additional privileges granted per the user guide, you'll also get database cpu, physical io, and logical io per layer. If all that info wasn't enough, the phases are documented in the user guide and the report will also list recommendations for improvement based on thresholds. If you are reprojecting on the fly, it will say so in the recommendations part of the report for each layer.
This tool comes with a well written users guide, and I've found that the author, Andrew S, has been VERY responsive to questions I've submitted via the download page for the tool in ArcScripts.
There are some system requirements for this tool, but as long as you have ArcGIS (licensed), the mxd you want to run through mxdperfstat, rdbms credentials for a connection to the geodatabase for which you want to test, and some additional rdbms privileges that allow you to see performance counter metrics for your session, you should be good. The specific system requirements are listed on the download page for the tool.
In addition to helping you identify the phase which is contributing to the bulk of the refresh time for a given layer during a crisis, MXDPERFSTAT is also good for tracking progress during general database, Geodatabase and map document tuning. I like to use it to capture a baseline, then repeat tests after tweaking database parameters, geometry types, spatial indexes, and map document layer configurations, etc. I also find it useful to capture oracle traces (with a logon trigger) or sqlserver profiler traces while MXDPERFSTAT is running to get detailed info as to what the database is doing (exec plans, rowsource operation costs, lio,pio,cpu, etc) to correlate with metrics provided via the mxdperfstat report. What I have yet to do is to capture performance monitor counters (perfmon) on the client, which could prove useful for correlating certain map document configurations with specific types of resource consumption.
End users have a job to do and depend on the flexibility of Arcmap and your enterprise GIS to get answers. Unfortunately, sometimes that means you get a phone call or an email because draw times are slow. In my experience, good communication with the end users is key, as well as having the tools available to help identify the source of the problem when its clearly not in the database. Thank you Andrew for publishing MXDPERFSTAT to the masses.
-Sancho
No comments:
Post a Comment