Multiple Queries vs Dataset

[Murdz]

Hello all.

I was wondering if there is much of a performance difference between multiple DB queries, which return a single datatable, vs a single query that returns multiple datatables.

eg:

DataTable dt1 = "SELECT * FROM tbl"
DataTable dt2 = "SELECT * FROM tbl2"
DataTable dt3 = "SELECT * FROM tbl3"

vs

DataSet ds1 = "SELECT * FROM tbl; SELECT * FROM tbl2; SELECT * FROM tbl3"

[ck9663]

A semicolon is a Transact-SQL statement terminator. So either you split it in different run or run it with statement separator, it will still run it sequentially. However, the latter, I think (read: not sure), it will only return the result of your last SELECT

-- CK

[Murdz]

Correct, but my question centred more around that one method only required one connection to the db to perform multiple queries; but it also meant that it was bringing back a bigger result set.

So the question, really, is the time it takes to transform those multiple result sets into a datatable faster than using the mutiple db connections needed if they were returned as single result sets?

[ck9663]

In theory, the multiple connection will take some time than a single connection. Since, the connection has to be established by your Dataset. It would depend, also on how you connect to your db from your Apps. I am also not sure which recordset will your query return, since sql-server will treat that as 3 query and will execute it one after the other, but I am assuming you're trying to execute a multiple t-sql and not just select. If you need all those returned, you might need a UNION and you just need to use a single connection.

Did I even make sense? :)

-- CK