The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.

Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1, 16.0 and On Demand) are shown here.

The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.

We are pleased to announce that the content for our 2014 conference in Atlanta, GA on April 14-17, is now online and registration is currently open!

We will be presenting more than 150 hours of in-conference sessions, and more than 60 hours of optional workshops before and after the main event. We have some phenomenal speakers who will be sharing their experiences, best practices and advice with our attendees. Some of the biggest names in the SAP database and developer ecosystem will be in Atlanta so be sure not to miss out!

We'll have seven product tracks during the conference: SAP Adaptive Server Enterprise, SAP HANA, SAP IQ, SAP PowerDesigner, SAP PowerBuilder, SAP Replication Server and SAP SQL Anywhere. There will also be a general SAP Development track that will cover developing applications for a variety of SAP Products.

Now for some more good news! We still have some of our extra-special early bird slots available, but these will sell out quickly. If you're not aware of what these are, we are bundling three nights of hotel accommodations at the Hilton Atlanta in with the conference fee to the first 250 people to register. That will save you more than $400 to attend this major event.

We're pleased to announce that SAP is our Diamond Sponsor for this conference. The support and commitment we've received from SAP is outstanding and we are all excited to see the company's passion in making this event a big success. But we need you to join us to make that happen. General registration for the conference is $1195, but our Gold members will receive a special discount of $300 off that fee. (Learn more about our Gold membership here.)

Wednesday, January 15, 2014

An earlier article about calling the GRAPHICAL_PLAN() procedure in SQL Anywhere showed an alternative to Database Tracing wherein you add custom code to your stored procedures to capture query plans. Both techniques (custom code and Database Tracing) share one very important advantage: capturing realistic production query plans in their natural habitat is better than struggling (and failing) to simulate production scenarios in ISQL.

When used together with the Procedure Profiling feature, the custom code approach lets you focus on queries you know are slow (because the Procedure Profiler says so) rather than wading through the massive amounts of output that Database Tracing produces.

Yes, you can tell the Database Tracing feature to filter out fast queries, but that reveals another advantage to the custom code approach: You can easily capture different plans for the same query; for example, one kind of plan when it's running slow-like-continental-drift, and another when it runs quickly. Some of the hardest problems to solve involve queries with changing behavior, and the custom code approach lets you capture both behaviors and compare them. With Database Tracing, unless you capture everything (all the fast plans as well as the slow), you'll only see the slow plans.

This article expands on the earlier technique by storing the plans returned by GRAPHICAL_PLAN() in a table rather than immediately writing them to *.saplan files for display in ISQL's Graphical Plan Viewer.

Here is a step-by-step demonstration using a stored procedure that was running very slowly, the new "Connection History" page being added to the next version of the Foxhound database monitor.

Step 1: Restart the target database.

This will ensure the performance tests will at least begin with a fresh (empty) RAM cache. It's good to take advantage of a large cache to improve performance, but sometimes you can't rely on data being in memory so testing with an empty cache is important.

Step 2: Turn on the procedure profiler.

Here's how to launch the SQL Anywhere 16 procedure profiler in Sybase Central:

Connect to the target database.

Click on the Mode menu item, then

click on Application Profiling.

The Application Profiling frame will appear at the bottom of the window.

Check Stored procedure, function, trigger, or event execution time, then

Look in the Profiling Stored Procedures, Function, Triggers, and Events display, then

click on the Stop Collecting Profiling Information on Selected Databases.

Step 3: Test the application.

It helps to have a script to follow so you can do before-and-after comparisons to prove the changes you make are actually improvements. In this example, the script contained 19 distinct user commands via the GUI: open the page, scroll down one row, scroll down 20 rows, and so on.

Step 4: Examine the procedure profiling results.

There's no easy way to do this if you're running a lot of large stored procedures; you have to open each procedure in Sybase Central's Profiling Results tab, then scroll . . . slowly . . . all . . . the . . . way . . . through the SQL code while watching carefully for hot spots.

Step 5: Identify which queries need attention.

Here's the slowest query in the test; according to procedure profiler it was executed 19 times for a total of 40 seconds:

Step 6. Insert "eyecatcher" comments in the code.

Line numbers change, so does performance; adding an identifying comment to each slow query lets you keep track of hot spots even if you don't get around to working on them right away.

Step 7. Turn off the procedure profiler.

The next step will be to capture graphical plans and you don't want the extra overhead of the procedure profiler to affect the results (although this may be a moot point since the procedure profiler seems to be a lightweight process as far as performance is concerned).

The query_title column uniquely identifies each SQL query being studied so multiple plans for the same query can be studied together.

The capture_started_at column keeps a record of when the test was run; this can be handy when you're purging old rows.

The capture_duration_msec gives a rough guess of how long the query ran... it is not accurate because it includes the time required to build the graphical plan XML, but in many cases it is helpful when looking for fast versus slow queries; e.g., 100 msec is faster than 10,000 msec regardless of overhead.

The plan column holds the GRAPHICAL_PLAN() return values.

Step 9. Prepare to call GRAPHICAL_PLAN().

Each block of code (stored procedure, trigger, event, etc) that's going to call GRAPHICAL_PLAN() needs these four local variables:

Line 2: Replace [TEXT TO UNIQUELY IDENTIFY THE SQL QUERY] with a string you will recognize when browsing through dozens of entries in the captured_plan table.

Line 4: Replace [MAXIMUMN NUMBER OF PLANS TO CAPTURE] with an integer like 1 or 2 to put a limit on the number of plans in case the query is executed many times.

Line 10: Replace [COPY AND PASTE THE SQL QUERY HERE] with the actual query, then double-up any embedded single quotes, and make any other necessary changes as discussed in Capture Plans With GRAPHICAL_PLAN().

The call to GRAPHICAL_PLAN() is coded ahead of the INSERT statement, rather than being included in the INSERT VALUES list, so a value can be calculated for the capture_duration_msec column.

This table often contains millions of rows and consumes gigabytes of disk space; in this example it "only" contained 397,858 rows in 270M of disk space. Indexes do exist on the columns referenced in both the WHERE clause and the ORDER BY...

SELECT TOP 1
rroad_group_2_property_pivot.sample_set_number
FROM rroad_group_2_property_pivot
WHERE rroad_group_2_property_pivot.connection_id_string = '1-21-20140114132910-406'
ORDER BY rroad_group_2_property_pivot.sample_set_number ASC

...but alas, SQL Anywhere's decision to use the PRIMARY KEY index did not result in very good performance.

SQL Anywhere did consider both the PRIMARY KEY index and the secondary CREATE INDEX xconnection_id_string...

...and while it's tempting to try a FORCE INDEX ( xconnection_id_string ) clause to make it use the other index, that should never be the first path chosen. Even if it did improve performance, today's excellent FORCE INDEX choice could create tomorrow's Query From Hell, and it's often (always?) better to let SQL Anywhere decide how to build your plans.

Instead of making SQL Anywhere choose between two indexes on two different columns, one alternative is to create a single index involving both columns. In this case, connection_id_string was chosen as the first column in the index since it's the one performing a selection (connection_id_string = '1-21-20140114132910-406'), and sample_set_number was placed second in the index because it's used to ORDER BY the rows returned by the selection.

In this case, rather than create another index, the existing CREATE INDEX was changed from this:

An "Index Only" scan is the Holy Grail of performance tuning; no data is read from the table, just the index, and the results here prove why it's popular.

Step 15. Make changes to optimize the queries.

In this case, "make changes" means putting the new CREATE INDEX into the Foxhound build.

Step 16. Comment-out the calls to GRAPHICAL_PLAN().

In this example, the new index definition was likely to improve many of the slow queries, so it's worth performing "after" performance tests on everything. The calls to GRAPHICAL_PLAN() add a lot of overhead, so they have to be removed... but not completely removed since some of the queries may still be slow, and they may be involved in the next round of optimization (thus requiring the calls to GRAPHICAL_PLAN() to be added back).

Step 17. Restart the target database.

Step 18: Turn on the procedure profiler.

Step 19. Re-run the test.

Step 20: Examine the new procedure profiling results.

Here's the slow query from before, now located at line 253. As predicted by the ISQL test in Step 14 the procedure profiler now reports a total of only 75 msec was required to run all 19 executions rather than the 40,472 msec reported in Step 5:

Step 21: Verify improvements and look for new hot spots.

In this example, the new CREATE INDEX did indeed speed up several of the old, slow queries, but not all of them... so the process begins again at Step 5, and repeats until the Third Rule Of Program Optimization applies:

Hubba, Hubba!

OK, at first glance it is impressive... any utility that can open a 16 million row, 11G .db file without launching SQL Anywhere itself, and display all the table names, and all the data, has my attention.

<

The GUI does have a few glitches. First of all, the right hand data pane doesn't display the table name, so it's easy to lose track of which table you're looking at when the name is no longer highlighted in the left pane... which is what happens when you click anywhere on the right pane.

Also, if you resize the column widths in the right pane, they tend to revert back to the (narrow) defaults if you click on something else, like the (same) table name in the left pane.

...but, the Stellar Phoenix Repair tool isn't ISQL or Sybase Central, you're not going to use it to write reports, you're going to use it ... when ... you're ... absolutely ... desperate to get your data back.

How well it works for that purpose, I don't know... and I'm not sure how to go about finding out other than waiting until I'm ... absolutely ... desperate :)

Monday, January 6, 2014

"... new CSS 3.0 features can help create lightweight pages by providing built-in support for gradients, rounded borders, shadows, animations, transitions, and other graphical effects that previously required images to be loaded."

First of all, the article is all about web page optimization, nothing about hardware, not even apps.

Second, it isn't just about mobile web pages, the article applies to tethered desktop performance as well. Yes, there's stuff about optimizing m. websites, but much much more about web pages in general.

The Title Is Not Important

What's important is that the tips and ideas apply to websites served up by SQL Anywhere's built-in HTTP server, and that's why it's important to readers of this blog.

Here are a few tidbits from the article...

Concentrate on reducing the number of HTTP requests, reducing the size of responses and optimizing client-side code.