imstac73
asked on
Need help updating stored procedure to run via vb.net
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
New stored procedure using table variables
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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;
ASKER
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.
ASKER
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?
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?
ASKER
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.
Null Value is eliminated by an aggregate or other SET operation.
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.
ASKER
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.
This actually increased the performance and fixed my error issue.
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