Looking for some efficency with multiple datagrids

[cardona]

I have 3 datagrids that all get populated when a web page loads, all data required is from the same table (in SQL Server 2000), but for each datagrid I am calling seperate connections to the database

example of 1 of my subs that fill a datagrid

Private Sub sbFillPending()

Dim strSelect As String = "SELECT CurrentStatus,TANumber FROM tblMain WHERE RequestingStaff='" & Me.lblStaff.Text & _
"' AND CurrentStatus<4 AND CurrentStatus>=2"

Try
objConnection.Open()
Catch ex As Exception
Me.lblError.Text = "An error occurred while opening the database, in sbFillPending."
Exit Sub
End Try

'Create a command Object using the passed in parameter strSelect
Dim objResults As New SqlCommand(strSelect, objConnection)

Dim objResults1 As New SqlDataAdapter
' Pass the Select command to the adapter.
objResults1.SelectCommand = objResults
'Use "ds" (declared at top of code page) DataSet to hold information from the worksheet.
ds = Nothing
ds = New DataSet()
' Fill the DataSet with the information from the worksheet.
Try
objResults1.Fill(ds)
Catch ex As Exception
Me.lblError.Text = "An error occurred while filling objResults1, in sbFillPending."
objConnection.Close()
Exit Sub
End Try

' Clean up objects.
objConnection.Close()

Me.dgPending.DataSource = ds
Me.dgPending.DataBind() 'bind the datagrid with the datasource

End Sub

so have 2 more of these for the other two datagrids, each one is slightly different in the 'WHERE' clauses and also the fields that are getting SELECTed vary.

Just been thinking about it, maybe the best idea is to dim each Select statement in the one sub, then dim a sqlcommand each, and run each one while the connection is open. or is it better to have one strSelect dim'ed, and seperate each select with a ';' i.e. "select * from tbl; select * from tbl where field=value; select * where field=another value" and then run that under an sqlCommand. But if you use one strSelect, how do you retrieve the results, to then bind to each datagrid?

any help would be appreciated, but since it is Firday knock-off time for me, you guys can have the weekend to think about it, and come up with the perfect solution :D

cya's Monday!!

[sirpelidor]

Suppose all the select statement is a subset of 1 big table with some common criteria.

You can make 1 trip to sql server, grab the big table and store it into your dataset object.

Then Filtering and Sorting with the DataTable Select Method,
i.e: use public DataRow[] Select(string) to get an array of all DataRow objects that match the filter criteria

You won't be able to bind the array of DataRows objects into datagrid directly, but you can translate those DataRows into DataView and bind it to datagrid.

That way, when a page is being request, say if you have 3 diff select statement, you will still only make 1 trip to sql server.

hope this article (http://www.akadia.com/services/dotnet_filter_sort.html) will help

[Cstick]

You can do a batch query like you suggested, something like this;

(untested)

Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlDataAdapter("select * from tbl; select * from tbl where field=value; select * where field=another value;", conn)

Dim ds As New DataSet

conn.Open()
cmd.Fill(ds)
conn.Close()

gv1.DataSource = ds.Tables(0)
gv1.DataBind()

gv2.DataSource = ds.Tables(1)
gv2.DataBind()

gv3.DataSource = ds.Tables(2)
gv3.DataBind()


However from a database architects standpoint, the way you are currently doing it with 3 separate queries is probably the best method for flexibility.

The fact that you are only making 3 queries leads me to believe that the inefficiency does not lie in your method for querying the database but rather the amount of data you are querying or the efficiency of your queries. Instead of using "SELECT *" in queries, pull down only the necessary fields.

How many records are you pulling down for each of the queries? Perhaps you can limit the number of records for each query by using custom paging. If you are pulling 1000s of records and only displaying 10, well I'm sure you see the overhead with that.

Generally, unless you are making a large number of queries in the range of 50+, there will be no noticeable benefit to reducing the number of queries you are making.

Hope that helps.

[cardona]

thanks for the replies!

I have been looking at and am now implementing sirpelidor's suggestion, mainly because sirpelidor got in first, and I only read Cstick's post today :)

sirpelidor the article you pointed me to was good, seemed a little confusing at first (maybe coz it was C#?) but I have seemed to have figured it out, I also needed to do a "Top 5" on 2 of the datagrids, it seems dataviews don't support this, but I found this page (http://www.aspapp.com/forums.asp?ForumId=11&TopicId=2979) and adapted it to what i needed.

Cstick, that was what I was looking for too! just got it a bit late! I didn't understand how the result could be bound to each data grid, but your explanation cleared it up for me; each select statement is another table in the dataset, pretty obvious i guess :cool:

I am still selecting all rows from the table in the DB, at the moment this is only a few, but could potentially/eventually be 1000s, I have restricted it to only the four fields that I need.

thanks again!

[sirpelidor]

article seemed a little confusing at first (maybe coz it was C#?)

if you ever run into problem with language translation, this site (http://www.carlosag.net/Tools/CodeTranslator/Default.aspx) will help u take care of most of the footwork.