Bulk import records from MySQL every hour

[Marc Lang]

Hi,

I have a requirement to essentially replicate a MySQL table and hold it in Ensemble.

I need to refresh this every hour or so - so either delete and re-import, or manage some kind of update.

I have used an SQL Outbound Adapter and MySQL ODBC driver. I run the query, loop round the primary key and if doesn't exist in Ensemble already, save it.

I find this is somewhat unreliable in Ensemble - processor spiked at 100% and I had to kill the production (Force Stop).

There could be a few hundred thousand rows.

Can anyone think of a better way of implementing the above?

[Marc Lang]

Should add, here is my code:

set pResponse = ##class(Ens.StringResponse).%New()
set pResponse.StringValue = "0"


set results = ##class(EnsLib.SQL.GatewayResultSet).%New()
$$$LOGINFO("Calling query...")

Set sql="select IDNAM Surname, IDFNAM Forename, IDCHI CHI, IDGRI HospitalNumber from p_person where isactive = -1;"
   Set status = ..Adapter.ExecuteQuery(.results,sql)
quit:$$$ISERR(status) status

$$$LOGINFO("Query completed. Processing results.")

set rows = 0

if (results'="")
{

while (results.Next()'=0)
{
set rows = rows + 1

do ##class(SERPR.MPI.Patient).Add(results.Get("CHI"),results.Get("HospitalNumber"),results.Get("Surname"),results.Get("Forename"))

}
  
   set pResponse.StringValue = rows

}
else
{
$$$LOGERROR("Unable to query from SERPR - results was empty")
}

$$$LOGINFO("Finished processing results.")

Quit status

And the SERPR.MPI.Patient methods:

ClassMethod Add(chi As %String, hospitalNumber As %String, surname As %String, forename As %String) As %Boolean
{

if (chi="")
{
$$$LOGWARNING("Unable to save as chi was blank ("_hospitalNumber_","_surname_","_forename_")")
quit 0
}

if (..ExistsByCHI(chi)=0)
{
NEW SQLCODE
&sql(INSERT INTO SERPR_MPI.Patient (HospitalNumber, CHI, Surname, Forename)
VALUES (:hospitalNumber, :chi, :surname, :forename))

if (SQLCODE'=0)
{
$$$LOGERROR("SQL error "_SQLCODE)
quit 0
}
else
{
quit 1
}
}
else
{
quit 1 
}
}

ClassMethod ExistsByCHI(chi As %String) As %Boolean
{

set patients = 0

NEW SQLCODE
&sql(SELECT COUNT(*) INTO :patients FROM SERPR_MPI.Patient WHERE CHI=:chi)

if (patients="")
{
quit 0
}
else
{
if (patients>0)
{
quit 1
}
}

quit 0
}

[Dale du Preez]

Hi Marc,

As a starting point, is your CHI field indexed? If not, the query you are using to find the CHI is going to scan the table to find all possible CHI values. (If you can't add an index, then it might be worth changing the query to stop after finding one row using a TOP clause or a WHERE EXISTS construct.)

In terms of the CPU spike, was it running cache.exe (or the *ix executable) when spiking or was it running the ODBC/JDBC library code?

Dale

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.
To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Marc Lang]

Hi Dale,

CHI is indexed yes.

Class SERPR.MPI.Patient Extends (%Persistent, %XML.Adaptor)
{

Property HospitalNumber As %String;

Property CHI As %String;

Property Surname As %String;

Property Forename As %String;

Index IdentifierIdx On CHI [ IdKey, PrimaryKey ];

...snipped

CPU spike was on Cache.exe

I will run some more tests tomorrow and see if I can pinpoint.

I was just wondering if there's a completely different way of doing it.

Thanks