Replies:

Based on 

it looks like History.Views may not have indices or at least doesn't have a bitmap index.  If the table had a bitmap index it shouldn't take 30 seconds to count the rows. ... but maybe I'm incorrect.... seeing the query plan would have provided that insight.

You report that the now generates in 60 seconds and previously it 28 minutes for a twelve-page report.  Was the primary problem /time consumed in gathering the data or something else?

In the past, I have utilized %CSP.StreamServer to accomplish this.  While this may have a %CSP package name its perfectly usable in a ZEN context.

some thoughts on what you have presented.

1. It doesn't seem like the usedclassesdef.zip is available any longer

2. When examining a query plan I mostly do CONTROL-F and then search for the word looping. The statements that say "using the given ideky value" generally are not going to be of concern.  I see in your query plan it has "Read index map MSDS_Serenity.CustomeInstrument.InstIndx looping on Instrument and ID."  This seems like one area of concern.  This is telling us I believe that a table scan is being don on CustomerInstrument.  Should there be a join between Instrument and CustomerInstrument?  

3. I don't think it technically makes a difference in performance but I like to write my queries with a JOIN clause so that I have a clear understanding with the relationships between tables vs. conditions that would remove rows.  So I might write it like

 FROM MSDS_Serenity.Kit k

  JOIN  MSDS_Serenity.KitContent KC on Kc.Kitid=k.Id

 JOIN   MSDS_Serenity.Instrument I on I.Id=kc.Instrument

 JOIN   MSDS_Serenity.InstrumentGroup G on G.Id=I.GroupId

  JOIN  MSDS_Serenity.CustomInstrument CI on CI.    >>>maybe I'm incorrect but I don't see where the ci table is related to any other table, this is what is likely causing the table scan, if this table has a large number of rows this could very well be causing an issue

WHERE i.active=1 and (i.IsImpants is null or i.IsImplans!=1) 

    and k.loc=5 and k.id=9237

Again I don't think this makes any technical difference but it does allow me to isloate the relationship between tables vs the filtering that occurs due to a WHERE clause.

4. I'm not saying this is absolutely true but when I see DISTINCT in a query I wonder why it was added.  Is it because of a lack of join that was producing duplicate rows and DISTINCT was a convenient way to "hide" the logic flaw?  I could be completely off base here so please ignore if 'm completely out of line.  This is a good article on DISTINCT https://wiki.c2.com/?SelectDistinctIsaCodeSmell

5. Without knowing what the stored procedures are doing this could be a place where performance is encountering issues.
 

Followers:
Following:
Stephen has not followed anybody yet.
Global Masters badges: