Posts Tagged performance

ColdFusion ORM : What is “N+1 Select problem”

In my last two posts, I mentioned that immediate fetching or lazy fetching can cause ‘N+1 select problem’. If you are wondering what exactly is this, read on.

Consider the example of Department and Employees. When you call EntityLoad(“Department”), following sqls will be executed.

SELECT * FROM department;
SELECT * FROM employees WHERE deptId = ?

The first query will be executed once (1) and the second query will be executed as many times as the department (N). Thus the above entityLoad call results into ‘N+1′ sql execution and thus can be a cause of performance bottleneck. Because of N+1 sqls, this is known as ‘N+1 select problem’. This will almost always happen when the fetching is “Immediate” (using fetch=”select”) or can happen with lazy loading.

With immediate fetching it is obvious why this would happen. When lazy=’true”, this can happen when the association is accessed immediately on each of the owning object (department in this case).

If you think this could be happening in your application, use either of these two options.

  1. set lazy=”false” and use fetch=”join” so that the departments and its employees get loaded together. (Eager fetch)
  2. Keep lazy=”true” but load the department using hql with join. So instead of using EntityLoad(“Department”), use
    ORMExecuteQuery("from Department dept left join fetch dept.employees") 

Tags: , , ,

ColdFusion ORM : Performance tuning – Lazy loading

In the previous post, we talked about different fetching strategies and when to use them. In this post, we will go little deep in lazy loading which is the most popular and commonly used fetching strategy.

As we said in the earlier post – with this strategy, when you load an entity, ColdFusion ORM will load the entity’s data but relations and any mapped collections and are not loaded. They are loaded only when you want to load them i.e by calling the getter method for it and accessing it. Thus the relations and collection mappings are lazily loaded. To give an example, when Department is loaded, all its employees are not loaded and they are loaded only when getEmployees() is called.

There are three types of lazy loading that is provided by ColdFusion ORM for relationship.

  • lazy : This is the default lazy loading that applies to collection mapping, one-to-many and many-to-many relationship. In this case, when you call the accessor for the collection/relation, the collection is fully loaded. Thus when you call EntityLoad() for a particular department, its employees are not loaded at that time. When you call dept.getEmployees(), all the employees object belonging to the department will get loaded. This is achieved by setting lazy=”true” on the relationship property definition in the CFC.Example : In Department.cfc
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="true" >
  • Extra lazy : This applies to one-to-many and many-to-many relationship. This is similar to lazy loading but goes one step ahead of it and does not load the associated objects for for calls like size(), contains(Object). This means that calls like ArrayLen(dept.getEmployees()) or ArrayContains(dept.getEmployees(), anEmployee) or ArrayFind(dept.getEmployees(), anEmployee) will not result into loading any employee object. It will just execute the sql for finding size or finding if the employee belongs to the department. The employee objects will be loaded only when a employee is accessed from this collection. This is very useful if the collection is huge. This is achieved by setting lazy=”extra” on the relationship property definition in the CFCExample : In Department.cfc
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="extra" >
  • proxy : This applies to one-to-one and many-to-one relationship. When an object is loaded, the associated object is not loaded from the database. ColdFusion will only create a proxy object for the related object and when any method is invoked on the related object, the data for the proxy object is loaded from the database and populated in the proxy object. To give an example, if the Employee-Department relation is lazy, when Employees is loaded, the department is not loaded and when you call employee.getDepartment(), you would only get a proxy object. When you call any method on the proxy object, query will be executed on the DB to load department’s data. This is achieved by setting lazy=”true” on the relationship property definition in the CFCExample : In Employee.cfc
    <cfproperty name="department" fieldtype="many-to-one" cfc="department" fkcolumn="deptId" lazy="true" >

    An important thing to note here is – An entity is loaded only once in the request (in Hibernate session to be more specific) and there will always be only one copy of it in the request. So for Employee-Department relationship, which is lazy, if the department is already loaded, calling employee.getDepartment() will not create a proxy object and will return the loaded department object.

Lazy loading can be disabled by setting lazy=”false” on the relationship property definition in the CFC.

Choosing an appropriate lazy loading option is very important for the performance of your application. Extra lazy means more number of trips to the database (each trip to the DB is expensive) but less data in memory whereas no lazy loading means a huge object graph in the memory. So you need to make a balance depending on the application need.

While lazy loading is very useful and helpful in reducing the amount of data loaded from the database and thus reducing the number of objects in memory, overdoing it can have an inverse effect. Lets say in your application, when you load an object, you always access its associated data, lazy loading will again cause ‘N+1 select problem’. This means that a huge number of sqls will be executed which can be avoided by using eager fetch or using HQL with join (See query example of “Eager Fetch” in this post).

There are some other important things to remember/note while using lazy loading

  1. The lazy collection (including one-to-many and many-to-many) is not immediately loaded when you call the getter for the relationship. The sql is executed only when you access anything on the result of the getter (either get its size, or iterate over it etc). lazy=”extra” is little extra lazy (see “Extra Lazy” above).
  2. has*** methods on the entity for relationship are optimized in such a way that it will not result into loading the associated object.
  3. You can quite easily hit the famous “LazyInitializationException“. Mark Mandel explains this nicely in his post on “Explaining Hibernate Sessions“. Ray Camden also talks about his experience with it here. So you need to be careful when using detached object.
  4. If you are retrieving ORM entities in flex, even if you set lazy=”false”, ColdFusion will not send the whole object graph. If you need the relation data to be serialized to flex, you need to set “remotingfetch=’true’” on the relationship property. More on this later.

Tags: , , ,

ColdFusion ORM : Performance tuning – Fetching Strategy

In any application that needs database interaction, DB operations are the key to the application performance. Most of the application performance problems come because the sqls being executed are not optimized or there are huge numbers of queries being executed or there is too much data getting loaded by the query or the columns are not properly indexed or there is no caching being done and the application always hits the DB. In this series, I will try to cover different strategies that you need to use for a good performing ORM based application.

As we all know, the fundamental strategy to tune an application performance is to optimize the sql queries. As a general practice, object retrieval using many round trips to the database is avoided and you would fetch all the required data for a particular operation using a single SQL query using Joins to retrieve related entities. Also, you would fetch only the data that is required i.e data will not be fetched unnecessarily if it is not needed so as to reduce the load on the DB. However this becomes an issue when you use ORM because you no longer write the SQL queries yourself and queries are generated and executed by the underlying ORM engine.

Thankfully ORM engine like Hibernate provides various hooks to optimize the sql as well no of trips that will be made to the database. The most important of these hooks is “fetching strategy” which defines what data will be fetched, when and how.

There are four fetching strategies for loading an object and its associations. (We will use Department-Employee relationship for all the explanation)

  1. Immediate fetching : In this strategy, the associated object is fetched immediately after the owning entity is fetched, either from the database using a separate SQL query or from the seconadary cache. This is usually not an efficient strategy unless the associated object is cached in the secondary cache or when separate queries are more efficient than a Join query. You can define this strategy by setting lazy=”false” and fetch=”select” for the relationship property definition in the CFC.example :
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="false" fetch="select">

    With this strategy, on loading the department object, its employees object will be loaded immediately using a separate SQL query. As a result, this strategy is extremely vulnerable to ‘N+1 Select problem’.

    pros :
    The association is loaded immediately and hence the associated object can be accessed even after the ORM session is closed.
    cons : A large number of sqls get executed causing a higher traffic between application and the database. The association is loaded even if it might not be needed.

    When to use : When the association is almost always read after loading the object and executing separate sql is more efficient than executing a join query.

  2. Lazy fetching : In this strategy, the associated object or collection is fetched lazily i.e only when required. For example, when you load a Department object, all the associated employees will not be loaded at all. It will be loaded only when you access it. This results in a new request to the database but it controls how much of data is loaded and when is it loaded. This helps in reducing the database load because you fetch only the data that is required and is a good default strategy. We will talk about this in much more detail in the next post. For the time being lets just say this is the most commonly used and the default strategy for obvious reasons. You can define this strategy by setting lazy=”true” or lazy=”extra”.
    example :

    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="true" >

    pros : Only the minimum required data is loaded. This avoids loading of entire object graph in memory and hence the performance is generally good.
    cons : If the association is always accessed after loading, this would result in extra sql execution. If the loaded object is accessed in another ORM session (i.e has become detached), extra care must be taken to avoid errors like ‘LazyInitializationException’ or ‘NonUniqueObjectException’.

    When to use : When the association is not immediately read after loading the object. This is the most commonly used and default strategy.

  3. Eager fetching : In this strategy, the associated object or collection is fetched together with the owning entity using a single SQL Join query. Thus, this strategy reduces the number of trips to the database and is a good optimization when you always access the associated object immediately after loading the owning entity. You can define this strategy by setting fetch=”join” for the relationship property definition in the CFC.example :
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" fetch="join">

    With this strategy, on loading the department object, both department and employees data will be fetched from the database using a single join query.

    Even if the eager fetching is not defined in the CFC metadata, it can be done at runtime using ORMExecuteQuery. This can be very powerful in scenarios where in most of the cases, you choose the assocition to be lazily loaded but in some cases, you want to immediately load it. In those case, use Join in the HQL and execute that using ORMExecuteQuery.

    Example :

    ORMExecuteQuery("from Department dept left join fetch dept.employees")
    ORMExecuteQuery("from Department dept left join fetch dept.employees where")

    pros : The association is loaded immediately and hence the associated object can be accessed even after the ORM session is closed. The association is loaded using a single join query which usually is more efficient than executing multiple queries.
    cons : The association is loaded even if it might not be needed. Since the query used is a join query, the resultset returned by the DB will typically contain lot of repititive data. If used for more than one collection of an entity, this will create a cartesian product of the collection’s data and thus causing creation of a huge resultset.

    When to use : When the association is almost always read after loading the object. More suitable for many-to-one and one-to-one association or single collection where the associated objects can be loaded using join query without much overhead.

  4. Batch fetching : This strategy tells Hibernate to optimize the second SQL select in Immediate fetching or lazy fetching to load batch of objects or collections in a single query. This allows you to load a batch of proxied objects or unitialized collections that are referenced in the current request. This is a blind-guess optimization technique but very useful in nested tree loading.
    The concept of batch-fetching is slightly confusing (at least I got confused when I first read about it). So you need to pay careful attention to this.
    This can be specified using “batch-size” attribute for CFC or relationship property. There are two ways you can tune batch fetching: on the CFC and on the collection.

    • Batch fetching at CFC level : This allows batch fetching of the proxied objects and hence is applied to one-to-one and many-to-one relationship. To give an example, cosider Employee-Department example where there are 25 employee instance loaded in the request(ORM session). Each employee has a reference to the department and the relationship is lazy. Therefore employee objects will contain the proxied object for Department.If you now iterate through all the employees and call getDepartment() on each, by default 25 SELECT statements will be executed to retrieve the proxied owners, one for each Department proxy object. This can be batched by specifying the ‘batch-size’ attribute on the Department CFC like

      <cfcomponent table=”Department” batch-size=”10″ …>

      When you call getDepartment() on the first employee object, it will see that department should be batch fetched, and hence it will fetch 10 department objects that are proxied in the current request.
      So for 25 employee objects, this will make Hibernate to execute at max three queries – in batch of 10, 10 and 5.
      You must note that batch-size at component level does not mean that whenever you load a Department object, 10 department objects will get loaded in the session. It just means that if there are proxied instances of Department object in the session, 10 of those proxied objects will get loaded together.

    • Batch fetching at collections : This allows batch fetching of value collections, one-to-many or many-to-many relationships that are unitialized. To give an example, consider Department-Employee one-to-many relationship where there are 25 departments loaded and each department has a lazy collection of employees. If you now iterate through the departments and call getEmployees() on each, by default 25 SELECT statements will be executed, one for each Department to load its employee objects. This can be optimized by enabling batch fetching which is done by specifying “batch-size” on the relationship property like

      In Department.cfc :

      &lt;cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="true" batch-size="10"&gt;

      One important thing to understand here is that batch-size here does not mean that 10 employees will be loaded at one time for a department. it actually means that 10 employee collections (i.e employees for 10 department objects) will be loaded together.
      When you call getEmployees() on the first department, employees for 9 other departments will also be fetched along with the one that was asked for.

    The value for batch-size attribute should be chosen based on the expected number of proxied objects or unitialized collections in the session.

Tags: , , ,

ColdFusion ORM : Troubleshooting – ‘Lazy’ does not work

Few days back Manju logged a bug in CF-ORM saying ‘lazy’ does not work for many-to-one relation and that too on non-Windows machine. At first, I simply rejected the bug because a) ORM can not have anything to do with OS and therefore, if it works on Windows, it works on all the plaform and b) I know it works :-) . But he did not agree and I had to take a look at that machine. And apparently he was right – lazy was not working ! The related entity was in-fact getting loaded immediately. (Question for you – how will you know that lazy is working or not?)

Even after seeing this, I did not believe it and asked him to replicate this on another system and he successfully showed that to me on one another system. And he agreed that it works fine on most of the configurations. The problem exists only on a few of the systems.

This got me thinking – Why would a relation get loaded immediately even after it is marked lazy? The only answer would be – if some one is accessing that lazy field and calling some method on it. I checked his code which was loading the entities to see if there could be any case, where the field would get loaded and unfortunately there was none.

And then suddenly it hit me – what if “memory tracking” is swithched on? That would access each of the field of each object recursively to compute the size of an object and that can definitely cause this. I immediately checked the server monitor and the “memory tracking” was right there staring at me in “red”! It was indeed enabled. I asked Manju to check the other system as well (where lazy was not working) and the memory tracking was enabled there as well.

So the lesson – If the ‘memory tracking’ is enabled on the server, the relationship will no longer remain lazy. And btw, you should enable “Memory tracking” on the server only if you need to track the memory for some troubleshooting. Memory tracking is really really expensive in terms of performance.

Another reason why it might not work for you could be – if you are sending the object to flex. Currently, during serialization, the related objects also get sent irrespective of the lazy attribute set on the relationship. We are still working on it and hopefully by the time we release, this will be fixed.

Tags: , , , ,

DNS lookup Caching in ColdFusion/Java

Two days back, I got an interesting question from our support team. The Customer in this case was using CFLdap which connected to a particular LDAP server and things were working fine. The problem came when they replaced this LDAP server with another LDAP server and assigned the same dns name to the new ldap server. Ideally any connection made henceforth should have worked with the new ldap server but actually that did not happen. ColdFusion started throwing error and it did not work until the ColdFusion server is restarted. Ever seen something similar?

It is obvious that it happend because the IP address was being cached for the host name as a result of which ColdFusion was still trying to connect to the old IP address even though the host name now pointed to a different IP address. This caching also applies for all other network protocol tags such as CFHTTP, CFFTP, CFFEED etc and is not limited to CFLDAP. It is actually the JVM that does this caching. When JVM is requested to resolve a host name, it does the host name to IP address resolution and caches the result. This result will be used for all subsequent lookups. It also caches the negative results. By that I mean, if the dns reolution fails, it caches the failed result for a certain period so that any lookup for that hostname in that period will not result into another resolution on network and will immediately return the failed result.

For more detail on this caching, check out the Javadoc for InetAddress class.

As per this doc, there are two parameters that control this caching

networkaddress.cache.ttl (default: -1) : Indicates the caching policy for successful name lookups from the name service. The value is specified as as integer to indicate the number of seconds to cache the successful lookup.A value of -1 indicates “cache forever”.

networkaddress.cache.negative.ttl (default: 10) : Indicates the caching policy for un-successful name lookups from the name service. The value is specified as as integer to indicate the number of seconds to cache the failure for un-successful lookups.A value of 0 indicates “never cache”. A value of -1 indicates “cache forever”.

Now where do you specify this settying? You can specify this setting in <Java_home>/jre/lib/security/ file. For standalone ColdFusion server it will be in <ColdFusion _dir>/runtime/jre/lib/security/ file.

As you see, by default networkaddress.cache.ttl caches the result for ever and hence it is configured for best performance. Any change to this mean drop in performance. If you don’t want to cache the resolved IP address for ever, as is the case here, you would need to change networkaddress.cache.ttl value to 60 seconds or 300 seconds or any value you feel suitable. You would not want to set it to 0 as that would mean “never cache” the result which might affect the performance significantly.

In which case you would want to change the value for networkaddress.cache.negative.ttl? That would be mostly in case when you want to cache the negative result for a longer time and in turn improving the performance. For example, if you are trying to connect to a hostname which can not be resolved to any ip address, and that happens very frequently, each of the call (as long as they are not in the same 10 sec window) would become very slow. Increasing this value would increase the performance but again you would not want to cache the negative result for ever.

After you change this setting, you will have to restart the ColdFusion server for this change to take effect.

Tags: , ,

Images and CFDocument performance

Sometime back one of our customer reported that using png images in cfdocument makes it very very slow. I could not replicate it with any png image but it did happen with his png image.

Today, Andy reported a similar issue but this time it was for jpeg images. In both the cases, performance hit is huge and it does not happen with all the images.

I spent a significant amount of time debugging it today and it turns out that the reason for both the issues are same – there is something special about these images and that is colorspace. All these images actually had a different colorspace than what java imaging uses. Because of this, when we need the pixel values of image to print it on pdf (by calling BufferedImage.getRGB()), it tries to convert this colorspace to RGB colorspace and that is very very costly. That is where the entire time goes. So how do you fix it? I opened all the images in an image editor and saved it again. This time it got saved in standard RGB colorspace and the time taken to create the pdf got reduced from 110 sec to 1.5 seconds. That is huge!!! Isn’t it? But can you control all the images over the web? NO.. right? Read on.. there is more to this story.

A little bit of looking up on web pointed me to this Sun bug which is the exact same bug which we were hitting. Thankfully it got fixed in mustang i.e JDK1.6 which ColdFusion8 uses by default. But hey wait a second.. Didn’t Andy say that he is seeing it on ColdFusion 8? why do we still see this happening when it is fixed in JDK1.6? It appears that this bug was fixed only in core JDK api but not in JAI (Java advanced imaging) codecLib that ColdFusion 8 uses. So what do we do now? You can do either of these two

  1. Remove clibwrapper_jiio.jar from “lib” folder.
  2. Or, set this system property to the JVM. . You can set this in [cf-install_dir]/runtime/bin/jvm.config if you are using standalone coldfusion server.

You should keep in mind that codecLib libraries are native libraries which are meant to increase the java imaging performance. So disabling it might degrade the performance of CFImage somewhere. Also keep in mind that removing this jar or disabling codecLib will not result into any loss of functionality – it just means that all image operations will be pure java.

There is another related Sun’s bug which I thought might be useful to you. Image loading might get very slow if the server is running in debug mode. Your server is running in debug mode if you see something like this in your jvm.config or VM startup option.

-Xdebug -Xnoagent -Djava.compiler=NONE -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005

This bug got introduced in JDK1.6 and does not exist on 1.5. So make sure that you are not running the server in debug mode.

To summarise, you can do following to increase the performance of image loading in cfdocument.

  1. If you are on JDK1.5, there is not much you can do. The only option is to change the colorspace of images. I will try to see if we can address this in ColdFusion code.
  2. If you are on JDK1.6,
    • Disable codecLib as mentioned above.
    • Disable debug mode by removing the complete debug string mentioned above
  3. In addition to this, you might want to use ‘localurl’ attribute for cfdocument tag. See this for more details.

Tags: , ,

ColdFusion 8 : Changes with File Upload

Till ColdFusion 7, it was not possible to upload a large file. The server could not handle uploading a file of probably anything more than 300-400 MB. And on top of that, if there are multiple people trying to upload large files simultaneously to the server, only thing that you can do is to pray :-)

You might wonder and I agree with you, that its a huge limitation. So why was it this way ? Well.. that was because the server had to keep the entire uploaded file in the memory. Whoa.. Why the heck does the server need to keep the entire uploaded file in memory? – For a single reason of serving the function GetHttpRequestData(). As you know, this function returns a ‘struct’ containing the metadata for the Http request that comes to the server. Since this struct also includes request body, and the uploaded file is part of the request, the file has to be retained in the memory in order to honor this function.

With ColdFusion 8, we have removed the limitation on file upload size. So now you can upload files of any size without causing any issue with the server. I have successfully tried with 5 GB file because that was the biggest I could get hold of but you can absolutely upload files of any size. ColdFusion nicely handles it, reads it the way it should without requiring much memory.

But hey, hold on a sec.. What about the function GetHttpRequestData()? Wasn’t that the reason for this limitation in the first place? Well, the function GetHttpRequestData() works perfectly fine except in this one case – In the multipart request, i.e; request in which a file is uploaded – “content” in the returned struct will be empty. If you need to get the uploaded file content in memory, you can always read the uploaded file and keep it in memory. So that should not be much we are asking for. Are we?

If in case, you absolutely want the content from GetHttpRequestData() and file upload size is of no concern to you, we allow you to revert back to ColdFusion 7 behaviour. All you need to do for this is to set the Java system property “coldfusion.markResetForMultiPart” to “true” and you are back to the old behavior.

Tags: , ,

ColdFusion 8 performance whitepaper

I am glad to see the ColdFusion community so excited with CF 8 performance. We got very encouraging response regarding the performance in our pre-release forums and we are thankful to everyone who gave such invaluable feedbacks. Though people started seeing a very good performance gain in their application on ColdFusion 8, we never talked about the performance benchmark numbers until CFUnited 2007, where Ben showed the performance numbers for ColdFusion 8. No wonder this is the #1reason in Top 8 reasons why you want ColdFusion 8 :-).

Extracting performance up to this extent was not an easy task. We analyzed nearly 2.4 million lines of real life CF application code, to zero-in on the most commonly used tags and functions. Main challenge was after that – analyze the generated java code for all the tags, change the compiler to generate more optimized code, run it through profiler and optimize CF engine for each of the tags and functions and their various combinations. And this went on and on in many many iterations. Overall it was real fun :-)

I still remember the most exciting moment when I ran the load test for CFC, after I had made some code changes, and the result was freaking unbelievable. It looked too good to be true and I was literally running around with the code changes to run it on other machines and verify it . That one small change gave nearly 6x gain :-) . It is not that the CF7 code was inefficient or poorly written. It was only matter of extracting juices as much as possible and putting some smart intelligence.

Check out the ColdFusion 8 performance whitepaper which talks in much detail about the performance numbers for different areas, the methodology used for benchmarking etc. Manju Kiran, who was my QA-buddy for most of the features I worked on, did a tremendous job in setting up and running the benchmark and creating the meat of this wonderful document (and of course keeping me on toes).

Tags: ,