Optimize the types of the columns to improve performance in serverless SQL pool databases

Published Nov 19 2021 01:00 PM 1,963 Views
Microsoft

The serverless SQL pools in the Azure Synapse Analytics workspace enable you to query data placed in Azure Data Lake, Data Verse, or Cosmos DB without the need to import data in some database. You just need to create a table on top of your Parquet, Delta, Cosmos DB data and use T-SQL language to query the data. In the following example you might see how to create an external table on top of Delta Lake data stored in Azure Data Lake storage:

 

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

 

One of the biggest challenges that you might face while creating a schema on your datasets is the proper choice of column types. In the files and collections, you will frequently find the generic number or string columns, and you might be tempted to use some type like VARCHAR(MAX) or VARCHAR(8000) to represent these columns, or you woudl not be sure should you use bigint or smallint for the numbers. You must be aware that choosing large column types might impact the performance of your queries.

You should minimize the column types to improve the performance and concurrency of the queries.

Type size impacts performance of SQL operations

Type minimization is a well-known technique in SQL Server and Azure SQL databases. 

The type size is especially important for the columns that you are using in the JOIN conditions or the columns that are in the GROUP BY list. Ideally, you should join the datasets by int/bigint columns and avoid string/guids. Even if you need to use strings, make sure that they are the smallest possible sizes. Performance of GROUP BY and similar operations directly depends on the amount of data used to group the rows. If you are processing large data sets, oversized columns might have a big impact on the performance of your queries.

Type sizes impact concurrency

The serverless SQL pool is a distributed computing system that executes concurrent queries on a set of distributed compute nodes. Multiple compute nodes are running the parts of a distributed query plan that read the underlying files, join the data sets, group, and aggregate results. Different queries might try to use the same compute nodes to execute the parts of the queries.

 

The oversized column types like VARCHAR(MAX) might trick the compute node to allocate more resources than is needed. However, the allocation is based on the estimate, but these over-allocated resources will not be used in actual execution because they are not needed. If a compute node needs 100MB to sort the results it will use these 100MB although the query optimizer allocated 4GB of memory for the task on the compute node.

Over-allocation will not help, but it might impact the concurrency of your workload. The parts of the queries might get too many resources on some nodes that they would not use. A part of a query that effectively uses 100MB of memory might get 4GB of memory if the query optimizer believes that the columns contain a lot of data. However, although it will use just 100MB, it will not release the allocated 4GB because it is not aware that the remaining memory might not be needed at the end of the task.

Why is this bad? The part of the query will use just the memory that it needs and will not leverage other resources that are allocated because they are not needed. However, the other queries that would need to place their operations on that compute node must wait for the resources to be released to deploy their parts of the queries. You might end up with queries that are unnecessary waiting for some other queries to release the resources to start the execution.

Type sizes might bring overhead in the distributed execution

The serverless SQL pool is a distributed computing system where multiple compute nodes are running the parts of a distributed query plan that read the underlying files, join the data sets, group, and aggregate results.

The number and organization of compute nodes that will execute the tasks within the query plan depend on the schema and data size.

Oversizing the column types might cause additional overhead in the distributed environment. Some simple operations that could be efficiently completed with a single compute node might be spread across tens of distributed compute nodes.

Imagine a query SELECT TOP 10 * FROM table_with_1024_colums where all columns are VARCHAR(MAX) with potentially 2GB size per cell. The query optimizer might assume that you are reading 2TB per row (1024x2GB) and allocate multiple compute to handle this query. More time might be spent on exchanging these 10 rows between the distributed components than the actual reading.

 

The serverless SQL pools use both type sizes and statistics to estimate the column size, but it might be misled by the column size.

Best practices

Try to guess what would be the size of the column and use VARCHAR(30) for names, VARCHAR(100) for addresses, etc. There are other values like SSN, codes, and abbreviations where you might guess max size.

This is important if you are using the OPENROWSET function without WITH schema where you let the OPENROWSET function to infer all types. This function will represent all strings as VARCHAR(8000) to avoid possible truncation error, but this is not good for performance on larger data sets. Make sure that you add the minimized types in the WITH clause once you expose the function to the end-users.

If you have some description columns you would probably need to use VARCHAR(MAX) or VARCHAR(8000), but you should try to make other columns smaller. Even if you represent dates or time as strings, try to calculate what is the minimum required size to store these values.

Conclusion

Properly sized column types might significantly improve the performance of your queries and the concurrency of your workload. This is one of the best practices that you should apply to optimize your schema.

1 Comment
%3CLINGO-SUB%20id%3D%22lingo-sub-2985085%22%20slang%3D%22en-US%22%3EOptimize%20the%20types%20of%20the%20columns%20to%20improve%20performance%20in%20serverless%20SQL%20pool%20databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2985085%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20serverless%20SQL%20pools%20in%20the%20Azure%20Synapse%20Analytics%20workspace%20enable%20you%20to%20query%20data%20placed%20in%20Azure%20Data%20Lake%2C%20Data%20Verse%2C%20or%20Cosmos%20DB%20without%20the%20need%20to%20import%20data%20in%20some%20database.%20You%20just%20need%20to%20create%20a%20table%20on%20top%20of%20your%20Parquet%2C%20Delta%2C%20Cosmos%20DB%20data%20and%20use%20T-SQL%20language%20to%20query%20the%20data.%20In%20the%20following%20example%20you%20might%20see%20how%20to%20create%20an%20external%20table%20on%20top%20of%20Delta%20Lake%20data%20stored%20in%20Azure%20Data%20Lake%20storage%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20EXTERNAL%20TABLE%20Covid%20(%0A%20%20%20%20%20date_rep%20date%2C%0A%20%20%20%20%20cases%20int%2C%0A%20%20%20%20%20geo_id%20varchar(6)%0A)%20WITH%20(%0A%20%20%20%20%20%20%20%20LOCATION%20%3D%20'covid'%2C%20--%26gt%3B%20the%20root%20folder%20containing%20the%20Delta%20Lake%20files%0A%20%20%20%20%20%20%20%20data_source%20%3D%20DeltaLakeStorage%2C%0A%20%20%20%20%20%20%20%20FILE_FORMAT%20%3D%20DeltaLakeFormat%0A)%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20of%20the%20biggest%20challenges%20that%20you%20might%20face%20while%20creating%20a%20schema%20on%20your%20datasets%20is%20the%20proper%20choice%20of%20column%20types.%20In%20the%20files%20and%20collections%2C%20you%20will%20frequently%26nbsp%3Bfind%20the%20generic%20number%20or%20string%20columns%2C%20and%20you%26nbsp%3Bmight%20be%20tempted%20to%20use%20some%20type%20like%20VARCHAR(MAX)%20or%20VARCHAR(8000)%20to%20represent%20these%20columns%2C%20or%20you%20woudl%20not%20be%20sure%20should%20you%20use%20bigint%20or%20smallint%20for%20the%20numbers.%20You%20must%20be%20aware%20that%20choosing%20large%20column%20types%20might%20impact%20the%20performance%20of%20your%20queries.%3C%2FP%3E%0A%3CP%3EYou%20should%20minimize%20the%20column%20types%20to%20improve%20the%20performance%20and%20concurrency%20of%20the%20queries.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--292392119%22%20id%3D%22toc-hId--292332541%22%20id%3D%22toc-hId--292332541%22%20id%3D%22toc-hId--292332541%22%3EType%20size%20impacts%20performance%20of%20SQL%20operations%3C%2FH2%3E%0A%3CP%3EType%20minimization%20is%20a%20well-known%20technique%20in%20SQL%20Server%20and%20Azure%20SQL%20databases.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20type%20size%20is%20especially%20important%20for%20the%20columns%20that%20you%20are%20using%26nbsp%3Bin%20the%20JOIN%20conditions%20or%20the%20columns%20that%20are%20in%20the%20GROUP%20BY%20list.%20Ideally%2C%20you%20should%20join%20the%20datasets%20by%20int%2Fbigint%20columns%26nbsp%3Band%20avoid%20string%2Fguids.%20Even%20if%20you%20need%20to%20use%20strings%2C%20make%20sure%20that%20they%20are%20the%20smallest%20possible%20sizes.%20Performance%20of%26nbsp%3BGROUP%20BY%20and%20similar%20operations%20directly%20depends%26nbsp%3Bon%20the%26nbsp%3Bamount%20of%20data%20used%20to%20group%20the%20rows.%20If%20you%20are%20processing%20large%20data%20sets%2C%20oversized%20columns%20might%20have%20a%20big%20impact%20on%20the%20performance%20of%20your%20queries.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--2099846582%22%20id%3D%22toc-hId--2099787004%22%20id%3D%22toc-hId--2099787004%22%20id%3D%22toc-hId--2099787004%22%3EType%20sizes%20impact%20concurrency%3C%2FH2%3E%0A%3CP%3EThe%20serverless%20SQL%20pool%20is%20a%20distributed%20computing%26nbsp%3Bsystem%20that%20executes%20concurrent%20queries%20on%20a%20set%20of%20distributed%20compute%20nodes.%20Multiple%20compute%26nbsp%3Bnodes%20are%20running%20the%20parts%20of%20a%20distributed%20query%20plan%20that%20read%20the%20underlying%20files%2C%20join%20the%20data%20sets%2C%20group%2C%20and%20aggregate%20results.%20Different%20queries%20might%20try%20to%20use%20the%20same%20compute%20nodes%20to%20execute%20the%20parts%20of%20the%20queries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20oversized%20column%20types%20like%20VARCHAR(MAX)%20might%20trick%20the%20compute%20node%20to%20allocate%20more%20resources%20than%20is%20needed.%20However%2C%20the%20allocation%20is%20based%20on%20the%20estimate%2C%20but%20these%20%3CSTRONG%3Eover-allocated%20resources%20will%20not%20be%20used%20in%20actual%20execution%20because%20they%20are%20not%20needed%3C%2FSTRONG%3E.%20If%20a%20compute%20node%20needs%20100MB%20to%20sort%20the%20results%20it%20will%20use%20these%20100MB%20although%20the%20query%20optimizer%20allocated%204GB%20of%20memory%20for%20the%20task%20on%20the%20compute%20node.%3C%2FP%3E%0A%3CP%3EOver-allocation%20will%20not%20help%2C%20but%20it%26nbsp%3Bmight%20impact%20the%20concurrency%20of%20your%20workload.%20The%20parts%20of%20the%20queries%20might%20get%20too%20many%20resources%20on%20some%20nodes%20that%20they%20would%20not%20use.%20A%20part%20of%20a%20query%20that%20effectively%20uses%20100MB%20of%20memory%20might%20get%204GB%20of%20memory%20if%20the%20query%20optimizer%20believes%20that%20the%20columns%20contain%20a%20lot%20of%20data.%20However%2C%20although%20it%20will%20use%20just%20100MB%2C%20it%20will%20not%20release%20the%20allocated%204GB%20because%20it%20is%20not%20aware%20that%20the%20remaining%20memory%20might%20not%20be%20needed%20at%20the%20end%20of%20the%20task.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EWhy%20is%20this%20bad%3F%3C%2FSTRONG%3E%20The%20part%20of%20the%20query%20will%20use%20just%20the%20memory%20that%20it%20needs%20and%20will%20not%20leverage%20other%20resources%20that%20are%20allocated%20because%20they%20are%20not%20needed.%20However%2C%20the%20other%20queries%20that%20would%20need%20to%20place%20their%20operations%20on%20that%20compute%20node%20must%20wait%20for%20the%20resources%20to%20be%20released%20to%20deploy%20their%20parts%20of%20the%20queries.%20You%20might%20end%20up%20with%20queries%20that%20are%20unnecessary%20waiting%20for%20some%20other%20queries%20to%20release%20the%20resources%20to%20start%20the%20execution.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-387666251%22%20id%3D%22toc-hId-387725829%22%20id%3D%22toc-hId-387725829%22%20id%3D%22toc-hId-387725829%22%3EType%20sizes%20might%20bring%20overhead%20in%20the%20distributed%20execution%3C%2FH2%3E%0A%3CP%3EThe%20serverless%20SQL%20pool%20is%20a%20distributed%20computing%26nbsp%3Bsystem%20where%20multiple%20compute%26nbsp%3Bnodes%20are%20running%20the%20parts%20of%20a%20distributed%20query%20plan%20that%20read%20the%20underlying%20files%2C%20join%20the%20data%20sets%2C%20group%2C%20and%20aggregate%20results.%3C%2FP%3E%0A%3CP%3EThe%20number%20and%20organization%20of%20compute%20nodes%20that%20will%20execute%20the%20tasks%20within%20the%20query%20plan%20depend%20on%20the%20schema%20and%20data%20size.%3C%2FP%3E%0A%3CP%3EOversizing%20the%20column%20types%20might%20cause%20additional%20overhead%20in%20the%20distributed%20environment.%20Some%20simple%20operations%20that%20could%20be%20efficiently%20completed%20with%20a%20single%20compute%20node%20might%20be%20spread%20across%20tens%20of%20distributed%20compute%20nodes.%3C%2FP%3E%0A%3CP%3EImagine%20a%20query%20SELECT%20TOP%2010%20*%20FROM%20table_with_1024_colums%20where%20all%20columns%20are%20VARCHAR(MAX)%20with%20potentially%202GB%20size%20per%20cell.%20The%20query%20optimizer%20might%20assume%20that%20you%20are%20reading%202TB%20per%20row%20(1024x2GB)%20and%20allocate%20multiple%20compute%20to%20handle%20this%20query.%20More%20time%20might%20be%20spent%20on%20exchanging%20these%2010%20rows%20between%20the%20distributed%20components%20than%20the%20actual%20reading.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20serverless%20SQL%20pools%20use%20both%20type%20sizes%20and%20statistics%20to%20estimate%20the%20column%20size%2C%20but%20it%20might%20be%20misled%20by%20the%20column%20size.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1419788212%22%20id%3D%22toc-hId--1419728634%22%20id%3D%22toc-hId--1419728634%22%20id%3D%22toc-hId--1419728634%22%3EBest%20practices%3C%2FH2%3E%0A%3CP%3ETry%20to%20guess%20what%20would%20be%20the%20size%20of%20the%20column%20and%20use%20VARCHAR(30)%20for%20names%2C%20VARCHAR(100)%20for%20addresses%2C%20etc.%20There%20are%20other%20values%20like%20SSN%2C%20codes%2C%20and%20abbreviations%20where%20you%20might%20guess%20max%20size.%3C%2FP%3E%0A%3CP%3EThis%20is%20important%20if%20you%20are%20using%20the%20OPENROWSET%20function%20without%20WITH%20schema%20where%20you%20let%20the%20OPENROWSET%20function%20to%20infer%20all%20types.%20This%20function%20will%20represent%20all%20strings%20as%20VARCHAR(8000)%20to%20avoid%20possible%20truncation%20error%2C%20but%20this%20is%20not%20good%20for%20performance%20on%20larger%20data%20sets.%20Make%20sure%20that%20you%20add%20the%20minimized%20types%20in%20the%20WITH%20clause%20once%20you%20expose%20the%20function%20to%20the%20end-users.%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20some%20description%20columns%20you%20would%20probably%20need%20to%20use%20VARCHAR(MAX)%20or%20VARCHAR(8000)%2C%20but%20you%20should%20try%20to%20make%20other%20columns%20smaller.%20Even%20if%20you%20represent%20dates%20or%20time%20as%20strings%2C%20try%20to%20calculate%20what%20is%20the%20minimum%20required%20size%20to%20store%20these%20values.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1067724621%22%20id%3D%22toc-hId-1067784199%22%20id%3D%22toc-hId-1067784199%22%20id%3D%22toc-hId-1067784199%22%3EConclusion%3C%2FH2%3E%0A%3CP%3EProperly%20sized%20column%20types%20might%20significantly%20improve%20the%20performance%20of%20your%20queries%20and%20the%20concurrency%20of%20your%20workload.%20This%20is%20one%20of%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql%2Fbest-practices-serverless-sql-pool%23data-types%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ethe%20best%20practices%3C%2FA%3E%20that%20you%20should%20apply%20to%20optimize%20your%20schema.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2985085%22%20slang%3D%22en-US%22%3E%3CP%3EMinimization%20for%20column%20sizes%20is%20one%20of%20the%20most%20important%20schema%20optimizations%20in%20serverless%20SQL%20pools.%20In%20this%20article%2C%20you%20will%20learn%20why%20is%20type%20minimization%20important%20and%20what%20would%20be%20impacted.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2985085%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2988115%22%20slang%3D%22en-US%22%3ERe%3A%20Optimize%20the%20types%20of%20the%20columns%20to%20improve%20performance%20in%20serverless%20SQL%20pool%20databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2988115%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20should%20always%20for%20the%20correct%20datatypes.%20Check%20the%20source%20model%20for%20more%20information%2C%20common%20best%20practice%20to%20increase%20performance.%20Nice%20article%2C%20thx!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎Nov 19 2021 04:29 AM
Updated by: