Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Cisco Community
- Technology and Support
- DevNet Hub
- DevNet Collaboration
- Collaboration Knowledge Base
- SOAP AXL Query Timeout
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
824
Views
0
Helpful
0
Comments
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 01-24-2014 12:31 PM
This document was generated from CDN thread
Created by: Mike Elvers on 30-09-2009 03:41:41 PM
I am issuing the following request to get data from a Cisco 6.1 device:
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><SOAP-ENV:Body><axl:executeSQLQuery xmlns:axl="http://www.cisco.com/AXL/1.0" xsi:schemaLocation="http://www.cisco.com/AXL/1.0 https://aaa.bbb.ccc.ddd:8443/schema/axlsoap.xsd"><sql>SELECT DISTINCT EU1.firstname AS firstname, EU1.middlename AS middlename, EU1.lastname AS lastname, EU1.mailid AS mailid, EU1.userid AS userid, EU1.department AS department, EU1.status AS status, EU1.manager AS manager, EU1.pkid AS pkid, EU2.mailid AS managermailid, device.name AS devicename, device.description AS devicedescription, device.isactive AS isactive, location.name AS locationname, numplan.dnorpattern AS dnorpattern, numplan.description AS dnordescription FROM enduser EU1 LEFT JOIN enduser EU2 ON EU2.userid = EU1.manager FULL JOIN enduserdevicemap ON enduserdevicemap.fkenduser = EU1.pkid FULL JOIN device ON device.pkid = enduserdevicemap.fkdevice LEFT JOIN devicenumplanmap ON devicenumplanmap.fkdevice = device.pkid LEFT JOIN numplan ON numplan.pkid = devicenumplanmap.fknumplan LEFT JOIN location ON location.pkid = device.fklocation WHERE (device.tkclass IS NULL OR device.tkclass = 1)</sql></axl:executeSQLQuery></SOAP-ENV:Body></SOAP-ENV:Envelope>
I removed the actual IP address for confidentiality. There are actually 6 different Cisco 6.1 devices. This request works on one of them and I get a timeout error on the other five. Any ideas on why this is timing out. One more thing, if I enter https://aaa.bbb.ccc.ddd:8443 in a browser (again, using the real IP address of the Cisco), I get to the CUCM Administration page and at that point I can login with the credentials I supply with the query.
Subject: RE: SOAP AXL Query Timeout
Replied by: Stephan Steiner on 30-09-2009 04:47:46 PM
What CCM version is it exactly? The 6.1 train got SQL trothling with 6.1.3 - for details see here: http://developer.cisco.com/c/document_library/get_file?folderId=38836&name=DLFE-13001.txt
And FULL JOIN? If I'm not mistaken you try to extract a logged in device report kind of thingie.. so devices with their logged in users, dns and a lot of details about the logged in users.
Also, don't you always have a location so you could swap out the LEFT JOIN for an INNER JOIN? Especially since it's being throttled, you should mind query performance. I don't know precisely what you need to see so I cannot comment further on optimization, but there are way too few INNER JOINs for my liking (as you might know, those are the efficient ones.. LEFT JOIN is to be used if you cannot avoid it (or you could run two queries as an alternative though), and FULL JOIN is downright evil.
Subject: RE: SOAP AXL Query Timeout
Replied by: Mike Elvers on 30-09-2009 05:23:23 PM
My understanding is that the Cisco version is 6.1.2.
As to the FULL JOINs, that gives me what I want -- all users and all extentions, even those not associated with each other. The LEFT JOINs are just used to fill in the extra data related to one of those two main entities.
Also, did you mispell something because I don't know what "trothing" is - was that suppose to be throttling? If so, are the limits documented somewhare?
PS - yes I could do separate queries for the users and extensions. But then I need to do the joining in code. I would have thought the database would be better optimized to do that sort of thing.
Subject: RE: SOAP AXL Query Timeout
Replied by: David Staudt on 30-09-2009 06:08:59 PM
You can take a look at the AXL logs on the UCM via the RTMT tool to see what kind of issues/errors - if any - are occurring on the AXL side.
As Stephan mentions, doing this kind of very heavy join/sort/processing via the onboard UCM SQL engine is highly inadvisable, especially as you are including three potentially huge tables (device, numplan, user). The likelihood is that one of the following is occurring: AXL is throttling the request due to response size, UCM Tomcat service is timing out the request due to time taken, or your client stack is timing out due to time taken. The UCM database is optimized - via indexes, normalization, etc. - for its operational efficiency requirements, not for general purpose bulk data retrieval/reporting.
The best advice for protecting UCM performance would be something like:
- Retrieve the contents of the large tables without joins/sorts/additional-processing, and store in an app-server DB
- Retrieve the large tables in chunks - using SQL SKIP/FIRST - to avoid prolonged processing spikes
- Retrieve large table contents as seldom as possible, during non-peak production times
- Perform joins/sorts/distinct/etc. against the table copies on the local app-server DB
Subject: RE: SOAP AXL Query Timeout
Replied by: Mike Elvers on 30-09-2009 08:25:06 PM
OK. It looks like I will have to get the data in multiple requests and process in in code. I was able to retrieve the users data and the extension data separately without any problem. But when I try to get the two together, bad things happen.
Thanks for the info.
Subject: RE: SOAP AXL Query Timeout
Replied by: Frederick Nielsen on 08-10-2009 05:12:28 AM
So I recently ran into a very similar issue as the one described by Mike in the original post. My query returned a timeout error as well, and even worse the cmoninit DB process pegged one CPU core and remained seemingly stuck in that state afterwards. Waited for eight hours to see if it would resolve itself and finally had to restart appropriate services to at last clear that condition.
My query was also complex; involving the device, numplan and CSS tables; with 19 outer joins total. In my case I had inadvertently coded two of them as full joins, which I believe caused my issue at this point. I didn't do precise math to see how many rows would have been returned had the query completed this way, but it would have certainly been in the range of multiple millions. Reworking it as a proper left join as I had originally intended immediately corrected the problem, and the query ran well (under 5 seconds) against a midrange server returning about 10000 rows.
Lesson for me, be a little more cautious in my use of full joins. Seems like complex queries with many joins do operate okay here, but standard sensibilities around the proper use of joins of course still apply.
Created by: Mike Elvers on 30-09-2009 03:41:41 PM
I am issuing the following request to get data from a Cisco 6.1 device:
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><SOAP-ENV:Body><axl:executeSQLQuery xmlns:axl="http://www.cisco.com/AXL/1.0" xsi:schemaLocation="http://www.cisco.com/AXL/1.0 https://aaa.bbb.ccc.ddd:8443/schema/axlsoap.xsd"><sql>SELECT DISTINCT EU1.firstname AS firstname, EU1.middlename AS middlename, EU1.lastname AS lastname, EU1.mailid AS mailid, EU1.userid AS userid, EU1.department AS department, EU1.status AS status, EU1.manager AS manager, EU1.pkid AS pkid, EU2.mailid AS managermailid, device.name AS devicename, device.description AS devicedescription, device.isactive AS isactive, location.name AS locationname, numplan.dnorpattern AS dnorpattern, numplan.description AS dnordescription FROM enduser EU1 LEFT JOIN enduser EU2 ON EU2.userid = EU1.manager FULL JOIN enduserdevicemap ON enduserdevicemap.fkenduser = EU1.pkid FULL JOIN device ON device.pkid = enduserdevicemap.fkdevice LEFT JOIN devicenumplanmap ON devicenumplanmap.fkdevice = device.pkid LEFT JOIN numplan ON numplan.pkid = devicenumplanmap.fknumplan LEFT JOIN location ON location.pkid = device.fklocation WHERE (device.tkclass IS NULL OR device.tkclass = 1)</sql></axl:executeSQLQuery></SOAP-ENV:Body></SOAP-ENV:Envelope>
I removed the actual IP address for confidentiality. There are actually 6 different Cisco 6.1 devices. This request works on one of them and I get a timeout error on the other five. Any ideas on why this is timing out. One more thing, if I enter https://aaa.bbb.ccc.ddd:8443 in a browser (again, using the real IP address of the Cisco), I get to the CUCM Administration page and at that point I can login with the credentials I supply with the query.
Subject: RE: SOAP AXL Query Timeout
Replied by: Stephan Steiner on 30-09-2009 04:47:46 PM
What CCM version is it exactly? The 6.1 train got SQL trothling with 6.1.3 - for details see here: http://developer.cisco.com/c/document_library/get_file?folderId=38836&name=DLFE-13001.txt
And FULL JOIN? If I'm not mistaken you try to extract a logged in device report kind of thingie.. so devices with their logged in users, dns and a lot of details about the logged in users.
Also, don't you always have a location so you could swap out the LEFT JOIN for an INNER JOIN? Especially since it's being throttled, you should mind query performance. I don't know precisely what you need to see so I cannot comment further on optimization, but there are way too few INNER JOINs for my liking (as you might know, those are the efficient ones.. LEFT JOIN is to be used if you cannot avoid it (or you could run two queries as an alternative though), and FULL JOIN is downright evil.
Subject: RE: SOAP AXL Query Timeout
Replied by: Mike Elvers on 30-09-2009 05:23:23 PM
My understanding is that the Cisco version is 6.1.2.
As to the FULL JOINs, that gives me what I want -- all users and all extentions, even those not associated with each other. The LEFT JOINs are just used to fill in the extra data related to one of those two main entities.
Also, did you mispell something because I don't know what "trothing" is - was that suppose to be throttling? If so, are the limits documented somewhare?
PS - yes I could do separate queries for the users and extensions. But then I need to do the joining in code. I would have thought the database would be better optimized to do that sort of thing.
Subject: RE: SOAP AXL Query Timeout
Replied by: David Staudt on 30-09-2009 06:08:59 PM
You can take a look at the AXL logs on the UCM via the RTMT tool to see what kind of issues/errors - if any - are occurring on the AXL side.
As Stephan mentions, doing this kind of very heavy join/sort/processing via the onboard UCM SQL engine is highly inadvisable, especially as you are including three potentially huge tables (device, numplan, user). The likelihood is that one of the following is occurring: AXL is throttling the request due to response size, UCM Tomcat service is timing out the request due to time taken, or your client stack is timing out due to time taken. The UCM database is optimized - via indexes, normalization, etc. - for its operational efficiency requirements, not for general purpose bulk data retrieval/reporting.
The best advice for protecting UCM performance would be something like:
- Retrieve the contents of the large tables without joins/sorts/additional-processing, and store in an app-server DB
- Retrieve the large tables in chunks - using SQL SKIP/FIRST - to avoid prolonged processing spikes
- Retrieve large table contents as seldom as possible, during non-peak production times
- Perform joins/sorts/distinct/etc. against the table copies on the local app-server DB
Subject: RE: SOAP AXL Query Timeout
Replied by: Mike Elvers on 30-09-2009 08:25:06 PM
OK. It looks like I will have to get the data in multiple requests and process in in code. I was able to retrieve the users data and the extension data separately without any problem. But when I try to get the two together, bad things happen.
Thanks for the info.
Subject: RE: SOAP AXL Query Timeout
Replied by: Frederick Nielsen on 08-10-2009 05:12:28 AM
So I recently ran into a very similar issue as the one described by Mike in the original post. My query returned a timeout error as well, and even worse the cmoninit DB process pegged one CPU core and remained seemingly stuck in that state afterwards. Waited for eight hours to see if it would resolve itself and finally had to restart appropriate services to at last clear that condition.
My query was also complex; involving the device, numplan and CSS tables; with 19 outer joins total. In my case I had inadvertently coded two of them as full joins, which I believe caused my issue at this point. I didn't do precise math to see how many rows would have been returned had the query completed this way, but it would have certainly been in the range of multiple millions. Reworking it as a proper left join as I had originally intended immediately corrected the problem, and the query ran well (under 5 seconds) against a midrange server returning about 10000 rows.
Lesson for me, be a little more cautious in my use of full joins. Seems like complex queries with many joins do operate okay here, but standard sensibilities around the proper use of joins of course still apply.
Labels:
Getting Started
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:
Customers Also Viewed These Support Documents