|
Not sure whether to put this query into the database section or here, but here goes.
I have a SQL Server database with a table of contacts (about 130,000 records). Each contact is a member of a category (9 different categories). What i need to do is count how many contacts are in each category for output in my vb.net application.
I can think of 2 methods to do this (actually 3 but the third is a definate no), which would you recommend.
Method 1 : Use a Cursor in a SQL Server stored procedure to iterate through all the contacts keeping a count of contacts in each category.
Method 2 : Get the data from the database and do the processing in my application.
Which approach do you think would ofrer the best performance?
(In case you were wondering, the 3rd method involves calling a stored procedure 9 times, once for each category and doing the count)
|
|
|
|
|
Since option 1 mentions access to stored procedures, why not write a stored procedure to return the data in one call? If the categories exist in a table, then you should be able to join on that table.
And this should be in the database section...
Tim
|
|
|
|
|
It is relatively easy to do in sql, depending on your database structure. If you give a few more details of the database structure I am sure it will be easy to write a sql query - after all, its this type of thing sql was designed for.
Liqz wrote: Method 1 : Use a Cursor in a SQL Server stored procedure to iterate through all the contacts keeping a count of contacts in each category.
Slow. Only ever use cursors if you have process on a record by record basis
Liqz wrote: Method 2 : Get the data from the database and do the processing in my application.
Could do, but you are return large volumes of data which again, you would end up processing on a record by record basis.
If the data is held in a link table something like
contactid, categoryid
The query is
select categoryid, count(*) from links group by categoryid
or, if its a single column on the contacts table with different values
select categoryid, count(*) from contacts group by categoryid
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Don't do cursors, but rather go with stored procedures. You'll be better off performance-wise.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Ah OK, thanks for the help. My novice-ness is obviously showing through!
|
|
|
|
|
Can some tell me why this would not work, i try everything and i still won't work
If updating = "y" Then
Application.ScreenUpdating = True
Else
Sheets("graph").Select
Application.ScreenUpdating = False
End If
Sheets("data").Select
endrow = Worksheets("path").Range("c12").Value
startrow = Worksheets("path").Range("c11").Value
num_columns = 1
str_col_11 = startrow & ":"
name_column = ""
If toomany = False Then
Do
num_columns = num_columns + 1
Loop While Not IsEmpty(Worksheets("data").Rows(10).Cells(num_columns))
num_columns = num_columns - 1
startc = 1
endc = num_columns
Else
to_plot = Worksheets("path").Range("c18").Value
Select Case to_plot
Case "a"
startc = 1
endc = Worksheets("path").Range("C15").Value
Case "b"
startc = 91
endc = Worksheets("path").Range("C16").Value + 90
Case "c"
startc = 181
endc = Worksheets("path").Range("C17").Value + 180
End Select
End If
' On Error GoTo errs
j = 4
If Graph.OptionButton1.Value = True Then
xax = 1
ElseIf Graph.OptionButton2.Value = True Then
xax = 2 'what do you want plotted on the x-axis?
End If
startt = Timer
For i = startc To endc
Application.StatusBar = "**** (Charting " & startc & " to " & endc & ") " & Application.WorksheetFunction.Round(((i / endc) * 100), 0) & "% Complete Memory in Use: " & Application.WorksheetFunction.Round(Application.MemoryUsed / 1000000#, 0) & " MB ****"
name_column = Worksheets("data").Cells(10, i).Value
Call Sheet1.col_lookup(i, alpha) 'lookup column alpha reference
str_setcolumn = alpha
strSelCol = str_setcolumn & startrow & ":" & str_setcolumn
Range(strSelCol & endrow).Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("data").Range(strSelCol & endrow), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=name_column
|
|
|
|
|
Hi!
Could you tell us what it's supposed to do? Then we might be able to give you some advice.
Thanks! 
MrWolfy
|
|
|
|
|
It has already been mentioned that you should provide more details. What is supposed to happen? What is actually happening? Are there erros? If so give details.
You're more likely to get help that way.
|
|
|
|
|
What the program does is that it takes a raw data and graphs each column individually... my problem is that it graphs everything until it gets to the tenth column that is where the error occurs
|
|
|
|
|
Gentlemen thank you for your help i found the problem, and the program ran fine
|
|
|
|
|
Hi All!
You've all been soooo helpful so far, so I was wondering wheather you'd help me with something
else.
I Use a text editor called 'Notepad ++' for HTML Editing and such, and it's syntax highlighting
library 'SciLexer.dll' (Originally used in the popular scintilla editor) caught my attention.
It is provided free for use in applications so I tried adding the SciLexer to the toolbox (Silly Me).
It says it isn't a valid .NET Control so how would i go about (If possible) integrating it into my VB Application?
I think Notepad ++ was written in C++ so it may not be possible in VB.
I should point out i know next to Nothing about C++ : I can just about print a line to a console window.
Thanks! 
MrWolfy
modified on Friday, June 27, 2008 11:09 AM
|
|
|
|
|
Hi Everybody!
I Just found a great control called ScintillaNET For use in Visual Studio 2005/2008, and it looks fantastic. There is only one problem...
I Can't get it to work.
The Editor Displays all right, but then it does not display line numbers, not highlight my code, anyone know what's wrong?
Thanks!
MrWolfy
|
|
|
|
|
MrWolfy wrote: ScintillaNET
Yeah, it's nice.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Okay, I'm a year late here but just in case...
Line numbers and folding: ScintillaNet.Margins.
If the margin width is zero, the line nums / fold marks (+/-) are hidden. Margins 0 & 1 do the lines, and margin2 handles the folding. Just set the width - about 40 for line nums and 14 for folding. They'll be there.
|
|
|
|
|
Hi!
I did work this out eventually, but thanks anyway! It's good to know that people out there are looking out for people who need help.
Thanks!
MrWolfy
|
|
|
|
|
Especially this "Graph.ComStart.Enabled = False "
i = 0
Sheets("Graph").Select
ActiveSheet.Cells(4, 2).Select
Delsheet(i) = Worksheets("graph").Cells(4, 2).Value
Do Until Delsheet(i) = ""
Delsheet(i) = Worksheets("graph").Cells(4, 2).Value
Worksheets("graph").Cells(4, 2).Delete
Sheets("Graph").Select
i = i + 1
max = i
Delsheet(i) = Worksheets("graph").Cells(4, 2).Value
Loop
i = 0
For i = 0 To max - 1
Sheets(Delsheet(i)).Select
ActiveWindow.SelectedSheets.Delete
Next i
Graph.TextBox1.Text = ""
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Graph.CommandButton2.Enabled = False
Graph.CommandButton2.BackColor = &H80&
Graph.ComStart.Enabled = False
Graph.ComStart.BackColor = &HC000&
Application.StatusBar = ""
Sheets("Graph").Select
|
|
|
|
|
The first part orders some coffee beans. It then grinds them in the Do..Loop until they are ready. The next For loop pours just the right amount of water (max-1). Finally, it puts it in the maker and enables the display of Alerts and screen Updating to show the progress in the StatusBar.
No, seriously, you might want to be a little more specific with your questions.
|
|
|
|
|
Be more verbose...explain what?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I have to create a desktop application on .net2.0. One of the part of the application is restrict user to access certain web site.After the installation of the application when user type the restricted word on the address bar the browser will stop automatically. During the Installation administrator will set the restricted word they need not know the actual url of the restricted web site.
Please provide me some gudience, it is very needed to me.
|
|
|
|
|
What, school project, do some research (try Google), do some reading, ask SPECIFIC questions where we can help. No one is going to do your project for you but we will help if you have a valid question.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
am74 wrote: it is very needed to me
Do you have a specific question? Try working on your own project then ask when stuck on something specific.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi there All !
Sorting Unicode is very easy ,
Combobox in VB.2005 is supported Unicode String
but some of the function like the AutoCompleteSource in search string in combobox is not work well with string unicode ,
does anyone know how to make it well ? , i need some of code and link , or any ideas as you can ..
or u have any way that similar to that function , pls speak out .. come on , i wait for you to reply na
best regard from
nimol men
|
|
|
|
|
Anatha wrote: need some of code and link
Try this site...[^]
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi
i have a gridview control and i want to export its data to sql database table.how can it possible.
thanx
|
|
|
|
|
You are using all the wrong terms - you cannot "export" a gridview to a SQL table.
However you can insert each row in the gridview into an existing table in SQL server. Use 2 loops, 1 for rows, the second for cells in the gridview. At each row populate the insert method (preferably a stored proc) with each cell from the gridview. Insert/execute the method. Process the next row.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
