CODEWITHSUNDEEP
c#sql
Liran Friedman
Liran Friedman

Reputation: 4287

Use SQL For XML Path or C# Dictionary?

I need to show a grid of contacts and their phone numbers, each contact has sevral phone numbers so I have 2 ways to do it: 1. Use SQL for xml path like this:

SELECT contacts.first_name,
       (SELECT SUB.display_phone + ',' AS [text()]
       FROM contact_phones SUB
       WHERE SUB.contact_id = contacts.id
       FOR XML PATH('')) AS Phones
 FROM contacts
  1. Use sql to get only the contacts into a DataTable, and use a second query to bring all phone numbers to a dictionary and use C# code to attach each contact his phone numbers.

Can someone tell me what approuce has better performance ?

Upvotes: 0

Views: 270

Answers (2)

Fredrik Johansson
Fredrik Johansson

Reputation: 3535

I've done some heavy XML in SQL Server and my experience is that it is terrible slow (even if indexed). I would query all data and then, while filling/constructing the DataTable, parse (using XmlDocument?) and attach the neccessary column.

Anyway, my XML blobs where quite large and complex (1-5mb each), and if smaller, perhaps using the SQL XML feature is better anyway. Sorry this wasn't really an answer :)

Good luck with your query!

Upvotes: 0

oleksii
oleksii

Reputation: 35925

As a rule of thumb, if you can do it in a database - do it.

However, this may depend. If you have very small table, it maybe faster to query an in-memory structure, as you save time you a round trip to a database. On the other hand, if a table is rather large, a database would usually filter it faster, plus it doesn't use compute and memory resources of the local process.

Upvotes: 1

Related Questions