新しい投稿

検索

記事
· 2025年5月7日 4m read

Long Running SQL Queries: a sample exploration

Here at InterSystems, we often deal with massive datasets of structured data. It’s not uncommon to see customers with tables spanning >100 fields and >1 billion rows, each table totaling hundred of GB of data. Now imagine joining two or three of these tables together, with a schema that wasn’t optimized for this specific use case. Just for fun, let’s say you have 10 years worth of EMR data from 20 different hospitals across your state, and you’ve been tasked with finding….
   every clinician within your network
      who has administered a specific drug
         between the years of 2017-2019
            to patients who reside outside the state
                and have one of the following conditions [diabetes, hypertension, asthma]
                    where the cost was covered by Medicaid

I’ve seen our technology handle these sort of cases just fine, but the query may still take a while to run. Can it be faster though? Let me walk you through a sample investigation.

 

///////////////////////////////////////////////////////////////////////////////////////////////

 

The Need:
Find all patients who have had an outpatient encounter at a facility located in one of these counties in the year 2022 or 2023

The Query:
SELECT DISTINCT enc.Patient->PatientNumber

FROM EMR.Encounter as enc
INNER JOIN State_Facility.Address as fa on enc.Facility = fa.FacilityCode
INNER JOIN State_Geography.Cities as city ON city.Zip = fa.ZipCode
WHERE enc.EncounterTime BETWEEN '2022-01-01' AND '2023-12-31'
AND enc.EncounterType IN ('OP','Outpatient','O')
AND city.County IN ('Los Angeles County', 'Orange County', 'Riverside County', 'San Bernardino County', 'Ventura County')

The Performance:
The query was taking >24 hours to complete

 

INVESTIGATION STEPS:

1) Review the tables that you’re querying. What relationships or foreign keys exist between them? What indices already exist? Is your SQL query making good use of the ones that already exist? Do the indices have Status = Selectable?

          We checked each field that was part of a WHERE, AND, or INNER JOIN. Most of them did have indices, including some bitmap indices. [NOTE: Further to the right of the screenshot page, the Status column shows that EncounterTypeIndex is Selectable]

 

 

2) Review the Query Plan. Does it make sense? Does it make use of the indices and relationships you expected it would? If not, does it seem more or less efficient?

           Yes, the Query Plan showed effective use of the indices on EncounterType and StartTime. [NOTE: This screenshot is for a simplified version of the query that does not consider the zip code of the encounter facility]

 

3) Ensure the table statistics up to date by running Tune Tables

 

4) Check whether the actual Query Plan at runtime matches the one you were shown. The "Show Plan" Query Plan does not utilize the Runtime Plan Choice (RTPC) optimization when it generates a Query Plan, but the RTPC is utilized when the query is actually run. That is why the Show Plan Query Plan and the runtime Query Plan can be different. The RTPC algorithm usually finds an optimal choice, but it can sometimes make a poor choice. If we find that the RTPC algorithm is making the wrong choice, it is possible to suppress the RTPC at runtime by using the %NORUNTIME keyword.

        Once the query was running, we looked at the Processes page and found the process that was running the query. We found the cached query that it was running (the Routine). We went to that cached query and looked at its Query Plan. We found that it was using a Query Plan that was very different from the one we’d seem before, and it looked much less efficient.

 

RECOMMENDATIONS:

We recommended that the customer take the following actions:
1) Use the %NORUNTIME keyword when executing the query, forcing it to use the more efficient Query Plan
2) Build a new bitmap index called EncounterDate based on the EncounterTime field. Date-based indices can be faster than DateTime-based indices, and bitmap indices are often significantly faster than normal indices

Once they implemented these two recommendations, their query was now completing in ~6 hours, a 75% improvement.

 

FURTHER READING:

Check out @Benjamin.Spead's excellent collection of resources, which includes links to online documentation, InterSystems online learning courses, presentation slideshows, and Developer Community articles.
https://community.intersystems.com/post/sql-performance-resources
 

1 Comment
ディスカッション (1)2
続けるにはログインするか新規登録を行ってください
お知らせ
· 2025年5月7日

Cambridge Developer Meetup - AI Coding Assistants & MCP [May 14 2025]

Join our next in-person Developer Meetup in Cambridge to explore the latest trends, tools, and features for innovating on health data.

Talk 1: AI Coding Assistants in Practice
Speaker: Derek Gervais, Developer Relations Evangelist, InterSystems

Talk 2: MCP, SkyNet and You!
Speaker: Dave McCaldon, Senior Development Manager, InterSystems

>> Register here

 
⏱ Day and Time: May 14, 5:30 p.m. to 7:30 p.m.
📍CIC Venture Café in Cambridge, Massachusetts

Save your seat now!

Food, beverages, and networking opportunities will be provided as always.
Join our Discord channel to connect with developers from the InterSystems developer ecosystem.

ディスカッション (0)1
続けるにはログインするか新規登録を行ってください
記事
· 2025年5月7日 4m read

Creación de una extensión primitiva en un recurso FHIR

Una extensión "extiende" o mejora un recurso FHIR o un elemento de datos de forma personalizada. La extensión puede añadirse a la raíz de un recurso, como “Patient.ethnicity” en el perfil US Core, y también pueden añadirse a elementos individuales como HumanName, Address o Identifier.

¿Sabíais que también podéis añadir una extensión a un tipo de dato primitivo?

Los elementos primitivos normalmente almacenan un solo elemento y son el componente más básico en FHIR. Por ejemplo: "Keren", false, 1234, 12/08/2024, etc.

Por ejemplo, los recursos del paciente podrían verse así:

Los elementos gender, birthDate o family son elementos primitivos, basados en tipos de datos primitivos.

¿Cómo añadiríais una extensión a la propiedad birthDate o a la propiedad deceased?

Necesitaréis crear un segundo atributo con el mismo nombre, pero con un guion bajo como prefijo, por lo que la extensión primitiva para la propiedad birthDate será _birthDate.

El guion bajo le indica a FHIR que estás accediendo al Elemento base subyacente del tipo de dato primitivo.

Todos los elementos en FHIR heredan o descienden del elemento base. Este contiene dos atributos: extension e id.

En el ejemplo de abajo, añadí una extensión de “Época del año” a la propiedad birthDate y una extensión de “Está vivo” a la propiedad deceasedBoolean.

Hay varias formas de crear un recurso FHIR, aquí tenéis un ejemplo de cómo añadir una extensión primitiva en cada una de ellas.

  1. Usando un objeto dinámico para construir el recurso FHIR

Si creáis el recurso FHIR utilizando un objeto dinámico, podéis acceder directamente a la nueva extensión primitiva:

    set resource = {}

    set resource.resourceType     = "Patient"
    do resource.%Set("active",1,"boolean")
    set resource.gender           = "female"
    set resource.birthDate        = "1984-12-24"
    set resource."_birthDate"     = {}
    set resource."_birthDate".id  = "123456"
    set resource."_birthDate".extension = []

    set extension               = {}
    set extension.url            = "http://example.org/fhir/StructureDefinition/Holiday"
    set extension.valueString   = "Christmas"
    do resource."_birthDate".extension.%Push(extension)
    write resource.toJson()

y este será el resultado:

  1. Usando las clases HS.FHIR.DTL.VR4.Model.*

Aunque no se recomienda, podéis usar las clases HS.FHIR.DTL.VR4.Model.*, ya que tendríais una lista de primitiveExtension en cada nivel. Deberéis añadir vuestras extensiones primitivas allí, y luego simplemente referenciar vuestra propiedad al índice de esa extensión.

Aquí tenéis un ejemplo usando el Data Transformation Builder:

y así es como se haría lo mismo en código:

 // Create a new patient resource
 set resource=##class(HS.FHIR.DTL.vR4.Model.Resource.Patient).%New()

 set resource.active=1
 set resource.gender="female"
 // cretate a new extension
 set extension=##class(HS.FHIR.DTL.vR4.Model.Base.Extension).%New()
 set extension.url="http://example.org/fhir/StructureDefinition/Holiday"
 set extension.valueString="Christmas"
 // Add the extension to the primitive extension list
 do resource.primitiveExtension.Insert(extension)

 // point the property to the extention index (#1 in this example)
 set resource.birthDate=$listbuild("1984-12-24",1)
 write resource.ToJSON().Read()

Básicamente, el valor de la propiedad es una función $listbuild que recibe varios parámetros:

set resource.property = $listbuild("valor original de la propiedad",<índice de la extensión primitiva>,...)

  • El primer parámetro es el valor para la propiedad original; si deseáis omitir completamente la propiedad original, simplemente enviad una cadena vacía en lugar de un valor.
  • El segundo parámetro es el número de índice de la extensión primitiva requerida desde la lista de extensiones primitivas.
  • Si deseáis añadir varias extensiones primitivas a la misma propiedad, simplemente agregadlas también (después de haberlas añadido a la lista de extensiones primitivas, por supuesto):
set resource.birthDate=$listbuild("1984-12-24",1,3,4,7)
  1. Usando las clases del FHIR Object Model (desde la versión 2024.2)

Si estáis trabajando en Iris for Health versión 2024.2 o superior, quizás queráis hacer lo mismo usando las nuevas clases del FHIR Object Model. En esas clases, las extensiones primitivas ya están definidas para cada propiedad que las tenga. Así que, en nuestro caso, existe una propiedad birthDate y también una propiedad _birthDate.

Podéis usar las estructuras predefinidas para añadir una extensión primitiva:

    #dim patient as HS.FHIRModel.R4.Patient
    #dim extension as HS.FHIRModel.R4.Extension

    set patient=##class(HS.FHIRModel.R4.Patient).%New()
    set patient.gender="female"
    set patient.active=1
    set patient.birthDate="1984-12-24"
    // create a new element
    set element=##class(HS.FHIRModel.R4.Element).%New()
    do element.IncludeExtension()

    // create a new extension
    set extension=element.extension.MakeEntry()
    set extension.url="http://example.org/fhir/StructureDefinition/Holiday"
    set extension.valueString="Christmas"
    // add the extension to the element
    do element.extension.add(extension)
    
    // add the element to the resource
    set patient."_birthDate"=element
    
    write patient.toString()

De cualquier forma en la que queráis trabajar, ¡ahora podéis crear una extensión primitiva como unos profesionales!

Mirad los ejemplos en el Open Exchange para las 3 formas:

https://openexchange.intersystems.com/package/FHIR-Primitive-Extension

ディスカッション (0)1
続けるにはログインするか新規登録を行ってください
Job
· 2025年5月7日

Why AchieversIT Offers the Best React JS Training in Hyderabad

Why AchieversIT Offers the Best React JS Training in Hyderabad

In today’s fast-paced digital world, mastering modern web technologies is essential for anyone looking to establish a successful career in software development. Among the various front-end libraries and frameworks available, React JS stands out as a powerful and in-demand technology used by top companies worldwide. If you're in Hyderabad and looking to learn React JS, AchieversIT is the go-to destination for high-quality training. Here's why AchieversIT offers the best React JS training in Hyderabad.

1. Industry-Relevant Curriculum

AchieversIT has designed its React JS course to meet the current industry standards. The curriculum is continuously updated to include the latest versions, features, and best practices of React. From fundamental concepts like JSX, components, and props, to advanced topics like hooks, context API, Redux, and performance optimization — every topic is covered in depth.

This ensures that students are not just learning theory but gaining practical knowledge that aligns with real-world development environments.

2. Experienced and Certified Trainers

One of the key strengths of AchieversIT is its team of experienced instructors. All trainers are industry professionals with years of hands-on experience in front-end development, particularly in React JS. They bring real project insights into the classroom and provide mentorship to help students understand not just how to code, but how to think like developers.

The interactive teaching approach makes complex concepts easier to grasp and fosters a better learning environment.

3. Hands-On Learning with Live Projects

AchieversIT places a strong emphasis on practical training. Students are encouraged to build real-time projects throughout the course. From building single-page applications (SPAs) to integrating APIs and deploying apps, learners get hands-on experience that boosts their confidence and skill set.

This project-based learning ensures that students are job-ready by the time they complete the course.

4. Flexible Learning Options

Understanding the diverse needs of students and working professionals, AchieversIT offers multiple learning formats — including classroom training, online live classes, and self-paced video modules. Whether you're a college student, a job seeker, or a working professional looking to upskill, there’s a schedule that fits your needs.

This flexibility makes AchieversIT’s React JS course accessible and convenient without compromising on quality.

5. Strong Placement Support

AchieversIT is committed to helping students start their careers with confidence. The institute has a dedicated placement team that connects students with top IT companies in Hyderabad and beyond. Resume building, interview preparation, mock tests, and one-on-one career counseling are just some of the value-added services offered.

With tie-ups with multiple companies and a high placement success rate, AchieversIT ensures students don’t just learn — they get hired.

6. Affordable Fees with High ROI

Quality education doesn’t have to break the bank. AchieversIT offers its React JS training at a competitive price, making it affordable for most learners. Given the high demand for React developers and the quality of training provided, students can expect a solid return on their investment in terms of job opportunities and salary growth.

7. Positive Student Feedback and Reviews

AchieversIT boasts a strong track record of satisfied learners. The institute has received numerous positive reviews and testimonials from students who have successfully completed the course and landed jobs in reputed companies. This social proof speaks volumes about the quality and effectiveness of the training program.


Conclusion

If you're serious about building a career in front-end development, learning React JS is a must. And when it comes to React JS training in Hyderabad, AchieversIT stands out for its expert instructors, practical approach, flexible learning, and placement support. With a strong commitment to student success, AchieversIT continues to be the preferred choice for aspiring developers across the city.

Get started with AchieversIT today and take your first step toward a promising tech career!

ディスカッション (0)1
続けるにはログインするか新規登録を行ってください
お知らせ
· 2025年5月7日

Winners of the Code Your Way to InterSystems READY 2025

Hi Community,

A huge thank you to everyone who participated in the Code Your Way to InterSystems READY 2025 challenge! We enjoyed watching your videos and checking out your projects. And now it's time to announce the winners! 

🥇 1st place, a pass and a hotel accommodation go to @Shawntelle Madison-Coker for her wp-iris-project app

🥈 2nd place and a pass go to @Kurro Lopez for his Iris-nator app

🥉 3rd place and a pass go to @Oliver Wilms for his jupyter-for-money app

Our sincerest congratulations to our winners and we look forward to seeing you at the InterSystems Ready 2025!

3件の新着コメント
ディスカッション (3)2
続けるにはログインするか新規登録を行ってください