Solved

How to consolidate four SELECT statements into one.

Posted on 2015-02-24
20
110 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
  • 11
  • 6
  • 2
  • +1
20 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 125 total points
Comment Utility
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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
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
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
Do you think there is much likelihood that something that worked in compatibility mode 90 would not work in 100?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:megnin
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Oh, ROLLUP return the total.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
I'll just leave well enough alone.  It's dog slow, but it works.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now