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
Set nocount on;
where NASA_Contract = @NASA_Contract and Rev = @Rev
order by WeekEnding ASC
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, 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
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.