sqlite insert/update slowness with Epic ADT

Hello,

Since not of all our ancillary applications send back a full provider list we use the ADT from Epic to capture those different roles and a few other various ADT data elements per encounter or Epic CSN saved in sqlite database file.  This allows us to query and see who the PCP was on a lab result since our Lab application doesn’t send this information on the result.  Currently we have one sqlite database file with 15 tables created per facility which has reached around 500mb  in size.  Reading on the sqlite page it looks like this size is well below the maximum database files size.

With more of our facilities being added to Epic our ADT feed volume has increased, we have weeded out a lot of the noise regarding duplicate A08 and A31 updates however it seems the past few weeks we’ve noticed some recovery database queues in the site which houses this process.  The site in question only has 7 processes with 20 threads. I’m  curious if anyone has experienced similar issues with high volume data feeds that may be doing a similar process with sqlite?  In Test we are looking at splitting these facilities into their own separate database file with individual table to see if that improves performance.  This process is doing a select as well as insert or update based on return, other interfaces that use this data would only be doing a query against it.

#echo ” > begin process of writing vars to sqlite table”
set dbFile “$HciRoot/sqlLiteDbs/ADT/EpicADT.db”
#echo “dbFile $dbFile”

# sqlite table name based on hospital and environment
set tableName “$hosp\_$env”
#echo “tableName $tableName”

# assign db name
sqlite SqDbHandle $dbFile

# set write ahead logging
SqDbHandle eval {PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;}

# Create table for data
SqDbHandle eval “CREATE TABLE if not exists $tableName (msgDT TEXT, msgID TEXT, ptMRN TEXT, acctNum TEXT, har TEXT, pcp TEXT, atten TEXT, refer TEXT, consul TEXT, admit TEXT, serviceArea TEXT, room TEXT, bed TEXT, admitDT TEXT, dischDT TEXT, clinicID TEXT, clinicName TEXT, finClass TEXT, ptClass TEXT, optOut TEXT, FirstName TEXT, MI TEXT, LastName TEXT, DOB TEXT, Gender TEXT)”

# Query sqlite table by account number to see if a record already exists
set dbMsgID [SqDbHandle eval “SELECT msgID FROM $tableName WHERE acctNum = \’$acctNum\'”]
#echo “dbMsgID $dbMsgID”

if {$dbMsgID == “”} {
# record not found so insert to table
echo “tps_SaveEpicADMinfo.tcl ==> msgID $msgID inserting new record into table for acctNum $acctNum”
SqDbHandle eval “INSERT INTO $tableName (msgDT, msgID, ptMRN, acctNum, har, pcp, atten, refer, consul, admit, serviceArea, room, bed, admitDT, dischDT, clinicID, clinicName, finClass, ptClass, optOut, FirstName, MI, LastName, DOB, Gender) VALUES (\’$msgDT\’, \’$msgID\’, \’$ptMRN\’, \’$acctNum\’, \’$har\’, \’$pcp\’, \’$atten\’, \’$refer\’, \’$consul\’, \’$admit\’, \’$serviceArea\’, \’$room\’, \’$bed\’, \’$admitDT\’, \’$dischDT\’, \’$clinicID\’, \’$clinicName\’, \’$finClass\’, \’$ptClass\’, \’$optOut\’, \’$FirstName\’, \’$MI\’, \’$LastName\’, \’$DOB\’, \’$Gender\’)”
} else {
# update existing record with most recent fields if msgID is greater than or equal to current record
if {$msgID < $dbMsgID} {
echo “tps_SaveEpicADMinfo.tcl ==> message ID $msgID is older than current messageID $dbMsgID for acctNum $acctNum so doing nothing”
} else {
echo “tps_SaveEpicADMinfo.tcl ==> msgID $msgID updating existing record in table for acctNum $acctNum”
SqDbHandle eval “UPDATE $tableName SET msgDT = \’$msgDT\’, msgID = \’$msgID\’, ptMRN = \’$ptMRN\’, har = \’$har\’, pcp = \’$pcp\’, atten = \’$atten\’, refer = \’$refer\’, consul = \’$consul\’, admit = \’$admit\’, serviceArea = \’$serviceArea\’, room = \’$room\’, bed = \’$bed\’, admitDT = \’$admitDT\’, dischDT = \’$dischDT\’, clinicID = \’$clinicID\’, clinicName = \’$clinicName\’, finClass = \’$finClass\’, ptClass = \’$ptClass\’, optOut = \’$optOut\’, FirstName = \’$FirstName\’, MI = \’$MI\’, LastName = \’$LastName\’, DOB = \’$DOB\’, Gender = \’$Gender\’ WHERE acctNum = \’$acctNum\'”
}
}
# Close the database
SqDbHandle close

lappend dispList “KILL $mh”