Solved

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

Posted on 2016-07-20
16
49 Views
Last Modified: 2016-07-22
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)
                        Else
                            Dim row As DataGridViewRow = TblCrazyLandDataGridView.Rows(rowIndex - 1)
                            lastWeek = row.Cells(2).Value

                        End If
                    Else
                        '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

========THEN IF STATEMENT H-E-DOUBLE TOOTHPICKS STARTS======================

 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
                            Else
                                planRegHrsDIA = thisWeekPlans.Item(0).RegHrs
                            End If

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

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

                            If thisWeekPlans.Item(0).SubOTHrs Is Nothing Then
                                'do nothing
                            Else
                                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
                    Try
                        d.SubmitChanges()

                    Catch ex As Exception

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

                    End Try

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


Help!
0
Comment
Question by:Karen Wilson
  • 8
  • 7
16 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 41723254
I think you are correct in you assesment of using SQL

You can move this to an SSIS process, and still keep most your vb.net code if you wish
0
 

Author Comment

by:Karen Wilson
ID: 41723314
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.
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 41723764
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
0
 

Author Comment

by:Karen Wilson
ID: 41723770
I am downloading the SSDT package right now.  I found a tutorial website called TechBrotherIT unless you have another suggestion.
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 41723791
Its pretty easy once you get the hang of it


Good luck.  let me know if you have any questions.
0
 

Author Comment

by:Karen Wilson
ID: 41723793
Is it better to award you the points now or wait until I have all my questions answered?
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 41723970
Karen,

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 vb.net 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

Mike
1
 
LVL 15

Expert Comment

by:Ess Kay
ID: 41724964
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Karen Wilson
ID: 41724992
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.
getFY13.docx
processPic.png
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 41725038
Where do you get the input variables?
0
 

Author Comment

by:Karen Wilson
ID: 41725047
They are added during the process by the use of a view, if I am understanding your question correctly.
0
 

Author Comment

by:Karen Wilson
ID: 41725054
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
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 41725228
I noticed you have several datasources such as
TblCrazyLandDataGridView
d.tblTaskPlanBaseLines
d.tblCrazyLandDevels



Are these located on the same sql server?
0
 

Author Comment

by:Karen Wilson
ID: 41725232
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.
0
 
LVL 15

Accepted Solution

by:
Ess Kay earned 500 total points
ID: 41725313
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.
0
 

Author Closing Comment

by:Karen Wilson
ID: 41725318
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now