Does NEWID() affect my insert performance?

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.googlegrou ps.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.googlegrou ps.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.googlegrou ps.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.
>

Does NEWID() affect my insert performance?

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,Field
7)
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,Field
7)
> 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,Field
7)
> 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,Field
7)
> 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.
>

Does NEWID() affect my insert performance?

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.
>

Does DBREINDEX run longer on tables with lower scan density

I am looking into a way to generate and execute DBCC DBREINDEX statements
during scheduled down time. For a particular table - I need to be able to
estimate how long it will take to REINDEX a particular table.
Let say for example I have a table that had 25000 extents and
1) the ScanDensity was 50.
2) the ScanDensity was 15.
Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
ScanDensity of 15 (as opposed to 50)?
Thanks in advance
Tom-- TJTODD wrote: --
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
--
Hi Tom,
I have not found any official documentation on this area. The only way to find out is to do your own empirical benchmarks.
Hope this helps,
-Eric Cárdenas
SQL Server support|||Tom,
The only thing I can think of is that it will obviously take longer time for SQL Server to scan the
source data if the scan density is low. I'd think that in the whole, this would count as marginal,
though.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:OzZlndSuDHA.2304@.tk2msftngp13.phx.gbl...
> I am looking into a way to generate and execute DBCC DBREINDEX statements
> during scheduled down time. For a particular table - I need to be able to
> estimate how long it will take to REINDEX a particular table.
> Let say for example I have a table that had 25000 extents and
> 1) the ScanDensity was 50.
> 2) the ScanDensity was 15.
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
> Thanks in advance
> Tom
>