stored proc insert,update and cursor

microsoft.public.sqlserver.programming

5 Sep 2006 7:46 PM

Dariusz Tomon

Hello

I'm very suprised to see that following procedure inserts only 1 company
(tb_firma) during 2 seconds.
Below are quantities of rec. in each inserted (or updated) table:

XX_ZRODLO - 1 mln records
TB_FIRMA - 400 000 rec
TB_FIRMA_BRANZA - 490 000
TB_ADRES - 1 400 000
TB_ADRES_FIRMA - 442 357
TB_KONTAKT - 1 900 000

Below is the procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure XX_IMPORT
as

set nocount on
--##### SEKCJA DEKLARACJI

declare @nazwa nvarchar(255)
declare @id int
declare @id_woj int
declare @id_miasto int
declare @ulica nvarchar(255)
declare @telefon nvarchar(255)

declare @id_branza int
declare @www nvarchar(255)
declare @mail nvarchar(255)

--nowo dodane
declare @kod nvarchar(255)
declare @numer_pos nvarchar(255)
declare @kierunkowy nvarchar(255)
declare @tb_firma_identity int
declare @tb_adres_identity int
--koniec

declare @id_firma int
declare @id_adres int

set @nazwa = ''
set @id = 0
set @id_woj = 0
set @id_miasto=0
set @ulica=''
set @telefon=''
set @kierunkowy=''
set @id_branza=0
set @www=''
set @mail=''

--nowo dodane
set @kod = ''
set @numer_pos =''
set @kierunkowy =''
--koniec

-- ####### KONIEC SEKCJI DEKLARACJI

--######## POCZATEK

begin

declare tb cursor for (select
nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy
from xx_zrodlo where

id_portal is null)

open tb
fetch next from tb into
@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@numer_pos,@kierunkowy
while @@fetch_status = 0
begin

INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@nazwa,@id)
--select @@Identity
set @tb_firma_identity = @@Identity
--select @id_firma = max(idTB_FIRMA) FROM TB_FIRMA
UPDATE XX_ZRODLO set id_portal = 1 where id=@id
INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
VALUES (@tb_firma_identity,@id_branza,30000,1)
INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
(@ulica,@numer_pos,@kod,@id_miasto)
--select @@Identity
set @tb_adres_identity = @@Identity
--select @id_adres = max(idTB_ADRES) from TB_ADRES
INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
(@tb_firma_identity,@tb_adres_identity,1)

--przypadek telefonu komórkowego
--IF substring(@telefon,1,1) = 0
IF @kierunkowy = 0
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@telefon,5,@tb_adres_identity,1)
END

--przypadek telefonu stacjonarnego
--IF substring(@telefon,1,1) != 0
IF @kierunkowy <>0 AND @kierunkowy is not null
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@telefon,2,@tb_adres_identity,1)
END

--przypadek adresu www

IF len(@www) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@www,3,@tb_adres_identity,1)
END

--przypadek adresu email

IF len(@mail) > 1
BEGIN
INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
VALUES
(@mail,4,@tb_adres_identity,1)
END

fetch next from tb into
@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@numer_pos,@kierunkowy

END
end

close tb
deallocate tb

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Can anybody suggest me how to change this procedure to boost the process? -
I think it should be in cursor but maybe I'm wrong

Best Regards

Darek

5 Sep 2006 8:01 PM

Adrian Parker

First thing I'd suggest is running the Profiler and watching the
SP:StmtStarting and SP:StmtComplete events to see which statements are slow.

Are you using triggers, and if so, how much work are they doing ?

How many indexes are there on the tables you're inserting into ?

Have you debugged the procedure, sometimes, stepping through it can
hightlight a statement that is taking a long time.

-Adrian

Show quote

nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@numer_pos,@kierunkowy
Show quote

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@numer_pos,@kierunkowy
Show quote

5 Sep 2006 8:55 PM

Dariusz Tomon

I'm using SQL 2000. I run profiler but I cannot see the processes behind my
procedure
?

Show quote

5 Sep 2006 9:09 PM

Adrian Parker

Make sure you're selecting Stored Procedure events and not TSQL events.

Show quote

"Dariusz Tomon" <d.to***@mazars.pl> wrote in message
news:eF0gr2S0GHA.4772@TK2MSFTNGP03.phx.gbl...
| I'm using SQL 2000. I run profiler but I cannot see the processes behind
my
| procedure
| ?
|
| "Adrian Parker" <apparker@nospam.nospam> wrote in message
| news:%23HuKMYS0GHA.2072@TK2MSFTNGP06.phx.gbl...
| > First thing I'd suggest is running the Profiler and watching the
| > SP:StmtStarting and SP:StmtComplete events to see which statements are
| > slow.
| >
| > Are you using triggers, and if so, how much work are they doing ?
| >
| > How many indexes are there on the tables you're inserting into ?
| >
| > Have you debugged the procedure, sometimes, stepping through it can
| > hightlight a statement that is taking a long time.
| >
| > -Adrian
| >
| > "Dariusz Tomon" <d.to***@mazars.pl> wrote in message
| > news:%23i14KQS0GHA.328@TK2MSFTNGP06.phx.gbl...
| > | Hello
| > |
| > | I'm very suprised to see that following procedure inserts only 1
company
| > | (tb_firma) during 2 seconds.
| > | Below are quantities of rec. in each inserted (or updated) table:
| > |
| > | XX_ZRODLO - 1 mln records
| > | TB_FIRMA - 400 000 rec
| > | TB_FIRMA_BRANZA - 490 000
| > | TB_ADRES - 1 400 000
| > | TB_ADRES_FIRMA - 442 357
| > | TB_KONTAKT - 1 900 000
| > |
| > |
| > | Below is the procedure:
| > |
| > |
| > | SET QUOTED_IDENTIFIER ON
| > | GO
| > | SET ANSI_NULLS ON
| > | GO
| > |
| > | ALTER procedure XX_IMPORT
| > | as
| > |
| > | set nocount on
| > | --##### SEKCJA DEKLARACJI
| > |
| > | declare @nazwa nvarchar(255)
| > | declare @id int
| > | declare @id_woj int
| > | declare @id_miasto int
| > | declare @ulica nvarchar(255)
| > | declare @telefon nvarchar(255)
| > |
| > | declare @id_branza int
| > | declare @www nvarchar(255)
| > | declare @mail nvarchar(255)
| > |
| > | --nowo dodane
| > | declare @kod nvarchar(255)
| > | declare @numer_pos nvarchar(255)
| > | declare @kierunkowy nvarchar(255)
| > | declare @tb_firma_identity int
| > | declare @tb_adres_identity int
| > | --koniec
| > |
| > | declare @id_firma int
| > | declare @id_adres int
| > |
| > | set @nazwa = ''
| > | set @id = 0
| > | set @id_woj = 0
| > | set @id_miasto=0
| > | set @ulica=''
| > | set @telefon=''
| > | set @kierunkowy=''
| > | set @id_branza=0
| > | set @www=''
| > | set @mail=''
| > |
| > | --nowo dodane
| > | set @kod = ''
| > | set @numer_pos =''
| > | set @kierunkowy =''
| > | --koniec
| > |
| > | -- ####### KONIEC SEKCJI DEKLARACJI
| > |
| > |
| > | --######## POCZATEK
| > |
| > | begin
| > |
| > |
| > | declare tb cursor for (select
| > |
| >

nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kierunkowy

| > | from xx_zrodlo where
| > |
| > | id_portal is null)
| > |
| > |
| > | open tb
| > | fetch next from tb into
| > |
| >

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@numer_pos,@kierunkowy
Show quote

| > | while @@fetch_status = 0
| > | begin
| > |
| > |
| > |
| > |
| > | INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@nazwa,@id)
| > | --select @@Identity
| > | set @tb_firma_identity = @@Identity
| > | --select @id_firma = max(idTB_FIRMA) FROM TB_FIRMA
| > | UPDATE XX_ZRODLO set id_portal = 1 where id=@id
| > | INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA,
HIERARCHIA,import)
| > | VALUES (@tb_firma_identity,@id_branza,30000,1)
| > | INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
| > | (@ulica,@numer_pos,@kod,@id_miasto)
| > | --select @@Identity
| > | set @tb_adres_identity = @@Identity
| > | --select @id_adres = max(idTB_ADRES) from TB_ADRES
| > | INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
| > | (@tb_firma_identity,@tb_adres_identity,1)
| > |
| > | --przypadek telefonu komórkowego
| > | --IF substring(@telefon,1,1) = 0
| > | IF @kierunkowy = 0
| > | BEGIN
| > | INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT,
idTB_ADRES,import)
| > | VALUES
| > | (@telefon,5,@tb_adres_identity,1)
| > | END
| > |
| > | --przypadek telefonu stacjonarnego
| > | --IF substring(@telefon,1,1) != 0
| > | IF @kierunkowy <>0 AND @kierunkowy is not null
| > | BEGIN
| > | INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT,
idTB_ADRES,import)
| > | VALUES
| > | (@telefon,2,@tb_adres_identity,1)
| > | END
| > |
| > | --przypadek adresu www
| > |
| > | IF len(@www) > 1
| > | BEGIN
| > | INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT,
idTB_ADRES,import)
| > | VALUES
| > | (@www,3,@tb_adres_identity,1)
| > | END
| > |
| > | --przypadek adresu email
| > |
| > | IF len(@mail) > 1
| > | BEGIN
| > | INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT,
idTB_ADRES,import)
| > | VALUES
| > | (@mail,4,@tb_adres_identity,1)
| > | END
| > |
| > |
| > | fetch next from tb into
| > |
| >

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@numer_pos,@kierunkowy
Show quote

| > |
| > | END
| > | end
| > |
| > | close tb
| > | deallocate tb
| > |
| > | GO
| > | SET QUOTED_IDENTIFIER OFF
| > | GO
| > | SET ANSI_NULLS ON
| > | GO
| > |
| > |
| > | Can anybody suggest me how to change this procedure to boost the
| > process? -
| > | I think it should be in cursor but maybe I'm wrong
| > |
| > | Best Regards
| > |
| > | Darek
| > |
| > |
| >
| >
|
|

5 Sep 2006 9:00 PM

Jim Underwood

It seems to me that all of your inserts should be instantaneous, unless
something is blocking them.

The one statement that I think COULD have room for tuning is:
UPDATE XX_ZRODLO set id_portal = 1 where id=@id

If the column [ID] is not the primary key, or at least an indexed column,
this could slow you down. If [ID] is an identity column then it should have
a unique index on it, if not a primary key index.

If [ID] is your primary key then the update should be instantaneous, and you
will want to look at locking. If another process is locking your tables it
might be holding up your execution while SQL server waits for the blocking
process to finish.

The only other thing I can think of is if your cursor is selecting a huge
number of rows, you could end up low on resources and the server will start
to crawl, even on the simplest of tasks. Splitting the processing up into
batches might be faster if that is the case.

The only other suggestion I could make is to change your entire approach,
along with your database design, and don't use identity for your PK and FK.
Then you could use the actual data in your tables as your PK, and you would
be able to do all this with one single statement per table, instead of one
per table row. You may be able to combine some of the data from multiple
tables into single tables if you have one to one relationships, but I don't
know anything about your data, so it's just a thought. At first glance, I
get the impression that some of this might be over normalized, but again, I
do not know your data. This would be a huge amount of work for you, but you
may find out it is worth the effort.

Show quote

"Dariusz Tomon" <d.to***@mazars.pl> wrote in message
news:%23i14KQS0GHA.328@TK2MSFTNGP06.phx.gbl...
> Hello
>
> I'm very suprised to see that following procedure inserts only 1 company
> (tb_firma) during 2 seconds.
> Below are quantities of rec. in each inserted (or updated) table:
>
> XX_ZRODLO - 1 mln records
> TB_FIRMA - 400 000 rec
> TB_FIRMA_BRANZA - 490 000
> TB_ADRES - 1 400 000
> TB_ADRES_FIRMA - 442 357
> TB_KONTAKT - 1 900 000
>
>
> Below is the procedure:
>
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER procedure XX_IMPORT
> as
>
> set nocount on
> --##### SEKCJA DEKLARACJI
>
> declare @nazwa nvarchar(255)
> declare @id int
> declare @id_woj int
> declare @id_miasto int
> declare @ulica nvarchar(255)
> declare @telefon nvarchar(255)
>
> declare @id_branza int
> declare @www nvarchar(255)
> declare @mail nvarchar(255)
>
> --nowo dodane
> declare @kod nvarchar(255)
> declare @numer_pos nvarchar(255)
> declare @kierunkowy nvarchar(255)
> declare @tb_firma_identity int
> declare @tb_adres_identity int
> --koniec
>
> declare @id_firma int
> declare @id_adres int
>
> set @nazwa = ''
> set @id = 0
> set @id_woj = 0
> set @id_miasto=0
> set @ulica=''
> set @telefon=''
> set @kierunkowy=''
> set @id_branza=0
> set @www=''
> set @mail=''
>
> --nowo dodane
> set @kod = ''
> set @numer_pos =''
> set @kierunkowy =''
> --koniec
>
> -- ####### KONIEC SEKCJI DEKLARACJI
>
>
> --######## POCZATEK
>
> begin
>
>
> declare tb cursor for (select
>

nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kie
runkowy

> from xx_zrodlo where
>
> id_portal is null)
>
>
> open tb
> fetch next from tb into
>

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@nu
mer_pos,@kierunkowy
Show quote

> while @@fetch_status = 0
> begin
>
>
>
>
> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@nazwa,@id)
> --select @@Identity
> set @tb_firma_identity = @@Identity
> --select @id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> UPDATE XX_ZRODLO set id_portal = 1 where id=@id
> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
> VALUES (@tb_firma_identity,@id_branza,30000,1)
> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> (@ulica,@numer_pos,@kod,@id_miasto)
> --select @@Identity
> set @tb_adres_identity = @@Identity
> --select @id_adres = max(idTB_ADRES) from TB_ADRES
> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> (@tb_firma_identity,@tb_adres_identity,1)
>
> --przypadek telefonu komórkowego
> --IF substring(@telefon,1,1) = 0
> IF @kierunkowy = 0
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@telefon,5,@tb_adres_identity,1)
> END
>
> --przypadek telefonu stacjonarnego
> --IF substring(@telefon,1,1) != 0
> IF @kierunkowy <>0 AND @kierunkowy is not null
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@telefon,2,@tb_adres_identity,1)
> END
>
> --przypadek adresu www
>
> IF len(@www) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@www,3,@tb_adres_identity,1)
> END
>
> --przypadek adresu email
>
> IF len(@mail) > 1
> BEGIN
> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> VALUES
> (@mail,4,@tb_adres_identity,1)
> END
>
>
> fetch next from tb into
>

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@nu
mer_pos,@kierunkowy
Show quote

>
> END
> end
>
> close tb
> deallocate tb
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> Can anybody suggest me how to change this procedure to boost the
process? -
> I think it should be in cursor but maybe I'm wrong
>
> Best Regards
>
> Darek
>
>

6 Sep 2006 2:14 PM

Dariusz Tomon

Thanks, a lot!

There was not an index in XX_ZRODLO on id. I created index and it boosted my
procedure significantly.

Darek

Show quote

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ORWjH5S0GHA.1300@TK2MSFTNGP05.phx.gbl...
> It seems to me that all of your inserts should be instantaneous, unless
> something is blocking them.
>
> The one statement that I think COULD have room for tuning is:
> UPDATE XX_ZRODLO set id_portal = 1 where id=@id
>
> If the column [ID] is not the primary key, or at least an indexed column,
> this could slow you down. If [ID] is an identity column then it should
> have
> a unique index on it, if not a primary key index.
>
> If [ID] is your primary key then the update should be instantaneous, and
> you
> will want to look at locking. If another process is locking your tables
> it
> might be holding up your execution while SQL server waits for the blocking
> process to finish.
>
> The only other thing I can think of is if your cursor is selecting a huge
> number of rows, you could end up low on resources and the server will
> start
> to crawl, even on the simplest of tasks. Splitting the processing up into
> batches might be faster if that is the case.
>
> The only other suggestion I could make is to change your entire approach,
> along with your database design, and don't use identity for your PK and
> FK.
> Then you could use the actual data in your tables as your PK, and you
> would
> be able to do all this with one single statement per table, instead of one
> per table row. You may be able to combine some of the data from multiple
> tables into single tables if you have one to one relationships, but I
> don't
> know anything about your data, so it's just a thought. At first glance, I
> get the impression that some of this might be over normalized, but again,
> I
> do not know your data. This would be a huge amount of work for you, but
> you
> may find out it is worth the effort.
>
> "Dariusz Tomon" <d.to***@mazars.pl> wrote in message
> news:%23i14KQS0GHA.328@TK2MSFTNGP06.phx.gbl...
>> Hello
>>
>> I'm very suprised to see that following procedure inserts only 1 company
>> (tb_firma) during 2 seconds.
>> Below are quantities of rec. in each inserted (or updated) table:
>>
>> XX_ZRODLO - 1 mln records
>> TB_FIRMA - 400 000 rec
>> TB_FIRMA_BRANZA - 490 000
>> TB_ADRES - 1 400 000
>> TB_ADRES_FIRMA - 442 357
>> TB_KONTAKT - 1 900 000
>>
>>
>> Below is the procedure:
>>
>>
>> SET QUOTED_IDENTIFIER ON
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>> ALTER procedure XX_IMPORT
>> as
>>
>> set nocount on
>> --##### SEKCJA DEKLARACJI
>>
>> declare @nazwa nvarchar(255)
>> declare @id int
>> declare @id_woj int
>> declare @id_miasto int
>> declare @ulica nvarchar(255)
>> declare @telefon nvarchar(255)
>>
>> declare @id_branza int
>> declare @www nvarchar(255)
>> declare @mail nvarchar(255)
>>
>> --nowo dodane
>> declare @kod nvarchar(255)
>> declare @numer_pos nvarchar(255)
>> declare @kierunkowy nvarchar(255)
>> declare @tb_firma_identity int
>> declare @tb_adres_identity int
>> --koniec
>>
>> declare @id_firma int
>> declare @id_adres int
>>
>> set @nazwa = ''
>> set @id = 0
>> set @id_woj = 0
>> set @id_miasto=0
>> set @ulica=''
>> set @telefon=''
>> set @kierunkowy=''
>> set @id_branza=0
>> set @www=''
>> set @mail=''
>>
>> --nowo dodane
>> set @kod = ''
>> set @numer_pos =''
>> set @kierunkowy =''
>> --koniec
>>
>> -- ####### KONIEC SEKCJI DEKLARACJI
>>
>>
>> --######## POCZATEK
>>
>> begin
>>
>>
>> declare tb cursor for (select
>>
> nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kie
> runkowy
>> from xx_zrodlo where
>>
>> id_portal is null)
>>
>>
>> open tb
>> fetch next from tb into
>>
> @nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@nu
> mer_pos,@kierunkowy
>> while @@fetch_status = 0
>> begin
>>
>>
>>
>>
>> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@nazwa,@id)
>> --select @@Identity
>> set @tb_firma_identity = @@Identity
>> --select @id_firma = max(idTB_FIRMA) FROM TB_FIRMA
>> UPDATE XX_ZRODLO set id_portal = 1 where id=@id
>> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA, HIERARCHIA,import)
>> VALUES (@tb_firma_identity,@id_branza,30000,1)
>> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
>> (@ulica,@numer_pos,@kod,@id_miasto)
>> --select @@Identity
>> set @tb_adres_identity = @@Identity
>> --select @id_adres = max(idTB_ADRES) from TB_ADRES
>> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
>> (@tb_firma_identity,@tb_adres_identity,1)
>>
>> --przypadek telefonu komórkowego
>> --IF substring(@telefon,1,1) = 0
>> IF @kierunkowy = 0
>> BEGIN
>> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
>> VALUES
>> (@telefon,5,@tb_adres_identity,1)
>> END
>>
>> --przypadek telefonu stacjonarnego
>> --IF substring(@telefon,1,1) != 0
>> IF @kierunkowy <>0 AND @kierunkowy is not null
>> BEGIN
>> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
>> VALUES
>> (@telefon,2,@tb_adres_identity,1)
>> END
>>
>> --przypadek adresu www
>>
>> IF len(@www) > 1
>> BEGIN
>> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
>> VALUES
>> (@www,3,@tb_adres_identity,1)
>> END
>>
>> --przypadek adresu email
>>
>> IF len(@mail) > 1
>> BEGIN
>> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
>> VALUES
>> (@mail,4,@tb_adres_identity,1)
>> END
>>
>>
>> fetch next from tb into
>>
> @nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@nu
> mer_pos,@kierunkowy
>>
>> END
>> end
>>
>> close tb
>> deallocate tb
>>
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>>
>> Can anybody suggest me how to change this procedure to boost the
> process? -
>> I think it should be in cursor but maybe I'm wrong
>>
>> Best Regards
>>
>> Darek
>>
>>
>
>

6 Sep 2006 3:56 PM

Jim Underwood

Make certain that you have it defined as a unique index or as a primary key
if that is the purpose it serves. Identity does allow for duplicates unless
you specify otherwise. This can happen if you have identity insert on, or
if you reseed your identity value with one that is already used.

Show quote

"Dariusz Tomon" <d.to***@mazars.pl> wrote in message
news:%23PhT%236b0GHA.3752@TK2MSFTNGP02.phx.gbl...
> Thanks, a lot!
>
> There was not an index in XX_ZRODLO on id. I created index and it boosted
my
> procedure significantly.
>
> Darek
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:ORWjH5S0GHA.1300@TK2MSFTNGP05.phx.gbl...
> > It seems to me that all of your inserts should be instantaneous, unless
> > something is blocking them.
> >
> > The one statement that I think COULD have room for tuning is:
> > UPDATE XX_ZRODLO set id_portal = 1 where id=@id
> >
> > If the column [ID] is not the primary key, or at least an indexed
column,
> > this could slow you down. If [ID] is an identity column then it should
> > have
> > a unique index on it, if not a primary key index.
> >
> > If [ID] is your primary key then the update should be instantaneous, and
> > you
> > will want to look at locking. If another process is locking your tables
> > it
> > might be holding up your execution while SQL server waits for the
blocking
> > process to finish.
> >
> > The only other thing I can think of is if your cursor is selecting a
huge
> > number of rows, you could end up low on resources and the server will
> > start
> > to crawl, even on the simplest of tasks. Splitting the processing up
into
> > batches might be faster if that is the case.
> >
> > The only other suggestion I could make is to change your entire
approach,
> > along with your database design, and don't use identity for your PK and
> > FK.
> > Then you could use the actual data in your tables as your PK, and you
> > would
> > be able to do all this with one single statement per table, instead of
one
> > per table row. You may be able to combine some of the data from
multiple
> > tables into single tables if you have one to one relationships, but I
> > don't
> > know anything about your data, so it's just a thought. At first glance,
I
> > get the impression that some of this might be over normalized, but
again,
> > I
> > do not know your data. This would be a huge amount of work for you, but
> > you
> > may find out it is worth the effort.
> >
> > "Dariusz Tomon" <d.to***@mazars.pl> wrote in message
> > news:%23i14KQS0GHA.328@TK2MSFTNGP06.phx.gbl...
> >> Hello
> >>
> >> I'm very suprised to see that following procedure inserts only 1
company
> >> (tb_firma) during 2 seconds.
> >> Below are quantities of rec. in each inserted (or updated) table:
> >>
> >> XX_ZRODLO - 1 mln records
> >> TB_FIRMA - 400 000 rec
> >> TB_FIRMA_BRANZA - 490 000
> >> TB_ADRES - 1 400 000
> >> TB_ADRES_FIRMA - 442 357
> >> TB_KONTAKT - 1 900 000
> >>
> >>
> >> Below is the procedure:
> >>
> >>
> >> SET QUOTED_IDENTIFIER ON
> >> GO
> >> SET ANSI_NULLS ON
> >> GO
> >>
> >> ALTER procedure XX_IMPORT
> >> as
> >>
> >> set nocount on
> >> --##### SEKCJA DEKLARACJI
> >>
> >> declare @nazwa nvarchar(255)
> >> declare @id int
> >> declare @id_woj int
> >> declare @id_miasto int
> >> declare @ulica nvarchar(255)
> >> declare @telefon nvarchar(255)
> >>
> >> declare @id_branza int
> >> declare @www nvarchar(255)
> >> declare @mail nvarchar(255)
> >>
> >> --nowo dodane
> >> declare @kod nvarchar(255)
> >> declare @numer_pos nvarchar(255)
> >> declare @kierunkowy nvarchar(255)
> >> declare @tb_firma_identity int
> >> declare @tb_adres_identity int
> >> --koniec
> >>
> >> declare @id_firma int
> >> declare @id_adres int
> >>
> >> set @nazwa = ''
> >> set @id = 0
> >> set @id_woj = 0
> >> set @id_miasto=0
> >> set @ulica=''
> >> set @telefon=''
> >> set @kierunkowy=''
> >> set @id_branza=0
> >> set @www=''
> >> set @mail=''
> >>
> >> --nowo dodane
> >> set @kod = ''
> >> set @numer_pos =''
> >> set @kierunkowy =''
> >> --koniec
> >>
> >> -- ####### KONIEC SEKCJI DEKLARACJI
> >>
> >>
> >> --######## POCZATEK
> >>
> >> begin
> >>
> >>
> >> declare tb cursor for (select
> >>
> >

nazwa,id,id_woj,id_miasto,ulica,telefon,id_branza,www,mail,kod,numer_pos,kie

> > runkowy
> >> from xx_zrodlo where
> >>
> >> id_portal is null)
> >>
> >>
> >> open tb
> >> fetch next from tb into
> >>
> >

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@nu
Show quote

> > mer_pos,@kierunkowy
> >> while @@fetch_status = 0
> >> begin
> >>
> >>
> >>
> >>
> >> INSERT INTO TB_FIRMA (nazwa_firma,import) VALUES (@nazwa,@id)
> >> --select @@Identity
> >> set @tb_firma_identity = @@Identity
> >> --select @id_firma = max(idTB_FIRMA) FROM TB_FIRMA
> >> UPDATE XX_ZRODLO set id_portal = 1 where id=@id
> >> INSERT INTO TB_FIRMA_BRANZA (idTB_FIRMA, idTB_BRANZA,
HIERARCHIA,import)
> >> VALUES (@tb_firma_identity,@id_branza,30000,1)
> >> INSERT INTO TB_ADRES (ulica,nr_adresowy,kod,idTB_MIEJSOWOSC) VALUES
> >> (@ulica,@numer_pos,@kod,@id_miasto)
> >> --select @@Identity
> >> set @tb_adres_identity = @@Identity
> >> --select @id_adres = max(idTB_ADRES) from TB_ADRES
> >> INSERT INTO TB_ADRES_FIRMA (idTB_FIRMA,idTB_ADRES,import) VALUES
> >> (@tb_firma_identity,@tb_adres_identity,1)
> >>
> >> --przypadek telefonu komórkowego
> >> --IF substring(@telefon,1,1) = 0
> >> IF @kierunkowy = 0
> >> BEGIN
> >> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> >> VALUES
> >> (@telefon,5,@tb_adres_identity,1)
> >> END
> >>
> >> --przypadek telefonu stacjonarnego
> >> --IF substring(@telefon,1,1) != 0
> >> IF @kierunkowy <>0 AND @kierunkowy is not null
> >> BEGIN
> >> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> >> VALUES
> >> (@telefon,2,@tb_adres_identity,1)
> >> END
> >>
> >> --przypadek adresu www
> >>
> >> IF len(@www) > 1
> >> BEGIN
> >> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> >> VALUES
> >> (@www,3,@tb_adres_identity,1)
> >> END
> >>
> >> --przypadek adresu email
> >>
> >> IF len(@mail) > 1
> >> BEGIN
> >> INSERT INTO TB_KONTAKT (WARTOSC, idTB_NAZWA_KONTAKT, idTB_ADRES,import)
> >> VALUES
> >> (@mail,4,@tb_adres_identity,1)
> >> END
> >>
> >>
> >> fetch next from tb into
> >>
> >

@nazwa,@id,@id_woj,@id_miasto,@ulica,@telefon,@id_branza,@www,@mail,@kod,@nu
Show quote

> > mer_pos,@kierunkowy
> >>
> >> END
> >> end
> >>
> >> close tb
> >> deallocate tb
> >>
> >> GO
> >> SET QUOTED_IDENTIFIER OFF
> >> GO
> >> SET ANSI_NULLS ON
> >> GO
> >>
> >>
> >> Can anybody suggest me how to change this procedure to boost the
> > process? -
> >> I think it should be in cursor but maybe I'm wrong
> >>
> >> Best Regards
> >>
> >> Darek
> >>
> >>
> >
> >
>
>