Solved

Need help updating stored procedure to run via vb.net

Posted on 2014-07-18
9
257 Views
Last Modified: 2014-07-22
Hi,
I need to run a stored procedure from  my Windows Application.  I added it to my dataset and when I try to run it it gives me an error because it has temporary tables in it.  I tried using table variables instead but then the stored procedure takes too long to run.  The original procedure runs in 15 seconds where the new one I created runs past 8 minutes.  

How can I update my stored procedure so I can run it from my Windows application without sacrificing performance?  I've posted my original sp code and the new one I created

Original Stored Procedure with Temp tables
BEGIN
	SET NOCOUNT ON

	CREATE TABLE #ClaimCountsByAge
	(
		AgeRange VARCHAR(25)
		,NewClaims INT
		,NewClaimsPaidSum DECIMAL(20,4)
		,FollowUpClaims INT
		,FollowUpClaimsPaidSum DECIMAL(20,4)
		,ClosedClaims INT
		,ClosedClaimsPaidSum DECIMAL(20,4)
	)

	DECLARE @ValueRange TABLE
	(
		AgeRange VARCHAR(20)
		,StartAge INT
		,EndAge INT
		,PRIMARY KEY CLUSTERED (AgeRange, StartAge, EndAge)
	)

	INSERT INTO @ValueRange VALUES ('0-90',	0, 90)
	INSERT INTO @ValueRange VALUES ('91-180', 91, 180)
	INSERT INTO @ValueRange VALUES ('181-270', 181, 270)
	INSERT INTO @ValueRange VALUES ('271-360', 271, 360)
	INSERT INTO @ValueRange VALUES ('361-450', 361, 450)
	INSERT INTO @ValueRange VALUES ('451-540', 451, 540)
	INSERT INTO @ValueRange VALUES ('541-630', 541, 630)
	INSERT INTO @ValueRange VALUES ('631-720', 631, 720)
	INSERT INTO @ValueRange VALUES ('721-810', 721, 810)
	INSERT INTO @ValueRange VALUES ('811-900', 811, 900)
	INSERT INTO @ValueRange VALUES ('901-990', 901, 990)
	INSERT INTO @ValueRange VALUES ('991-1080', 991, 1080)
	INSERT INTO @ValueRange VALUES ('1081-1170', 1081, 1170)
	INSERT INTO @ValueRange VALUES ('1170+', 1171, 10000000)	

	SELECT 
		cl.tarsClaimID
		,cl.ICN
		,cl.TMHPPaid
		,cl.FollowUpUser
		,cl.CompleteUser
		,cl.CompleteDate
	INTO
		#ClaimsInfo
	FROM
		dbo.Claims cl
	WHERE
		( (cl.FollowUpUser IS NULL
			AND cl.CompleteUser IS NULL
			AND cl.CompleteDate IS NULL)
		 OR (cl.FollowUpUser IS NOT NULL
			AND cl.FollowUpUser NOT IN ('hms', 'system'))
		 OR (cl.CompleteUser IS NOT NULL
			AND cl.CompleteUser NOT IN ('hms', 'system')))

	CREATE CLUSTERED INDEX idx_ClaimsInfo__tarsClaimID__ICN ON #ClaimsInfo(tarsClaimID, ICN)
	CREATE INDEX idx_ClaimsInfo__User ON #ClaimsInfo(FollowUpUser, CompleteUser) INCLUDE (TMHPPaid)

	SELECT
		x.ICN
		,(CASE 
			WHEN x.CompleteDate IS NULL THEN DATEDIFF(d, x.LDOS, GETDATE())
			ELSE DATEDIFF(d, x.LDOS, x.CompleteDate)
		END) AS ClaimAge
	INTO
		#ClaimsByAge
	FROM
		(SELECT  
			ci.ICN
			,max(dtl.toDOS) AS LDOS
			,max(ci.CompleteDate) AS CompleteDate
		FROM 
			#ClaimsInfo ci
			INNER JOIN dbo.Detail dtl
				ON ci.tarsClaimID = dtl.tarsClaimID
		GROUP BY
			ci.ICN) x

	CREATE CLUSTERED INDEX idx_ClaimsByAge__ICN__ClaimAge ON #ClaimsByAge(ICN, ClaimAge)

-- New/Remaining Claim COunt
	INSERT INTO #ClaimCountsByAge 
		(
			AgeRange 
			,NewClaims
			,NewClaimsPaidSum 
		)
		SELECT 
			x.AgeRange
			,COUNT(x.ICN)
			,SUM(x.TMHPPaid)
		FROM 
			(SELECT 
				vr.AgeRange
				,ci.ICN
				,max(TMHPPaid) as TMHPPaid
			FROM 
				#ClaimsInfo ci
				INNER JOIN #ClaimsByAge ca 
					ON ca.ICN = ci.ICN
				INNER JOIN @ValueRange vr
					ON ca.ClaimAge BETWEEN vr.StartAge AND vr.EndAge
			WHERE 
				ci.CompleteUser IS NULL 
				AND ci.CompleteDate IS NULL --not complete
				AND ci.FollowUpUser IS NULL -- not currently being worked
				-- no associated payment
				AND NOT EXISTS (SELECT 
									1
								FROM 
									dbo.Finr226c f WITH(NOLOCK) 
								WHERE 
									f.XRef = ci.ICN) 
			GROUP BY 
				vr.AgeRange
				,ci.ICN) x
		GROUP BY
			x.AgeRange

-- Follow-Up Claim COunt
	INSERT INTO #ClaimCountsByAge 
		(
			AgeRange 
			,FollowUpClaims
			,FollowUpClaimsPaidSum 
		)
		SELECT 
			x.AgeRange
			,COUNT(x.ICN)
			,SUM(x.TMHPPaid)
		FROM 
			(SELECT 
				vr.AgeRange
				,ci.ICN
				,max(TMHPPaid) as TMHPPaid
			FROM 
				#ClaimsInfo ci
				INNER JOIN #ClaimsByAge ca 
					ON ca.ICN = ci.ICN
				INNER JOIN @ValueRange vr
					ON ca.ClaimAge BETWEEN vr.StartAge AND vr.EndAge
			WHERE 
				ci.CompleteUser IS NULL 
				AND ci.CompleteDate IS NULL --not complete
				AND ci.FollowUpUser IS NOT NULL 
				AND ci.FollowUpUser NOT IN ('hms', 'system') 
			GROUP BY 
				vr.AgeRange
				,ci.ICN) x
		GROUP BY
			x.AgeRange

-- Closed Claim COunt
	INSERT INTO #ClaimCountsByAge 
		(
			AgeRange 
			,ClosedClaims
			,ClosedClaimsPaidSum 
		)
		SELECT 
			x.AgeRange
			,COUNT(x.ICN)
			,SUM(x.TMHPPaid)
		FROM 
			(SELECT 
				vr.AgeRange
				,ci.ICN
				,max(TMHPPaid) as TMHPPaid
			FROM 
				#ClaimsInfo ci
				INNER JOIN #ClaimsByAge ca 
					ON ca.ICN = ci.ICN
				INNER JOIN @ValueRange vr
					ON ca.ClaimAge BETWEEN vr.StartAge AND vr.EndAge
			WHERE 
				ci.CompleteDate IS NOT NULL 
				AND ci.CompleteUser NOT IN ('hms', 'system') 
			GROUP BY 
				vr.AgeRange
				,ci.ICN) x
		GROUP BY
			x.AgeRange

	SELECT 
		vr.AgeRange
		,vr.StartAge 
		,MAX(cl.NewClaims) AS NewClaimsCount
		,MAX(cl.NewClaimsPaidSum) AS NewClaimsPaidSum
		,MAX(cl.FollowUpClaims) AS FollowUpClaimsCount
		,MAX(cl.FollowUpClaimsPaidSum) AS FollowUpClaimsPaidSum
		,MAX(cl.ClosedClaims) AS ClosedClaimsCount
		,MAX(cl.ClosedClaimsPaidSum) AS ClosedClaimsPaidSum
	FROM 
		@ValueRange vr
		LEFT OUTER JOIN #ClaimCountsByAge cl
			ON vr.AgeRange = cl.AgeRange
	GROUP BY
		vr.AgeRange
		,vr.StartAge 


	DROP TABLE #ClaimsInfo
	DROP TABLE #ClaimsByAge
	DROP TABLE #ClaimCountsByAge

END

Open in new window


New stored procedure using table variables
BEGIN
	SET NOCOUNT ON

	Declare @ClaimCountsByAge table
	(
		AgeRange VARCHAR(25)
		,NewClaims INT
		,NewClaimsPaidSum DECIMAL(20,4)
		,FollowUpClaims INT
		,FollowUpClaimsPaidSum DECIMAL(20,4)
		,ClosedClaims INT
		,ClosedClaimsPaidSum DECIMAL(20,4)
	)

	DECLARE @ValueRange TABLE
	(
		AgeRange VARCHAR(20)
		,StartAge INT
		,EndAge INT
		,PRIMARY KEY CLUSTERED (AgeRange, StartAge, EndAge)
	)

	INSERT INTO @ValueRange VALUES ('0-90',	0, 90)
	INSERT INTO @ValueRange VALUES ('91-180', 91, 180)
	INSERT INTO @ValueRange VALUES ('181-270', 181, 270)
	INSERT INTO @ValueRange VALUES ('271-360', 271, 360)
	INSERT INTO @ValueRange VALUES ('361-450', 361, 450)
	INSERT INTO @ValueRange VALUES ('451-540', 451, 540)
	INSERT INTO @ValueRange VALUES ('541-630', 541, 630)
	INSERT INTO @ValueRange VALUES ('631-720', 631, 720)
	INSERT INTO @ValueRange VALUES ('721-810', 721, 810)
	INSERT INTO @ValueRange VALUES ('811-900', 811, 900)
	INSERT INTO @ValueRange VALUES ('901-990', 901, 990)
	INSERT INTO @ValueRange VALUES ('991-1080', 991, 1080)
	INSERT INTO @ValueRange VALUES ('1081-1170', 1081, 1170)
	INSERT INTO @ValueRange VALUES ('1170+', 1171, 10000000)	

	
	
	Declare @ClaimsInfo table
	(tarsClaimID varchar(14) not null,
	ICN varchar(24),
	TMHPPaid money,
	Followupuser varchar(25),
	CompleteUser varchar(25),
	Completedate datetime,
	Primary key clustered(tarsClaimID, ICN))
	
	
	Insert into @ClaimsInfo
	
	
	SELECT 
		cl.tarsClaimID
		,cl.ICN
		,cl.TMHPPaid
		,cl.FollowUpUser
		,cl.CompleteUser
		,cl.CompleteDate

	FROM
		dbo.Claims cl
	WHERE
		( (cl.FollowUpUser IS NULL
			AND cl.CompleteUser IS NULL
			AND cl.CompleteDate IS NULL)
		 OR (cl.FollowUpUser IS NOT NULL
			AND cl.FollowUpUser NOT IN ('hms', 'system'))
		 OR (cl.CompleteUser IS NOT NULL
			AND cl.CompleteUser NOT IN ('hms', 'system')))



Declare @ClaimsbyAge table
(ICN varchar(24),
Claimage int)


Insert into @ClaimsbyAge
	SELECT
		x.ICN
		,(CASE 
			WHEN x.CompleteDate IS NULL THEN DATEDIFF(d, x.LDOS, GETDATE())
			ELSE DATEDIFF(d, x.LDOS, x.CompleteDate)
		END) AS ClaimAge

	FROM
		(SELECT  
			ci.ICN
			,max(dtl.toDOS) AS LDOS
			,max(ci.CompleteDate) AS CompleteDate
		FROM 
			@ClaimsInfo ci
			INNER JOIN dbo.Detail dtl
				ON ci.tarsClaimID = dtl.tarsClaimID
		GROUP BY
			ci.ICN) x


-- New/Remaining Claim COunt
	INSERT INTO @ClaimCountsByAge 
		(
			AgeRange 
			,NewClaims
			,NewClaimsPaidSum 
		)
		SELECT 
			x.AgeRange
			,COUNT(x.ICN)
			,SUM(x.TMHPPaid)
		FROM 
			(SELECT 
				vr.AgeRange
				,ci.ICN
				,max(TMHPPaid) as TMHPPaid
			FROM 
				@ClaimsInfo ci
				INNER JOIN @ClaimsByAge ca 
					ON ca.ICN = ci.ICN
				INNER JOIN @ValueRange vr
					ON ca.ClaimAge BETWEEN vr.StartAge AND vr.EndAge
			WHERE 
				ci.CompleteUser IS NULL 
				AND ci.CompleteDate IS NULL --not complete
				AND ci.FollowUpUser IS NULL -- not currently being worked
				-- no associated payment
				AND NOT EXISTS (SELECT 
									1
								FROM 
									dbo.Finr226c f WITH(NOLOCK) 
								WHERE 
									f.XRef = ci.ICN) 
			GROUP BY 
				vr.AgeRange
				,ci.ICN) x
		GROUP BY
			x.AgeRange

-- Follow-Up Claim COunt
	INSERT INTO @ClaimCountsByAge 
		(
			AgeRange 
			,FollowUpClaims
			,FollowUpClaimsPaidSum 
		)
		SELECT 
			x.AgeRange
			,COUNT(x.ICN)
			,SUM(x.TMHPPaid)
		FROM 
			(SELECT 
				vr.AgeRange
				,ci.ICN
				,max(TMHPPaid) as TMHPPaid
			FROM 
				@ClaimsInfo ci
				INNER JOIN @ClaimsByAge ca 
					ON ca.ICN = ci.ICN
				INNER JOIN @ValueRange vr
					ON ca.ClaimAge BETWEEN vr.StartAge AND vr.EndAge
			WHERE 
				ci.CompleteUser IS NULL 
				AND ci.CompleteDate IS NULL --not complete
				AND ci.FollowUpUser IS NOT NULL 
				AND ci.FollowUpUser NOT IN ('hms', 'system') 
			GROUP BY 
				vr.AgeRange
				,ci.ICN) x
		GROUP BY
			x.AgeRange

-- Closed Claim COunt
	INSERT INTO @ClaimCountsByAge 
		(
			AgeRange 
			,ClosedClaims
			,ClosedClaimsPaidSum 
		)
		SELECT 
			x.AgeRange
			,COUNT(x.ICN)
			,SUM(x.TMHPPaid)
		FROM 
			(SELECT 
				vr.AgeRange
				,ci.ICN
				,max(TMHPPaid) as TMHPPaid
			FROM 
				@ClaimsInfo ci
				INNER JOIN @ClaimsByAge ca 
					ON ca.ICN = ci.ICN
				INNER JOIN @ValueRange vr
					ON ca.ClaimAge BETWEEN vr.StartAge AND vr.EndAge
			WHERE 
				ci.CompleteDate IS NOT NULL 
				AND ci.CompleteUser NOT IN ('hms', 'system') 
			GROUP BY 
				vr.AgeRange
				,ci.ICN) x
		GROUP BY
			x.AgeRange

	SELECT 
		vr.AgeRange as [Age(Days)]
		,vr.startage		
		,MAX(cl.NewClaims) AS [New Claims]
		,MAX(cl.NewClaimsPaidSum) AS [New Claims Paid Sum]
		,MAX(cl.FollowUpClaims) AS [Follow-Up]
		,MAX(cl.FollowUpClaimsPaidSum) AS [Follow-Up Paid Sum]
		,MAX(cl.ClosedClaims) AS [Closed Count]
		,MAX(cl.ClosedClaimsPaidSum) AS [Closed Paid Sum]
	FROM 
		@ValueRange vr
		LEFT OUTER JOIN @ClaimCountsByAge cl
			ON vr.AgeRange = cl.AgeRange
	GROUP BY
		vr.AgeRange
		,vr.StartAge 



END

Open in new window

0
Comment
Question by:imstac73
  • 5
  • 3
9 Comments
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40205752
You could try creating normal tables. You would just have to make sure they were dropped at the beginning:
	IF OBJECT_ID('Temp_ClaimCountsByAge', 'U') IS NOT NULL
		DROP TABLE dbo.TempClaimCountsByAge
	IF OBJECT_ID('Temp_ValueRange', 'U') IS NOT NULL
		DROP TABLE dbo.Temp_ValueRange
	IF OBJECT_ID('Temp_ClaimsInfo', 'U') IS NOT NULL
		DROP TABLE dbo.Temp_ClaimsInfo

	--	-----------------------------------------------------
	--	-----------------------------------------------------
	CREATE TABLE Temp_ClaimCountsByAge
	(
		AgeRange VARCHAR(25)
		,NewClaims INT
		,NewClaimsPaidSum DECIMAL(20,4)
		,FollowUpClaims INT
		,FollowUpClaimsPaidSum DECIMAL(20,4)
		,ClosedClaims INT
		,ClosedClaimsPaidSum DECIMAL(20,4)
	)
	--	-----------------------------------------------------
	CREATE TABLE Temp_ValueRange
	(
		AgeRange VARCHAR(20)
		,StartAge INT
		,EndAge INT
		,PRIMARY KEY CLUSTERED (AgeRange, StartAge, EndAge)
	)
	--	-----------------------------------------------------
	CREATE TABLE Temp_ClaimsInfo
	(
		tarsClaimID varchar(14) not null,
		ICN varchar(24),
		TMHPPaid money,
		Followupuser varchar(25),
		CompleteUser varchar(25),
		Completedate datetime,
		Primary key clustered(tarsClaimID, ICN)
	) ...

Open in new window

0
 
LVL 79

Expert Comment

by:David Johnson, CD, MVP
ID: 40205761
I added it to my dataset and when I try to run it it gives me an error because it has temporary tables in it.  

Under which account and which security permissions are you running the stored procedure? You may just have to alter your database configuration to run as dbo or sysadmin
0
 

Author Comment

by:imstac73
ID: 40209408
I tried changing them to normal tables. It works fine in Management Studio but when I add it to my dataset in my Windows application it gives me an invalid object name on all tables I am creating in the stored procedure.
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40209589
Sounds like a problem with the credentials on the account that's running the Windows app. If that account doesn't have admin rights on the server, you may need to grant it rights on the new tables:
GRANT ALL ON dbo.Temp_ClaimCountsByAge TO YourWindowsUserAccount;

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:imstac73
ID: 40209880
The Windows application is ran by whoever is logged into it.  So when I do the testing it is my account that is running it.  I am also using the same account in Management Studio.  So I'm not sure why I would get an error via Windows but it works fine with the same account in Mgmt Studio.
0
 

Author Comment

by:imstac73
ID: 40210047
I fixed the invalid name issue but now I am getting a time out expired error.  

The stored procedure takes 1:12 minutes to run.  I have set my connection timeout on my connection string to 1200.  Is this not long enough?
0
 

Author Comment

by:imstac73
ID: 40211625
When I try to preview the data in the dataset it gives me an additional error.

Null Value is eliminated by an aggregate or other SET operation.
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40212366
Try changing "COUNT(x.ICN)" to "COUNT(COALESCE(x.ICN, 0))". It's warning you that a grouping operation is eliminating some NULL values that you might actually want. It's just a warning, not an error, so you can also ignore it if you're getting the expected results.
0
 

Author Comment

by:imstac73
ID: 40212409
I went ahead and just changed how I was pulling the data into the Windows application.  I used the original stored procedure and made the actual table(s) with the data I needed in the application normal tables.  Then in my Windows application I run the stored procedure using sql command and added my data table to the dataset and used the data from the table.  

This actually increased the performance and fixed my error issue.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

28 Experts available now in Live!

Get 1:1 Help Now