> For the complete documentation index, see [llms.txt](https://docs.pentaho.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/optimize-a-pentaho-data-service.md).

# Optimize a Pentaho Data Service

As you [Test a Pentaho Data Service](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/test-a-pentaho-data-service.md), you might notice certain bottlenecks, or parts of the transformation that could run more efficiently. If you want to improve the performance of your data service, apply an optimization technique. Some techniques are specifically designed for Pentaho Data Services. See the **Administer Pentaho Data Integration and Analytics** document to learn about other general design and optimization techniques that can improve the performance of your transformation.

| Optimization Technique                                                                                                                                                                                                                       | When to Use                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [Service Cache](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/optimize-a-pentaho-data-service/apply-the-service-cache-optimization.md)         | <p>For a regular data service only, adjust how long data results are cached. Consider using this technique if either of the following situations apply:</p><ul><li>Your result set contains modest data sizes.</li><li>You query Big Data sources. Increasing the cache duration can help subsequent follow-on queries run more quickly.</li></ul><p><strong>Note:</strong> This optimization technique is not available for a streaming data service. It will not appear as an optimization tab if <strong>Data Service Type</strong> is set to <strong>Streaming</strong>.</p>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| [Query Pushdown](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/optimize-a-pentaho-data-service/apply-a-query-pushdown-optimization.md)         | <p>Handle input step queries at the source. Consider using this technique if both of the following situations apply:</p><ul><li>Your transformation contains the Table Input or MongoDB Input steps.</li><li>You are using simple or complex <code>WHERE</code> clauses that include <code>AND</code>, <code>IN</code>, or other specific operators in your query. Limits for the <code>WHERE</code> clause construction appear in <a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/test-a-pentaho-data-service/examine-test-results/pentaho-data-service-sql-support-reference-and-other-development-considerations.md">Pentaho Data Service SQL support reference and other development considerations</a>.</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| [Parameter Pushdown](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/optimize-a-pentaho-data-service/apply-a-parameter-pushdown-optimization.md) | <p>Handle step queries at the source. Consider using this technique if both of the following situations apply:</p><ul><li>Your transformation contains any step that should be optimized, including input steps like REST where a parameter in the URL could limit the results returned by a web service.</li><li>You do not use more complex <code>WHERE</code> clauses in your query that might contain <code>IN</code> or <code>OR</code> keywords such as <code>WHERE REGION = "South" OR Code = "Yellow"</code>. Limits for the <code>WHERE</code> clause construction appear in <a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/test-a-pentaho-data-service/examine-test-results/pentaho-data-service-sql-support-reference-and-other-development-considerations.md">Pentaho Data Service SQL support reference and other development considerations</a>.</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| [Streaming Optimization](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/optimize-a-pentaho-data-service/apply-streaming-optimization.md)        | <p>For a streaming data service only, adjust the maximum number of rows and elapsed time to produce a new streaming window for processing. Consider using this technique if you are creating a data service from one of the following streaming data steps:</p><ul><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/amqp-consumer.md">AMQP Consumer</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/amqp-producer.md">AMQP Producer</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/get-records-from-stream.md">Get Records from Stream</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/jms-consumer.md">JMS Consumer</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/jms-producer.md">JMS Producer</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/kafka-consumer.md">Kafka consumer</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/kafka-producer.md">Kafka Producer</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/mqtt-consumer.md">MQTT Consumer</a></li><li><a href="https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview/mqtt-producer.md">MQTT Producer</a></li></ul><p><strong>Note:</strong> This optimization technique is not available for a regular data service. It will not appear as an optimization tab if <strong>Data Service Type</strong> is set to <strong>Regular</strong>.</p> |


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/advanced-topics-pentaho-data-integration-overview/pentaho-data-services/optimize-a-pentaho-data-service.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
