Home Forums Discussions IT, Networking, PCs and more Question on SQL

Just checking if JamesNT is around as I have a setup question for him about SQL.

Yep. I'm here.

JamesNT

Just wondering if you could put the inactive meds and the active meds in different tables and just bring over the active meds. You don't need to access the inactive meds often and it would save time loading the script writer. Or do I have SQL all wrong?

To put the inactive meds in their own table:

SELECT * INTO MyInactiveMeds FROM ListMeds WHERE Inactive = 1

Delete inactive meds from the table so they no longer show up in AC.

DELETE FROM ListMeds WHERE Inactive = 1

NOTE: Make a backup of your database first.
NOTE: Adding the inactive meds back later because you changed your mind is not a procedure I will place on a public forum. The procedure could change over time.
NOTE: You do this at your own risk. My rate is $85/hour if something goes wrong and you need help.
NOTE: I have several ongoing projects and my availability is thin right now. Choose your course of action wisely.
NOTE: I'm not aware of any table that refers to the sciptID field as a foreign key. But I could be wrong.

JamesNT

James,

I don't think anyone is going to feel comfortable doing that. And, the whole idea whould be (if I wasn't clear, sorry) that when we did click on "Inactive" the inactive meds would come over. This would likely be an AC thing. Besides the fact that if anyone screwed anything up, support would not help. Plus, most people don't know how to use SSMS.

I was just asking what you thought if AC changed it so that when you have four active meds and ten inactive meds, if only brining over the active meds would save time loading the patient since most of the time (at least for me), I don't need to look at the inactive meds.

Bert,

Apologies for the confusion.

Yes, AC could have a table for active meds and one for inactive. Whenever an active med is made inactive, the now inactive med is moved to the inactive table. Users, by default, would see only active meds. If users wanted to see all meds or just inactive meds, they can click a checkbox or radio button indicating as such. The EMR could then show one table, or the other, or both at the same time.

Other EMRs do work that way and they do it for the sole purpose of your thought - to save retrieval time. Others, like AC, use only one table and an inactive field set to 1 or 0.

JamesNT

Thank you.

So AC has Active, Inactive and ALL drop downs in the prescribing window. What you both are saying is that the AC version has the 3 options in one programming string but could be 2 separate strings which in theory would save time loading the pt medication list? Forgive me if not technical enough, just trying to understand.

No, you are correct. Everytime you click on something, AC will run a query on the SQL database to get information. When you first open AC, it loads some data so you see "preloading data for speed."

When you click on meds, it could choose to query just the active meds until you click on inactive meds. It doesn't query demographics which is why the first time you click ln that tab, it takes three to four seconds to open. Next time, the data is already there, and it is instantaneous or close.

A lot of programs will not populate a field such as patient list until you type two letters. It figures why bring move every name into a list when you know that you want Sm for Smith. The developers try to predict what info you will want if you ask for certain info. If you click on demographics, both billing and general are brought over as you may need both. But who needs their imported items when they click on a chart?

Same with medications. Someone made a calculated decision to bring over every med even if the patient has 90 inactive meds. Which I think is a poor decision.

Gosh, it's been like 15 years since I last posted here... I was the insane person who wrote his own EMR using the complete MS Office platform- MS Access, MS Word, MS Outlook, MS Excel. I use MS Excel for electronic billing (ie Access-> daily generated Excel text based spreadsheet-> Office Ally). I used to post a lot in the EMRUpdate site before it became stale (I'm being kind). I really liked AC and eMDs, and I was unfortunately hated by NextGen.

Why is your SQL backend having such a difficult time with your medications table? I don't get it. You shouldn't have to split it into 2 tables. There must be something else slowing down the transfer of data between your backend and your frontend computers. Could it be your LAN (100 GBPS is more than enough)? Is another program interfering with AC?

I'm still using the program that I've used/programmed since 1995, using the actual free MS Access back end tables. My medications table with both active/inactive meds currently holds just under 35000 records and the retrieval is instantaneous. I've found the limit to MS Access tables to be about 50000 records (my accounts table has to be thinned every 3 years). I do have backend and frontend i7 computers with 64GB RAM each. My frontend computer uses a TB SSD drive, so my hardware really helps with the speed.

The only issue I've had in terms of slowness is when Dragon Naturally Speaking is loaded. It's a wonderful dictation program but it uses the same processor core as MS Access so things grind almost to a halt. This is one reason why I've switched to Windows dictation- not that great, but there's almost no sacrificed speed.

Just a thought...

Regards,
Al

---------------------------------------
Alberto Borges MD
alborgmd@yahoo.com

Welcome back Alberto,

What would happen if AC did a query for EVERY bit of information in SQL when you started it?

@Bert

My argument is this: Why does it need to? AC is the only EMR I am aware of that builds such a massive cache upon startup.

JamesNT

James,

You totally missed that. My response was sarcasm to answer the question. If SQL brings all data over it is slower. If it brings over unnecessary inactive meds then it is slower. That is what we were referring to, not 100 MB switches.

@Bert,

Caffeine supplies are running out. That's my story and I'm sticking to it.

JamesNT

Drinking a Red Bull as we write.

I simply bring over all the patient meds for the patient I'm working on. The following code takes about a second to execute-

SELECT MEDICATIONS.* FROM MEDICATIONS WHERE MEDICATIONS.STATUS = "CA" AND MEDICATIONS.ACCT = Forms![PtMeds]![ACCT] ORDER BY [MEDICATIONS].[MD1];

("CA" refers to chronic active, the ACCT is patient's account number and the [MD1] field is the medication name)

(picture of my prescription form) https://1drv.ms/i/s!Am9xB7i5mNRoqsAr-3Yksuwq-tlbQA?e=nC66XM

As you can see on this form, I have several variations of this select query, all running in an instantaneous manner. AC has a superior SQL backend, so it should theoretically run better. At emrupdate I used to argue that in a hospital setting, this would hold true, but in an office setting, not so much. If the AC select query is slow, there must be a reason for that which can be easily fixed.

@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:


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.


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

@JamesNT

Yeah, then Nick turned the emrupdate site into one big Ad for NextGen. It used to have pretty good forum discussions concerning IT issues- computers and software were still in their infancy back 15 years ago.

AC should try to pay you, or IT folks like you to fix their legacy code. That would be a good thing.

When I got into a fight with Nick Harrington over government regulation of the healthcare here in the USA (s.a. meaningful use), I actually got offered a job at eMDs to roll out the oncology portion of their program. Although it was a lucrative offer, I chose to remain a full-time physician who would code "part-time" (ie 4 hours a day, going on 32 years now). I would have had to get certified in C++ which is what they were using at the time and would have had to move to Texas.