1

Let me clear the question with an example.

If I want to query a complex data gathered from different table in SQL Server View, that look something like this (assume that the view is very complex)

SELECT e.ID, e.etc s.etc
FROM TableE AS e 
LEFT OUTER JOIN TableS AS s ON e.ID = s.NIP
WHERE ID > 1000

Then on C# I query it like this:

new SqlCommand("SELECT * FROM VeryComplexView", connection)

IS the above way better than doing like this, without creating the view first:

var query = @"SELECT e.ID, e.etc s.etc
              FROM TableE AS e 
              LEFT OUTER JOIN TableS AS s ON e.ID = s.NIP
              WHERE ID > 1000";

new SqlCommand(query, connection)

The view is actually more complex than the example above.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SIRS
  • 624
  • 6
  • 20
  • If this is a simple view - there is no difference. But if that is indexed view then it can be faster (because then it's more like a separate table with materialized query results). – Evk Apr 28 '18 at 07:44
  • Note an exact duplicate, but see https://stackoverflow.com/questions/8559443/why-execute-stored-procedures-is-faster-than-sql-query-from-a-script?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa and in particular the importance of parameters. – sgmoore Apr 28 '18 at 09:54

1 Answers1

1

To achieve better performance and more secure approach, please create stored procedure, and include yuor query in it. Then call your procedure from C# code.

  • procedure is pre-compiled structure(view - not), you can benefit from cache, etc.
  • in this way you can organize secure server rights chain: login - database user - database role - procedure
Juozas
  • 916
  • 10
  • 17