December 9, 2008 at 2:08 am
Hi,
I want to create an index in an UDF.
I've read one article that creates an index with schemabinding on an existing table. That works.
But I want to create the index on a table variable.
I already tried creating a primary key, that works also and is a lot faster, but the data being inserted can contain duplicate keys, and I cannot get the option WITH IGNORE_DUP_KEY working on the table variable.
Can anyone help?
thx
Regards,
Heisa
December 9, 2008 at 2:31 am
Prevent duplicates from being inserted.
_____________
Code for TallyGenerator
December 9, 2008 at 2:42 am
Thx...
I know that is also an option...
But that would mean an extra step, that would make the process slower...
or does the WITH IGNORE_DUP_KEY the same?
I am looking for the best way to optimize the process.
But, what I am trying to do is not possible?
Regards,
Heisa
December 9, 2008 at 2:53 am
Heisa (12/9/2008)
I am looking for the best way to optimize the process.
Change the insert statement so that it doesn't insert dupes.
If you want specific help, post the UDF.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2008 at 3:11 am
Hi,
Here is the UDF,
It is a quite some text... But I don't see an option to attach a file in the post....
There are two table variables @ArtNrs1 and @ArtNrs2 on those tables I would like to place an index (or primary key) on the ID column... That would speed up the process considerably.
The data being read, to select the records looks like this (this is a small recordset):
agaIDagaActielevIDmrkidsclIDargIDartid
3230+335743050019440
3231-335743050114119440
3232-335743050114719440
Thx...
/****** Object: UserDefinedFunction [dbo].[fnGet_AankomstenArtikelGroep] Script Date: 12/09/2008 10:56:56 ******/
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnGet_AankomstenArtikelGroep](@argid1 int, @stcid int,
@begindatum datetime, @einddatum datetime)
RETURNS @artnrs TABLE (id int,
LeverancierNr bigint,
ArtNr varchar(50),
aantal int,
OGWaarde float,
Vestiging tinyint,
Soort varchar(2),
Datum datetime,
argid int,
argomschrijving varchar(50))
AS
BEGIN
DECLARE @intLevIDINT
DECLARE@intSclIDINT
DECLARE@intMrkIDINT
DECLARE@intArtIDINT
DECLARE @agaIDINT
DECLARE @strAgaActieVARCHAR(5)
DECLARE @argid INT
DECLARE @argomschrijving varchar(50)
--tijdelijke tabellen
DECLARE @ArtNrs1 table (id int ,
LeverancierNr bigint,
ArtNr varchar(50),
aantal int,
OGWaarde float,
Vestiging tinyint,
Soort varchar(2),
Datum datetime,
argid int,
argomschrijving varchar(50))
DECLARE @ArtNrs2 table (id int,
LeverancierNr bigint,
ArtNr varchar(50),
aantal int,
OGWaarde float,
Vestiging tinyint,
Soort varchar(2),
Datum datetime,
argid int,
argomschrijving varchar(50))
declare @OnderGrens int
declare @BovenGrens int
select @OnderGrens = CAST((dbo.fnGetSysteemgegeven ('SYSTEEM' ,'INTLEVOND')) as int)
select @BovenGrens = CAST((dbo.fnGetSysteemgegeven ('SYSTEEM' ,'INTLEVBOV')) as int)
-- HET ARTIKEL NUMMER IN DE ARTIKEL TABEL IS EEN VARCHAR. WE DOEN EEN IMPLICIETE CAST NAAR EEN INT.
-- DIT LEVERT EEN ENORME SNELHEIDSWINST OP, OMDAT HET ARTIKEL NUMMER IN DE AANKOMSTEN TABEL OOK EEN INT IS.
-- DIT ALLES GAAT GOED, OMDAT ER TEGENWOORDIG ALLEEN NUMERIEKE ARTIKELNUMMERS AANGELEVERD WORDEN DOOR ZEGRIS.
IF @argId1 <> 0
BEGIN
DECLARECUR_AGA
CURSOR FOR
SELECTaga.agaID, aga.argid, arg.argomschrijving
FROMtblArtikelGroepen arg
LEFT JOIN tblargArtikelen aga
ON arg.argID = aga.argID
ANDarg.argID = @argid1
where not aga.agaID is null --and arg.StcID = @StcID
OPENCUR_AGA
END
ELSE
BEGIN
DECLARECUR_AGA
CURSOR FOR
SELECTaga.agaID, aga.argid, arg.argomschrijving
FROMtblArtikelGroepen arg
LEFT JOIN tblargArtikelen aga
ON arg.argID = aga.argID
WHEREarg.StcID = @StcID andnot aga.agaID is null
--and arg.argomschrijving in ('A&S', 'A&S 2007 incl Office')
OPENCUR_AGA
END
FETCH NEXT FROM CUR_AGA INTO @agaID, @argid, @argomschrijving
-- Voor ieder record uitzoeken welke artikelen er wel en niet bij horen
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT@intLevID=isnull((select leverancier_nr from vwleverancier where leverancier_id=levID),0),
@intSclID=isnull((select sclCode from tblStClass CLS where CLS.sclID = AGA.sclID), 0),
@intMrkID=mrkID,
@intArtID= isnull((select artikel_nr_oc from vwartikel where artikel_id=artID), 0),
@strAgaActie=agaActie
FROMtblargartikelen AGA
WHEREagaID = @agaID
IF @strAgaActie = '+'
BEGIN
IF @intArtID <> 0
BEGIN
--artikelnummer
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanArtikelNr = @intArtid
AND aanDatumAankomst between @begindatum and @einddatum
END
ELSE
BEGIN
IF @intLevID <> 0 AND @intSclID = 0 AND @intMrkID = 0
BEGIN
--leverancier
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID = 0 AND @intSclID = 0 AND @intMrkID <> 0
BEGIN
--merk
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanMerk = @intMrkID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID = 0 AND @intSclID <> 0 AND @intMrkID = 0
BEGIN
--staples class
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanClass = @intsclID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID <> 0 AND @intSclID <> 0 AND @intMrkID <> 0
BEGIN
--leverancier,merk,staples class
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
ANDaanMerk = @intMrkID
ANDaanClass = @intsclID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID = 0 AND @intSclID <> 0 AND @intMrkID <> 0
BEGIN
--merk,staples class
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHEREaanClass = @intsclID
ANDaanMerk = @intMrkID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID <> 0 AND @intSclID <> 0 AND @intMrkID = 0
BEGIN
--leverancier,staples class
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
ANDaanClass = @intsclID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID <> 0 AND @intSclID = 0 AND @intMrkID <> 0
BEGIN
--leverancier,merk
INSERT INTO @ArtNrs1 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
ANDaanMerk = @intMrkID
AND aanDatumAankomst between @begindatum and @einddatum
END
END
END
ELSE
BEGIN
--artikelnummer
IF @intArtID <> 0
BEGIN
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanArtikelNr = @intArtid
AND aanDatumAankomst between @begindatum and @einddatum
END
ELSE
BEGIN
IF @intLevID <> 0 AND @intSclID = 0 AND @intMrkID = 0
BEGIN
--leverancier
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID = 0 AND @intSclID = 0 AND @intMrkID <> 0
BEGIN
--merk
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanMerk = @intMrkID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID = 0 AND @intSclID <> 0 AND @intMrkID = 0
BEGIN
--staples class
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanClass = @intsclID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID <> 0 AND @intSclID <> 0 AND @intMrkID <> 0
BEGIN
--leverancier,merk,staples class
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
ANDaanMerk = @intMrkID
ANDaanClass = @intsclID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID = 0 AND @intSclID <> 0 AND @intMrkID <> 0
BEGIN
--merk,staples class
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHEREaanClass = @intsclID
ANDaanMerk = @intMrkID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID <> 0 AND @intSclID <> 0 AND @intMrkID = 0
BEGIN
--leverancier,staples class
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
ANDaanClass = @intsclID
AND aanDatumAankomst between @begindatum and @einddatum
END
IF @intLevID <> 0 AND @intSclID = 0 AND @intMrkID <> 0
BEGIN
--leverancier,merk
INSERT INTO @ArtNrs2 (id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, Datum, argid, argomschrijving)
SELECT aanid, aanLeverancierNr, aanArtikelNr, aanAantalArtikelen,
aanOGWaarde, aanVesNummer, aanSoort, aanDatumAankomst, @argid, @argomschrijving
FROM tblAankomsten
WHERE aanLeverancierNr=@intLevID
ANDaanMerk = @intMrkID
AND aanDatumAankomst between @begindatum and @einddatum
END
END
END
FETCH NEXT FROM CUR_AGA INTO @agaID, @argid, @argomschrijving
END
-- Hier wel even de eventuele niet-numerieke
-- waarden uit de artikel tabel uitsluiten!
INSERT @ArtNrs
SELECT id, leveranciernr, artnr, aantal, OGWaarde, vestiging, soort, datum, argid, argomschrijving
FROM @artnrs1
WHERE NOT id IN (SELECT id from @artnrs2)
AND isnumeric(ArtNr) = 1
AND NOT ArtNr IS NULL
AND leveranciernr not between @OnderGrens and @BovenGrens
CLOSE CUR_AGA
DEALLOCATE CUR_AGA
RETURN
END
December 9, 2008 at 3:12 am
Heisa (12/9/2008)
But that would mean an extra step, that would make the process slower...
1 write operation (actual insert of a duplicate) equals 4-5 read operations (checking if the same key exists).
Your "extra step" must be implemented pretty terrible way to be slower than pumping temp table with useless data.
_____________
Code for TallyGenerator
December 9, 2008 at 3:20 am
Ok didn't realize that...
December 9, 2008 at 3:33 am
Heisa (12/9/2008)
Hi,Here is the UDF,
It is a quite some text... But I don't see an option to attach a file in the post....
Right below the textbox where you type the post is a section Post Options. There's an attachment button at the bottom
There are two table variables @ArtNrs1 and @ArtNrs2 on those tables I would like to place an index (or primary key) on the ID column... That would speed up the process considerably.
I can think of a lot of things that will speed this up a lot, an index on the table vars is not one of them.
What is this actually doing? (I know there are comments, but I can't read dutch easily)
Why do you need a cursor?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2008 at 3:50 am
Hi,
I am creating a cursor to read these records one by one:
agaID agaActie levID mrkid sclID argID artid
3230 + 33574305 0 0 1944 0
3231 - 33574305 0 1141 1944 0
3232 - 33574305 0 1147 1944 0
and use the record as a parameter to select records from another table (tblAankomsten) which contains articles (actually arrivals of articles in our shops). When the agaActie of the record is a "+" the records selected from tblaankomsten are added to @ArtNrs1, when it contains a "-" they are added to @ArtNrs2.
Then the records in @Artnrs2 are excluded from @Artnrs1 and returned as a table.
Users "build" these, so called ArticleGroups, to calculate the arrival worth.
The table tblAankomsten contains about 1.2 million records.
When I make the ID in the vars the primary key, the exclusion is a lot faster. I tested it for a particular "Articlegroup" and it processed in 7 seconds in stead of 11 minutes...
Hope this makes it a bit clearer?
Regards,
Heisa
December 9, 2008 at 4:20 am
Can you post definition of the tblAankomsten table (along with any indexes), some sample data (as insert statements) and your expected results? I'm pretty sure this doesn't need a cursor.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2008 at 5:02 am
Hi,
Thx!
I've added the definition of the table tblaankomsten and a file with the inserts.
This is based on this selection recordset:
agaID agaActie levID mrkid sclID argID artid
3230 + 33574305 0 0 1944 0
3231 - 33574305 0 1141 1944 0
3232 - 33574305 0 1147 1944 0
3233 - 33574305 100 0 1944 0
3234 - 33574305 0 0 1944 5
3235 - 33574305 110 0 1944 6
3236 - 33574305 120 1142 1944 7
3237 - 33574305 130 0 1944 0
From the inserts I would expect only the first two inserts returned...
Regards,
Heisa
December 12, 2008 at 4:17 am
Hi Gail,
Did you have a chance to look at the UDF?
I already altered the UDF to check for duplicate keys, so the (damn) thing works now....
But I was still wondering if it was possible to execute the process without the use of a cursor?
I understand if you don't have the time or if it's just too much work... 🙂
But I was just curious....
thx...
Regards,
Heisa
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply