@alborg,

Yes, AC does have the superior back end to most other EMRs out there that use things such as Pervasive SQL, MySQL, and so on. The problem isn't, and never was, SQL Server. The problem is implementation.

Consider the following coding example to update a table of almost 200,000 rows:

Code
       SQL = "SELECT BABACD,BAD4ST FROM DSBAREP"
        dt = FillTableODBC(SQL)

        For Each row As DataRow In dt.Rows
            runQry("update itemmaster set active = '" & row.Item(1).ToString.Trim & "' where itemnumber = " & row.Item(0).ToString.Trim)
        Next

The code above looks very simple but can take several minutes to execute. The SELECT statement loads 200,000 rows from an AS400 database into a data table and then proceeds to loop through each row of the datatable one at a time to update a SQL Server table.

Here is the improved code courtesy of yours truly. Still not ideal, but has an execution time of less than 8 seconds.

Code
        SQL = "SELECT BABACD,BAD4ST FROM DSBAREP"
        dt = FillTableODBC(SQL)
        If CheckIfTableExists("UBS") = True Then
            comm.CommandText = "DROP TABLE UBS"
            comm.ExecuteNonQuery()
        End If

        Dim CreateTable As String = "CREATE TABLE UBS (BABACD NVARCHAR(50), BAD4ST NVARCHAR(50))"
        comm.CommandText = CreateTable
        comm.ExecuteNonQuery()
        Dim bulkcopy As SqlBulkCopy = New SqlBulkCopy(SqlConn)

        bulkcopy.DestinationTableName = "UBS"
        bulkcopy.WriteToServer(dt)

        comm.CommandText = "UPDATE ItemMaster set active = BAD4ST FROM ItemMaster INNER JOIN UBS ON ItemMaster.ItemNumber = UBS.BABACD"
        comm.ExecuteNonQuery()
        comm.CommandText = "DROP TABLE UBS"
        comm.ExecuteNonQuery()

Notice the increase in number of steps. I create a temporary table (after checking that it doesn't already exist), use SQL Bulk Copy to dump the data from the datatable to the temporary table in SQL Server, then perform the update using an INNER JOIN SQL Statement, then delete the temporary table. Far more steps, but still much less time.

The fix is simple enough. However, that's not the point. The point is the fix could only happen with access to the source code. And since no one on this forum has any such access with Amazing Charts, we sit and we wait.

Amazing Charts is one of the better EMR programs out there. I know because I've seen the rest. However, AC does still suffer from the legacy of a certain someone who bought a copy of Visual Studio 6.0 all those years go and decided to learn coding in his spare time while being a doctor full time. And plenty of things happened since then such as meaningful use.

Conclusion: Just because something is an easy fix doesn't mean you can do it or it will get done.

I remember the EMRUpdate forum. I also remember how ridiculously stupid many of the people on there were and that one doctor from Canada who spent all day trolling the forum to bad-mouth US healthcare to anyone who would listen. I do not miss that forum.

JamesNT

James Summerlin
My personal site: http://www.dataintegrationsolutions.net
james@dataintegrationsolutions.net