Performance problem?

I'm having performance problems in the following scenario:
I import about 3 million rows into a table with DTS(no indexes because of
the large amount of data). I then run a script with 3 delete and about 25
update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
table and recreate this process(with new data ofcourse). It takes longer and
longer on each iteration. What can i do to improve the performance? Would
dropping and recreating the table or the actual DB help? I've noticed that
the time I save running this script with an index during the import is offset
, since it takes much longer to import that much data with an index. Any
ideas would be greatly appreciated! Thanks!
Also, recovery is set to SIMPLE!
Hi
Why don't you:
Drop the Indexes
Load the Data
Create the Indexes
Run the Update/Deletes
Running deletes and updates against a suitably indexed table is a lot faster.
"mikeb" wrote:

> I'm having performance problems in the following scenario:
> I import about 3 million rows into a table with DTS(no indexes because of
> the large amount of data). I then run a script with 3 delete and about 25
> update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
> table and recreate this process(with new data ofcourse). It takes longer and
> longer on each iteration. What can i do to improve the performance? Would
> dropping and recreating the table or the actual DB help? I've noticed that
> the time I save running this script with an index during the import is offset
> , since it takes much longer to import that much data with an index. Any
> ideas would be greatly appreciated! Thanks!
> Also, recovery is set to SIMPLE!
>

Performance problem?

I'm having performance problems in the following scenario:
I import about 3 million rows into a table with DTS(no indexes because of
the large amount of data). I then run a script with 3 delete and about 25
update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
table and recreate this process(with new data ofcourse). It takes longer and
longer on each iteration. What can i do to improve the performance? Would
dropping and recreating the table or the actual DB help? I've noticed that
the time I save running this script with an index during the import is offset
, since it takes much longer to import that much data with an index. Any
ideas would be greatly appreciated! Thanks!
Also, recovery is set to SIMPLE!Hi
Why don't you:
Drop the Indexes
Load the Data
Create the Indexes
Run the Update/Deletes
Running deletes and updates against a suitably indexed table is a lot faster.
"mikeb" wrote:
> I'm having performance problems in the following scenario:
> I import about 3 million rows into a table with DTS(no indexes because of
> the large amount of data). I then run a script with 3 delete and about 25
> update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
> table and recreate this process(with new data ofcourse). It takes longer and
> longer on each iteration. What can i do to improve the performance? Would
> dropping and recreating the table or the actual DB help? I've noticed that
> the time I save running this script with an index during the import is offset
> , since it takes much longer to import that much data with an index. Any
> ideas would be greatly appreciated! Thanks!
> Also, recovery is set to SIMPLE!
>

Performance Monitors Start/Stop 7AM

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_logman.mspx
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx
--
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>

Performance monitoring

Hi,

I want to collect performance measures regarding the import of data
and the growth of resulting extract_tables.

I use - say - 15 tables from a erp-system (like JDE Edwards)
to build a -say - sales-warehouse and a MS-OLAP-cube.

For every incoming table I got a dts-package witch is
protocolled into msdb.sysdtspackagelog.
Every package got the name
[Build]_[Subsystem]_[Table_name]
e.g. JDEdwards_Sales_F0005
The destination table is namend e.g. extr_F0005

Now:
With a seperate DTS-package I transport the
records from msdb-db into my build-db
- say - JDEdwardsExtract.
Name: extr_performance_monitor
(eventually filter on buildname, because there are several builds in
my system)

So this result is quit good and easy to handle for
seeing elapsed time per day.

But the dtslog won't tell me, how many records the dtspackage
had to copy.(and there is one at least with no records (Cubeupdate))

Now the count(*) comes in.

In the dts-package
sys...log -- to -- extr_performance_monitor
I added the columns
extr_table_name,
extr_table_rowcount,
extr_table_timestamp.

With
select name, 'extr_' + replace(name, '[Build]_[Subsystem]','') as
extr_table_name from extr_performance_monitor
I cut the original dts packagename down to the extr_.. name.

But I don't have a clue how to solve the count(*)

Example:
dts_package_name,... , extr_table_name,extr_table_rowcount, extr_table_timestamp

[Build]_[SS]_[F0005], ..., extr_F0005, 0, sysdate.

I think about a package wich is running after the last
data_import (and cube_refresh) is done.
(but the same day)

So the result could be:

Table_name (as dimension category)
Time to perform
Number of records in import table
Records per second.

The next step could be to look for required space.

The result should be a grafik - say - over 12 month
were you can easily see the amount of data performend
time consumend, (table space used),
and - very important -
you could extrapolate your hardware requirements.

Looking forward for any hints.

Thanks in advance.

MichaelMichael,

How "married" are you to the DTS methodology?

What are the formats of the files?

Are you in the middle of building this, or maintaining it?

If I had a choice, I'd use stored procedures...

I guess you could use ActiveX in DTS

Where are all the DTS mavens?

Stored procedures are just more flexible and faster..

OK a statement like that deserves a big

MOO *

* My own opinion