Stock service's data warehouse now delivers in seconds, not hours

Pink OTC delivers virtual real-time stock reports using a new database, software

Until a year ago, Pink OTC Markets Inc. had a problem. The company, which operates the Pink Sheets over-the-counter stock quotation service and the OTCQX service for foreign stocks to trade in the United States, couldn't deliver the reports its customers wanted.

Companies whose stock prices were quoted on the exchanges wanted data to understand why their stock price moved, as did trading firms.

Head traders, meanwhile, wanted to get data to analyze the performance of traders working underneath them, while compliance officers wanted the same data to make sure they were following beefed-up SEC regulations.

The company used an Informix database from IBM to process stock trades and quotes as well as store it for data warehousing purposes. But reports took several hours to generate.

And without a Web front-end, they would have to be created by a Pink OTC employee for every ad hoc inquiry, said Rahul Bose, director of application development for Pink OTC.

Last year, Pink OTC began testing a data warehouse using a columnar database from Vertica Systems Inc. and high-speed data-integration software from SyncSort Inc.

"Reports that used to take two hours to run now can show up 10-15 seconds later," said Bose.

The reports can be generated on stock quotes and trades executed as recent as 5 seconds ago. That virtual real-time performance is enabled by SyncSort's DMExpress software, which the vendor says can load 5.4 TB of data into the Vertica data warehouse in an hour, and the three server cluster now running Vertica in production.

Not that Pink OTC needs that kind of performance, yet. Its billion record database takes up about half a terabyte. But the firm is adding several million records a day, a rate of data volume that should grow, said Bose, as the markets recover and as Pink OTC continues to attract more large foreign firms to OTCQX.

OTXQX lets foreign stocks make themselves available to U.S. investors without having to, for example, list as an American Depository Receipt (ADR) on the New York Stock Exchange. This allows them to avoid more direct SEC scrutiny and the extra costs associated with Sarbanes-Oxley.

Firms listing on OTCQX already have a combined market capitalization of $420 billion, said Bose. They include Benetton, Adidas and Roche, said Bose.

Pink OTC looked at three main alternatives to Vertica-SyncSort: a separate Informix instance tuned for data warehousing, the InfoBright columnar database, and a popular capital markets application called OneTick, said Bose.

The firm eliminated OneTick because it didn't support the JDBC API for Java. Pink OTC is a Java shop.

The firm then eliminated Informix because of the cost. According to Bose, Informix licenses combined with a Storage Area Network (SAN) he felt would be needed to "get good performance" with Informix would have brought the cost to more than a million dollars, compared to the several hundred thousand it spent on Vertica and its cluster.

That cluster is made up of Sun servers with dual quad-core CPUs and the Red Hat Linux operating system, using conventional hard drives.

"In the grand scheme of things, these are pretty low-end machines," he said.

It then chose Vertica over InfoBright for several reasons. InfoBright, while easier to use, "doesn't give you as many knobs and dials" to enable performance optimization, he said, though he cautioned this was based on a bakeoff from last year.

While InfoBright was roughly as fast as Vertica out of the box, it lagged by up to a factor of ten after Vertica was optimized, said Bose.

Vertica was also easier to set up in a cluster than InfoBright, Bose said, and can continue to run even when one node crashes, is taken down, and then rejoined.

Join the PC World newsletter!

Error: Please check your email address.

Tags stock marketsreportsdata warehousejavabusiness intelligence

Our Back to Business guide highlights the best products for you to boost your productivity at home, on the road, at the office, or in the classroom.

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

Eric Lai

Computerworld (US)
Show Comments

Essentials

Microsoft L5V-00027 Sculpt Ergonomic Keyboard Desktop

Learn more >

Lexar® JumpDrive® S57 USB 3.0 flash drive

Learn more >

Mobile

Lexar® JumpDrive® S45 USB 3.0 flash drive 

Learn more >

Exec

Lexar® Professional 1800x microSDHC™/microSDXC™ UHS-II cards 

Learn more >

Lexar® JumpDrive® C20c USB Type-C flash drive 

Learn more >

Audio-Technica ATH-ANC70 Noise Cancelling Headphones

Learn more >

HD Pan/Tilt Wi-Fi Camera with Night Vision NC450

Learn more >

Budget

Back To Business Guide

Click for more ›

Most Popular Reviews

Latest News Articles

Resources

PCW Evaluation Team

Azadeh Williams

HP OfficeJet Pro 8730

A smarter way to print for busy small business owners, combining speedy printing with scanning and copying, making it easier to produce high quality documents and images at a touch of a button.

Andrew Grant

HP OfficeJet Pro 8730

I've had a multifunction printer in the office going on 10 years now. It was a neat bit of kit back in the day -- print, copy, scan, fax -- when printing over WiFi felt a bit like magic. It’s seen better days though and an upgrade’s well overdue. This HP OfficeJet Pro 8730 looks like it ticks all the same boxes: print, copy, scan, and fax. (Really? Does anyone fax anything any more? I guess it's good to know the facility’s there, just in case.) Printing over WiFi is more-or- less standard these days.

Ed Dawson

HP OfficeJet Pro 8730

As a freelance writer who is always on the go, I like my technology to be both efficient and effective so I can do my job well. The HP OfficeJet Pro 8730 Inkjet Printer ticks all the boxes in terms of form factor, performance and user interface.

Michael Hargreaves

Windows 10 for Business / Dell XPS 13

I’d happily recommend this touchscreen laptop and Windows 10 as a great way to get serious work done at a desk or on the road.

Aysha Strobbe

Windows 10 / HP Spectre x360

Ultimately, I think the Windows 10 environment is excellent for me as it caters for so many different uses. The inclusion of the Xbox app is also great for when you need some downtime too!

Mark Escubio

Windows 10 / Lenovo Yoga 910

For me, the Xbox Play Anywhere is a great new feature as it allows you to play your current Xbox games with higher resolutions and better graphics without forking out extra cash for another copy. Although available titles are still scarce, but I’m sure it will grow in time.

Featured Content

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?