0

I have multiple SQL select queries (Which i need to call multiple times) all returning similar result sets with Type and value as two selected columns(Column names are different in each table but same datatype).However the selection criteria (where clause) in each table is different as they have different conditions to choose from. Currently we are calling each Select query seperately which results in multiple DB calls. We are trying to improve the response time for our application and just thinking if it would help reducing db calls by putting these select queries together in some stored procedure returnign multiple result sets?

Appreciate any good advise on this.Any reference links or samples i can look at would also be very helpful.

Prags
  • 5
  • 3
  • 1
    That's hard to say without know what each query does and exactly how the tables are related. – wilsotc May 29 '14 at 02:18
  • Thanks for your response mate. I have multiple queries which give me rates and i do some calculations based on selected rates.There is no common key for all tables. The extracted rates are later used in calculations,Everytime i need to call the same code but the selection criteria mght vary. – Prags May 29 '14 at 02:39

1 Answers1

0

Having a stored proc return multiple result sets that land in a DataSet is a way to add efficiency. But I would do some testing to see where the time is actually spent.

Sometimes I see the same code over and over again even in the same call back to the web site. Create a connection, create a command object, etc. You can simplify this and make it more efficient if you design the architecture smartly with the goal of efficiency. I wrote about having a single application wide data object spun up once here.

JBrooks
  • 9,901
  • 2
  • 28
  • 32
  • But i am not pretty sure searching across multiple tables together will be efficient or multiple short calls will be efficient. Like you said will need to run many trial runs i believe. – Prags May 29 '14 at 02:47