Nouvelle publication

查找

Article
· Mai 12 7m de lecture

An Overview of Database Degrade

Introduction

Hello! In this article, I will be discussing database degrade, a type of data integrity issue one can face when using IRIS. First, I will be going over a review of the structure of IRIS databases. I'll then discuss how database degrade can manifest and common causes of degrade issues. I'll then conclude with general tips we give our customers about how to prevent or prepare for database degrade issues.

2 Comments
Discussion (2)2
Connectez-vous ou inscrivez-vous pour continuer
Question
· Mai 12

Problem deploying to a namespace

Hello,

I have a problem with the deployment. When I deploy using the Ens.Deployment.Deploy class, I no longer receive the logs in the terminal. However, the deployment went well, I see it in the history on the portal.

It works on our environment, but not on the client's.

 

Set tSC = ##class(Ens.Deployment.Deploy).DeployCode(chemin_"GESL_SVCCLI_DEPLOY_CPT-243_V4_0.xml","SVCCLI.ProdClient",1,chemin_"GESL_SVCCLI_DEPLOY_CPT-243_V4_0_rb.xml","L",chemin_"GESL_SVCCLI_DEPLOY_CPT-243_V4_0.log",1,1)

 

Thank you 🙂

Discussion (0)1
Connectez-vous ou inscrivez-vous pour continuer
Article
· Mai 12 4m de lecture

Tempos de resposta ao usar SQL Dinâmico e Embutido

Ao trabalhar com InterSystems IRIS, desenvolvedores e arquitetos de banco de dados frequentemente enfrentam uma decisão crítica: usar Dynamic SQL ou Embedded SQL para consultar e atualizar dados. Ambos os métodos têm seus pontos fortes e casos de uso únicos, mas entender suas implicações de desempenho é essencial para fazer a escolha certa. O tempo de resposta, uma métrica chave na avaliação do desempenho de aplicações, pode variar significativamente dependendo da abordagem SQL utilizada. Dynamic SQL oferece flexibilidade, pois as consultas podem ser construídas e executadas em tempo de execução, tornando-o ideal para cenários com necessidades de consulta imprevisíveis ou altamente variáveis. Por outro lado, Embedded SQL enfatiza a estabilidade e a eficiência ao integrar código SQL diretamente na lógica da aplicação, oferecendo tempos de resposta otimizados para padrões de consulta predefinidos.

Neste artigo, explorarei os tempos de resposta ao usar esses dois tipos de SQL e como eles dependem de diferentes estruturas de classe e do uso de parâmetros. Para fazer isso, usarei as seguintes classes do diagrama:

Para testar os tempos, criei o seguinte número de objetos para cada classe:

  • Paciente - 50 milhões
  • Visita - 150 milhões
  • Médico - 500 mil
  • Endereço - 50 milhões

Dessa forma, esperava ver um tempo razoável para executar as consultas e observar as diferenças entre a execução de Embedded SQL e Dynamic SQL. O único índice que adicionei foi o índice automático de um-para-muitos para o relacionamento Médico-Visita.

Então, vamos dar uma olhada nas consultas que vou executar e, em seguida, na duração da execução:

  1. select count(*) from Hospital.Address
  2. select count(*) from Hospital.Address where State = :param
  3. select count(*) from Hospital.Patient left join Hospital.Address on p.address = a.id
  4. select count(*) from Hospital.Patient left join Hospital.Address on p.address = a.id where a.State = :param
  5. select count(a.Address->State) from Hospital.Patient a
  6. select count(*) from Hospital.Patient where p.Address->State = :param
  7. select count(p.Visit->VisitDate) from Hospital.Patient p
  8. select count(*) from Hospital.Patient where p.Visit->VisitDate > :param
  9. select count(v.Patient->Name) from Hospital.Visit v
  10. select count(*) from Hospital.Visit where v.Patient->Name %startswith :param
  11. select count(v.Patient->Address->State) from Hospital.Visit v
  12. select count(*) from Hospital.Visit where v.Patient->Address->State = :param
  13. select count(v.Doctor->Name) from Hospital.Visit v
  14. select count(*) from Hospital.Visit where v.Doctor->Name %startswith :param
  15. select count(*) into :p from Hospital.Visit where v.Doctor->Name %startswith :param and v.Patient->Name %startswith :param
  16. select count(*) into :p from Hospital.Visit where v.Doctor->Name %startswith :param and v.Patient->Name %startswith :param and v.Patient->Address->State = :param1

Obviamente, o código acima é a sintaxe para Embedded SQL (porque há parâmetros nomeados). Para Dynamic SQL, as consultas são quase as mesmas, mas em vez de parâmetros nomeados, tenho parâmetros não nomeados 😉. Por exemplo, para a última, tenho a seguinte consulta:

select count(*) from Hospital.Visit v where v.Doctor->Name %startswith ? and v.Patient->Name %startswith ? and v.Patient->Address->State = ?

Agora, vamos dar uma olhada nos resultados:

No of query Embedded SQL (sec) Dynamic SQL (sec)
1 49 12
2 3 3
3 32 26
4 47 46
5 48 46
6 47 46
7 1767 1806
8 1768 1841
9 31 26
10 83 81
11 41 45
12 73 71
13 23 26
14 1 1
15 2 2
16 3 3

Podemos observar um valor discrepante colossal, que é a primeira consulta. O Embedded SQL levou muito mais tempo para executar do que o Dynamic SQL. Executar as mesmas consultas várias vezes me deu resultados mais ou menos iguais. Então, é o que é.

Em geral, podemos ver que o relacionamento pai-filho é muito mais lento do lado da propriedade dos filhos, mesmo que todos os dados de Paciente e Visita estejam armazenados no global de Paciente. O índice salvou o dia para o relacionamento de um-para-muitos, e o tempo de execução foi consideravelmente menor. No geral, o tempo de resposta é quase sempre similar e difere em menos de 10%; às vezes, é o mesmo. Claro, usei consultas simples que não demoraram muito para preparar, então essa etapa pôde ser quase ignorada.

Discussion (0)1
Connectez-vous ou inscrivez-vous pour continuer
Annonce
· Mai 12

Do you have the InterSystems IRIS SQL Specialist certification? Beta testers needed for our upcoming InterSystems IRIS SQL Professional certification exam

Hello IRIS community,

InterSystems Certification is currently developing a certification exam for InterSystems IRIS SQL professionals, and if you match the exam candidate description given below, we would like you to beta test the exam! The exam will be available for beta testing starting May 19, 2025.

Please note: Only candidates with the pre-existing InterSystems IRIS SQL Specialist certification are eligible to take the beta. Interested in the beta but don’t have the SQL Specialist certification? Take the SQL Specialist exam!

Eligible candidates will receive an email from the certification team on May 19, 2025 with instructions on scheduling the exam.

Beta testing will be completed June 30, 2025.

What are my responsibilities as a beta tester?

You will schedule and take the exam by June 30th. The exam will be administered in an online proctored environment free of charge (the standard fee of $150 per exam is waived for all beta testers). The InterSystems Certification team will then perform a careful statistical analysis of all beta test data to set a passing score for the exam. The analysis of the beta test results will take 6-8 weeks, and once the passing score is established, you will receive an email notification from InterSystems Certification informing you of the results. If your score on the exam is at or above the passing score, you will have earned the certification!

Note: Beta test scores are completely confidential.

How is this exam different from the InterSystems IRIS SQL Specialist exam?

This new exam - InterSystems IRIS SQL Professional - covers higher-level SQL topics and is recommended for candidates with 4 to 6 years of relevant experience, compared to the 1 to 2 years recommended for the SQL Specialist exam.

Interested in participating? Read the Exam Details below.

Exam Details

Exam title: InterSystems IRIS SQL Professional

Candidate description: A developer or solutions architect who

  • Designs IRIS SQL applications
  • Manages IRIS SQL operations
  • Uses IRIS SQL
  • Loads and efficiently queries datasets stored in IRIS SQL

Number of questions: 38

Time allotted to take exam: 2 hours

Recommended preparation: Review the content below before taking the exam.

Online Learning:

Recommended practical experience:

  • 4 to 6 years of experience developing and managing IRIS SQL applications is recommended.
  • At least 2 years of experience working with ObjectScript and globals in InterSystems IRIS is recommended.

Exam practice questions

A set of practice questions is provided here to familiarize candidates with question formats and approaches.

Exam format

The questions are presented in two formats: multiple choice and multiple response. Access to InterSystems IRIS Documentation will be available during the exam.

DISCLAIMER: Please note this exam has a 2-hour time limit. While InterSystems documentation will be available during the exam, candidates will not have time to search the documentation for every question. Thus, completing the recommended preparation before taking the exam, and searching the documentation only when absolutely necessary during the exam, are both strongly encouraged!

System requirements for beta testing

  • Working camera & microphone
  • Dual-core CPU
  • At least 2 GB available of RAM memory
  • At least 500 MB of available disk space
  • Minimum internet speed:
    • Download - 500kb/s
    • Upload - 500kb/s

 

Exam topics and content

The exam contains questions that cover the areas for the stated role as shown in the exam topics chart immediately below.

 

Topic

Subtopic

Knowledge, skills, and abilities

1. Designs IRIS SQL applications 1.1 Designs a SQL schema
  1. Distinguishes use cases for row vs columnar table layout
  2. Distinguishes use cases for different index types
  1.2 Designs advanced schemas
  1. Recalls anatomy of Globals (subscript and value)
  2. Interprets relationship between table structure and Globals
  3. Distinguishes the (Globals) level at which mirroring/journaling operates from the SQL layer
  4. Distinguishes the differences between date/time data types
  5. Interprets the overhead associated with stream data
  6. Identifies use cases for text search
  1.3 Writes business logic 
  1. Identifies use cases for UDFs, UDAFs, and SPs
  1.4 Develops Object/Relational applications
  1. Recalls SQL best practices when defining classes
  2. Uses Object access to interact with individual rows
  3. Identifies SQL limitations with class inheritance
  4. Uses serial and object properties
  5. Identifies use cases for collection properties
  6. Distinguishes class relationships from Foreign Keys
  1.5 Deploys SQL applications
  1. Determines what needs to be part of a deployment
2. Uses IRIS SQL   2.1 Manages IRIS query processing
  1. Identify benefits of the universal query cache
  2. List considerations made by the optimizer
  3. Differentiates client and server-side problems
  4. Uses Statement Index to find statement metadata
  5. Distinguishes between the use of parameters and constants in a query
  6. Distinguishes between transaction and isolation levels
  2.2 Interprets query plans 
  1. Identifies the use of indices in a query plan
  2. Identifies vectorized (columnar) query plans
  3. Uses hints to troubleshoot query planning
  4. Identifies opportunities for indices, based on a query plan
  2.3 Uses IRIS SQL in applications
  1. Distinguishes use cases for Dynamic SQL and Embedded SQL 
  2.4 Uses IRIS-specific SQL capabilities 
  1. Uses arrow syntax for implicit joining
  2. Determines use cases for explicit use of collation functions
3. Manages IRIS SQL operations 3.1 Manages SQL operations
  1. Identifies use cases for purging queries and rebuilding indices
  2. Recalls impact of purging queries and rebuilding indices
  3. Identifies use cases for un/freezing query plans, including automation
  4. Identifies use cases for (bitmap) index compaction
  5. Uses the runtime stats in the Statement Index to find statements with optimization opportunities
  3.2 Configures InterSystems SQL options 
  1. Recalls relevant system configuration options (e.g. lock threshold)
  2. Differentiates scale-out options, ECP, and sharding
  3.3 Manages SQL security
  1. Recalls to apply SQL privilege checking when using Embedded SQL
  3.4 Uses PTools for advanced performance analysis  
  1. Identifies use cases for using PTools

Interested in participating? Eligible candidates will receive an email from the certification team on May 19th with instructions on how to schedule and take the exam.

3 Comments
Discussion (3)2
Connectez-vous ou inscrivez-vous pour continuer
Résumé
· Mai 12

Publications des développeurs d'InterSystems, semaine Mai 05 - 11, 2025, Résumé

Mai 05 - 11, 2025Week at a GlanceInterSystems Developer Community