As far as I can tell from the limited information provided in your question, you don't really need to test for the existence of each and every newly generated ReceiptNum. If you ensure each @recId is unique when generated, you can be certain the combination of @recId and ReceiptNum will be unique. If you are in fact trying to ensure each and every ReceiptNum is unique, regardless of which @recID it's associated with, you can pre-allocate the 1,000,000 receipt numbers each time a @recId is created.
If your system allows duplicate ReceiptNum values for each unique @recId, you could really benefit from creating a clustered index based on ffid and ReceiptNum... Take this example:
USE tempdb;
DROP TABLE IF EXISTS dbo.Receipts;
GO
CREATE TABLE dbo.Receipts
(
RecID int NOT NULL
, RecNum varchar(10) NOT NULL
, SomeData varchar(2000) NOT NULL
DEFAULT (CRYPT_GEN_RANDOM(1000))
, CONSTRAINT PK_Receipts
PRIMARY KEY CLUSTERED
(RecID, RecNum)
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);
GO
The table above will only allow rows to be inserted where the combination of RecID and ReceiptNum are unique, by virtue of the PRIMARY KEY CLUSTERED (RecID, RecNum) clause.
Next, I'll create a procedure to insert 1,000,000 rows into the table. This procedure does not check if each RecID/ReceiptNum already exists in the table since it cannot, by virtue of the table's primary key:
DROP PROCEDURE IF EXISTS dbo.InsertReceiptBlanks;
GO
CREATE PROCEDURE dbo.InsertReceiptBlanks
(
@RecID int
)
AS
BEGIN
;WITH src AS
(
SELECT v.Num
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, MillionRows AS
(
SELECT RecID = @RecID
, RecNum = s1.Num + (s2.Num * 10) + (s3.num * 100) + (s4.num * 1000)
+ (s5.num * 10000) + (s6.Num * 100000)
FROM src s1
CROSS JOIN src s2
CROSS JOIN src s3
CROSS JOIN src s4
CROSS JOIN src s5
CROSS JOIN src s6
)
INSERT INTO dbo.Receipts (RecID, RecNum)
SELECT RecID
, RecNum
FROM MillionRows;
END
GO
For fun, we'll create a stored procedure that does check to see if the RecID/ReceiptNum combination exists, prior to doing the insert.
DROP PROCEDURE IF EXISTS dbo.InsertReceiptBlanks_WithNotExists;
GO
CREATE PROCEDURE dbo.InsertReceiptBlanks_WithNotExists
(
@RecID int
)
AS
BEGIN
;WITH src AS
(
SELECT v.Num
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, MillionRows AS
(
SELECT RecID = @RecID
, RecNum = s1.Num + (s2.Num * 10) + (s3.num * 100) + (s4.num * 1000)
+ (s5.num * 10000) + (s6.Num * 100000)
FROM src s1
CROSS JOIN src s2
CROSS JOIN src s3
CROSS JOIN src s4
CROSS JOIN src s5
CROSS JOIN src s6
)
INSERT INTO dbo.Receipts (RecID, RecNum)
SELECT RecID
, RecNum
FROM MillionRows
WHERE NOT EXISTS (
SELECT 1
FROM dbo.Receipts r
WHERE r.RecID = MillionRows.RecID
AND r.RecNum = MillionRows.RecNum
);
END
GO
Here I'm inserting 5,000,000 rows into the table; each execution of the stored proc takes about 30 seconds total.
EXEC dbo.InsertReceiptBlanks @RecID = 1;
EXEC dbo.InsertReceiptBlanks @RecID = 2;
EXEC dbo.InsertReceiptBlanks @RecID = 3;
EXEC dbo.InsertReceiptBlanks @RecID = 4;
EXEC dbo.InsertReceiptBlanks @RecID = 5;
Here I'm executing the WithNotExists procedure to insert 1,000,000 rows into the table, which takes around 40 seconds on my machine, with the primary key constraint in place.
EXEC dbo.InsertReceiptBlanks_WithNotExists @RecID = 6;
Next we remove the primary key, and re-run the WithNotExists proc:
ALTER TABLE dbo.Receipts
DROP CONSTRAINT PK_Receipts;
The table will now allow us to insert non-unique RecID/ReceiptNum combinations.
EXEC dbo.InsertReceiptBlanks_WithNotExists @RecID = 7;
Running the proc took 1 minute 18 seconds; three times longer than the table with the primary key constraint on RecID/ReceiptNum.
If you need to ensure each and every ReceiptNum is unique, I'd recommend inserts into the table are based upon the RecID value, as in:
DROP PROCEDURE IF EXISTS dbo.InsertReceiptBlanks_UniqueReceiptNum;
GO
CREATE PROCEDURE dbo.InsertReceiptBlanks_UniqueReceiptNum
(
@RecID int
)
AS
BEGIN
;WITH src AS
(
SELECT v.Num
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, MillionRows AS
(
SELECT RecID = @RecID
, RecNum = s1.Num + (s2.Num * 10) + (s3.num * 100) + (s4.num * 1000)
+ (s5.num * 10000) + (s6.Num * 100000)
FROM src s1
CROSS JOIN src s2
CROSS JOIN src s3
CROSS JOIN src s4
CROSS JOIN src s5
CROSS JOIN src s6
)
INSERT INTO dbo.Receipts (RecID, RecNum)
SELECT RecID
, (RecID * 1000000) + RecNum
FROM MillionRows;
END
We'll add a unique index on the ReceiptNum column, and test the proc above:
TRUNCATE TABLE dbo.Receipts;
ALTER TABLE dbo.Receipts ADD CONSTRAINT PK_Receipts PRIMARY KEY CLUSTERED (RecNum);
EXEC dbo.InsertReceiptBlanks_UniqueReceiptNum @RecID = 1;
Since it's the 1,000,000 inserts that are killing performance, you might also be interested in allocating receipt numbers as they are required; I've written a reliable and scalable answer showing how to do that here.
recIdandffidwould seem to be in order as well. Why do you need to check if there's an existing record whereffidmatches your newly-enteredrecIdand the record number is the one you're trying to add?