OMIGOSH! Proxy tables are FAST!

Normally, everything about a SQL Anywhere Beta is Super Secret Stuff but Sybase has given me explicit permission to publish this article.

There are strings attached:

• I have to tell you where to go (to get the Beta ...why, what did you think I meant? :)

• I have to tell you that I received explicit permission from Sybase to publish this.

• I have to tell you the Beta is Super Secret Stuff... that the Beta agreement does not normally allow articles like this.

There, all done, twice.

OK, got that? Here's the story: I've been working for oh-too-long on an article entitled "Downloading Data into a SQL Anywhere In-Memory Database from Microsoft SQL Server", and that article discusses several techniques using SQL Server 2008 and SQL Anywhere 11.0.1. Two of those techniques are BCP and LOAD TABLE (always the fastest) and proxy tables (always the slowest).

Folks who know about BCP will claim nothing on earth can unload data from a SQL Server database faster than that, and folks who know about SQL Anywhere will say the same thing about loading data via LOAD TABLE.

And lots of people will tell that proxy tables, well, suck when it comes to performance.

All of that is is certainly true for SQL Anywhere 11.0.1: the BCP and LOAD TABLE test for the article took 720 seconds for 1.9 million rows whereas INSERT ... SELECT * FROM a proxy table took 4230 seconds... think "rocket fast" versus "continental drift".

But that's all changing with SQL Anywhere Innsbruck! The INSERT ... SELECT * FROM a proxy table only took 687 seconds! Proxy tables went from last to first place in the performance tests!

Here's my guess what's happened: First, the iAnywhere engineers have made some performance improvements to proxy tables, that I've been told is true.

Second, and this is guesswork on my part, the proxy table path runs directly from one database to another, it doesn't involve writing to or reading from an external disk file like BCP and LOAD TABLE. So, that bit of extra file I/O overhead is always gonna be an anchor 'round the neck of BCP and LOAD TABLE.

FYI, here is my very first post on the SQL Anywhere Beta forum (the "over a minute" claim was wrong, the proxy table test was only 33 seconds faster than BCP and LOAD TABLE):

Subject: OMIGOSH! Proxy tables are FAST!

Here's the full text of my forthcoming article "Top 1 New Features Of
SQL Anywhere Innsbruck"... seriously... I *live* for proxy tables...

CREATE SERVER mss 
   CLASS 'MSSODBC' 
   USING 'DSN=main_BRECK-PC';

CREATE EXTERNLOGIN DBA  
   TO mss  
   REMOTE LOGIN "sa" 
   IDENTIFIED BY 'j68Fje9#fyu489';

CREATE EXISTING TABLE proxy_mss_source  
   AT 'mss.main.dbo.mss_source';

INSERT sa_target
SELECT *
  FROM proxy_mss_source;

-- 11.0.1...
1925469 row(s) inserted
Execution time: 4229.875 seconds

-- Innsbruck...
1925469 row(s) inserted
Execution time: 686.859 seconds

...ok, I'm REALLY serious, that just beat the combination of MSS 2008
BCP and 11.0.1 LOAD TABLE for transferring data straight across, by
over a minute, and hey, NOTHING beats BCP and LOAD TABLE... nothing!

Woohoo!

Breck Is Tilting On His Axis