Visual Basic

Application processing versus sql cursor processing

27-Jun-08 16:05

As Christian has said, do multi-part file and just have a way to be able to work with all the parts when unzipping.

"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

Jay Royall27-Jun-08 5:44

Jay Royall

27-Jun-08 5:44

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)

Re: Application processing versus sql cursor processing

Tim Carmichael27-Jun-08 6:57

Tim Carmichael

27-Jun-08 6:57

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

Re: Application processing versus sql cursor processing

Ashfield27-Jun-08 9:17

Ashfield

27-Jun-08 9:17

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

Re: Application processing versus sql cursor processing

Paul Conrad27-Jun-08 16:07

Re: Application processing versus sql cursor processing

27-Jun-08 16:07

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

Jay Royall29-Jun-08 22:19

Jay Royall

29-Jun-08 22:19

Ah OK, thanks for the help. My novice-ness is obviously showing through!

Can some tell me why this would not work

Kahuna27-Jun-08 4:52

27-Jun-08 4:52

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

Saul Johnson27-Jun-08 5:00

27-Jun-08 5:00

Hi!

Could you tell us what it's supposed to do? Then we might be able to give you some advice.

Thanks! Big Grin | :-D

MrWolfy

Jay Royall27-Jun-08 5:02

Jay Royall

27-Jun-08 5:02

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.

Kahuna27-Jun-08 5:18

27-Jun-08 5:18

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

Kahuna27-Jun-08 5:21

Possible to use SciLexer in VB? [modified]

27-Jun-08 5:21

Gentlemen thank you for your help i found the problem, and the program ran fine

Saul Johnson27-Jun-08 4:00

27-Jun-08 4:00

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! Smile | :)

MrWolfy

modified on Friday, June 27, 2008 11:09 AM

Saul Johnson27-Jun-08 9:55

27-Jun-08 9:55

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 Smile | :)

Paul Conrad27-Jun-08 16:08

27-Jun-08 16:08

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

Alan Burkhart5-Oct-09 7:28

Alan Burkhart

5-Oct-09 7:28

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.

Saul Johnson8-Oct-09 8:17

Can someone please explain this to me!!!!!!

8-Oct-09 8:17

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 Big Grin | :-D

Kahuna27-Jun-08 1:55

Re: Can someone please explain this to me!!!!!!

27-Jun-08 1:55

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

jzonthemtn27-Jun-08 2:28

jzonthemtn

27-Jun-08 2:28

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.

Re: Can someone please explain this to me!!!!!!

Paul Conrad27-Jun-08 16:09