May 1, 2009 at 11:17 pm
Hi,
I have Asset table ,I need to insert the AssetId into Asset table from AssetUser table
I have mentioned 2 options Can u Plz Guide me towards the Best option
considering the prfomance
Option1
declare @AssetTable(AssetId INT,Id INT IDENTITY(1,1))
INSERT INTO @AssetTable(AssetId)
SELECT AssetId FROM AssetUser WHERE UserId=1
while (@I<=100)
begin
select @AssetId=AssetId from @assetTable where Id=@I
INSERT INTO Asset(AssetId)
SELECT @AssetId
set @i=@I+1
end
Option2
INSERT INTO Asset(AssetId)
SELECT INSERT INTO Asset(AssetId)
Thanks in Advance
May 2, 2009 at 5:11 am
Hi
Your first option is not a good practice since it uses a cursor (while loop) to insert 100 single rows.
Your second option seems to have a little typo, but is the right direction.
If I understood your statement you want to insert all AssetIds for a specified UserId from your AssetUser table. If I'm correct, try this:
INSERT INTO Asset (AssetId)
SELECT AssetId
FROM AssetUser
WHERE UserId = 1
Greets
Flo
May 4, 2009 at 3:52 am
You are already performing th etrick in Option 1:
declare @AssetTable(AssetId INT,Id INT IDENTITY(1,1))
INSERT INTO @AssetTable(AssetId)
SELECT AssetId FROM AssetUser WHERE UserId=1
Why are you inserting the AssetIDs in a temporry table?
Why not directly to the Main table?
May 4, 2009 at 4:36 am
Most of the time cursors/loops performs poorly than set based methods with only some rare cases when loops performs better than a set-based approach.
In your case, one go insert is far better than doing N go insert of single record both performance and IO usage wise.
--Ramesh
May 8, 2009 at 9:16 pm
Thanx Ramesh....
But If the data is huge ,Is there any problem Occurs due to batch Insertion in terms of Network Traffic?
May 8, 2009 at 10:10 pm
SQL commands execute on the SQL Server, so batch Inserts should not have any network traffic issues.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 10:47 pm
Ramesh (5/4/2009)
Most of the time cursors/loops performs poorly than set based methods with only some rare cases when loops performs better than a set-based approach.In your case, one go insert is far better than doing N go insert of single record both performance and IO usage wise.
Hi Ramesh,
Would you provide some examples where a cursor/loop based approach is superior to a set-based approach? I am just curious. I am new and trying to learn.
Regards,
Saurabh.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 9, 2009 at 3:18 am
saurabh.dwivedy (5/8/2009)
Ramesh (5/4/2009)
Most of the time cursors/loops performs poorly than set based methods with only some rare cases when loops performs better than a set-based approach.In your case, one go insert is far better than doing N go insert of single record both performance and IO usage wise.
Hi Ramesh,
Would you provide some examples where a cursor/loop based approach is superior to a set-based approach? I am just curious. I am new and trying to learn.
Regards,
Saurabh.
Here is one of the posts where a cursor/loop based approach is superior..
http://www.sqlservercentral.com/Forums/Topic695508-338-1.aspx
--Ramesh
May 9, 2009 at 3:27 am
Ramesh (5/9/2009)
saurabh.dwivedy (5/8/2009)
Ramesh (5/4/2009)
Most of the time cursors/loops performs poorly than set based methods with only some rare cases when loops performs better than a set-based approach.In your case, one go insert is far better than doing N go insert of single record both performance and IO usage wise.
Hi Ramesh,
Would you provide some examples where a cursor/loop based approach is superior to a set-based approach? I am just curious. I am new and trying to learn.
Regards,
Saurabh.
Here is one of the posts where a cursor/loop based approach is superior..
http://www.sqlservercentral.com/Forums/Topic695508-338-1.aspx
... and (s)he will read the next hours. I just tried the Print-view of the thread and copied all to Word. 251 pages :crazy:
:laugh:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply