• About

On Technology

~ SOA and Integration blog

On Technology

Monthly Archives: July 2013

Importance of understanding your database

25 Thursday Jul 2013

Posted by Padmarag Lokhande in Integration, Uncategorized

≈ Leave a comment

Recently I was working on an integration project where the database used was MS SQL server and the integration platform was Oracle SOA Suite.

The service itself was quite simple – to fetch some data from SQL server and Enqueue it on JMS queue. We used DB Adapter for polling the database. We used the Delete strategy and the service was distributed on a cluster.

Once the ids were enqueued, there was a seperate EJB-based webservice which queried same database to create the canonical. We have used JPA Entity Beans for ORM. There is a particular query to get some extra information from a table which does not have foreign-key relation. The query used a single parameter – a string.

However we observed a huge performance issue for SQL server as well as the website hosted on the database. We observed 99% CPU usage.

It was our SQL DBA who found out the issue. The column in database was varchar, the index was based on same. However, the query parameter that got sent to the database was using nvarchar. This caused a full table scan and completely skipped the index.

The solution use “sendStringParametersAsUnicode” property of Weblogic SQL Server driver. By default everything gets sent as Unicode from JDBC Driver, using “sendStringParametersAsUnicode=false”, we made the driver send the parameter as varchar and immediately saw the difference. CPU usage was down to 1%.

This underscores the point that Frameworks and Engines abstract out a lot of features, but it is necessary to understand you database to make optimal use of it.

Reference – http://msdn.microsoft.com/en-us/library/ms378988.aspx

Advertisement

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • April 2020
  • February 2019
  • April 2018
  • July 2015
  • July 2013
  • October 2012
  • June 2012
  • May 2012
  • September 2011
  • April 2011
  • March 2011
  • December 2010
  • August 2010

Categories

  • Camel
  • Database
  • Devops
    • Amazon AWS
    • Docker
    • Kubernetes
  • Integration
  • Java
  • JMS
  • MuleSoft
  • Oracle
  • Siebel
  • SOA
    • BPEL
    • REST
  • Uncategorized
  • Zapier

Meta

  • Register
  • Log in

Blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • On Technology
    • Already have a WordPress.com account? Log in now.
    • On Technology
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
%d bloggers like this: