New post
Question
Rochdi Badis · May 7, 2022

Cloning a class

#Ensemble

Hi Guys,

we have a class with over 24 million records and despite indexes querying is still slow, so we are looking to create a copy or clone of our current class have an scheduled task that runs every night or month, copy old records this new clone (eg. from last month)   and remove old them from our current class.

one way is maybe to do an insert into from the current to clone class but I'm afraid that could duplicate things and might be so slow and fail like:

Insert into clonedclass values (select * from myclass where mydate < somedate)

maybe merging the global of my current class to the global of the cloned (copy) class would faster but what's happens when I do the next merge would it override the old one or just merge the new records, FYI both classes have a default cachestorage not a SQLstorage if that make any difference, also there IdLocation,IndexLocation and streamlocationm globals should I merge those globals as well, I made a merge of data global and index and all records are there but when I run a count(*) in the clone class I get zero!?

or is there a better way?

 

thanks

Product version: Ensemble 2014.1
0
0 274
Discussion (6)3
Log in or sign up to continue
Herman Slagman · May 8, 2022

I don't know what you call slow, but a class with 24 mio objects shouldn't be slow.
Even in our biggest class with 3 billion objects a rather complex query takes about 30 secs.

2 0
Eduard Lebedyuk · May 8, 2022

Please post SQL plans for slow queries and class def.

WRC handles SQL performance issues.

0 0
Rochdi Badis · May 15, 2022

Sorry guys for the delay but it has been a busy week.

basically, this is the query I'm using and attached is a file with the classes used:

Select distinct kc.id,MSDS_UI_Serenity_Report.KitContentMissingType16_FindSerialNo(i.id,kc.id,9237,'00007SV3') As SerialNo, CASE WHEN i.Code IS NOT NULL THEN i.Code ELSE ' ' END As Code, MSDS_UI_Serenity_Report.KitContentType3_GetGroupedName(i.Name,kc.GroupFlag,kc.SubGroupFlag) As InstName, kc.Quantity as Qty,MSDS_UI_Serenity_Report.KitContentMissingType12_GetContentRemarks(k.Loc,k.id,kc.id,'00007SV3') As ContentRemarks, kc.GroupCount As GroupCount, g.Name As grp, MSDS_UI_Serenity_Report.KitContentMissingType16_Getpck(i.id,'00007SV3',kc.Quantity,kc.id,kc.kitId) As pck, MSDS_UI_Serenity_Report.KitContentMissingType15_GetDecon(kc.id,5) As decon, MSDS_UI_Serenity_Report.KitContentMissingType16_GetComments(i.id,kc.id) As Comment, MSDS_UI_Serenity_Report.KitContentMissingType16_GetReason(i.id,kc.id) As Reason, MSDS_UI_Serenity_Report.KitContentMissingType16_GetRemarks(i.id) As Remark 
     From MSDS_Serenity.KitContent kc, MSDS_Serenity.Kit k, MSDS_Serenity.InstrumentGroup g, MSDS_Serenity.Instrument i, MSDS_Serenity.CustomInstrument ci 
     where i.active = 1 and (i.IsImplants is null or i.IsImplants!=1) and kc.instrument = i.id and i.groupid = g.id and k.id = kc.kitid and k.Loc = 5 and k.id = 9237 order by kc.Position,i.Name

  

usedclassesdef.zip  

although all the fields used are indexed but still this sample query takes around 1 min & 40 sec to generate 48 records and some other queries with more records could take around 10 mins.

I noticed, that most time is consumed and heavy processing is in class calls in the query :

Eg. MSDS_UI_Serenity_Report.KitContentMissingType16_FindSerialNo around 40 sec but I’ve created and built indexes for all used fields (through SMP)!?

Thanks

0 0
Stephen Canzano · May 16, 2022

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.
 

0 0