Is SQL Insert is faster than %Save( ) ?

[Dhanapal]

I want load 0.1 Millions of records of data into table. Here i can create two classes and create Parent - Children relationship among them.

Initially i used %Save for store the data, later i tried to load the data using SQL Insert. Now i found the SQL Insert was faster than %Save.

Find the below my observations, when i load 0.1Million records of data,

In SQL Insert  : 8.633 Secs/record

Object Save (%Save) : 21.94 Secs/record

But some of my friends not accept this. Now i want to know which one faster.

Hope to get feedback on this stuff..

Thanks,
Dhanapal

[OldMster]

The answer is it depends, but generally, SQL access is faster than Object access.  Same for updates.  Opening an object involves some significant overhead,  SQL does not have as much overhead.  If you want to see it REALLY fast, just do global sets.

Mark

[peter cooper]

Hi Dhanapal

 

My rule of thumb is

                Global inserts = factor of 1

                SQL Inserts/updates = 2-3 x the above

                Object Inserts/Updates = 8-10 x the above

 

Which you use depends on the use case – I mostly use SQL and only use objects if I have to (ie in the ZEN MVC setup)

 

= =

If you are doing a one off bulk load a quick and easy way to speed things up is to turn off transaction journaling with

                Do DISABLE^%NOJRN

And turn it on again after with

                Do ENABLE^%NOJRN

 

This prevents the journal file needing to be written – you lose transactions (trollback) but if it’s a one off then that probably does  not really matter

 

= =

 

You might ask why use objects at all rather than a plain old SQL database….

For me the answer is that you get a rich data structure (parent/child and one/many relationships) and the ISC extended SQL makes good use of this

Eg suppose you have

                Invoice Header (with a relationship to the Sales Account)

                                Invoice Lines (with a parent relationship of a header and a relationship with Product Code)

 

So you can construct some SQL like

                Select Quantity,Cost from InvoiceLines where rParent->SalesAccount.Code=’ABC’ and ProductCode->Name=’widget’

 

Which will tell you how many “widgets” you have sold to company “abc”

This extended SQL is many times more compact that standard SQL with inner/outer joins (which I have never fully understood)

 

And if you have indices (which are defined automatically with relationships) then the above will be milli-second quick even if there are a squillion records

 

HTH

Peter

 

PS it’s been several major versions of Cache since I did the timings above – and I know the gap between Object & SQL has narrowed – but it’s still there

--
--
Caché, Ensemble, DeepSee
 
---
You received this message because you are subscribed to the Google Groups "Caché, Ensemble, DeepSee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[kevin furze]

another thing to do is to disable index's at the time of a bulk insert. (if you can)
its frequently quicker to do this and then issue an index rebuild at the end of the bulk upload

[icargill]

I find it truely brilliant that you can swap so easily between SQL and Objects in Caché, using the best choice for the task in hand.

But yes, SQL is much better for bulk updates.  Make me wonder just where all that extra overhead comes from in object mode, and whether it wouldn't be possible for ISC to offer an open method or flag to open an object in  "minimal" mode that (for example) only allowed you to access and update property values. Which would be just like SQL UPDATE/SELECT, and often all you need, expecially for bulk inserts or updates.

 

Oh and other things to consider when doing bulk inserts are:

 

the %NOINDEX option

the %NOCHECK restriction argument

$SORTBEGIN and $SORTEND

---

Ian

 

 

[sprth.49]

Additionally, things to consider when using objects are:

Use Employee.CompanySetObjectId(CompanyID) instead of Employee.Company = Company when possible
Use %Save(0) when possible

and for globals (w/ listnode):
Use Set ^Company(ID) = $ListBuild(Name, Mission, ...) instead of Set $List(^Company(ID), 2 /* Name */) = Name, $List(^Company(ID), 3 /* Mission */) = Mission,...


Thomas

2013/4/3 icargill <ian.c...@e-dendrite.com>

 

 

--