1 2 Previous Next 15 Replies Latest reply on Jan 24, 2013 5:03 PM by michaeljconner

    How does the Web Service Model Work

    michaeljconner Newbie

      I'm new to teiid in general as well as the Teiid Designer (7.4.2), and have gotten web service generation to work, but I don't understand how it is working.

      Specifically, I am generation a web service from an existing virtual procedure, lets call it ProcX.  ProcX takes a couple of string arguments, i_y and i_z, and returns a ProcXResult.

       

      In the Designer, I right-click on ProcX, Modeling > Create Web Service, and go through the wizard.  It generates a ProcX_WS.xmi, a ProcX_OutputView.xmi and the input and output xsds used by them.

      OK so far (except that the code it generates for the output view attempts to select from the result, and not the proc, but I figured out how to fix that).

       

      Here's the code I can see in the code for the ProcX_WS.xmi (names change by hand from my actual web service model, so please bear with me if I missed something):


      CREATE VIRTUAL PROCEDURE

      BEGIN

                DECLARE string VARIABLES.IN_I_Y;

                DECLARE string VARIABLES.IN_I_Z;

                VARIABLES.IN_I_Y = xPathValue(ProcX_WS.Procedures_ProcXResult.getProcXResult.ProcX_InputMsg, '/*:ProcX_Input/*:i_y');

                VARIABLES.IN_I_Z = xPathValue(ProcX_WS.Procedures_ProcXResult.getProcXResult.ProcX_InputMsg, '/*:ProcX_Input/*:i_z');

                SELECT * FROM ProcX_Output_View.ProcXResult_OutputView

           WHERE (ProcX_Output_View.ProcXResult_OutputView.ProcXResult_Output.ProcXResult_Output_Instance.i_y = VARIABLES.IN_I_Y) AND           (ProcX_Output_View.ProcXResult_OutputView.ProcXResult_Output.ProcXResult_Output_Instance.i_z = VARIABLES.IN_I_Z);

      END

       

      And here, the transform for the ProcX_Output_View (after I remove the ".ProcXResult" which was causing an error ):

       

                SELECT                    *           FROM                     Procedures.ProcX

       

      And this all seems to work.  I was able to generate the JBossWS-CXF war file, deploy it to a local JBoss and test it with SoapUI with several different sets of arguments, and it worked fine.

      However, I am at a loss to understand how the parameters are getting to the proc.  I see that the params are pulled from the input xml with the xPathValue.  I see that the WHERE clause is filtering on those parameters, and that they are mapped in the output view, even though we don't need them in the output.

       

      I don't understand why we are selecting from

      ProcX_Output_View.ProcXResult_OutputView, but filtering on

      ProcX_Output_View.ProcXResult_OutputView.ProcXResult_Output.ProcXResult_Output_Instance

      (nor what ProcXResult_Output_Instance is, for that matter)

       

      Nor do I understand how the procedure, ProcX is getting the parameters it requires.  I saw in the documentation for teiid  where you can effectively do an exec on a procedure with a select by providing the parameters in the where (e.g.:  select * from Procedures.ProcX where i_y = 'foo' and i_z = 'bar'), but we aren't doing that here.

       

      Can someone enlighten me on how this all fits together?

       

      thanks

       

      Mike

       


        • 1. Re: How does the Web Service Model Work
          Ted Jones Master

          Mike,

           

          You should not need to change the generated transformation. If you find that you do need to then there is a bug. Can you please log here? https://issues.jboss.org/browse/TEIIDDES

           

          Maybe this diagram will help with the big picture in terms of how things fit together:

           

          web-service-models-data-flow.png

           

          Some definitions:

           

          Web Service Model - a construct that defines the web service operations, input parameters, and select from the generate XML document model (xxx_OutputView).

          Output View - The XML Document model that pulls from the underying physical source. XML Document models contain "mapping classes" which are used to map xml and relational sources.

          View Model - This is the target model containing the virtual view(s) or procedure(s) from which the web service model is created from.

           

          I would suggest opening the XML Document model in the Model Editor in Teiid Designer and drilling into it. It makes more sense in that context.

           

          Let me know if you need more info.

           

          Thanks,

          Ted

          • 2. Re: How does the Web Service Model Work
            michaeljconner Newbie

            Ted,

             

            Regarding the bug,  I added TEIIDDES-1566

             

            Regarding the rest: your diagram illustrates my question, but does not answer it.  I'll rephrase the question. Given:

            1. The Web service model gets parameters from input xml (see original post), and ...
            2. selects on Output View with filtering.
            3. The Output_Instance uses the procedure (physical model)  as the source  in the transformation (with the SELECT * FROM Procedures.ProcX) I gave in the original post.


            But the procedure has parameters, and requires arguments for those parameters.   So, ...

             

            How are the arguments, pulled from the input xml in the Web Service Model, being communicated down to the procedure in the Physical Model if the Output View in between isn't using them in its select?

             

             

             

             

            As a side note, the generated code also results in a warning in the Output View:

                 "i_y" is excluded from the Document, but is still mapped. ....

             


             


            • 3. Re: How does the Web Service Model Work
              Ted Jones Master

              Mike,

               

              Thanks for logging the Jira. I understand your confusion WRT the parameters since they are not shown in the transformation for the XML document mapping class. The Teiid engine handles the filtering based on the web service procedure execution. The parameter values are used internally.

               

              Steve Hawkins or Ramesh Reddy can give you more technical insight as to how the query engine works if you need more.

               

              Thanks again,

              Ted

              • 4. Re: How does the Web Service Model Work
                Ramesh Reddy Master

                Michael,

                How are the arguments, pulled from the input xml in the Web Service Model, being communicated down to the procedure in the Physical Model if the Output View in between isn't using them in its select?

                 

                This is multi-layred modeling [magic]. To describe at high level..

                 

                1) A physical model defines a tables/procedure in terms of scalar inputs and resultset outputs.

                2) Now, a XML model can uses these physical models builds a XML document on top of these. The execution of XML model is little specific to Teiid, but using "select" query you can retrieve a XML document.

                3) A web service model is built on top of a XML model, where web-service model is based on generated WSDL or user provided WSDL. So the web service model knows the input and output message formats. When user submits a query with input message (aka soap message), now this model knows how to parse the input XML using XQuery/XPath since it knows the structure. So, it parses those inputs and issues a "select" call into XML model using appropriate where clause etc. When the document is returned from XML model then web-service model returns that calling user. Since XML model is built on schema using output message format of the web-service model, the out XML requirements will be met.

                 

                Hope this helps.


                Ramesh..

                • 5. Re: How does the Web Service Model Work
                  michaeljconner Newbie

                  Ramesh,

                   

                  "... So the web service model knows the input and output message formats. When user submits a query with input message (aka soap message), now this model knows how to parse the input XML using XQuery/XPath since it knows the structure. So, it parses those inputs and issues a "select" call into XML model using appropriate where clause etc." 

                  I stated as much in my first message and reiterated that in my first reply.

                   

                  "When the document is returned from XML model..."

                  This is the part I've been trying to get clarification on, and no offense, but still haven't gotten a straight answer.  In order for the document to be  returned from the XML model, the results have to be returned from the procedure. Before that, the arguments have to be communicated to the procedure in some way.  My concern is that Teiid might, in some cases, be doing something like not using the parameters in the procedure, returning all results, and then filtering the results.  That would at least explain why the parameters are mapped into the output view instance XML, and why the filtering is being done at in the web service model (top level) instead. Given that the underlying procedure is a virtual procedure (that is, its not a stored procedure off in the database), then I suppose Teiid may be effectively joining it all together.  But if that is what Teiid is doing under the covers, then the  query being run when I do a web service call might be somewhat different than a more direct call on the procedure through JDBC.  And, possibly, might result in a different execution plan.  If so, the performance of that query might also be different.  Under a traditional web service, I would not expect that a web service call would add much more to the execution cost than that incurred by marshalling the result set to XML. I'm concerned that this might not necessarily be the case with the way Teiid is implementing the web service.  Having a better understanding of how the "magic" works might help to alleviate these concerns.


                  "... then web-service model returns that calling user. Since XML model is built on schema using output message format of the web-service model, the out XML requirements will be met"

                  Again, I think this was already understood, and not the point of my question.

                   

                  Thanks,

                   

                  Mike


                   

                  Message was edited by: michaeljconner  (clarifying that the arguments to the parameters have to be communicated to the procedure).

                  • 6. Re: How does the Web Service Model Work
                    Steven Hawkins Master

                    > My concern is that Teiid might, in some cases, be doing something like not using the parameters in the procedure, returning all results, and then filtering the results. 

                     

                    The procedural relational syntax does not have a concept of default paramters - it always expects parameters to be provided from the surrounding usage of the procedure.  Can you be more specific about what you mean by returning all results? 

                     

                    For the generated logic you show above, the web service procedure hook will always associate parameters from the incoming message to the xml document select.  From there I gather you have a mapping class or a view underlying a mapping class in your document model that is defined by a procedure.  See https://docs.jboss.org/author/display/TEIID/DML+Commands for more on proc relational syntax.

                     

                    > Given that the underlying procedure is a virtual procedure (that is, its not a stored procedure off in the database), then I suppose Teiid may be effectively joining it all together.  But if that is what Teiid is doing under the covers, then the  query being run when I do a web service call might be somewhat different than a more direct call on the procedure through JDBC.  And, possibly, might result in a different execution plan.  If so, the performance of that query might also be different.  Under a traditional web service, I would not expect that a web service call would add much more to the execution cost than that incurred by marshalling the result set to XML. I'm concerned that this might not necessarily be the case with the way Teiid is implementing the web service.  Having a better understanding of how the "magic" works might help to alleviate these concerns.

                     

                    This is where you loose me a bit.  The breakdown of the processing layers of interest here:

                     

                    - ProcX_WS web service operation procedure is responsible for parsing the input doc and getting the xml results (in this case from a generated document model).  This is still just a procedure like any other in Teiid and can be called independently of the web services layer that sits on top of it.  As for overhead, the worst is probably the usage of the xpathvalue functions for extraction since each use will currently require another processing pass over the xml.

                     

                    - The ProcXResult_OutputView document model is responsible for mapping relational results to xml.   This too is freestanding from the web services layer and may have queries issued directly against it.  The Teiid xml document processing model are queryable, supporting both selective projection and filtering at both the root and nested mapping class levels.  All relational work is pushed to the mapping class queries, which are in turn optimized by the engine similar to any other relational query.  One of the first stages of xml document model query planning is to associate the  WHERE clause with actual mapping class filters.  You can get a sense of this in https://docs.jboss.org/author/display/TEIID/Query+Structure - note the usage of the context function that highlights the difference between filtering the root and filtering child mapping class results. The simplistic processing model is that results are obtained for the root mapping class and then for each row results are obtained for each nested mapping class (which typically represents some iteration point in the document).  The xml optimizer will attempt to speed up document generation by automatically determining when all child results can be fetched, fetching parent and child results together, etc.

                     

                    - The final layer is comprised of the mapping class queries themselves.  The xml logic will only be as good as the user query and mapping class queries allow it to be.  If there is a case such that your procedure produces too many results, then that will still be possible through a document model call.

                     

                    Hope this helps,

                    Steve

                    • 7. Re: How does the Web Service Model Work
                      michaeljconner Newbie

                      OK,  given that I wasn't getting answers, I was taking some out-there guesses that, based on your last reply, i now suspect were completely wrong. 

                       

                      So lets start over:

                       

                      Given:

                      A procedure, ProcX, that takes a single parameter, i_z, and returns a resultSet defined by ProcXResult, with columns COLUMNA and COLUMNB.

                       

                      We generate a web service model from ProcX.  One of the things it generates is ProcX_Output_View.xmi:

                       

                      ProcX_Output_View.xmi:

                           import delclarations

                           Package Diagram

                           ProcXResult_OutputView

                                Mapping Diagram

                                (e) ProcXResult_Output

                                     { } sequence

                                          (e) ProcXResult_Output_Instance

                                               { } sequence

                                                    (e) COLUMNA

                                                    (e) COLUMNB

                                                    (e) i_z [excluded]

                       

                       

                      And within the ProcXResult_Output_Instance (the mapping class), the Transformation Editor has:

                       

                           SELECT *          FROM                    Procedures.ProcX

                       

                      Questions:  Forgetting, for the moment, about the rest of the code generated for the web service:

                      1. How does ProcX get the parameter, i_z, it requires if it is not included by the transformation?
                      2. Why is the i_z parameter included in the ProcXResult_Output_Instance when it is not in the original ProcXResult?
                      • 8. Re: How does the Web Service Model Work
                        Steven Hawkins Master

                        > How does ProcX get the parameter, i_z, it requires if it is not included by the transformation?

                         

                        When you use a procedural relational select (such as is outlined in the reference), not only are the result set columns projected but the inputs are "projected" as well.  The procedural relational select expects predicates against the input columns, which is maps to inputs to the procedure call.

                         

                        So the mapping class not only projects the result set columns, but the input parameters as columns as well.  Just like any view any higher level usage of the view columns in predicates (in your case they are in the xml select where clause, which then get associated with the mapping class) will get pushed as far as possible by the optimizer.  This in turn means that the predicates will be recognized as input values.

                         

                        > Why is the i_z parameter included in the ProcXResult_Output_Instance when it is not in the original ProcXResult?

                         

                        It has been added so that a query against the document model can specify a predicate and therefore an input value.  Without this, you would have to have a mapping class that uses hard-coded parameter values.  The mapping is marked as excluded so that the input value is not actually used in generating the output document.

                         

                        Essentially it was an early design choice of querable xml to allow this style of input via predicate approach, similar to our procedural relational handling, rather than having a concept of a parameterized document syntax such as "SELECT * FROM doc(param1) WHERE ..."

                         

                        Steve

                        1 of 1 people found this helpful
                        • 9. Re: How does the Web Service Model Work
                          michaeljconner Newbie

                          If I understand you correctly, then the concern I voiced in my reply to Ramesh, is, in fact still valid, after all. That is:

                           

                          The query that I make through a direct jdbc call to the procedure, ProcX, is logically different than the same query made through web service that sits on top of it, because the input parameters are not passed directly to the proc, but are communicated through the projection of those parameters as columns. Hopefully, the optimizer will handle this all nicely, and so the two end up being essentially equivalent (except for the additional mapping to xml, of course). 

                          • 10. Re: How does the Web Service Model Work
                            Steven Hawkins Master

                            > If I understand you correctly, then the concern I voiced in my reply to Ramesh, is, in fact still valid, after all.

                             

                            Just to reiterate, procedures called through procedural relational invocation do not have a concept of default or input ranges thus input values must be inferred from their usage otherwise an exception will be thrown.  With the set of stuff you have generated you are always passing an appropriate input and should expect to get the desired values.

                             

                            > The query that I make through a direct jdbc call to the procedure, ProcX, is logically different than the same query made through web service that sits on top of it, because the input parameters are not passed directly to the proc, but are communicated through the projection of those parameters as columns.

                             

                            I'm not sure what you mean by logically different.  They use a different syntax and there are view layers involved, but the the ultimate execution plans will basically be identical except for the proc relational projection of the input values.

                             

                            > Hopefully, the optimizer will handle this all nicely, and so the two end up being essentially equivalent (except for the additional mapping to xml, of course).

                             

                            Luckily you don't have to rely on hope.  You can check the code, unit tests, or just run this and similar circumstances to understand what the optimizer does.  And keep in mind that if aren't able to determine the inputs in the procedural relational scenario, then an exception will be thrown - so you will know early if something is not working (which cannot happen in this generated example, but can in general if you for example attempt to use a non-equality predicate to specify an input).

                             

                            Steve

                             


                            • 11. Re: How does the Web Service Model Work
                              michaeljconner Newbie

                              >> If I understand you correctly, then the concern I voiced in my reply to Ramesh, is, in fact still valid, after all.

                               

                              >Just to reiterate, procedures called through procedural relational invocation do not have a concept of default or input ranges thus input values must be inferred from their usage otherwise an exception will be thrown.  With the set of stuff you have generated you are always passing an appropriate input and should expect to get the desired values.

                               

                              I've never mentioned defaults.  You brought that up.

                               

                              >> The query that I make through a direct jdbc call to the procedure, ProcX, is logically different than the same query made through web service that sits on top of it, because the input parameters are not passed directly to the proc, but are communicated through the projection of those parameters as columns.

                               

                              >I'm not sure what you mean by logically different.  They use a different syntax and there are view layers involved, but the the ultimate execution plans will basically be identical except for the proc relational projection of the input values.

                               

                              Because of the relational projection of the input values (and because of the predicates in the WS.xmi procedure that uses it) they are logically different.  As you indicate, and I would hope, the ultimate execution plans should be the essentially same.  However, I've worked with databases enough to see circumstances where a seemingly innocuous change can adversely effect an execution plan.

                               

                              >> Hopefully, the optimizer will handle this all nicely, and so the two end up being essentially equivalent (except for the additional mapping to xml, of course).

                               

                              > Luckily you don't have to rely on hope.  You can check the code, unit tests, or just run this and similar circumstances to understand what the optimizer does.  And keep in mind that if aren't able to determine the inputs in the procedural relational scenario, then an exception will be thrown - so you will know early if something is not working (which cannot happen in this generated example, but can in general if you for example attempt to use a non-equality predicate to specify an input).

                               

                              My concern isn't that it will works.  I can test that it works.  My concern is that someone might change the underlying procedure, run some tests against it an using an exec through jdbc , and it works properly and efficiently. But, maybe, because of some quirk of the optimizer, it results in a less efficient execution plan when using the web service.  It's just a possibily we need to be aware of, and that needs to be communicated to anyone maintaining the project.

                               


                              • 12. Re: How does the Web Service Model Work
                                Steven Hawkins Master

                                > I've never mentioned defaults.  You brought that up.

                                 

                                I understand that.  It may be beating a dead horse at this point, but that is relevant since it guarentees at some level someone must explicitly provided the inputs - there is no case in which "all results" or some other unaccounted for execution is happening.

                                 

                                My concern is that someone might change the underlying procedure, run some tests against it an using an exec through jdbc , and it works properly and efficiently. But, maybe, because of some quirk of the optimizer, it results in a less efficient execution plan when using the web service.  It's just a possibily we need to be aware of, and that needs to be communicated to anyone maintaining the project.

                                 

                                There may still be some disconnect here then.  Expecting, or trying to be aware of, fundimentally different performance from a *single* invocation (as is the case here) of a procedure using proc relational syntax vs. a direct execute call is not warrented. Assuming that the procedure performs any amount of real work any additional time spent in the optimizer will be negligible (and will be typically not be a factor at all given the use of preparedstatements). 

                                 

                                The only changes to your procedure that come into player here would be to the number or handling of the input parameters.  If you change the number of inputs obviously the mapping class and the mapping document have to be updated correspondingly.  If you change the handling of the inputs such that it has unacceptable performance via a direct execute, then most assuradly you'll get unacceptable performance through the xml path as well.

                                 

                                Steve

                                • 13. Re: How does the Web Service Model Work
                                  michaeljconner Newbie

                                  I'm not talking about drawing a conclusion from a single invocation. 

                                   

                                  Here's the scenario: 

                                  New requirements for the procedure come along.  Not a new parameter, but some additional logic.  Maybe pulling in data from a new data source. Doesn't matter. 

                                  They test the heck out of it, making execs on the proc and verify that it is both working, and performs acceptably.

                                   

                                  It is a reasonable expectation that the performance of the web service -- whose interface did not change, because the procedure's interface did not change -- should be about the same as calling the procedure directly plus the original overhead for the mapping to xml. The cost of mapping the results to xml should not change from before the procedure was changed to after, for the same amount of data returned.  If we wrote the web service ourselves and simply exec-ed the procedure, that would be true, since the mapping to xml is a completely separate process from the generation of the results.

                                   

                                  However, when using the web service model, and because there add additional  views involved  the optimizer is doing something that it doesn't have to do when the procedure is simply exec-ed. That something apparently involves all the layers (web service, output view etc), and so might come up with a different execution plan.

                                   

                                   

                                   

                                   

                                   


                                  • 14. Re: How does the Web Service Model Work
                                    Steven Hawkins Master

                                    > However, when using the web service model, and because there add additional  views involved  the optimizer is doing something that it doesn't have to do when the procedure is simply exec-ed. That something apparently involves all the layers (web service, output view etc), and so might come up with a different execution plan.

                                     

                                    Can you clarify what you mean by a different execution plan?  There will be a different execution plan for the overall query yes - since it will be encapulated in the web services operation procedure and then wrapped by an xml docuement model plan .  But there will not be a different plan for the root procedure.  Internally we use shared prepared plans for all virtual procedure invocations.

                                     

                                    Steve

                                    1 of 1 people found this helpful
                                    1 2 Previous Next