-
Mar 14th, 2018, 01:48 AM
#1
Thread Starter
Frenzied Member
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
-
Mar 14th, 2018, 02:36 AM
#2
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.
-
Mar 14th, 2018, 03:22 AM
#3
Re: Help with query
Hello again... how about something like:
SQL Code:
SELECT tblRiskAssessment.Hazard, TOTALS_Risks = COUNT(*) FROM FROM tblContext LEFT OUTER JOIN tblRiskAssessment ON tblContext.ContextID = tblRiskAssessment.ContextID GROUP BY tblRiskAssessment.Hazard HAVING (tblContext.ContextID LIKE @ContextID)
Kris
-
Mar 14th, 2018, 05:24 AM
#4
Re: Help with query
Do you have indexes on the tables?
Please remember next time...elections matter!
-
Mar 14th, 2018, 06:20 AM
#5
Re: Help with query
This might work for you:
SQL Code:
SELECT tblRiskAssessment.Hazard, 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 IN ('Doors','Windows','Ladders') GROUP BY tblRiskAssessment.Hazard
-
Mar 14th, 2018, 06:55 AM
#6
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
-
Mar 14th, 2018, 08:00 AM
#7
Thread Starter
Frenzied Member
Re: Help with query
Originally Posted by TysonLPrice
Do you have indexes on the tables?
Hi, in SQL Server the tables is all "Indexable" YES?
-
Mar 14th, 2018, 08:07 AM
#8
Thread Starter
Frenzied Member
Re: Help with query
Originally Posted by si_the_geek
This might work for you:
SQL Code:
SELECT tblRiskAssessment.Hazard, 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 IN ('Doors','Windows','Ladders') 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?
-
Mar 14th, 2018, 08:31 AM
#9
Re: Help with query
Originally Posted by schoemr
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!
-
Mar 14th, 2018, 11:29 AM
#10
Re: Help with query
Originally Posted by schoemr
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.
-
Mar 14th, 2018, 10:54 PM
#11
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
-
Mar 15th, 2018, 12:36 AM
#12
Thread Starter
Frenzied Member
Re: Help with query
Originally Posted by si_the_geek
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|