I have a SQL script, which is run during installation, as I want a
particular table to be populated initially. For this particular table I
have around 4000 recs that need to be populated. The script first
deletes all the rows in the table by the following statement:
Delete from [dbo].[Table1]
GO
Then there are 4000 following insert statement is as follows:
INSERT INTO [dbo].[Table1]
(Field1, Field2,Field3,Field4,Field5,Field6,Field7)
VALUES
(NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
GO
Since I have started using this script, my installer takes considerably
more time ....I cannot do a bulk insert using CSV because I am using
NEWID().
Is there a workaround?
Thanks."Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
>
The problem with using NEWID() like this is that the values are very
unordered, and so the index inserts get scattered all over. SQL Server 2005
introduces NEWSEQUENTIALID() to address this problem.
http://msdn2.microsoft.com/en-us/library/ms189786.aspx
But 4000 isn't a ton of rows in any case, and should take less than 10
seconds either way.
David|||Thanks David, But I am using SQL 2000. Does anyone know, a way to do
this without changing the schema of the table?
Thanks|||I agree with David that there should be no noticeable difference using
NEWID() with only 4000 rows. You mentioned that your installer takes
considerably longer but are you certain that it is this script that is the
cause?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>|||You're change is probably because you are doing single inserts instead of
bulk insert. You might see if doing a truncate instead of delete buys you
enough to get your performance back.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>
