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.
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.