Results 1 to 12 of 12

Thread: Help with query

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Help with query

    Hi, I have a table that looks like this:

    Code:
    RiskID
    ContextID
    Hazard
    RiskID is primary key.

    Then I am count specific Hazards in that Hazard column e.g. I want to count the number of "Windows" BUT I am counting the number of "Windows" for a specific ContextID.

    Then, I have a frmTotals with a lot of textboxes that is display the results. All this is work...

    My problem is that there is about 200 of these items I am counting and is taking very long time (on a good day I am get maybe 1mb/sec)

    Now I can just imagine this must be very stupid way but this is how I do it below. Here is segment of code (there are 200 in total).
    I was thinking maybe not display results in userform/texboxes.. Maybe a report. But I don't know how to write such a query then...



    Code:
    Dim cmd1 As New SqlClient.SqlCommand("Select COUNT(tblRiskAssessment.Hazard) as TOTALS_Risks FROM tblContext LEFT OUTER JOIN tblRiskAssessment ON tblContext.ContextID = tblRiskAssessment.ContextID WHERE (tblContext.ContextID LIKE @ContextID) AND (tblRiskAssessment.Hazard = 'Doors')", con)
            cmd1.Parameters.AddWithValue("ContextID", MyGlobalVariableContextID)
            Dim cnt1 As Integer = CInt(cmd1.ExecuteScalar)
            Me.txt1.Text = cnt1
            If CInt(txt1.Text) > 0 Then
                txt1.BackColor = Color.Gold
            End If
    
            Dim cmd2 As New SqlClient.SqlCommand("Select COUNT(tblRiskAssessment.Hazard) as TOTALS_Risks FROM tblContext LEFT OUTER JOIN tblRiskAssessment ON tblContext.ContextID = tblRiskAssessment.ContextID WHERE (tblContext.ContextID LIKE @ContextID) AND (tblRiskAssessment.Hazard = 'Windows')", con)
            cmd2.Parameters.AddWithValue("ContextID", MyGlobalVariableContextID)
            Dim cnt2 As Integer = CInt(cmd2.ExecuteScalar)
            Me.txt2.Text = cnt2.ToString
            If CInt(txt2.Text) > 0 Then
                txt2.BackColor = Color.Gold
            End If
    
            Dim cmd3 As New SqlClient.SqlCommand("Select COUNT(tblRiskAssessment.Hazard) as TOTALS_Risks FROM tblContext LEFT OUTER JOIN tblRiskAssessment ON tblContext.ContextID = tblRiskAssessment.ContextID WHERE (tblContext.ContextID LIKE @ContextID) AND (tblRiskAssessment.Hazard = 'Ladders')", con)
            cmd3.Parameters.AddWithValue("ContextID", MyGlobalVariableContextID)
            Dim cnt3 As Integer = CInt(cmd3.ExecuteScalar)
            Me.txt3.Text = cnt3.ToString
            If CInt(txt3.Text) > 0 Then
                txt3.BackColor = Color.Gold
            End If

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Help with query

    Hi,

    take a look at PIVOT for Sql-Server

    here a sample...
    https://stackoverflow.com/questions/...count-in-pivot

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    Re: Help with query

    Hello again... how about something like:

    SQL Code:
    1. SELECT   tblRiskAssessment.Hazard,
    2.          TOTALS_Risks = COUNT(*)
    3. FROM     FROM tblContext LEFT OUTER JOIN tblRiskAssessment ON tblContext.ContextID = tblRiskAssessment.ContextID
    4. GROUP BY tblRiskAssessment.Hazard
    5. HAVING  (tblContext.ContextID LIKE @ContextID)

    Kris

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Help with query

    Do you have indexes on the tables?
    Please remember next time...elections matter!

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Help with query

    This might work for you:
    SQL Code:
    1. SELECT tblRiskAssessment.Hazard, COUNT(tblRiskAssessment.Hazard) as TOTALS_Risks
    2.  
    3. FROM tblContext
    4. LEFT OUTER JOIN tblRiskAssessment ON tblContext.ContextID = tblRiskAssessment.ContextID
    5.  
    6. WHERE (tblContext.ContextID LIKE @ContextID)
    7.   AND (tblRiskAssessment.Hazard IN ('Doors','Windows','Ladders')
    8.  
    9. GROUP BY tblRiskAssessment.Hazard

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Help with query

    Unless ContextID is a string and the parameter includes wildcards in it, don't use the LIKE. It should be a straight equals. That's probably where part of your performance issues are coming from.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Help with query

    Quote Originally Posted by TysonLPrice View Post
    Do you have indexes on the tables?
    Hi, in SQL Server the tables is all "Indexable" YES?

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Help with query

    Quote Originally Posted by si_the_geek View Post
    This might work for you:
    SQL Code:
    1. SELECT tblRiskAssessment.Hazard, COUNT(tblRiskAssessment.Hazard) as TOTALS_Risks
    2.  
    3. FROM tblContext
    4. LEFT OUTER JOIN tblRiskAssessment ON tblContext.ContextID = tblRiskAssessment.ContextID
    5.  
    6. WHERE (tblContext.ContextID LIKE @ContextID)
    7.   AND (tblRiskAssessment.Hazard IN ('Doors','Windows','Ladders')
    8.  
    9. GROUP BY tblRiskAssessment.Hazard
    Hi, I like the stipulate "what" to count 'Doors' , 'Windows' etc.. This counts that item IF there is something to count. But suppose I would like to see all items with the results? Eg. Say There is 10 x Windows but 0 x Doors then can it show like this:?

    Code:
    Windows   |10
    Doors       | 0
    Is this possible?

  9. #9
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Help with query

    Quote Originally Posted by schoemr View Post
    Hi, in SQL Server the tables is all "Indexable" YES?
    If that means can indexes be applied the answer is yes. I'm aware of basic indexing but it can get involved.
    Please remember next time...elections matter!

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Help with query

    Quote Originally Posted by schoemr View Post
    Hi, I like the stipulate "what" to count 'Doors' , 'Windows' etc.. This counts that item IF there is something to count. But suppose I would like to see all items with the results? Eg. Say There is 10 x Windows but 0 x Doors then can it show like this:?

    Code:
    Windows   |10
    Doors       | 0
    Is this possible?
    It would be possible, but I'm pretty sure it would be easier to do on the VB side of things.

  11. #11
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Help with query

    Maybe you can do something with relations. Probablyfaster if you already have the data in the dataset

    Code:
    Public Class DsetRelations
        Dim Dset As New DataSet
        Dim ParentBS, ChildBS As New BindingSource
        Private Sub DsetRelations_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With Dset
                .Tables.Add(New DataTable With {.TableName = "Parent"})
                .Tables.Add(New DataTable With {.TableName = "Child"})
                With .Tables("Parent")
                    .Columns.Add("ID", GetType(Integer))
                    .Columns("ID").AutoIncrement = True
                    .Columns.Add("Type", GetType(String))
                    'Create a column and concat ID+Whatever to "groupby"
                    .Columns.Add("ParentID", GetType(String), String.Format("{0}+{1}", "ID", "Type"))
    
                End With
                With .Tables("Child")
                    .Columns.Add("ID", GetType(Integer))
                    .Columns.Add("Type", GetType(String))
                    'Create a column and concat ID+Whatever to "groupby"
                    .Columns.Add("ChildID", GetType(String), String.Format("{0}+{1}", "ID", "Type"))
                    .Columns.Add("QTY", GetType(Double))
                End With
    
                'Join by newcrated concat cols
                Dim ParentCol As DataColumn = .Tables("Parent").Columns("ParentID")
                Dim ChildCol As DataColumn = .Tables("Child").Columns("ChildID")
    
                Dset.Relations.Add(New DataRelation("ParentChild", ParentCol, ChildCol, False))
    
                'create Datacolumn in paret with expression to aggregate child QTY col
                With .Tables("Parent")
                    .Columns.Add("TotalQTY", GetType(Double), "Sum(Child.QTY)")
                End With
    
                'review
                ParentBS.DataSource = .Tables("Parent")
                ChildBS.DataSource = ParentBS
                ChildBS.DataMember = "ParentChild"
    
                DataGridView1.DataSource = ParentBS
                DataGridView2.DataSource = ChildBS
            End With
        End Sub
    End Class

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Help with query

    Quote Originally Posted by si_the_geek View Post
    It would be possible, but I'm pretty sure it would be easier to do on the VB side of things.
    But I am use VB2017

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width