Convert a complicated Windows Form application process to a SQL Process.

I have a process that I run in a Windows Form application,  I use LINQ to connect to a SQL server database.  The process has over 100 variables. It was running fine until a few weeks ago when we added all the financial data for year 4 in our contract.  Year 5 will probably cause a melt down so I need a better plan of attack.  Currently, I bring in the info, process it using queries in the application and views in SQL, then update the bound record and send it back to SQL.  A full process can take up to 20 minutes now, which is ridiculous yet not if the analyst wants water cooler time.

I think the answer lies in using SQL for the whole thing but I'm not sure where to begin.   Below is parts of the code to help you understand what I'm doing.

If TblCrazyLandDataGridView.Rows.Count > 0 Then

                For Each idRow As DataGridViewRow In TblCrazyLandDataGridView.Rows
                    tblCd = idRow.DataBoundItem

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

                    '============planned side variables===================
                    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

 '========Set up dates
                    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)
                            Dim row As DataGridViewRow = TblCrazyLandDataGridView.Rows(rowIndex - 1)
                            lastWeek = row.Cells(2).Value

                        End If
                        'do nothing
                    End If

                    '===============planned side processing ===========================

                    Dim lwPlans = (From id In d.tblCrazyLandDevels _
                                   Where id.NASA_Contract = CStr(Me.NASA_ContractComboBox.SelectedItem) _
                                   And id.Rev = CStr(Me.ComboBoxRev.SelectedItem) _
                                   And id.WeekEnding = CDate(lastWeek) _
                                   Select id).ToList

                    Dim thisWeekPlans = (From id In d.vw_PlansByOrigBases _
                                   Where id.NASAContract = CStr(Me.NASA_ContractComboBox.SelectedItem) _
                                   And id.Rev = CStr(Me.ComboBoxRev.SelectedItem) _
                                    And id.WeekEnding = CDate(currentWeek) _
                                   Select id).ToList


 If lwPlans.Count > 0 Then

                        If thisWeekPlans.Count > 0 Then

                            planLab = thisWeekPlans.Item(0).Labor
                            planMat = thisWeekPlans.Item(0).Materials
                            planTrav = thisWeekPlans.Item(0).Travel
                            planOther = thisWeekPlans.Item(0).Other
                            planSubC = thisWeekPlans.Item(0).SubC
                            planURS = thisWeekPlans.Item(0).URS
                            planURSMat = thisWeekPlans.Item(0).URSMat
                            planProv = provTotal
                            planTotal = thisWeekPlans.Item(0).WkTotal + provTotal

                            If thisWeekPlans.Item(0).RegHrs Is Nothing Then
                                'do nothing
                                planRegHrsDIA = thisWeekPlans.Item(0).RegHrs
                            End If

                            If thisWeekPlans.Item(0).OTHours Is Nothing Then
                                'do nothing
                                planOTHrsDIA = thisWeekPlans.Item(0).OTHours
                            End If

                            If thisWeekPlans.Item(0).SubRegHrs Then
                                'do nothing
                                planSubRegHrsDIA = thisWeekPlans.Item(0).SubRegHrs
                            End If

                            If thisWeekPlans.Item(0).SubOTHrs Is Nothing Then
                                'do nothing
                                planSubRegHrsDIA = thisWeekPlans.Item(0).SubOTHrs
                            End If

  '========================accept record=======================
                    tblCd.NASA_Contract = CStr(Me.NASA_ContractComboBox.SelectedItem)
                    tblCd.WeekEnding = CDate(Me.WeekEndingDateTimePicker.Text)
                    tblCd.Rev = CStr(Me.ComboBoxRev.SelectedItem)

                    tblCd.planLabor = planLab
                    tblCd.planMat = planMat
                    tblCd.planOther = planOther
                    tblCd.planTravel = planTrav
                    tblCd.planSubContrURS = planURS
                    tblCd.planSubContr = planSubC
                    tblCd.planURSMat = planURSMat
                    tblCd.planProv = planProv
                    tblCd.planTotal = planTotal

                    tblCd.plRegHrs = planRegHrsDIA
                    tblCd.plOTHrs = planOTHrsDIA
                    tblCd.plSubRegHrs = planSubRegHrsDIA
                    tblCd.plSubOThrs = planSubOTHrsDIA

 'update the record

                    Catch ex As Exception

                        MessageBox.Show(ex.Message, ex.GetType.ToString)

                    End Try

And then it goes to the next row........  

Karen WilsonProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ess KayEntrapenuerCommented:
I think you are correct in you assesment of using SQL

You can move this to an SSIS process, and still keep most your code if you wish
Karen WilsonProgrammerAuthor Commented:
I need to finish up a project before I get too involved with this but it looks like I need to download something or is it already available in Visual Studio.  I am using VS2013.
Ess KayEntrapenuerCommented:
Yes, It is a part of visual studio under business intelligence

SSIS in Visual Studio

In short , its a workflow that runs and computes data using sql and vb. It can be run as a scheduled job, so you dont have to go through the hassle of running it yourself
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Karen WilsonProgrammerAuthor Commented:
I am downloading the SSDT package right now.  I found a tutorial website called TechBrotherIT unless you have another suggestion.
Ess KayEntrapenuerCommented:
Its pretty easy once you get the hang of it

Good luck.  let me know if you have any questions.
Karen WilsonProgrammerAuthor Commented:
Is it better to award you the points now or wait until I have all my questions answered?

I would agree about using SSIS to arrange and automate the process, but I think that you hit the nail squarely on the head when you suggested originally that using SQL rather than or C#. If you leave the code as-is you'll just create a new system doing the same thing as your current one, with the same problems.

You have a program that is, structurally:
1. Decide which data rows to get (you didn't show that above)
2. Pull those rows into TblCrazyLandDataGridView
3. For each row in TblCrazyLandDataGridView
3a. Do some processing
3b. Create a list of plans and weekPlans
3c. Do plan processing
3d. Write row back to database

To improve the performance your first instinct was right - don't take it out of the database at all until you have what you want to display. The simple act of not bring the data to and from the database will save a lt of work and time, but you can look closely at the whole loop part and end up doing it with set-based inserts and updates instead of a cursor, and that conversion will yield a lot more gain.

I hesitate to go into actual code because obviously there's a lot you're not posting and the context of the whole thing may make a lot of difference in how you attack the problem. Just what the process is supposed to achieve is also something one needs to consider - there may be a lot going on in the code you're shown that is only there simply because it's written in a procedural language and not in SQL.

hope this helps

Ess KayEntrapenuerCommented:
I agree with mike, regarding the process and steps you should take.  I suggest is you have more specific questions,  you should start a separate thread.

I think ssis would be the best way to go for you but as Mike noted,  without seeing your complete code its difficult to give you a direct guideline.  

We can help you better if you include a workflow overview
Karen WilsonProgrammerAuthor Commented:
I've begun the tutorials on SSIS.  I need to make sure that it will update the data and send it back to the server.

What the process does:

For each task order on a contract, it picks the last revision and feeds the datagridview with all the rows from the table for that particular task order.  The rows are every weekending from the start of the contract, which in this case, runs for 5 years or 260 rows.  Currently, there are 200 task orders or 52,000 rows of data that is going through the process that I am attaching.  

The process is compiling data from several sources of data that at the end of the day, produces a chart for a project manager to review.  I've attached a picture of it for you.

I hope this helps.
Ess KayEntrapenuerCommented:
Where do you get the input variables?
Karen WilsonProgrammerAuthor Commented:
They are added during the process by the use of a view, if I am understanding your question correctly.
Karen WilsonProgrammerAuthor Commented:
Here's the variable:
Dim planLab As Decimal = 0

I run this view:
Dim thisWeekPlans = (From id In d.vw_PlansByOrigBases _
                                   Where id.NASAContract = CStr(Me.NASA_ContractComboBox.SelectedItem) _
                                   And id.Rev = CStr(Me.ComboBoxRev.SelectedItem) _
                                    And id.WeekEnding = CDate(currentWeek) _
                                   Select id).ToList

Then I update the variable:
planLab = thisWeekPlans.Item(0).Labor
Ess KayEntrapenuerCommented:
I noticed you have several datasources such as

Are these located on the same sql server?
Karen WilsonProgrammerAuthor Commented:
Yes.  tblCrazyLandDevels is the table that populates tblCrazylandDatagridview.  

Each contract has task orders.  Each task order has associated charge numbers.  Each charge number has a baseline and a task plan.  Each charge number gets charged with labor, costs and commits (payables).

I compile all these numbers into tblCrazylandDevels with this process.  Crazyland supports the chart screen and many other reports.

It used to be done in Excel so when I took over I said...  WT?, this is a crazy land!  The process is still better than Excel put it could use some speed.
Ess KayEntrapenuerCommented:
If its all in the same server you can handle the entire process using a stored procedure, no need for ssis

Simply create Store Procedure CrazyTableNasaStuffComputation  

Add the variable from text and comboboxes  as @parameters

All the computation should be handled inside the SP. At the end you select the result into a table which gets bound to the datasource in your charts/grids..etc

Since done inside the sql server, which is native to data processing it should go extremely quick.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen WilsonProgrammerAuthor Commented:
Thank you so much for your thorough review of the process.  As I went through the SSIS tutorials, I kept thinking - I don't think this is what I need.  

I've never done stored procedures so it's time I learn.  LINQ, views and queries in the application met my needs.  I will head in that direction.  

Thanks again.  Have a nice weekend.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.