1 2 Previous Next 20 Replies Latest reply on Nov 19, 2014 3:47 PM by Ramesh Reddy

    Caching Virtual Stored Procedure Results

    Ranjith Menon Newbie

      Hi Team,

       

      I have created a Web Service to do data lookup using JBoss Data Virtualization tool.. I created a virtual procedure (using teiid 8.4) and that is working properly. But when i use cache hints it looks like it is still getting the data from the back-end data source instead from the cache for the same input that i provided.. I have set the caching hints as per the document

       

      /*+ cache */

      CREATE VIRTUAL PROCEDURE

      BEGIN

      ......

      END

       

      Is there anything more i need to do to get the cache working. Also after deployment when i look at the admin console for cache statistics it looks

      Hit Ratio: NaN    Total Entries: 0 Request Count: 0

       

      Appreciate if anybody can help me out.

       

      Thank you

      RM

        • 1. Re: Caching Virtual Stored Procedure Results
          Ramesh Reddy Master

          Ranjith,

           

          May be add "resultSetCacheMode=true" on your JDBC URL. See Driver Connection - Teiid 8.9 (draft) - Project Documentation Editor

           

          Ramesh..

          • 2. Re: Caching Virtual Stored Procedure Results
            Steven Hawkins Master

            resultSetCacheMode is not necessary for procedure caching.  It is for user level statements/result sets.  If you enable debug logging you should see in the log statements related to cache usage.  What are the parameter types to your procedure?

            • 3. Re: Caching Virtual Stored Procedure Results
              Ranjith Menon Newbie

              There are two arguments and they are of String type

               

              Thank you

              RM

              • 4. Re: Caching Virtual Stored Procedure Results
                Steven Hawkins Master

                Yes, that should be eligible for caching.  Have a look at the logs at a more detailed level.

                • 5. Re: Caching Virtual Stored Procedure Results
                  Ranjith Menon Newbie

                  Hi Steven,

                   

                  Here is what i see in the log

                   

                  20:16:11,077 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #1)     START USER COMMAND:    startTime=2014-11-06 20:16:11.077    requestID=aDGUIxViDOsm.0    txID=null    sessionID=aDGUIxViDOsm    applicationName=JDBC    principal=xxxxxx    vdbName=ENTS_LDAPAuthN_v1_0_VDB    vdbVersion=1    sql=call LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER(?);

                  20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread aDGUIxViDOsm.0 with state NEW

                  20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) aDGUIxViDOsm.0 Non-cachable command.

                  20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) aDGUIxViDOsm.0 executing prepared call LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER(?);

                  20:16:11,702 DEBUG [org.teiid.PLANNER] (Worker0_QueryProcessorQueue0) [QueryAnnotation<Cached Procedure, MEDIUM,Procedure caching will not be used for LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER since the result set cache is disabled or the results/parameters cannot be cached.,null>]

                  20:16:11,702 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for aDGUIxViDOsm.0 ProjectNode(1) output=[LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER."Message Samples"] [LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER."Message Samples"]

                    PlanExecutionNode(2) output=[LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER."Message Samples"]

                   

                  Not sure what this means. The SP is working fine and i giving the result.

                  For your info my data source is LDAP and i am trying to get the User/Pwd for verification. And i have two parameter as input - UserId and Password

                   

                  Thank you

                  RM

                  • 6. Re: Caching Virtual Stored Procedure Results
                    Steven Hawkins Master

                    > 20:16:11,702 DEBUG [org.teiid.PLANNER] (Worker0_QueryProcessorQueue0) [QueryAnnotation<Cached Procedure, MEDIUM,Procedure caching will not be used for LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER since the result set cache is disabled or the results/parameters cannot be cached.,null>]

                     

                    That confirms we're not caching, but unfortunately doesn't clarify for which reason.  There possibilities are:

                     

                    - the result set cache is disabled.  By default it is enabled.  There would need to be an enabled="false" setting on the resultset-cache config for it to be disabled.

                    - the parameters contain a lob type, which should not be the case since you indicated they are strings

                    - the procedure is performing an update. This is determined by looking at the update count metadata property.  If it's greater than 0, then we assume an update is being performed and thus should not cache.

                     

                    We'll clarify the logging so that it's clear which is occurring.

                     

                    Also anything that is a query annotation information is attached to the plan, so they are available from SHOW PLAN without putting the server into mode into debug.

                     

                    > 20:16:11,077 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) aDGUIxViDOsm.0 Non-cachable command.

                     

                    That line Is referring to the user query level result set cache.  If the result set cache mode is not set, then that is expected.

                    • 7. Re: Caching Virtual Stored Procedure Results
                      Ranjith Menon Newbie

                      Hi Steven,

                       

                      Where do i set of check if result set cache is enabled or not  It is in JBoss Admin Console >> Profile > Teiid > Query Engine >> Result Cache Tab ??

                      Please let me know where to check

                       

                      Also pasting my SP

                       

                      /*+ cache */

                      CREATE VIRTUAL PROCEDURE

                      BEGIN

                          DECLARE string VARIABLES.IN_userPassword = xpathvalue(LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER.USER_PROFILE_InputMsg, '/*:USER_PROFILE_Input/*:userPassword');

                          DECLARE string VARIABLES.IN_UID;

                          VARIABLES.IN_UID = xPathValue(LDAP_USERView_WS.LDAP_USERView_USER_PROFILE.authNUSER.USER_PROFILE_InputMsg, '/*:USER_PROFILE_Input/*:uid');

                          SELECT LDAP_USERView_OUT_View.USER_PROFILE_OutputView.USER_PROFILE_Output.USER_PROFILE_Output_Instance.uid FROM LDAP_USERView_OUT_View.USER_PROFILE_OutputView WHERE (LDAP_USERView_OUT_View.USER_PROFILE_OutputView.USER_PROFILE_Output.USER_PROFILE_Output_Instance.uid = VARIABLES.IN_UID) AND (LDAP_USERView_OUT_View.USER_PROFILE_OutputView.USER_PROFILE_Output.USER_PROFILE_Output_Instance.userPassword = VARIABLES.IN_userPassword);

                      END

                       

                      I am calling this Procedure a web service call. Response is a XML with UserID. Does web service invocation has any bearing to caching ???

                       

                      Thank you

                      RM

                      • 8. Re: Caching Virtual Stored Procedure Results
                        Steven Hawkins Master

                        > Where do i set of check if result set cache is enabled or not  It is in JBoss Admin Console >> Profile > Teiid > Query Engine >> Result Cache Tab ??

                        > Please let me know where to check

                         

                        Unless you have disabled it, it should be enabled by default.  I'm not sure if you can disable it from the admin console.

                         

                        > I am calling this Procedure a web service call. Response is a XML with UserID. Does web service invocation has any bearing to caching ???

                         

                        No it should not.

                         

                        If you are using designer, can you look at the metadata properties for this procedure.  What is the value for the update count?

                        • 9. Re: Caching Virtual Stored Procedure Results
                          Ramesh Reddy Master

                          >> Where do i set of check if result set cache is enabled or not  It is in JBoss Admin Console >> Profile > Teiid > Query Engine >> Result Cache Tab ??

                          >> Please let me know where to check

                          Yes, there should be "edit" box to change it.

                          • 10. Re: Re: Caching Virtual Stored Procedure Results
                            Ranjith Menon Newbie

                            Hi All,

                             

                            Steven,

                            The Update Count meta data is AUTO. I changed this to ZERO and then re-deployed VDB and WS. But no luck on caching. The Cache Count hasn't increased.

                             

                            Ramesh,

                            The ResultCache is by default set to true. So the flag is set, so not sure why it is not caching.

                             

                            I am attaching the standalone-ha.xml which is the configuration used to run the JBoss Server. Not sure looking into this would help.

                            Also do i need to send the JBoss Studio projects if you need to take a look. ???

                             

                            Thank you

                            RM

                            • 11. Re: Re: Caching Virtual Stored Procedure Results
                              Steven Hawkins Master

                              Presumably the cache is enabled.

                               

                              Can you export the DDL for the procedure?  That would be the AdminApi getSchema, or can you post a condensed version of the vdb?

                              • 12. Re: Caching Virtual Stored Procedure Results
                                Ranjith Menon Newbie

                                Hi Steven

                                 

                                Can do the export of VDB from Teiid Designer ???. I am not sure who to run the getSchema Admin API. Can this be done from Admin console ???

                                 

                                thank you

                                RM

                                • 13. Re: Caching Virtual Stored Procedure Results
                                  Ramesh Reddy Master

                                  From web-console application, you can click on the DDL button on the model to get the schema.

                                  • 14. Re: Re: Caching Virtual Stored Procedure Results
                                    Ranjith Menon Newbie

                                    Hi Team,

                                     

                                    Attaching the DDL for the Service.

                                     

                                    Thank you

                                    RM

                                    1 2 Previous Next