asked on

How do I convert vb.net code into a SQL stored procedure?

I recently asked the best method for me to speed up a process in an application. The answer was to convert it to a stored procedure. Let the server do the work, versus the application. I use LINQ to SQL in a Visual Studio 2013 windows forms application.

I am attaching the original code.

I never use stored procedures because the method I use works. So this is new to me.

First, I created a stored procedure and it works fine.
CREATE PROCEDURE [dbo].[sp_GetCrazyWeeks]
      @NASA_Contract nvarchar(50) = NULL,
      @Rev nvarchar(10) = NULL
AS
      Set nocount on;
      SELECT *
      from tblCrazyLandDevel
      where NASA_Contract = @NASA_Contract and Rev = @Rev
      order by WeekEnding ASC
RETURN 0

I used LINQ and added the SPROC to my dbml. Works fine.

I call it up in my application. Works fine.

Dim crzyWk = d.sp_GetCrazyWeeks(CStr(Me.NASA_ContractComboBox.SelectedItem), CStr(Me.ComboBoxRev.SelectedItem))

What I do in the app is "For Each" and "Next" each row I retrieve. I try it out in a simple thing and it works.

For Each x In crzyWk
Dim xTO As String = x.NASA_Contract
Dim xRev As String = x.Rev
Dim xWE As String = CStr(x.WeekEnding)
Next

Next, inside this, I pull additional data from the server, as this is a compiling process and update many variables.

So I wrote some more SPROCs.
Dim lwpPlans = d.sp_LastWeeksPlans(CStr(xTO), CStr(xRev), CDate(xWE))
Dim thisWeekPlans = d.sp_ThisWeeksPlans(CStr(xTO), CStr(xRev), CDate(xWE))

Now I am stuck because I don't know how to use was I am pulling. It's there, I can see it, but what to do. I usually do it like this, but something similar is not working.
planLab = thisWeekPlans.Item(0).Labor

And I am also thinking, what is the point between the SPROC and what I am currently doing?? Seriously, it doesn't seem to be any faster. So I must be approaching the SPROC-thing wrong. Should I be SPROC-ing exclusively in SQL Server and just passing parameters through the application?

Any suggestions, direction, links would be appreciated.
Many thanks!
getFY13.docx

Chris Luttrell

And I am also thinking, what is the point between the SPROC and what I am currently doing?? Seriously, it doesn't seem to be any faster. So I must be approaching the SPROC-thing wrong. Should I be SPROC-ing exclusively in SQL Server and just passing parameters through the application?

:-)
Correct, there is probably not a point in just doing the same thing but using Stored Procedures to interact with the database instead of your regular code.
As has been said, sometimes attributed to Einstein,

Insanity: doing the same thing over and over again and expecting different results.

I guess it would hold true, even if you are doing it in a different place?
If these things apply then doing things in a Stored Procedure might be for you, otherwise maybe not.
1. You are working with a lot of data, especially lots of rows, that are in the database and you need to get some set of rows back from it, even if it is a single row of calculated values.
2. You can pass in the set of "parameters" in one pass to the procedure to cut down on the back and forth.

There is an expression used in the SQL community to describe the bad practice of trying to do everything in a single row procedural way, RBAR, for Row by Agonizing Row. And no matter where you do that, it is not going to perform well when scaled.

I was intrigued by your question the other day but others were trying to help you and I don't like to interfere as going in multiple directions at the same time is not a good path. I am looking at your posted code, but I am not a VB expert, like you are not familiar with SQL, but I can parse most of it out but it will take a bit.

If you think this is the right path, what you could do to help is provide this type of info:
1. I need a stored procedure that takes parameters that look like this:
@NASA_Contract nvarchar(50) = NULL,
@Rev nvarchar(10) = NULL
2. It needs to use tables with these definitions: (just grab them from SSMS)
Create table xyz (
Col1 INT NOT NULL,
etc...
);
3. Sample Data for the tables is extremely helpful.
4. And finally, I need output to look like this:
ColHeader1 ColHeader2 etc
07/28/2016 200.75
07/29/2016 199.99
etc.

hope this helps give you direction,
Chris

Karen Wilson

ASKER

Hi Chris,

Thanks for responding. I am most definitely doing RBAR and using the row immediately above as well. So maybe it should be called TRBAR (two rows by agonizing row).

I am attaching a sample of the crazy table. I use other tables and systematically insert that data into the corresponding field for that week ending. Compiling. I need several table's data to compile into this one in an efficient manner.

Karen
crazySample.xlsx

Jeff Darling

What data is specified or changed by the user in the VB.NET app? This data should be passed as parameters to the SQL Stored Procedure.

I see from your code that there is setup for the dates, starting with a date from a DateTimePicker. So the user specifies the date for the process.

Dim currentWeek As Date = WeekEndingDateTimePicker.Text
                    Dim lastWeek As Date

                    If TblCrazyLandDataGridView.Rows.Count > 0 Then
                        Dim rowIndex As Integer
                        rowIndex = TblCrazyLandDataGridView.CurrentCell.RowIndex

                        If rowIndex = 0 Then
                            lastWeek = DateAdd(DateInterval.Day, -7, currentWeek)
                        Else
                            Dim row As DataGridViewRow = TblCrazyLandDataGridView.Rows(rowIndex - 1)
                            lastWeek = row.Cells(2).Value

                        End If
                    Else
                        'do nothing
                    End If

Open in new window

I also see that you have some drop down boxes that are also parameters.

Me.NASA_ContractComboBox.SelectedItem = tblCd.NASA_Contract
Me.ComboBoxRev.SelectedItem = tblCd.Rev
Me.WeekEndingDateTimePicker.Text = CStr(tblCd.WeekEnding)

Open in new window

Then, the data is populated into a DataGridView and the rows of the grid processed one at a time.

I think you may be able to eliminate the DataGridView Load and just have the stored procedure process the data. This alone will be dramatically faster, even if you process it as RBAR on the server using a cursor. Although I wouldn't recommend using a cursor. This way, all the data does not need to be passed back and forth.

Chris Luttrell

Karen,
Thanks for the info. I will dig into it and your code a little and see what I can come up with. I agree with Jeff that you have some other parameter values that should be passed into the procedure. Can you identify the names and type for those?
Thanks,
Chris

Karen Wilson

ASKER

I explained the parameters I pass and how I set up the SPROC in the beginning.

The parameters I collect during the process are as follows:

'============planned side
Dim planLab As Decimal = 0
Dim planMat As Decimal = 0
Dim planTrav As Decimal = 0
Dim planOther As Decimal = 0
Dim planSubC As Decimal = 0
Dim planURS As Decimal = 0
Dim planURSMat As Decimal = 0
Dim planProv As Decimal = 0
Dim planTotal As Decimal = 0

Dim planRegHrsDIA As Decimal = 0
Dim planOTHrsDIA As Decimal = 0
Dim planSubRegHrsDIA As Decimal = 0
Dim planSubOTHrsDIA As Decimal = 0

Dim planCumLab As Decimal = 0
Dim planCumMat As Decimal = 0
Dim planCumTrav As Decimal = 0
Dim planCumOther As Decimal = 0
Dim planCumSubC As Decimal = 0
Dim planCumURS As Decimal = 0
Dim planCumURSMat As Decimal = 0
Dim planCumProv As Decimal = 0
Dim planCumTotal As Decimal = 0

Dim planCumRegHrs As Decimal = 0
Dim planCumOTHrs As Decimal = 0
Dim planCumSubRegHrs As Decimal = 0
Dim planCumSubOTHrs As Decimal = 0

Dim planCumFYLab As Decimal = 0
Dim planCumFYMat As Decimal = 0
Dim planCumFYTrav As Decimal = 0
Dim planCumFYOther As Decimal = 0
Dim planCumFYSubC As Decimal = 0
Dim planCumFYURS As Decimal = 0
Dim planCumFYURSMat As Decimal = 0
Dim planCumFYProv As Decimal = 0
Dim planCumFYTotal As Decimal = 0

Dim planCumFYRegHrs As Decimal = 0
Dim planCumFYOTHrs As Decimal = 0
Dim planCumFYSubRegHrs As Decimal = 0
Dim planCumFYSubOTHrs As Decimal = 0

'=============spent side
Dim spentLab As Decimal = 0
Dim spentMat As Decimal = 0
Dim spentTrav As Decimal = 0
Dim spentOther As Decimal = 0
Dim spentSubC As Decimal = 0
Dim spentURS As Decimal = 0
Dim spentURSMat As Decimal = 0
Dim spentCarryOver As Decimal = 0
Dim spentTotal As Decimal = 0

Dim spentFringe As Decimal = 0
Dim spentOH As Decimal = 0
Dim spentFee As Decimal = 0
Dim spentGA As Decimal = 0
Dim spentGRT As Decimal = 0

Dim spentCumLab As Decimal = 0
Dim spentCumMat As Decimal = 0
Dim spentCumTrav As Decimal = 0
Dim spentCumOther As Decimal = 0
Dim spentCumSubC As Decimal = 0
Dim spentCumURS As Decimal = 0
Dim spentCumURSMat As Decimal = 0
Dim spentCumCarryOver As Decimal = 0
Dim spentCumTotal As Decimal = 0

Dim spentCumFringe As Decimal = 0
Dim spentCumOH As Decimal = 0
Dim spentCumFee As Decimal = 0
Dim spentCumGA As Decimal = 0
Dim spentCumGRT As Decimal = 0

Dim spentCumFYLab As Decimal = 0
Dim spentCumFYMat As Decimal = 0
Dim spentCumFYTrav As Decimal = 0
Dim spentCumFYOther As Decimal = 0
Dim spentCumFYSubC As Decimal = 0
Dim spentCumFYURS As Decimal = 0
Dim spentCumFYURSMat As Decimal = 0
Dim spentCumFYCarryOver As Decimal = 0
Dim spentCumFYTotal As Decimal = 0

Dim spentCumFYFringe As Decimal = 0
Dim spentCumFYOH As Decimal = 0
Dim spentCumFYFee As Decimal = 0
Dim spentCumFYGA As Decimal = 0
Dim spentCumFYGRT As Decimal = 0

'=====================Work Performed
Dim wkPerfCPI As Decimal = 0
Dim wkPerfSPI As Decimal = 0
Dim Comts As Decimal = 0
Dim cpi As Decimal = 0
Dim costvar As Decimal = 0
Dim spi As Decimal = 0
Dim schedvar As Decimal = 0
Dim percComp As Decimal = 0

'======================Work Scheduled
Dim schedLab As Decimal = 0
Dim schedMat As Decimal = 0
Dim schedTrav As Decimal = 0
Dim schedOther As Decimal = 0
Dim schedSubC As Decimal = 0
Dim schedURS As Decimal = 0
Dim schedURSMat As Decimal = 0
Dim schedTotal As Decimal = 0

Dim schedRegHrs As Decimal = 0
Dim schedOTHrs As Decimal = 0
Dim schedSubRegHrs As Decimal = 0
Dim schedSubOTHrs As Decimal = 0

Dim schedFringe As Decimal = 0
Dim schedOH As Decimal = 0
Dim schedFee As Decimal = 0
Dim schedGA As Decimal = 0
Dim schedGRT As Decimal = 0

Jeff Darling

I think they way to approach this problem is to imagine that you only have SQL Server to process the data and take VB.NET out entirely. With the exception of 1 button to execute the SQL code.

This project is not small, it has quite a number of tables involved and is too much to code here.

Here is a list that I got from the Code.

Karen Wilson

ASKER

That's what I think too but I don't know how since my experience is on the "other" side. And yes, crazyland is compiled from a LOT of tables and views hence why I am having a 20 minute process run.

ASKER CERTIFIED SOLUTION

Karen Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Wilson

ASKER

I never got a useable solution and wanted the question closed.