How to consolidate four SELECT statements into one.

I made a web application for managing about three tables in a 2008 R2 database in VB.Net with several GridViews in a <asp:MultiView> using Tabs to navigate from View to View. ...Maybe 4 years ago.

The pages take excessive time to refresh.  Half a minute for some of the GridViews to refresh data or even to Select a row.  

The first thing I want to fix is in the Page_Load I have this block of code to get four "counts" from a table to display in labels at the top of the page.  It's using four separate SqlCommands and I know it can be done with a single Select with a Group By maybe.  I'm just not sure how to do it.

Here is the whole thing.  Could you show me a better way, using only a single "trip" to the database?
        If Not Page.IsPostBack Then
            Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("CSPConnectionString1").ConnectionString)

            Dim DBCmd As New SqlCommand
            Dim DBCmd2 As New SqlCommand
            Dim DBCmd3 As New SqlCommand
            Dim DBCmd4 As New SqlCommand
            DBConn.Open()
            Try
                DBCmd = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants]", DBConn)
                DBCmd2 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'North')", DBConn)
                DBCmd3 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'Central')", DBConn)
                DBCmd4 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'South')", DBConn)
                Dim varCount As Integer = DBCmd.ExecuteScalar()
                Dim varCount2 As Integer = DBCmd2.ExecuteScalar()
                Dim varCount3 As Integer = DBCmd3.ExecuteScalar()
                Dim varCount4 As Integer = DBCmd4.ExecuteScalar()
                lblCounts.Text = " Participants: " & varCount.ToString & ", " & " North: " & varCount2.ToString & ", " & " Central: " & varCount3.ToString & ", " & " South: " & varCount4.ToString
            Catch exp As Exception
                Response.Write(exp)
            End Try
            DBCmd.Dispose()
            DBConn.Close()
            DBConn = Nothing
        End If

Open in new window

LVL 1
megninAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Apart of the SQLCommand you will need a SQLDataReader. The following code is only a base that you need to work in to fit your needs:
Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("CSPConnectionString1").ConnectionString)

Dim DBCmd As New SqlCommand
DBConn.Open()

DBCmd = New SqlCommand("SELECT Center, COUNT(keyApplicantID) 
FROM Applicants
GROUP BY ROLLUP(Center)", DBConn)    
SqlDataReader rdr = DBCmd.ExecuteReader();            	     
    
While rdr.Read()
    lblCounts.Text =  lblCounts.Text & ", " & rdr.Item(0) ": " & rdr.Item(1)
End While
rdr.Close()

Open in new window

0
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
How about:

DBCmd = New SqlCommand("SELECT 
(SELECT COUNT(keyApplicantID) FROM[Applicants]) as All, 
(SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'North')) as North, 
(SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'Central')) as Central, 
(SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'South')) as South", DBConn)

Open in new window

0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You can use the ROLLUP function that will give you a COUNT by Center and the last line (CENTER=NULL) is the grand total:
DBCmd = New SqlCommand("SELECT Center, COUNT(keyApplicantID) 
FROM Applicants
GROUP BY ROLLUP(Center)", DBConn)

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
megninAuthor Commented:
Vitor, that's what I'm looking for.  

Phillip, it looks like that has the same number of select or round trips to the database as my original code, or does putting the selects into a single SqlCommand make it a single round trip.  I suspect that's the case.

Vitor, sorry for the newb questions, but how would I then plug the returned counts into the label texts?
Phillip, I'm not even sure how to do that with your method.

I'm currently putting the count values into four variables with these:
               Dim varCount As Integer = DBCmd.ExecuteScalar()
                Dim varCount2 As Integer = DBCmd2.ExecuteScalar()
                Dim varCount3 As Integer = DBCmd3.ExecuteScalar()
                Dim varCount4 As Integer = DBCmd4.ExecuteScalar()
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to loop through all the records and in each loop you take the center name and the value.
0
 
megninAuthor Commented:
I'm sorry, really newb here.  ;-)  I don't even know how to loop through all the records.  Could you show me what that would look like?
0
 
megninAuthor Commented:
I built this several years ago I think I need to adjust the "compatibility mode."  I'm getting this when I just try the query in SSMS:
Msg 10707, Level 15, State 1, Line 12
The CUBE() and ROLLUP() grouping constructs are not allowed in the current compatibility mode.  They are only allowed in 100 mode or higher.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The CUBE() and ROLLUP() grouping constructs are not allowed in the current compatibility mode.  They are only allowed in 100 mode or higher.
So, you migrated the database from a SQL Server 2000 or 2005, right?
You really need to set the compatibility mode to 100 to have it working for you.
0
 
megninAuthor Commented:
Do you think there is much likelihood that something that worked in compatibility mode 90 would not work in 100?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The ROLLUP function didn't exist before SQL Server 2008 (compability 100) so if a database has a compability set for a previous version it can't use the ROLLUP function.
0
 
megninAuthor Commented:
I get that.  That's why I got that message when I tried the query.  That database is set for compatibility mode 90 since it was migrated from a SQL Server 2005.

I checked most of my other databases and they are set to mode 100.  This one is just older.

Anyway, my problem still remains that I don't know how to assign four "counts" to the four variables; varCount, varCount2, varCount3, and varCount4.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Anyway, my problem still remains that I don't know how to assign four "counts" to the four variables; varCount, varCount2, varCount3, and varCount4.
You won't need it. In the example I gave I just built the label on the fly:
While rdr.Read()
    lblCounts.Text =  lblCounts.Text & ", " & rdr.Item(0) ": " & rdr.Item(1)
End While
0
 
megninAuthor Commented:
Ah, gotcha.  I didn't catch that.  That should work great.  

 I'm going to still have to change the compatibility mode from 90 to 100 to use the ROLLUP function.  I think I need to make sure no one is actually using the database when I change the mode.  In a forum post someone said it could cause errors if a query was in the middle of being run when the mode changed.  That the execution plan could become based part on one mode and part on the new one.   I didn't understand it but it sounded like a concern.
0
 
megninAuthor Commented:
Since I don't need to display the output in a columnar table, can I use the Group By without the ROLLUP to be compatible with the compatibility mode 90?
0
 
megninAuthor Commented:
Oh, ROLLUP return the total.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
How about this?:

DBCmd = New SqlCommand("SELECT
SUM(1) AS [All],
SUM(CASE WHEN Center = 'North' THEN 1 ELSE 0 END) as North,
SUM(CASE WHEN Center = 'Central' THEN 1 ELSE 0 END) as North,
SUM(CASE WHEN Center = 'South' THEN 1 ELSE 0 END) as South
FROM[Applicants]", DBConn)
0
 
megninAuthor Commented:
Hi Scott,

Yeah, that would work great, too.  

How would I assign the counts to the variables for the label values or as Vitor did just build the label with all four values included?

Since this doesn't use a reader I can't use item() index the way Vitor did.

Thanks
0
 
Scott PletcherSenior DBACommented:
Since this query returns more than one value, I believe you have to use ExecuteReader.

Then, I think you can use rdr.GetValue(n) to pull out the relevant values, although I am NOT an expert on this.

I suspect you'll need to use some type of "toInt" or other casting to get the values correctly:

Dim varCount As Integer = rdr.GetValue(0)
Dim varCount2 As Integer = rdr.GetValue(1)
...
0
 
megninAuthor Commented:
Okay.  I tried piecing together Scott's SqlCommand with Vitor's label text assignment.
Vitor's SqlCommand isn't compatible with my database's compatibility mode

I tried this in my application and just got an error:
        If Not Page.IsPostBack Then
            'Dim DBConn As New SqlConnection("UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CSP;Data Source=MSQLServ.broward-workforce.org;Packet Size=4096;")
            Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("CSPConnectionString1").ConnectionString)

            Dim DBCmd As New SqlCommand
            'Dim DBCmd2 As New SqlCommand
            'Dim DBCmd3 As New SqlCommand
            'Dim DBCmd4 As New SqlCommand
            DBConn.Open()
            Try
                Dim sqlCmd As String
                sqlCmd = "'SELECT "
                sqlCmd = sqlCmd + "SUM(1) AS [All], "
                sqlCmd = sqlCmd + "SUM(CASE WHEN Center = 'North' THEN 1 ELSE 0 END) as North, "
                sqlCmd = sqlCmd + "SUM(CASE WHEN Center = 'Central' THEN 1 ELSE 0 END) as North, "
                sqlCmd = sqlCmd + "SUM(CASE WHEN Center = 'South' THEN 1 ELSE 0 END) as South "
                sqlCmd = sqlCmd + "FROM[Applicants]', DBConn)"

                DBCmd = New SqlCommand(sqlCmd)

                Dim rdr As SqlDataReader
                rdr = DBCmd.ExecuteReader()

                While rdr.Read()
                    lblCounts.Text = lblCounts.Text & ", " & rdr.Item(0) & ": " & rdr.Item(1)
                End While
                rdr.Close()

                'DBCmd = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants]", DBConn)
                'DBCmd2 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'North')", DBConn)
                'DBCmd3 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'Central')", DBConn)
                'DBCmd4 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'South')", DBConn)
                ''lblCounts.Text = Convert.ToString(DBCmd.ExecuteScalar())
                'Dim varCount As Integer = DBCmd.ExecuteScalar()
                'Dim varCount2 As Integer = DBCmd2.ExecuteScalar()
                'Dim varCount3 As Integer = DBCmd3.ExecuteScalar()
                'Dim varCount4 As Integer = DBCmd4.ExecuteScalar()
                'lblCounts.Text = " Participants: " & varCount.ToString & ", " & " North: " & varCount2.ToString & ", " & " Central: " & varCount3.ToString & ", " & " South: " & varCount4.ToString
            Catch exp As Exception
                Response.Write(exp)
            End Try
            DBCmd.Dispose()
            DBConn.Close()
            DBConn = Nothing
        End If

Open in new window

0
 
megninAuthor Commented:
I'll just leave well enough alone.  It's dog slow, but it works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.