This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(115.19999999999999, 41%, 21%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Hi,
I need to create a view with some joins but I cannot create a materialized view because I haven't any aggregations (no group by).
The view performances are important and I'd like to engage a minimum administrative effort.
So, does it exist an alternative solution to a materialized view?
Thanks
Create suitable indexes on the base tables for the JOIN conditions; much better then a materilized (indexed) view.
Hi, I'm talking about Synapse Analytics.
Does managing some indexes mean minimum administrative effort?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 61%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Thanks for reaching out to Microsoft Q&A.
In the context of Azure Synapse Analytics, managing indexes does require some administrative effort, but it’s often considered minimal compared to the performance benefits it can provide.
Here’s why:
Index Creation: The initial setup of creating indexes requires understanding of the data and the queries that will be run against it. This involves some effort.
Index Maintenance: Over time, as data changes, indexes can become fragmented and less effective. Therefore, they need to be periodically rebuilt or reorganized. Azure Synapse Analytics provides automatic index maintenance, but you may still want to manually tune and maintain your indexes depending on your specific workload.
Storage Considerations: Indexes take up storage space. This is an important consideration especially if you are working with large volumes of data.
Update Overhead: While indexes can speed up read operations, they can slow down write operations (INSERT, UPDATE, DELETE) because each write operation must also update the index.
So, while there is some administrative effort involved in managing indexes, the potential performance gains often make it a worthwhile investment. Plus, with the tools and automation provided by Azure Synapse Analytics, much of the routine maintenance can be handled for you, minimizing the administrative burden.
Remember, the key to effective indexing is understanding your data and how it’s queried. Regular monitoring and tuning can help ensure your indexes continue to improve performance over time.
Hope this helps. Do let us know if you any further queries.
If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Hi, I've more experience to use indexes in SQL Server than Synapse Analytics. It seems that the performance gain for Synapse is lower than SQL Server, isn't it?
@pmscorca The performance gain from using indexes can vary between SQL Server and Azure Synapse Analytics, and it largely depends on the specific use case and data workload.
In Azure Synapse Analytics, the default indexing option is a clustered columnstore index, which offers both the highest level of data compression and the best overall query performance. Clustered columnstore tables will generally outperform clustered index or heap tables and are usually the best choice for large table.
However, there are scenarios where other index types or even heap tables may be more efficient. For example, for small lookup tables with less than 60 million rows, heap tables or clustered indexes might provide faster query performance. Similarly, for queries where a single or very few row lookup is required to perform with extreme speed, a clustered index or nonclustered secondary index may outperform clustered columnstore tables
In contrast, SQL Server has a more traditional indexing model, and the performance gain from using indexes can be quite significant, especially for transactional workloads.
So, while it’s true that the performance gain from using indexes in Synapse Analytics might seem lower compared to SQL Server, it’s important to remember that Synapse is designed for large-scale data warehousing workloads. The indexing strategies in Synapse, particularly the use of columnstore indexes, are optimized for these types of workloads.
It’s always a good idea to test different indexing strategies and see which one works best for your specific scenario. Remember, the best practices and strategies can vary depending on the specifics of your data and workload. It’s always a good idea to test different approaches and see which one works best for your scenario.
Hi @phemanth , many thanks for your reply.
I'm trying to optimize a left join between two round robin tables each with 100.000 rows.
I've tried to use the clustered columnstore table, the heap table, the clustered index and the non-clustered index respect to a set of columns (the join columns).The elaboration time remains nearly to 550 milliseconds with a tolerance of 10-15 milliseconds.
@pmscorca You’re welcome! It seems like you’ve already tried a variety of strategies to optimize your join operation. Here are a few additional suggestions that might help:
Statistics: Ensure that statistics are up to date on your tables. SQL Server uses these statistics to determine the optimal query plan. If the statistics are outdated, SQL Server might choose a suboptimal query plan.
Query Hints: SQL Server allows you to provide hints to the query optimizer to influence the choice of the query plan. For example, you can specify the join type (HASH, MERGE, LOOP) that should be used for the join operation.
Partitioning: If your tables are large, consider partitioning them on the join columns. This can significantly improve the performance of join operations.
Indexed Views: If you frequently perform the same join operation, consider creating an indexed view that stores the result of the join operation. This can be especially effective if your data is relatively static and doesn’t change frequently.
Hardware: Finally, the hardware can often be a limiting factor. If your server is low on memory or if the disk I/O is slow, it can slow down even the most optimized queries. Consider upgrading your hardware or moving to a faster disk system.
Hi @phemanth . Now I'm working with Synapse Analytics and not SQL Server. So as I said, in Synapse I've tried to optimize a left join between two round robin tables each with 100.000 rows using the different options for the indexing.
Thanks for your suggests, but f.e. I cannot use the query hints in Synapse.
However, for me the indexing offers major advantages in SQL Server than Synapse Analytics.
@pmscorca since you’re working with Synapse Analytics, you might want to consider the following:
Table Distribution: In Synapse, you can control how data is distributed across the nodes in the system. For join operations, you might get better performance by distributing the tables on the join columns.
Columnstore Index: Synapse uses columnstore indexes by default, which are optimized for large-scale analytical queries. Make sure your tables are using columnstore indexes.
Resource Classes: In Synapse, you can assign queries to different resource classes, which control the amount of memory and CPU resources that the query can use. For large join operations, you might get better performance by assigning the query to a larger resource class.