Query Performance

Question

Query Performance

kapil_kk

SSC-Insane

Points: 21316
More actions
April 3, 2013 at 7:03 am

#270588

Hi, I have written a script in which I am performing various scenario.. First I will validate if a voucher is already in use or not then if a voucher is not in use then I will insert into a table..after inserting data into table I have to change the status of those voucher in some another table..
Is is correct way to write a script or it will have performance issues later. please suggest
DECLARE @storecode int,
@STNNo varchar(20),
@vochertype int,
@Isbooklet int,
@denomination int,
@quantity int,
@FirstVoucherID varchar(10),
@lastvoucherID varchar(10)
IF EXISTS(SELECT * FROM GV_Voucher gv
JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusId
WHERE gv.VoucherNo = @FirstVoucherID AND gv.VoucherNo = @lastvoucherID
AND gvs.VoucherStatus <> 'NEW')
BEGIN
PRINT 'Voucher No already in use'
END
ELSE
BEGIN
INSERT INTO GV_StoreAllocation (StoreId, VoucherId, STNNo, GRNNo, VoucherStatusId, DispatchDate, ReceivedDate)
VALUES
(
@storecode,
@STNNo,
@FirstVoucherID,
@lastvoucherID,
@quantity
)
END
DECLARE @voucherlist TABLE
(
VoucherNo varchar(10)
)
INSERT INTO @voucherlist
SELECT
gv.VoucherNo
FROM GV_Voucher gv WHERE gv.VoucherNo BETWEEN @FirstVoucherID AND @lastvoucherID
UPDATE GV
SET GV.VoucherStatusId = gvs.VoucherStatusId
FROM GV_Voucher GV
JOIN @voucherlist vc ON vc.VoucherNo = GV.VoucherNo
JOIN GV_VoucherStatus gvs ON gvs.VoucherStatusId = GV.VoucherStatusId
WHERE gvs.VoucherStatus = 'Dispatched'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Use MERGE with an output clause. Dwain Camps has an excellent article on the front page today which describes a potential pratfall issue with MERGE. It's perfectly adequate to get you on the right track and includes some data samples to code against.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden