Solved

How to consolidate four SELECT statements into one.

Posted on 2015-02-24
20
115 Views
Last Modified: 2015-02-24
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

0
Comment
Question by:megnin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 2
  • +1
20 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 125 total points
ID: 40628088
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
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40628101
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
 
LVL 1

Author Comment

by:megnin
ID: 40628313
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40628321
You need to loop through all the records and in each loop you take the center name and the value.
0
 
LVL 1

Author Comment

by:megnin
ID: 40628328
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
 
LVL 1

Author Comment

by:megnin
ID: 40628370
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
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40628393
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40628398
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
 
LVL 1

Author Comment

by:megnin
ID: 40628441
Do you think there is much likelihood that something that worked in compatibility mode 90 would not work in 100?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40628529
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
 
LVL 1

Author Comment

by:megnin
ID: 40628540
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40628544
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
 
LVL 1

Author Comment

by:megnin
ID: 40628648
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
 
LVL 1

Author Comment

by:megnin
ID: 40628655
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
 
LVL 1

Author Comment

by:megnin
ID: 40628675
Oh, ROLLUP return the total.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 40628724
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
 
LVL 1

Author Comment

by:megnin
ID: 40628833
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40628990
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
 
LVL 1

Author Comment

by:megnin
ID: 40629061
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
 
LVL 1

Author Comment

by:megnin
ID: 40629072
I'll just leave well enough alone.  It's dog slow, but it works.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question