The Ups And Downs Of Analyzing Middleware

During the test runs involving the poorly coded SQL, both EcoSCOPE and Sniffer let us know that response times had degraded beyond our threshold value of 500 milliseconds. Neither tool directly blamed the SQL for the poor performance, but both let us look in detail at the request and response packets making up the too-slow SQL Server TDS and Oracle SQL*NET database transactions. Detailed decoding of DB2 DRDA packets was beyond the ability of both tools, though EcoSCOPE let us monitor DRDA from a network-utilization perspective. We could see that the convoluted SQL verb statements flowed quickly to the database server, and the subsequent responses took very little time to reach the clients. It was obvious that the database server was the bottleneck (see "How to Use the Analyzer's Statistics" page 114). Inspecting the packet-level detail and event delta times, unfortunately, was neither easy nor quick. Barry Nance, a computer analyst and consultant for 25 years, is the author of Introduction to Networking, 4th Edition (Que, 1997), Using OS/2 Warp (Que, 1994) and Client/Server LAN Programming (Que, 1994). He can be reached at barryn@bix.com.
	How To Use The Analyzer's Statistics The protocol analyzer alerts you when a data-access request occurs and reports the time the response goes out on the wire. An inordinately long time lag between the two events is a common performance problem on many networks, and you may not be able to solve it by changing the network itself. The culprit is often the time the database server requires to process SQL queries. Knowing what h appens on the database server between the request and the response can help you tune SQL statements for faster processing. Eliminating redundant joins and simplifying the SQL can help the SQL compiler process a database request faster. As a result, middleware will appear to consume fewer resources. Before the RDBMS (relational database management system) engine can store or retrieve data, the SQL compiler component has to recognize and understand English verbs, nouns and prepositions (a SQL Select command, for instance), then turn these SQL statements into internal instructions, which it gives to the database engine's retrieval and update processes. The SQL compiler's job is complicated by the fact that it has to operate as quickly as possible. A SQL compiler processes a SQL statement in five basic steps. The first parses the SQL, examines the SQL for syntax errors, then converts the SQL parse tree into an internal representation. (IBM, which invented SQL, calls the internal format QGM, or Query Graph M odel.) The second step examines the reformatted SQL to ensure that executing the statement won't violate referential integrity, and notes whether the database engine should process a constraint or trigger for the SQL. Next, the SQL compiler rewrites the SQL statement, replacing view references with actual column names and transforming the SQL for processing by the optimizer. The transformation eliminates redundant joins, adds implied predicates and converts Intersect clauses to Exists subqueries. The optimizer itself, the fourth step, uses cost-based algorithms to determine the most efficient execution method for the SQL. The optimizer finds the best join order, for example, and decides whether the execution of the SQL statement will be CPU- or I/O-bound. The fifth step "remembers" the essence of the SQL for later comparison with and optimization of subsequent SQL statements and delivers the compiled, optimized SQL statement to the database engine. The engine accesses the data and sends a response to the c lient.

Other Workshops Guarding the Flaank With RADIUS & TACACS+ By Dan Backman Integrating LDAP and The Exchange Directory By Nancy Cox Achieving Production Quality Messaging By Nancy Cox