August 13, 2015 at 5:19 am
WHILE (@MyLoop3 > 0)
BEGIN
SELECT Top 1 @UploadId = UploadId,@FirstName = (CASE WHEN FirstName = '' THEN @Update ELSE FirstName END),
@LastName = (CASE WHEN LastName = '' THEN @Update ELSE LastName END),
@Claim = (CASE WHEN Claim = '' THEN @Update ELSE Claim END),
@Insurer = (CASE WHEN Insurer = '' THEN @Update ELSE Insurer END),
@InsurerBranch = (CASE WHEN InsurerBranch = '' THEN @Update ELSE InsurerBranch END),
@Employer = (CASE WHEN Employer = '' THEN @Update ELSE Employer END),
@Adjuster = (CASE WHEN Adjuster = '' THEN @Update ELSE Adjuster END),
@InvoiceNumber = InvoiceNumber,@InvoiceAmount = InvoiceAmount,@InvoiceDate = InvoiceDate, @InvoiceSent = InvoiceSent,@BillingWeek = BillingWeek,
@InvoiceDept = InvoiceDept,@PaymentAmount = PaymentAmount,@PaymentReceived = PaymentReceived,@CheckNumber = CheckNumber
FROM global.tblUploadTemps WITH(READPAST,ROWLOCK)
WHERE PendingUploadId = @PendingUploadId
------------INSURER------------
IF EXISTS (SELECT insuranceBilling.InsurerID FROM Liens.dbo.insuranceBilling WITH(READPAST,ROWLOCK)
WHERE insuranceBilling.insurerName=@Insurer)
BEGIN
SET @InsurerId = (SELECT insuranceBilling.InsurerID FROM Liens.dbo.insuranceBilling WITH(READPAST,ROWLOCK)
WHERE insuranceBilling.insurerName = @Insurer)
SET @IsLienInsurerID = 1
END
ELSE
BEGIN
IF EXISTS(SELECT * FROM global.tblInsurers WHERE InsurerName= @Insurer)
BEGIN
SET @InsurerId = (SELECT InsurerId FROM global.tblInsurers WHERE InsurerName= @Insurer)
SET @IsLienInsurerID = 0
END
ELSE
BEGIN
INSERT INTO global.tblInsurers(InsurerName)Values(@Insurer)
SET @InsurerId = (SELECT SCOPE_IDENTITY())
SET @IsLienInsurerID = 0
END
END
---------------------------------
-------INSURER BRANCH-------------
IF EXISTS (SELECT insuranceBranch.InsurerBranchId FROM Liens.dbo.insuranceBranch WITH(READPAST,ROWLOCK)
WHERE (insuranceBranch.InsurerBranchName = @InsurerBranch And insuranceBranch.insurerId = @InsurerId))
BEGIN
SET @InsurerBranchId = (SELECT insuranceBranch.InsurerBranchId FROM Liens.dbo.insuranceBranch WITH(READPAST,ROWLOCK)
WHERE (insuranceBranch.InsurerBranchName = @InsurerBranch And insuranceBranch.insurerId = @InsurerId))
SET @IsLienInsurerBranchID = 1
END
ELSE
BEGIN
IF EXISTS(SELECT * FROM global.tblInsurerBranches WHERE InsurerId = @InsurerId AND InsurerBranchName= @InsurerBranch)
BEGIN
SET @InsurerBranchId = (SELECT InsurerBranchId FROM global.tblInsurerBranches WHERE InsurerId = @InsurerId AND InsurerBranchName= @InsurerBranch)
SET @IsLienInsurerBranchID = 0
END
ELSE
BEGIN
INSERT INTO global.tblInsurerBranches(InsurerId,InsurerBranchName)VALUES(@InsurerId,@InsurerBranch)
SET @InsurerBranchId = (SELECT SCOPE_IDENTITY())
SET @IsLienInsurerBranchID = 0
END
END
---------------------------------
----ADJUSTER----
SET @AdjusterCount = (SELECT count(adjuster.AdjusterId) as totalCOunt FROM Liens.dbo.adjuster WITH(READPAST,ROWLOCK)
WHERE adjuster.AdjusterFirstName =@Adjuster )
IF(@AdjusterCount >0)
BEGIN
SET @AdjusterId = (SELECT adjuster.AdjusterId FROM Liens.dbo.adjuster WITH(READPAST,ROWLOCK)
WHERE adjuster.AdjusterFirstName = @Adjuster)
SET @IsLienAdjusterID = 1
END
ELSE
BEGIN
SET @AdjusterCountIn = (SELECT count(AdjusterId) as totalCOunt FROM global.tblAdjusters WITH(READPAST,ROWLOCK)
WHERE AdjusterFirstName = @Adjuster)
IF(@AdjusterCountIn >0)
BEGIN
SET @AdjusterId = (SELECT AdjusterId FROM global.tblAdjusters WITH(READPAST,ROWLOCK)
WHERE AdjusterFirstName =@Adjuster)
SET @IsLienAdjusterID = 0
END
ELSE
BEGIN
INSERT INTO global.tblAdjusters(AdjusterFirstName,AdjusterLastName,AdjusterPhone)
SELECT @Adjuster AS AdjusterFirstName,' ' AS AdjusterLastName,'00000'
SET @AdjusterId = (SELECT SCOPE_IDENTITY())
SET @IsLienAdjusterID = 0
END
END
--------------------------------
----EMPLOYER----
IF EXISTS(SELECT employer.EmployerId FROM Liens.dbo.employer WITH(READPAST,ROWLOCK)
WHERE (employer.EmployerName = @Employer))
BEGIN
SET @EmployerId = (SELECT employer.EmployerId FROM Liens.dbo.employer WITH(READPAST,ROWLOCK)
WHERE (employer.EmployerName = @Employer))
SET @IsLienEmployerID = 1
END
ELSE
BEGIN
IF EXISTS(SELECT * FROM global.tblEmployers WHERE EmployerName =@Employer)
BEGIN
SET @EmployerId = (SELECT EmployerId FROM global.tblEmployers WHERE EmployerName =@Employer)
SET @IsLienEmployerID = 0
END
ELSE
BEGIN
INSERT INTO global.tblEmployers(EmployerName)Values(@Employer)
SET @EmployerId = (SELECT SCOPE_IDENTITY())
SET @IsLienEmployerID = 0
END
END
---------------------------------
---tblFiles----------
IF EXISTS(SELECT * FROM global.tblFiles WHERE ClaimNumber = @Claim AND IsDeleted = 0)
BEGIN
SET @FILEID = (SELECT FileID FROM global.tblFiles WHERE ClaimNumber = @Claim AND IsDeleted = 0)
END
ELSE
BEGIN
INSERT INTO global.tblFiles (FirstName,LastName,ClaimNumber,InsurerId,InsurerBranchId,EmployerId,AdjusterId,
IsLienClaimNumber,IsLienInsurerID,IsLienInsurerBranchID,IsLienEmployerID,IsLienAdjusterID)
VALUES(@FirstName,@LastName,@Claim,@InsurerId,@InsurerBranchId,@EmployerId,@AdjusterId,
0,@IsLienInsurerID,@IsLienInsurerBranchID,@IsLienEmployerID,@IsLienAdjusterID)
SET @FILEID = (SELECT SCOPE_IDENTITY())
END
-----tblInvoices-------
IF(@InvoiceNumber <> @Update AND @InvoiceAmount <> @Update AND @InvoiceDate <> @Update AND @InvoiceSent <> @Update AND @BillingWeek <> @Update)
BEGIN
SET @_InvoiceDate = (SELECT Convert(date,@InvoiceDate) as InvoiceDate)
SET @_InvoiceSent = (SELECT Convert(date,@InvoiceSent) as InvoiceSent)
SET @_BillingWeek = (SELECT Convert(date,@BillingWeek) as BillingWeek)
END
IF(@Department=1)
BEGIN
SET @InvoiceDept = 1
END
ELSE IF(@Department=2)
BEGIN
SET @InvoiceDept = 2
END
ELSE IF(@Department=3)
BEGIN
SET @InvoiceDept = 3
END
ELSE IF(@Department=4)
BEGIN
SET @InvoiceDept = 4
END
SET @AlreadyExistInvoice = (SELECT Count(tblFiles.FileID) as TotalCount FROM global.tblInvoices INNER JOIN
global.tblFiles ON tblFiles.FileID = tblInvoices.FileId
WHERE tblInvoices.InvoiceNumber = @InvoiceNumber And tblFiles.ClaimNumber = @Claim)
IF (@AlreadyExistInvoice > 0)
BEGIN
SET @INVOICEID = (SELECT tblInvoices.InvoiceID FROM global.tblInvoices INNER JOIN
global.tblFiles ON tblFiles.FileID = tblInvoices.FileId
WHERE tblInvoices.InvoiceNumber = @InvoiceNumber And tblFiles.ClaimNumber = @Claim)
END
ELSE
BEGIN
IF(@InvoiceNumber <> @Update AND @InvoiceAmount <> @Update AND @InvoiceDate <> @Update AND @InvoiceSent <> @Update AND @BillingWeek <> @Update)
BEGIN
INSERT INTO global.tblInvoices (FileId,InvoiceNumber,InvoiceAmt,InvoiceDate,InvoiceSent,BillingWeek,DepartmentId)
VALUES(@FILEID,@InvoiceNumber,@InvoiceAmount,@_InvoiceDate,@_InvoiceSent,@_BillingWeek,@InvoiceDept)
SET @INVOICEID = (SELECT SCOPE_IDENTITY())
END
ELSE
BEGIN
SET @INVOICEID = 0
END
END
-----tblPayments-------
IF(@INVOICEID <> 0 AND @PaymentAmount <> @Update AND @PaymentReceived <> @Update AND @CheckNumber <> @Update)
BEGIN
SET @_PaymentReceived = (SELECT Convert(date,@PaymentReceived) as PaymentReceived)
INSERT INTO global.tblPayments (InvoiceId,PaymentAmount,PaymentReceived,CheckNumber,PendingUploadId)
VALUES (@INVOICEID,@PaymentAmount,@_PaymentReceived,@CheckNumber,@PendingUploadId)
END
---------DELTE THE PROCESSED ENTRY---------------
DELETE FROM global.tblUploadTemps
WHERE global.tblUploadTemps.UploadId =@UploadId
SET @MyLoop3 = @MyLoop3 - 1
END
August 13, 2015 at 5:54 am
The INSERT statements look pretty benign. As for the rest of it, you're doing a lot of duplicate processing. For example:
IF EXISTS(SELECT * FROM global.tblInsurers WHERE InsurerName= @Insurer)
BEGIN
SET @InsurerId = (SELECT InsurerId FROM global.tblInsurers WHERE InsurerName= @Insurer)
Means you read the global.tblInsurers twice. Why not just run the SET query and then check in an IF statement to see if the value loaded. That means you only read the table one time instead of two. That should improve performance on the whole thing right there.
Otherwise, I'd suggest using extended events to identify which of the statements is the most costly and then focus there for performance tuning.
A looping process in general is frequently a poor way to get stuff done within SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2015 at 9:07 am
Just to expand on what Grant said, instead of having this:
IF EXISTS (
SELECT insuranceBilling.InsurerID
FROM Liens.dbo.insuranceBilling WITH (READPAST,ROWLOCK)
WHERE insurerName = @Insurer
)
BEGIN
SET @InsurerId = (
SELECT insuranceBilling.InsurerID
FROM Liens.dbo.insuranceBilling WITH (READPAST,ROWLOCK)
WHERE insuranceBilling.insurerName = @Insurer
)
SET @IsLienInsurerID = 1
END
ELSE
BEGIN
IF EXISTS (
SELECT *
FROM GLOBAL.tblInsurers
WHERE InsurerName = @Insurer
)
BEGIN
SET @InsurerId = (
SELECT InsurerId
FROM GLOBAL.tblInsurers
WHERE InsurerName = @Insurer
)
SET @IsLienInsurerID = 0
END
ELSE
BEGIN
INSERT INTO GLOBAL.tblInsurers (InsurerName)
VALUES (@Insurer)
SET @InsurerId = (
SELECT SCOPE_IDENTITY()
)
SET @IsLienInsurerID = 0
END
END
You could have something like this:
SELECT @InsurerId = NULL,
@IsLienInsurerID = NULL;
SELECT @InsurerId = insuranceBilling.InsurerID,
@IsLienInsurerID = 1
FROM Liens.dbo.insuranceBilling WITH (READPAST,ROWLOCK)
WHERE insuranceBilling.insurerName = @Insurer
IF @InsurerId IS NULL
SELECT @InsurerId = InsurerId,
@IsLienInsurerID = 0
FROM GLOBAL.tblInsurers
WHERE InsurerName = @Insurer;
IF @InsurerId IS NULL
BEGIN
INSERT INTO GLOBAL.tblInsurers (InsurerName)
VALUES (@Insurer);
SELECT @InsurerId = SCOPE_IDENTITY(),
@IsLienInsurerID = 0;
END
There are lots of things that can be improved in here, but it would require some work and time.
Some seem like unnecessary code. This is an example:
--Using this:
IF (@Department = 1)
BEGIN
SET @InvoiceDept = 1
END
ELSE IF (@Department = 2)
BEGIN
SET @InvoiceDept = 2
END
ELSE IF (@Department = 3)
BEGIN
SET @InvoiceDept = 3
END
ELSE IF (@Department = 4)
BEGIN
SET @InvoiceDept = 4
END
--Instead of this
SET @InvoiceDept = @Department
The @Update variable is a complete mistery as it's compared to many different things.
August 13, 2015 at 2:07 pm
Good advice thus far. I didn't take the time to completely analyze your code, but one thing that I have seen help with looping inserts is to use explicit transactions within the loop.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply