|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/29/2009 5:36:29 PM
Posts: 2,
Visits: 5
|
|
What coding techniques are best to get a few rows access under half a second?
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/16/2009 8:43:47 AM
Posts: 2,
Visits: 3
|
|
To get a subsecond query reliably, a few elements of coding plus TASM need to be used.
1) Use a unique key primary or secondary index that always narrows down to a single AMP. Once group AMP or all amp queries start occurring, the response time may be 1 second and can vary well beyond 10 seconds because of congestion and locking elsewhere in the system. Hopefully its obvious that this kind of query should also return a few rows, all from the same AMP in the same data block on disk.
2) Use a macro where possible, especially when sending multiple SQL statements. The cost of TCP/P going across the network and back adds up to almost one disk I/O in many sites. So going over the network once, running multiple SQL statements, then getting all the answers back at once saves multiple hops using TCP/IP.
3) Use parameterized SQL to ensure the Parsing Engine can parse and save the steps in memory for reuse when the same SQL is seen again. Parsing can cost up to half a second on older slower Intel processors. Parsing burns a lot of CPU cycles, pulling apart the SQL and rewriting it into the "plastic steps" to be used by the optimizer. If your tactical query parsing plan is saved in cache and reused for subsequent tactical queries, you can save a lot of response time.
Rose
|
|
|
|