CODEWITHSUNDEEP
sql-serverperformancegraphql
cloudsafe
cloudsafe

Reputation: 2504

Can GraphQL break SQL Server when attempting queries with 1 to very high number JOINs? What issues can arise from such JOINs?

Our code development team is implementing a GraphQL API to replace the current method for our website to access the AWS RDS Web Edition SQL Server 2017 back-end. I have noticed that a query across multiple tables does not use the DB native relations, but loads each table individually and passes filtered parameters of the rows required for each table, derived from the previous table keys.

Example of TSQL query:

Select c.Name
from a
inner join b on b.b_id = a.b_id
inner join c on c.c_id = b.c_id

Similar generated through GraphQL. Bit pseudo-codish because it is GraphQL holding the rseults of the SELECTs:

Select a.b_id [into a table within GraphQL API. Let's call it *b_ids*] 
from a

Select b.c_id [again into a GraphQL table *c_ids*]
from b
where b.id IN([list of ids in *b_ids*])

Select c.Name
from c
WHERE c.id IN ([list of ids in *c_ids*])

All we see on a trace is:

Select a.b_id 
from a

Select b.c_id
from b
where b.id IN(1, 2, 3, 4 etc..)

Select c.Name
from c
WHERE c.id IN (1, 2, 3, 4 etc..)

I am concerned about this method, the performance that may suffer and the threshold of SQL Server queries (64KB) that may be breached on 1 to very many rows in cascades. We have junction tables with hundreds of thousands of rows.

I would have thought if my concerns were valid then there would be much to find online, but I haven't found anything. Has anyone used these platforms together that can give some indications, warnings or reassurance, particularly in regard to using with a website requiring quick responses. Tips appreciated.

Upvotes: 0

Views: 201

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

I have no idea about GraphQL, but queries with large IN lists can be expensive to parse and compile and will not scale to arbitrary-sized data. However, limit on the size of a TSQL query is ~65MB not 64KB, and performance should become an issue well before you hit that limit.

As a more-scalable alternative, pass the data using Table-Valued Parameters, a JSON array, or bulk load a temporary table.

Upvotes: 1

Related Questions