When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Showing posts with label million. Show all posts
Showing posts with label million. Show all posts
Sunday, February 26, 2012
DTS - 5 million row load with indexes?
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
DTS - 5 million row load with indexes?
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
The table that you are loading into - is that an empty table?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegr oups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegr oups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.
|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
The table that you are loading into - is that an empty table?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegr oups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegr oups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.
|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com
DTS
I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do'Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
Is this the right exe file to use?|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:
> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
> Is this the right exe file to use?
>|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:
> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do'Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
Is this the right exe file to use?|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:
> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
> Is this the right exe file to use?
>|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:
> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>
Subscribe to:
Posts (Atom)