News, Events and Articles
You are here: / News, Events and Articles

MySQL Query Latency with Temporary Tables

, ,

When working with MySQL, you might notice something surprising: a query that runs quickly on its own suddenly becomes slow when you try to save the results into a temporary table.

For example, let’s say you run a SELECT query that completes in about two minutes. But when you try to capture those results with:

CREATE TEMPORARY TABLE table_name

SELECT field1, field2, field3;

the process hangs, forcing you to kill it after five minutes. What’s going on here?

Why It Happens

When you run CREATE TEMPORARY TABLE … SELECT, MySQL is actually doing two jobs:

  1. Running the SELECT – fetching rows from the source tables, applying filters, joins, and any transformations.
  2. Creating and populating the temporary table – defining a new schema on the fly and writing the rows into it.

That second step adds overhead. Here are the most common reasons for the slowdown:

  • Data Volume: Copying millions of rows into a new table takes time, even if the query itself executes quickly.
  • Indexes: Your source query might benefit from indexes, but inserts into the temporary table don’t—MySQL has to write all rows one by one.
  • Storage Engine: By default, temporary tables may spill to disk if they exceed memory limits, which is much slower.
  • Server Resources: CPU, memory, and I/O pressure can cause inserts into the temp table to crawl.
  • Locking/Concurrency: Other queries on the same data can introduce waits and contention.

Creating a Temporary Table in Memory

If your dataset isn’t too large, you can speed things up by creating the temporary table in memory instead of on disk. MySQL supports this via the MEMORY storage engine:

CREATE TEMPORARY TABLE temp_in_memory (

id INT,

name VARCHAR(255)

) ENGINE=MEMORY;

Then, you can insert data directly into this in-memory structure:

INSERT INTO temp_in_memory

SELECT field1, field2

FROM source_table;

Because the table lives in RAM, inserts and reads are much faster—though the tradeoff is size limits (governed by the max_heap_table_size and tmp_table_size settings).

Best Practices

If you’re seeing big slowdowns when creating temporary tables, try:

  • Reviewing and optimizing your SELECT query with proper indexing.
  • Using ENGINE=MEMORY if your temp dataset fits in RAM.
  • Monitoring resource usage during the query (SHOW PROCESSLIST, or performance schema tables).
  • Splitting large inserts into smaller batches.
  • Making sure the temp table schema only includes the columns and datatypes you really need.

Takeaway

A SELECT query that runs fast doesn’t always translate to a fast CREATE TEMPORARY TABLE. The extra overhead of creating and writing the table can introduce new bottlenecks. If you only need the results for lightweight, session-specific work, an in-memory temporary table (ENGINE=MEMORY) can be a game-changer.

By understanding how MySQL handles temporary tables under the hood, you can choose the right approach and keep your queries moving fast.

Lawson Retirement Evaluation Checklist

Key Questions Every Organization Should Ask Before Choosing a Lawson Data Archive Platform

Retiring a legacy Lawson system can significantly reduce infrastructure costs and eliminate the burden of maintaining aging ERP servers. However, once Lawson is retired, the archive platform becomes the long-term system of record for historical financial and operational data.

Before selecting a solution, organizations should verify the following areas.

  1. Security & Compliance

☐ Does the platform support encryption at rest and in transit?
☐ Does it integrate with your existing identity provider (SSO/SAML)?
☐ Are role-based permissions available to control user access?
☐ Does the system maintain detailed audit logs of all user activity?
☐ Has the vendor undergone SOC 2 or equivalent security assessments?
☐ Can administrators manage user access policies centrally?

  1. Data Volume & Performance

☐ Has the solution been tested with full production-scale Lawson datasets?
☐ Can it handle 10+ years of transactional history without performance issues?
☐ Are query results returned quickly even with billions of records?
☐ Is the architecture designed to scale as data volumes grow over time?

  1. Query Coverage & Data Accessibility

☐ Does the platform support detailed queries across major Lawson modules?

Examples include:

☐ General Ledger (GL)
☐ Accounts Payable (AP)
☐ Vendor Records
☐ Payroll History
☐ Procurement Data
☐ Transaction Distributions

☐ Can users access transaction-level detail, not just summary reports?
☐ Are query screens designed to mirror familiar Lawson workflows?

  1. Long-Term Platform Sustainability

☐ Does the architecture eliminate the need to maintain legacy Lawson servers?
☐ Is the platform built on modern cloud infrastructure?
☐ Does the vendor have a clear long-term roadmap for the product?
☐ Will the platform remain viable for 10–20 years of data retention?

  1. Governance & Audit Readiness

☐ Can auditors retrieve historical transactions quickly and easily?
☐ Are access logs and user activity recorded for compliance reviews?
☐ Can organizations demonstrate clear data stewardship and governance?
☐ Does the platform support internal and external audit processes?

Final Consideration

Retiring Lawson is often a once-in-a-decade decision. The archive platform selected will become the system your organization relies on to access historical financial and operational data for years to come.

Ensuring the platform meets the requirements above helps protect your organization from costly surprises after the legacy ERP has been retired.

 

Save this checklist and follow this guide before choosing a Lawson Archive Platform:

Lawson Retirement Evaluation Checklist

How to add LBI bookmark to users Mingle Menu

, ,

Problem:

User does not have access to LBI. See screenshot below.

 

 

Resolution:

First, go to Mingle/InforOS, then go to manage users, and then search for the user.

Next, click on the arrow shown below which will take you to the user profile.

Click on the Roles tab:

Then click on the + to add roles:

Next, search for Mingle Role for LBI and click on the +Add & Close:

Finally, Click the Save icon. And you should be good!

5 Things Public Sector Organizations Must Verify Before Choosing a Lawson Archive Platform

Across state and local governments, Lawson environments that have supported financial, HR, and procurement operations for decades are approaching retirement. Maintaining aging ERP infrastructure simply to access historical data is costly and increasingly difficult to justify.

Archiving Lawson data allows agencies to retire the legacy application while preserving access to historical records. However, not all archive platforms are designed to support the transparency, auditability, and long-term retention requirements of the public sector.

Before selecting a Lawson archive solution, government organizations should verify the following five areas.

  1. Security and Access Governance

Public sector systems often contain sensitive financial, employee, and vendor information. Even when data is historical, agencies must maintain strict controls over who can access it.

A Lawson archive platform should support:

  • Role-based access control
  • Integration with government identity providers (SSO/SAML)
  • Encryption at rest and in transit
  • Detailed audit logs of user access
  • Clear separation of duties for administrators and users

These controls ensure agencies can maintain proper governance over historical data while meeting security requirements.

  1. Proven Performance with Large Historical Data Sets

Government Lawson systems often contain decades of transactional history. Financial systems may include millions—or even billions—of records across modules such as:

  • General Ledger
  • Accounts Payable
  • Procurement
  • Payroll
  • Vendor records
  • Distribution transactions

Archive platforms should be tested with full production-scale datasets, not just sample data. Agencies should confirm that queries remain responsive even when accessing large volumes of historical transactions.

  1. Transparency and Accessibility of Public Records

Public sector organizations have unique transparency requirements. Historical financial data may need to be retrieved for:

  • Public records requests
  • Legislative inquiries
  • Internal investigations
  • Financial reporting

An archive platform should allow authorized users to quickly locate detailed transaction records without requiring technical expertise.

The system should provide intuitive query capabilities and data views that mirror the structure users were familiar with in Lawson.

  1. Long-Term Sustainability of the Platform

Government data retention requirements can extend for many years. In some cases, financial and personnel records must remain accessible for decades.

An archive platform should be built on modern infrastructure that ensures long-term accessibility without requiring agencies to maintain outdated hardware or legacy software environments.

Organizations should verify that the platform architecture supports long-term sustainability and can evolve alongside modern cloud technologies.

  1. Audit Readiness and Financial Oversight

Public sector organizations operate under significant oversight from auditors, regulators, and governing bodies. Historical financial records must remain readily available to support audits and reviews.

A Lawson archive platform should provide:

  • Comprehensive audit trails
  • The ability to retrieve detailed transaction records
  • Clear tracking of who accessed data and when
  • Reliable support for financial and compliance audits

Maintaining this level of audit readiness is essential to ensuring continued accountability and transparency.

Final Thoughts

Retiring a legacy Lawson system can significantly reduce operational complexity and infrastructure costs for government organizations. However, the archive platform chosen becomes the long-term repository for historical financial and operational records.

Ensuring the platform supports strong security, scalability, transparency, sustainability, and audit readiness helps protect both the organization and the public interest.

Careful evaluation at the outset can prevent challenges years down the road.

5 Things Hospitals Must Verify Before Choosing a Lawson Archive Platform

A practical checklist for healthcare IT leaders planning a Lawson retirement

 

Many hospitals are reaching the point where maintaining legacy Lawson environments is no longer practical. Hardware is aging, support costs continue to rise, and IT teams are increasingly focused on modern platforms. Archiving Lawson ERP (enterprise resource planning) data so the application can be retired is often the most efficient path forward.

However, not all archive solutions are built the same. Some tools work well for small datasets or quick projects but can struggle when deployed in complex healthcare environments with strict security and compliance requirements.

Before selecting a Lawson archive platform, hospitals should verify the following five areas to avoid costly surprises later.

  1. Security Controls and Compliance Posture

Healthcare organizations operate in one of the most highly regulated environments in the world. Any system that stores historical patient, employee, financial, or vendor data must maintain strong security controls.

At a minimum, hospitals should verify:

  • Whether the vendor maintains SOC 2 compliance or equivalent security audits
  • Encryption of data at rest and in transit
  • Role-based access controls for sensitive records
  • Audit logging of user access and data queries
  • Integration with the hospital’s identity provider (SSO/SAML)

Even though the system stores historical data, it still contains highly sensitive information and must meet the same governance standards expected of modern enterprise systems.

  1. Proven Performance with Full Production Data Sets

A Lawson ERP archive solution should be tested against the hospital’s entire production history, not just small sample datasets.

Hospitals often maintain 10–20 years of Lawson data, including:

  • General Ledger
  • Accounts Payable
  • Payroll
  • Vendor records
  • Transaction history
  • Distributions

Solutions that work well in demonstrations can sometimes struggle when faced with billions of rows of historical transactions.

Before committing, organizations should confirm that the system has been:

  • Demonstrated using large, real-world datasets
  • Tested for query performance at scale
  • Architected to handle long-term data growth

An archive platform should remain responsive even with decades of financial and operational history.

  1. Query Coverage and Data Accessibility

One of the most important goals of archiving Lawson is ensuring that historical data remains accessible and usable.

Hospitals should confirm that the archive platform supports:

  • Comprehensive query coverage across Lawson modules
  • Familiar data views that mirror the original Lawson structure
  • The ability to retrieve transaction-level details
  • Flexible reporting capabilities for audits and financial reviews

If users cannot easily locate and interpret historical data, the archive quickly becomes a liability instead of an asset.

  1. Long-Term Sustainability of the Platform

Archiving Lawson is not a short-term project. Hospitals typically need access to historical data for many years, sometimes decades, due to audit, legal, and compliance requirements.

When evaluating a vendor, it’s important to understand:

  • The long-term roadmap for the platform
  • The vendor’s experience supporting healthcare organizations
  • The underlying architecture and whether it can evolve with modern infrastructure
  • Whether the system eliminates dependence on legacy servers

A successful archive platform should ensure that historical data remains accessible without requiring the hospital to maintain obsolete infrastructure.

  1. Governance, Audit Readiness, and Data Stewardship

Healthcare organizations must be able to demonstrate clear governance over archived data.

This includes:

  • Maintaining detailed access logs
  • Enforcing data access policies
  • Supporting internal and external audits
  • Providing tools to manage data retention and lifecycle

Hospitals should ensure that their archive platform supports these governance capabilities from the beginning rather than relying on manual processes later.

Final Thoughts

Retiring Lawson can significantly reduce operational overhead and allow IT teams to focus on modern platforms. But the archive solution chosen today will become the system of record for historical Lawson data going forward.

By verifying security, scalability, query coverage, sustainability, and governance capabilities upfront, hospitals can ensure their archive platform supports both operational needs and compliance obligations for years to come.

Careful evaluation today can prevent costly challenges tomorrow.

Closing the ERP Intelligence-to-Action Gap

Enterprise leaders are investing heavily in smarter enterprise resource planning (ERP) platforms, yet many still struggle to turn insights into action. In a recent article published by ERP Today, author Tirumala Rao Chimpiri explains why modern ERP systems often produce valuable intelligence without enabling faster decisions. According to Gartner, more than 70% of ERP initiatives may fail to fully meet their original business goals by 2027. The problem isn’t a lack of data, analytics, or AI—it’s structural. Insights often surface in one part of the ERP ecosystem while the authority to act resides elsewhere, creating what Chimpiri calls an “intelligence-to-action gap.” ERP systems are designed to ensure accurate transactions and maintain control across operations. While dashboards, predictive alerts, and automation are increasingly common, they don’t necessarily drive coordinated responses across departments. For example, a staffing risk might surface in HR analytics while budget approvals sit with finance and compliance oversight lies elsewhere—slowing action. Adding AI alone doesn’t fix the issue. Research from McKinsey & Company and International Data Corporation shows many organizations struggle to translate analytics into operational decisions at scale. To address this, Chimpiri introduces the CAIP-HE framework, a structural model that aligns four capabilities: cognitive automation, advanced analytics, integration and interoperability, and personalization. Rather than adding new technology, it helps organizations design how intelligence flows from insight to decision and execution. The takeaway: ERP modernization isn’t just about smarter tools—it’s about structuring systems so insights actually lead to action.

 

For Full Article, Click Here

How do Lawson File Channels work?

, ,

Lawson File Channels can be useful when importing files from a defined local Landmark directory or a remote one via FTP, SFTP etc. They are especially useful when a file from an outside organization is delayed. Unlike a IPA scheduler that only runs on a set schedule, a file channel will pick up a file that has been delayed for reasons outside of your organizations control.

 

First in Process Server Administrator, you need to go to Administrator >> Channels Administrator (assuming you have permissions)

Then create a new File Channel:

Now that you know how to create one, File Channels essentially act as a source directory path that is scanned every X minutes to then search for a file specified by a File Channel Receiver.

 

Below is an example of a File Channel searching a remote server via FTP:

Since the above is an FTP directory, it has a default directory upon connection. You then define the Source File Directory after that. If the FTP server connects and starts on ..\inbound directory, and your source directory is ..\inbound\APFinance, then Source File Directory is simple APFinance

Error file and In-Progress File Directory are always directories on the local Landmark server so make sure you establish those directories first.

 

Additionally, you can change the File Channel Type. Local is Landmark, Amazon S3 is a specified Amazon server instance:

All other Parameters are self-explanatory so let’s move on to File Channel Receivers.

You can have multiple File Channel Receivers defining different file names for every File Channel. This way if you have one Source Directory, you can search different file names like ACH_*.txt, APC_*.xml etc.

The “Process” field is the IPA process that will be processing the file.

 

The Data field is also important. File Data is the ideal selection.

File Name: Triggers one workunit with just the file name.

File Data: Triggers one workunit with the file’s entire contents.

File Line: Triggers a separate workunit for each line of the file.

Once the file is picked up, it moves it to the in-progress directory while it gets processed regardless the the Data field setting. The in-progress directory acts like an archive folder in this way.

 

That’s it, File Channels are great when receiving a file from an outside organization that sends it on a schedule. That way if it happens to be delayed, the File Channel will still scan it when it finally gets to Lawson.

 

Steering Cloud ERP Migrations with Data Governance

Cloud ERP (enterprise resource planning) migrations aren’t just about moving systems—they’re about cleaning up your data first. In an article ERP Today article written by Mageshwaran Subramanian, it argues that companies often rush to go-live and overlook the most critical factor: data quality. A cloud migration isn’t a simple “lift and shift.” It’s a chance to rethink, filter, and restructure data so the new system doesn’t inherit years of technical debt—especially in an era where AI-readiness is a major selling point. One key principle is shifting validation early. Borrowing from “shift-left” thinking in software development, Subramanian stresses that data should be validated at extraction—not after loading. The longer bad data lingers in the pipeline, the more expensive and disruptive it becomes. Archiving is another big lever. In on-prem systems, storing everything felt harmless. But cloud ERP pricing changes that equation. Migrating outdated purchase orders and decades-old records increases subscription costs and hurts performance. The smarter move? Separate active data (what runs the business today) from historical data (better stored in lower-cost archives or data lakes). He also highlights the importance of defining a “golden record.” Modern cloud platforms rely on unified entity models, so duplicate vendor/customer records must be consolidated with clear de-duplication and survivorship rules. The bottom line is t hat AI won’t fix messy data—it will amplify it. Strong governance, clean master data, and disciplined archiving turn a cloud migration from a software upgrade into a long-term strategic foundation.

For Full Article, Click Here

How To Build Adaptable ERPs

Modern businesses move fast—but many ERP systems don’t. In a recently published  article from Forbes, council member and Dynamics Square co-founder Manish Goyal argues that traditional ERP (enterprise resource planning) systems were built for stability and control, not constant change. As markets, regulations, and customer expectations evolve, companies are discovering their ERP platforms can’t keep up. In fact, research from McKinsey & Company shows only about 20% of organizations capture more than half of their expected ERP benefits—often because they treat ERP as a technical project instead of a strategic foundation.

Goyal’s solution is to rethink ERP as composable, configurable, and continuously evolving. Instead of relying on monolithic systems, he points to the idea of composable ERP—an approach championed by Gartner—where modular components can be assembled and reassembled as business needs change. The goal isn’t to eliminate a stable core, but to separate what must stay consistent from what can flex. He also warns against heavy customization. Research from the University of Agder and Deloitte suggests too much custom code increases cost and complexity. Instead, organizations should prioritize configurability—using business rules, APIs, and low-code tools to adapt processes without breaking the system. Most importantly, ERP shouldn’t be treated as a one-time transformation. It should evolve continuously through disciplined governance, cross-functional oversight, and incremental updates. The takeaway is to stop thinking of ERP as a project you “finish.” Start treating it as a platform you continuously refine to keep pace with strategy and change.

 

For Full Article, Click Here