Sunday, March 08, 2015

Hibernate queries are slow ! SQL Server's are not my type ? - Here is what you need to know!

  "Hibernate is one of most  cited culprit for slower queries !", though I am kind of victim of this thought for sometime until I "understood" the true wisdom of lame excuses :)

Though sometimes this epic comic reflects my mental turbulence during the performance improvement katas... I learned not to go by hunches, but on real hard facts and truths. One interesting problem I found recently pretty annoying me for days (actually months sparingly) was slow query performance of the total application I was working on. Though the problem is itself is pretty basics, but the process of tuning and troubleshooting is what I thought is interesting.

To give a background, I was running Hibernate/Java/Spring Stack SQL Server 2008 as my DB (it's out of choice unquestionably) but I don't mind and stick to one technology so opportunity to try new / old things are always good.

  Here is the graph of the actual output of the query through my application, a long spike is in CPU for the period of that query running. Initially looking for something to blame I thought the common culprit being the Hibernate (being innocent)  and thought removing it would be improving the query performance. Quickly changing the HQL to Native Queries, got response times around 2ms , WOW! just WOW!, so is the Hibernate being dumb? or some other thing! at least up to this point its just all pointing to Hibernate.

 So giving benefit of the doubt for Hibernate this time and not insanely changing all the app stack to just native queries or different ORM framework, just to keep my sanity and my application's , thought of venturing the journey of true wisdom, and let me take through the journey of findings.

Suspect No. 1 Hibernate

 Though initially I tried with few different common measures, checking out the queries and doing proper indexes, running load tests it's pretty annoying that just with 10 million records it took laborious 187 ms just to run a simple query without even joins !!

 Quickly bringing back the HQL queries and adding a second level cache just for enhancing the user experience, made the problem go away (Temporarily). I was also interested to find out what actual query is being sent by the Hibernate and what would be the Query plan for that query and why it's taking too long!

 So used the SQL Profiler to log the queries being sent to the server and to my surprise the queries are actually sent as stored procedures.

Hibernate Query:

select c.col1,c.col2 from customer c where like @param1 and like @param2

Actual Driver Sent Query:

@param1=somevalue, @param2=somevalue 
declar sp .... select c.col1,c.col2 from customer c where like @param1 and like @param2 

Enabling stats for Hibernate, clearly prints the proper HQL transformed queries, so we are good there, so Hibernate is out of the picture now.

Suspect No 2 JDBC Driver

I was actually using Microsoft JDBC Type 4 Driver.

Ok let's go back and see what is happening on the driver front. Little bit analysis from the documentation it's pretty clear that SQL Server caches the query plan efficiently for stored procedures, so driver is doing the right thing! , so who is the culprit here now ?

SQL Server ?!!! - I hate to say that it's not , it's being clever tries to cache the query, but having one caveat to it "Parameter Sniffing" , in short it's the sql server way to cache the queries and make the parameters dynamic to enable effective query plans, but the queries being sent with @param1 ... so there has to be common problem across.

One option is to stop the driver to send out the stored procedure or make adjustments to queries or remodel the schema to take advantage of the parameter sniffing (though it requires extensive analysis of the application running in prod. with time crunch of existing releases, though i like this way), but hastily I tried to find options to stop sending the stored procedures out of the app, it seems the driver is of no use in handling these through config, shopping around I found JTDS  claiming to be the fastest SQL driver available, so wanted to try this first as it has this parameter prepareSQL=false, which would disable the stored procedure queries

 This made a greater improvement from 180ms to around 90ms. Wait what?  remember 2ms query time ? that's the baseline to reach for, so under the hood, I see the queries being sent like

Select * from customer c where like **N**'somename' and**N**'somevalue'

what is this cryptic 'N' before the value ? oh yea Nvarchar, wait... what? why nvarchar for my varchar type, in short supporting UTF-8 chars, oh but my query is a userId and it's a hashed string, so why need a UTF-8, there is a parameter to disable it to sendUnicode=false pretty cool huh??. This did the trick, no type conversions were done and the query was running around the baseline we established before.

So now the application is back into business, but wait the app is not yet supporting under staging environments with even 1000 req, under load balanced env. two servers behind it, so what's happening initially the thread dumps points to the apache to tomcat connector AJP threads being waiting, ok so that's not acceptable

Ok digging it again, oh yea pretty lame bug in the jdbc url, there was a parameter in the url instance=SQL2008 why for a sql server 2012 R2? due to this setting the driver was trying to optimize the db connections as SQL2008 and 2012 server was so slow in establishing them. and the initial connections are taking too long to warm up the connection pool. So threads are actually waiting on the next set of connection pools to support extra load, initialSize being defaulted to 50, so changing and playing the params, finally fixed the problem just by removing the instance=SQL2008, param from the URL.

 Sigh of relief now, pretty much the environment is able to scale up and handle larger loads, having a proper basic setup ready and established baselines, now we have pretty good application setup to work with.
One thing to remind you, there will be more journey in the future, but would be interesting and rewarding  too...

 One final Thought
 @bpoetz: "programming is awesome if you like feeling dumb and then eventually feeling less dumb but then feeling dumb about something else pretty soon"

See you till the next post!

Thursday, January 09, 2014

Quick Maven Archetypes and Project Setup

If we need to re-use the maven based project as base for future projects, then we can create an archetype which can generate the skeleton for the future project. It applies to both single-module and multi-module maven projects
  1. Go to project root directory
  2. mvn archetype:create-from-project
      - This generates a target folder with generated-resource directory , find archetype-metadata.xml for the details about the file inclusions and module setup
  3. Got to target/generated-sources/archetype/ 
    mvn install 
      - This installs the current archetype into maven repo

[INFO] project created from Archetype in dir: kitchensink\target\generated-sources\archetype\target\test-classes\projects\basic\project\basic
[INFO] No post-archetype-generation goals to invoke.
[INFO] --- maven-install-plugin:2.5.1:install (default-install) @ kitchensink-archetype ---

 --- maven-archetype-plugin:2.2:update-local-catalog (default-update-local-catalog) @ kitchensink-archetype ---

  • Archetype gets created from above shown archetype location, this will be available in the default-update-local-catalog or local catalog

 Using the Archetype: (Eclipse)

  • New -> Maven -> Maven Projects
  • select local catalog , if nothing displays , make sure to check  "Include Snapshot Archetypes" option
  • Then click finish , project is created 

MySql Enable Logs for Queries , Expert Way!

show variables like 'general_log%'

| Variable_name    | Value
| general_log      | OFF
| general_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\Data\pixomachine.log

 set GLOBAL general_log='ON';

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.25a (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
130310  1:09:40    1 Query select * from users

Git 2 Minutes



Eclipse: E-git

Clone Repository:

git clone git_url
 Clones the git repository in the local

GIT Configuration:

git config --list
   Check the git configuration
git config --global --edit
  Edit the git config


Ignoring files during commit
 Get the attached file and put in the GIT HOME Directory..gitignore_global

Git Changes

git status 
  List the current changes

Git Pull

git pull
   Get the contents of remote repository changes into local

Git Add:

git add
 Adds the specified file for commit
 git add -u
 Adds all the files marked 'Untracked' to commit
git add .
 Add all the files tracked, untracked for commit

Removing file from cache:

 Not yet ready to commit some file just remove them from the commit cache
  git rm --cached filename


git checkout  
 To switch branches and to create new branches in the local
git branch -a 
 List all the branches including remote branch
git branch -r 
  List remote branches only
git branch
  Display the current branch
git branch new_branch 
 Create a new branch or switch branches

Push to remote branch

git push -u origin new_branch

Intermediate commits: Stashing

When you middle of something and want to work on some fix, don't want to commit it yet then stash it.
git stash
  Stacks up the current changes, does not commit it) , you can also do
git stash stash_name
  Stash name reference if you want refer in the future


git commit 'commit msg'
  Commits all the changes under commit label


git push
  Push the contents to the remote git repository

GIT Changes

Want to generate a report of recent changes then log it
git log -p > filepath
    To get code differences
git log > filepath
   To get commit differences

Wednesday, March 06, 2013

Hacking the development : Building by Definition

Hacking the project development !! I have been thinking lately, I personally do lot of projects outside my job, some ones are merely CRUD application over the web and some are novel applications.

Philosophy :)

The best way to do a job is not to repeat the same things again. I like products and I like components which we can re-use which can save hours setting up and starting things. Some of the best "on-the-fly" tools like 'Spring-ROO' miss the personalization of things we want on the web development projects.

For example, ROO can create a spring web project structure but what about wiring them with UI, customized UI templates to load the data. Input forms which just mimic the DB we have. So there should be a silver bullet customized to our needs where it can just create all the layers of the web project.

After working around for more than several years in lots of personalized projects, I would hate myself to copy a piece of code from other project and put it here. But yes, I agree I do it lot of times :). It will work fine, but I personally hate myself to do it. But just keeping a code common or building a jar will serve the solution, but not always.


The good solution is just to have a personalized application builder which can ask for what tables are, what is the UI designs of the form to get input, what are the joint forms across table columns and just create a complete project for you. So what it would be like a input mechanism where we say

table:{ 'tableName': 'crazy', columns:{'who','how','when'}}

form:{'table':'crazy','UI':{'who':'Myself','how':'thinking too much','when':'02:23:1983'}}


This is the simplest form of the definition of a simple page form. When it is fed, the builder should take care of creating a eclipse project, maven setup, sql files and finally the whole project should work perfectly.

Wow! that sounds magical. This is too good to be true. But yes as I am writing this, deep in my heart I smile because I have done that for all my projects. Which will have JS Library, Backend-Java Code all generated from this builder I wrote.

Naming It

What is in a name? Yes, But I have named it as 'Chitti Version 1.0' (Inspired by ROBO film). This way it makes me interesting to work on it and shape it as I want. This is basically an interpreter which takes input JSON (for now), for application definition and creates sources including the Controllers, DAO, Business Services plus this will include the JS library for form loading, table loading and all CRUD operations.

I never had any timeline for this to build, because I modularized whenever I was working on other projects. I built it and making it to include, as when I see anything to be included.

I don't know will that be a future taking into considerations about things that would change like technology , complexity, business rules, etc., but this make me to think modularized and saves me lot of time, even If I end up doing more things in future it can grow and can do more neat things just by definition.

Sharing Is Growing

I wish and Love to share the code, but things good to me would be irrelevant to others as what others will want. May be I will share the code if we people are interested. Let me know if you are. I am happy to help and grow. :)

Tuesday, August 14, 2012

The beginning: Some people are strangers to themselves

   May be not a new thing but certain things in life feels new and rejuvenating like rain, flowers, morning sun . They are always there, we have seen them million times but still we feel good everytime. So certain things in life always keeps us lively. New made goals, new perception about life, a new job,  a day before wedding always keep us excited and we are on the toe to start the new thing.

 As days pass by, we see ourselves in confusion as our new made goals or job or a wedding don't contribute what exactly we wanted to do. So this is when the reality strikes, the reality can be good about learning our own self, and yet we don't see our weekness inside, instead we blame it in our colleagues, company, spouse, children etc.,

  Ego is the supremacy which breaks / builds a person, the fear of failure and the fear of the inner-self make ourselves to out grow our fear temprorarily, but this will not last long as we have not made the choice of solving the problem but to postpone it, we changed only our perception about the problem but never thought the solution of it.

 So what is this all about? What we are trying to infer here? This all came from our very nature of the human and it's survival. Human is the lethal animal when mixed with ego, will do the most bigger and dangerous things ever possible.So what we are trying to focus is the very thin piece of the dangerous part of it. It kills our own self . So now being humble atleast to ourself is the key for our own success.

 Atleast once in a lifetime we have to make good friendship with our own heart, listen to what he says, what he really wants?. But this is not a easiest thing. All good things are always hard to get, the love of our life, the job we love the most, the things that we really wanted , all needs a price to be paid. But here the patience, preserverance and humbleness always helps and they are our friends, tools, map to road to success.

 This is samething with knowing one self, we can do mistakes and learn or think before doing a mistake, that will help in the long way. We have access to the information  that was not possible to any centuries earlier (As rightly said by Tonny Robbins). So this all comes to one thing, ME! WHO AM I! .

  "Some people are strangers to themselves" this word striked me really hard, what will be the solution for it. I am still thinking about what will be the solution for it , but what I can think of it as the inability to accept ourselves as we are. This is hard, this is abandoning ourselves from us. This is the most pathetic situation we can be in our life. So how do we come out?

    It's depend on each person and the human are made that way. Each one is unique, but providing a template is possibile and design pattern to solve the problem.

1. Accepting ourselves and others:

Accepting ourselves will make us understanding that mistakes happen, and learning from the mistake is what we should do, it might be a mistake of others. Your suggestion might also help them to correct it, so accept yourselves and others for their mistakes.

 2. Be Flexible:

 Sometimes we are not what we are, we don't get what we want, but still we would have learned something from our steps of failure. This will help us in the future decisions and goal settings. Understanding is the key.

3. Focus and Think:

 Don't be hard on yourself, every big problem will have a small knot which is tied to it, once that is solved then everything falls apart. You might have to search the underlying and fundamental problem insteading just looking the smoke, and sweat for it. So this should be easy if you keep your mind open, get advice from your honest friend, or speak to your mentor.

4. Move Forward, even if it hurts:

 Even you miss the deadlines, even you miss the timelines you set for yourself, don't bother see how even a small step makes some good improvement in your life. Keep Moving and things will go on it own.

5. Relax yourself:

 In 80-20 Rule, you get to relax at the 20, there is where you will get great ideas to do good things faster and more effective way.

Having said, practicing makes it hard. As harder it gets as interesting it will become, never lose hope, might be I will have thoughts about the hope , but for now good luck in the true learning and true freedom.

Happy Independence Day!

Friday, July 17, 2009

FCKeditor v2.4.3 Firefox "script stack space quota is exhausted" fixed !

Hi all,

I was uploading a document of about 10 MB doc in FCKeditor v 2.4.3 from server. In IE it works fine but for the Firefox v>2.0 , I was getting this strange bug "script stack space quota is exhausted" . Thought it was inherent firefox problem, But why did FCKeditor caused it ?

After reading the code and having some inline tests , I found that the following code in
fckeditorcode_ie.js FCKeditor start function caused this problem,

/* Buggy regex match for body contents*/
/* Results in "script stack space quota is exhausted" for large documents*/

var G=A.match(FCKRegexLib.BeforeBody);
var H=A.match(FCKRegexLib.AfterBody);
if (G&&H){
var I=A.substr(G[1].length,A.length-G[1].length-H[1].length);
A=G[1]+' '+H[1];
if (FCKBrowserInfo.IsGecko&&(I.length==0||FCKRegexLib.EmptyParagraph.test(I)))

Here the regex match for stripping the before body and after body elements is the main problem which makes the firefox browser to run out of the memory for large documents.

I replaced the regex match with indexOf to find out start and end of the body tags and stripped it , it solved this problem.

Here is the fixed code.

/*fixed the memory problem using indexOf in mozilla browsers*/
if (FCKBrowserInfo.IsIE)
else if (!B){
var G=A.indexOf("");
var H=A.indexOf("");
if (G!=-1&&H!=-1){
var I=A.substr(G,H);
//A=G[1]+' '+H[1];
if (FCKBrowserInfo.IsGecko&&(I.length==0||FCKRegexLib.EmptyParagraph.test(I)))

I don't see a fix for this in the future versions also , Is this fixed in CKEditor3.0 ? If not please FCKeditor guys save the developer man days ..............

Tuesday, July 07, 2009

Good to be back.

Reasons for my inactivity for past few months,

Reason 1: Have been involved in lots of projects.
Reason 2: Have been involved in hours of learning.
Reason 3: Have been involved in self-building and introspecting myself.
Reason 4: Found how a small dream can grow so much in span of months of hard-work and persistence.

What's the good news ?

Not being posting for a long time ?? :). Just joking . Good News is that there are lots of small things in store to share with you all.

Now I feel good to be back. So Keep checking.

Friday, September 12, 2008

HTML Search Text and Highlight

Jumping straight to the solution:

//modified search function
1.function searchtext(inputText, searchString){
4. //building RegExp object for the search text g-global match,i-ignore case
5. var myregexp = new RegExp(
searchString, "gi");
7. //custom replace function just to highlight the match word in the html source
8. var myNewString =
9. function(matchTxt,key,txt){
10. return "<span style='background-color:red;>'" + matchTxt + "</span>";
11. } );
13. //return the new highlighted text
14. return myNewString;

I think going through the code will be just self explanatory as the solution is straight-forward.But did you see some weird implementation ? Bingo! you found it :) It's the custom replace function.

Let's get dirty with some syntax explanation and we will be soon back with the explanation of its implementation here.

7. //custom replace function just to highlight the match word in the html source
8. var myNewString =
9. function(matchTxt,key,txt){
return "<span style='background-color:red;>'" + matchTxt + "</span>";
11. } );

Here we have written a custom replace function to replace the matching text.We will see how it works.

First it will match the regular expression and it passes the matched text to the first argument of the replace custom function , the second argument is the runtime unique key , the third is whole text to which the search has been made.

So here is the steps that will be followed while executing the code,

1. Match the
mySearchField with myregexp and find the match
2. Pass the match to matchTxt field of the custom function.
3. Wrap the matchTxt with some background color and return the modified text.
4. Replace the match with the modified text .
5. Repeat the steps 1-4 till no match is found.

Thus the explanation of the solution.


1.We can just write the implementation as mySearchField.replace(myregexp,
"" + textse + "");

textse - is the user entered search string

But the problem is the alphabets lower case and upper case of the original content will not be preserved, so our implementation will be a neat solution for this problem.

2. We can use indexOf and lastIndexOf of the search strings and replace the content to highlight search.
As shown in this page indexOf implementation

But when compared for efficiency ours O(n) algorithm and indexOf would be O(n*m)

n - is the number of search string match in the given content
m - is the number of tag matches

So our solution holds good for this case.

Hope this helps and will meet you
soon with another cool post fo custom replace method implementation :) .

An alternate efficient implementation posted by h3 for this code is

function highlight(i, s) {
return i.replace((new RegExp('('+s+')', 'gi')), '<span class="higlight">$1</span>');

This bit straight forward and more efficient . Thanks h3 for your comment. :)