0

I have a C# RestApi which need to extract information of records from database using ADO by executing queries like this:

declare @id1 int 
set @id1 =  (select id from myTable where col1= x1, col2 = y1, col3 = z1 from mappingTable)

declare @id2 int 
set @id2 =  (select id from myTable where col1= x2, col2 = y2, col3 = z2 from mappingTable)

declare @id3 int 
set @id3 =  (select id from myTable where col1= x3, col2 = y3, col3 = z3 from mappingTable)
.
.
.
declare @idN int 
set @idN =  (select id from myTable where col1= xN, col2 = yN, col3 = zN from mappingTable)

select @id1,@id2,@id3, ..., @idN

I run above query which runs N queries inside it using ADO.NET SqlCommand and read the results. I have two questions:

  1. Does running each of queries using separate SqlCommand lead to performance downgrade or not? Usually in I/O tasks, executing many small I/O tasks have lower performance than running all of them in one batch I/O tasks but I have no idea about the same scenario in Databases and ADO.
  2. Are there any better ways to extract the same result with a better SQL query? In other words can I write this query other way to run it with better performance?

Note: In above queries columns and tables are the same in all queries only values in where clause are modified.

Improve this question
3
  • 1
    It would certainly be faster to query the database once rather than N times. There are a number of ways you could do it, return a row with N columns, return N rows, return N datasets. Commented Dec 12, 2019 at 5:44
  • @DaleK Can I write this query in better way? Commented Dec 12, 2019 at 5:48
  • 1
    You need to show the code you are using to make the ADO call, and give us an realistic idea of what N might be. The code you have shown is a single database query, so I don't follow how you are using separate SqlCommand's. Commented Dec 12, 2019 at 5:53

1 Answer 1

Reset to default
2

Use a table valued parameter to store corresponding values of x, y and z. Use a single query where you inner join your mappingTable with that table valued parameter.

In SQL:

CREATE TYPE XYZ As Table -- Do not call it XYZ!!!
(
    x int,
    y int,
    z int -- I'm using ints because it's simple - you need the data types of your columns here
)

In c#, create a data table that corresponds to this type:

var dt = new DataTable();
dt.Columns.Add("x", typeof(int));
dt.Columns.Add("y", typeof(int));
dt.Columns.Add("z", typeof(int));

populate that data table with the correct values, and then you send the data table to SQL Server as a parameter of type SqlDbType.Structured:

 cmd.Parameters.Add("@tvp", SqlDbType.Structured).Value = dt;

To use it in a query:

SELECT id 
FROM mappingTable
JOIN @tvp
    ON col1= x
   AND col2 = y
   AND col3 = z

This will return a recordset containing a single column with all the ids you need.

Note: Code was written directly here - there might be some typos.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Does its have better performance than my query? If answer is yes, why?
Yes, it should have a better performance. The more ids you're fetching, the larger the performance difference would be. There are two main reasons: 1. Only one round-trip between the application and the database. 2. SQL is designed to work on sets of data - this is what it does best. Comparing both types of actions, even directly in SSMS, you will see that multiple select statements each selecting one record is far less efficient that a single query selecting the all these records at once.
Nice description :) So it is possible to write my SQL query in one query?
Yes. I know that. I was looking for its equivalent raw query.
I'm not sure I understand. What do you mean by raw query?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.