March 16, 2006 at 2:48 am
March 16, 2006 at 6:57 am
Can't comment on the performance not knowing what the DTS is doing.
Typically you should be able to utilize stored procedure to improve the performance of your query.
March 16, 2006 at 8:10 am
The DTS get's data from a source table for the last 8 days, each step in the DTS updates this table, but them updates are using sub views to compare the data to either update or not..
What am thinking is that if i do all the wok in a SP and use temp tables instead of views would this help as the DTS take a very long time to run..
Here is a example of the updates.
Update dbo.tbl_metrics_grca_fl
Set cmg_empl_id = dbo.svw_xsap_cmg_empl.empl_id
FROM dbo.tbl_metrics_grca_fl
INNER JOIN dbo.svw_xsap_cmg_empl ON
dbo.tbl_metrics_grca_fl.source_system = dbo.svw_xsap_cmg_empl.source_system AND
dbo.tbl_metrics_grca_fl.unique_id = dbo.svw_xsap_cmg_empl.unique_id
WHERE (dbo.tbl_metrics_grca_fl.source_system = N'xsap') and change_dt >= dbo.udf_date_only(GETDATE())
March 16, 2006 at 10:10 am
There is absolutely no doubt that a set based stored procedure would perform this task better than a procedural DTS package......as it sounds like you have.
DTS has never been used to benefit performance, except in the case of parrallelizing multiple work tasks....since a stored procedure is a single threaded transaction, and DTS does allow multiple threads simaltaneously. This does not sound like the case with what your talking about.
Even in that case, however, I would recommend the DTS running multiple procs, performing set based statements, in a parrallel manner, therby gaining the best of both worlds.
March 16, 2006 at 4:15 pm
It is often (but not always) preferrable to get the data into the target table as a single INSERT, rather than creating the records needed then running multiple UPDATEs.
Reason for this is that often these columns are initialised to NULL in an INSERT. Any subsequent UPDATE (and you have many of them) will not yield an update in place operation. Each update will be implemented under the covers as a delete followed by a re-insert (because replacing a NULL with a value changes the record length which forces a data page re-arrangement that can't be done in place ). This generates a larger than necessary number of txn log writes and page splits.
March 17, 2006 at 8:05 am
If I understand the question you are trying to ask, the answer is: "It depends".
I think you'd like to know if it is faster to re-use the same view 10 times in a sequential set of SQL statements, or is it faster to first create a temporary table and use that table in place of the 10 references to the view.
A few things to consider: If the view is expensive, it tips the balance toward wanting to calculate the result once and re-use the result set over and over. But, since a view is (usually) only an alias for a SQL select statement, it may be that the number of rows that would ever get used at one time is a tiny fraction of the number of rows that you would have to pre-calculate up front. You'll need to look at execution plans to get an idea of what is going on.
Also, if you create a temporary table, you may cause performace problems because that table won't have any indexes on it by default, whereas the execution with the view may be able to take advantage of indexes on the underlying tables. You might need to create indexes on the temporary table after it is created. That operation takes time. Again, you could look at individual execution plans to see which indexes are being used.
There are numerous other things to evaluate, but I think these two are the most important to consider.
The other things mentioned above should also be investigated if performance is a big consideration.
jg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply