12 Replies Latest reply on Mar 19, 2015 7:22 AM by tpratt

    Is there a way to prevent WebDesk queries from running Prior to entering Criteria?

    tpratt Apprentice

      This may be a question related to product development as much as anything, unless there is a simple solution I have overlooked. As we approach our 1 year anniversary using LDSD Incident management globally, our database is growing and I have concerns about how the WebDesk query execution functions.

       

      In Console - when a query is executed the user is first prompted to enter any of the execution variables, and then the query is processed. However in WebDesk all queries run first with no selection criteria, and then after the first result set is provided the user can put in selection criteria and execute a 2nd time.

       

      Of course this is quite wasteful (and I will be embarrassed in we are the cause of this behavior - but still very thankful to identify how to change). - it causes frequent full table scans on a number of operational queries that our analyst use in the course of the day

       

      Is it possible change the execution so that if mimics what console does? We do not use console at all - 100% of our analyst use Webdesk and I'm concerned that this will grow into a bigger problem.

       

      Terry

        • 1. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
          ITSMMVPGroup

          I don't think you can.  However it sounds like you are using Oracle, so a couple of thoughts.  Nail those full table scans - especially for large tables as it will make a huge difference to performance.  We improved performance by 300% for one client just by doing that.  Then consider migrating your data to SQL - for another client we got similar great performance improvements, same data, same servers, just moved everything to MS SQL.  I'm sure product management wouldn't want to comment, but I've always found Oracle isn't as well supported as MS SQL.

          1 of 1 people found this helpful
          • 2. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
            tpratt Apprentice

            Thanks for the information. I'll leave the Oracle vs. SOL discussion alone - a very religious subject. While we have a lot of Oracle LDSD is in fact running on SQL. My question is more generic and I really expected to hear the response you provided - that we can't change the behaviour.

             

            I do think this is a bit of a design flaw - but product development may not agree. I don;t see the issue as much one of "SQL tuning" - rather the system is running queries the user does not want, and the scope of the query is often much larger than the user wants. A FTS is a very efficient access method when all of the data is to be considered - and this is the case when none of the limiting selection criteria can be used to find a better access path. The "2nd query" can be easily optimized by effective indexing, etc. - it is thye fact that the initial query needs to run wide open because no limiting criteria can be input before execution.

             

            I do appreciate the confirmation since as much as anything that is what I was looking for, to make sure there was not something Stupid or Obscured I was missing.

            • 3. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
              Apprentice

              I'd like to echo the comments raised here by Terry.

               

              I too have started to become concerned about how LANDesk Web Desk processes it's queries.

               

              In our instance our Service Desk staff simply click our 'Search for a Call' query and by default the system returns all ~150,000 Incidents we currently have in the system.  This seems hugely inefficient for simply trying to access the query before the analysts/engineers have even had the opportunity to populate the search criteria to reduce the resultant dataset.

               

              Over the past few months we've seen a marked reduction in the performance of loading our search queries as our database has grown (note: loading the query in the first instance, not the subsequent application of the desired criteria).

               

              I only have to have x6 analysts/engineers access that one query at once (not beyond the realms of possibility in my environment) and the system has served nearly 1 million rows of data in a very short space of time.  In reality those analysts/engineers would have only been looking for a handful of calls at that one time and had the criteria been supplied before the dataset was returned the system would have been required to do far less work.

               

              I also echo that this isn't a SQL/Oracle debate - whilst tuning our respective database infrastructure may indeed help I believe that long term this isn't going to alleviate the inevitable problems.  I personally find that my DB is actually doing very little and my underlying  MSSQL infrastructure doesn't break a sweat; it's the front-end web services that struggle to quickly render so many lines of data on the query interface that the analysts/engineers are seeing.

               

              This is seriously starting to slow my analysts/engineers down in their day-to-day work.

               

              Our current implementation has only 6 months of data in it and it won't be too long before  we are returning serious volumes of data every time we access this one query (never mind everything else our system is doing query-wise apart from this).  It seems the system is geared to be it's own enemy in this regard - why has it been built to return the entire dataset before criteria has been supplied?

               

              Does anyone know if this is on a roadmap for enhancement?

              1 of 1 people found this helpful
              • 4. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                tpratt Apprentice

                At least I'm not the only one who sees this as a clear design issue - and in my opinion not a subtle one.The only saving grace is that the queries constitute a smaller percent of the actions we perform in LDSD – the queries and filters on the underlying collections are typically optimized based on run-time criteria. Still, it would be nice to understand a perspective from the development side and if there are any commitments to address in the suture as you suggest.

                 

                Terry

                • 5. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                  Stu McNeill Employee

                  One trick I've seen used in the past to change this behaviour is to make the criteria prompted but also specify a value as a starting point.

                   

                  For example if you have a condition on Status that appears as a dropdown you can set that to "Open" or whatever would be most commonly used by your staff which means the first load of the query results isn't so large.  You can of course then change the value in the condition dropdown if it needs changing.

                  • 6. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                    Apprentice

                    Hi Stu,

                     

                    I did wonder if I could populate default values into the drop-downs that could then be changed as per the users requirements.

                     

                    I couldn't find a way to do this though without converting the criteria into static entries.  Are you able to advise on how to achieve this so that the default value can simply be re-selected from the drop-down?

                    • 7. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                      tpratt Apprentice

                      I too believe I attempted this approach without success - but it was sometime back and not at our current release (7.7.1 - but likely looking to go 7.7.8 in the next quarter). I'm not sure I can really predict a useful initial value, but in a lot of cases I believe I could pick a "throttle". Not the most elegant - but would offer a potential approach.

                       

                      I suppose then that there is nothing on the development roadmap to change the original behaviour?

                       

                      Terry

                      • 8. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                        Stu McNeill Employee

                        I just tested this in 7.8 by running an existing query I had in Web Access, adding a prompted criteria and also specifying a value then saving that to a shortcut (either new or existing).  That worked fine.  It might be different on older versions or trying to do it in Query Designer in Console.

                         

                        I can't speak for the roadmap but I'll poke one of my Product Management friends with a stick...

                        1 of 1 people found this helpful
                        • 9. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                          tpratt Apprentice

                          Thanks Stu. This could be very useful in lieu of a design change.

                           

                          * I repeated what I believe you did using 7.7.1 (I had an existing prompted query created in Console. I opened the query, put in the prompt value, and used "add this page to shortcut bar" to make a copy of the query with the retained initial prompt). Like Stu referenced - the query now runs 1st with the limited value - and I can then remove, change, etc.

                          * I don;t see how to accomplish the same in Console still (you can put in a value - but as soon as you use the "Prompt for Input" check-box this is removed and not retained.

                           

                          So - it is a little 2-step (publish - stick in the initial values - save and then get rid of the original. It does offer relief - not a classic design - but an option as high use queries begin to fry the capacity. Thanks for the information!

                           

                          Terry

                          • 10. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                            Apprentice

                            Hi Stu,

                             

                            Sorry to be a real pain - but is there any possibility that you could post up some screenshots about where to set the appropriate settings to achieve this?  As well as a screenshot showing what the final result would look like to the end user?

                             

                            Interestingly I've noted that one of our primary queries, returning ~150,000 records, runs slower than another one of our other primary queries which returns ~160,000 records.

                             

                            The only difference between the two is the number of criteria available.  The former has probably around 12 criteria that a user can use to filter the resultant dataset, whilst the latter has only 5 or 6.

                             

                            Does the number of criteria impact upon the performance of a query?  It may be that I need to open a support ticket to get this query looked at directly...

                            • 11. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                              jasoncadman Expert

                              I've just done this in Self Service if it helps. I created a short cur to an existing query that did what I needed with a Prompted criteria option. I added that to a Group on the Self Service Portal menu bar by right clicking and going to Create Query. I then ran the query, typed in the required prompt details to return no results and hit enter. Once you have the query with no results, right click the menu option that you want the query with the pre populated prompt to appear on and click "Replace the shortcut with current page" - Viola!

                               

                              Web Query Tidy.JPG

                              Hope that helps. I'm assuming that it's similar for WebDesk

                              1 of 1 people found this helpful
                              • 12. Re: Is there a way to prevent WebDesk queries from running Prior to entering Criteria?
                                tpratt Apprentice

                                Thanks Jason. I performed the same in a similar manner. Like you - I have the query already so in Webdesk I'm just "fixing it up" to have the default criteria that returns nothing. While the original question was looking for a more elegant "answer", what Stu suggested is a viable workaround until this is included as an architecture change. I think I will mark Stu's answer as a correct answer so that searches on the community show there is an answer out there. And of course thanks to all!

                                 

                                Terry