synapse dedicated table vs serverless view

Question

synapse dedicated table vs serverless view

arkiboys 9,711

hello,
1-
in the severless sql pool, I create a view which reads the parquet files in the datalake...
2-
I know that I can create a table in dedicated sql pool and have it read from parquet files in the data lake as well.
Steps 1 and 2 can read from the datalake.
Question:
Which one is quicker to read from the data lake?
Thanks

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Bhargava-MSFT 31,361 Microsoft Employee Moderator

Hello @arkiboys ,

Welcome to the MS Q&A platform.

Reading the data from external tables using a serverless SQL pool is always faster because using a Dedicated SQL pool, you have to load the data in the first place

I just tested using dedicated and serverless SQL pool(read the data from my parquet file stored in Gen2)

My dedicated SQL pool took 06.309 sec whereas my serverless took- 00.846 sec for the count of 10K records.

You can see this document explaining about dedicated vs Serverless SQL pool:

https://data-mozart.com/mastering-dp-500-exam-dedicated-vs-serverless-sql-in-synapse-which-should-i-use/

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

arkiboys 9,711 Reputation points

2022-09-08T14:05:30.687+00:00

hello, thanks for the details. very useful.
for dedicated sql pool, once the data lake data is loaded into a table, then does this need to happen again or it it just a one off load and then table can be used in select and it will not need to read from data lake?
thanks
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-09-08T21:51:49.737+00:00

Hello @arkiboys ,
Thanks for the reply.

Using Dedicated SQL pools, you can create external tables(on the data lake) and Relational tables(Relational DW- This is the actual storage). And you can perform join operations to get the data from relational tables and laketables. There is a warm-up time of around 2 to 20 sec. Due to this reason, the dedicated SQL pool took more time from fetching the data using parquet file earlier in my example.

When creating external tables, the table structure is stored on the dedicated SQL pool but the data directly resides on the lake data(Gen2). Whenever we query, it fetches from the external storage.

If we use the relational DW in the dedicated SQL pools, these tables actually store the data.

In serverless: always points to external tables (logical DW on top of lake data(gen2). Serverless doesn't have any actual storage.

For your question- if we create external tables(using data lake) for a dedicated SQL pool, creating table structure is one-time process. But the actual storage resides in the lake data. So whenever we query those tables, it needs to fetch data from the lake data every time.

I hope this clarifies your question. If you have any further questions, please let me know.

The diagram below illustrates a Dedicated SQL pool Relational DW and Serverless logical DW on top of ADLS Gen2.

Share via

synapse dedicated table vs serverless view

0 additional answers

Your answer