August 12, 2014 at 6:32 am
Its strange really because whether its runs on SSMS or SSIS, its will operate on the SQL SERVER level. so time should not be the different. if the query you mentioned above is causing the performance issue this should definitely have performance issue when you run it SSMS as well. Cursor do not seems to be a big issue as you are using is to generate dynamic sql.
if you really want to use the SSIS, then implement the logic inside SSIS. But 1st let Adjust your query from the cursors
SELECT ts.object_id,
'Delete From ' + ts.name + ' Where ' + x.colQuery AS DeleteQuery
FROM sys.tables ts
CROSS APPLY
(
select STUFF((SELECT 'AND ' + QuoteName(c.name) + ' is null '
FROM sys.all_columns c
WHERE c.object_id = ts.object_id
AND c.name != 'ID'
AND c.name != 'INDEX0'
AND (c.name NOT LIKE 'MAS%' AND c.name NOT LIKE 'Z%')
For xml path ('')
),1,3,'') AS colquery
) X
WHERE ts.object_id IN (
SELECT ac.object_id
FROM sys.all_columns ac
WHERE ac.name = 'INDEX0'
)
ORDER BY ts.name
2nd part, in which you delete from the table by using While loop (Row by row). not good its will take ages to finish. to avoid this
delete data in chunks. so if merge both logic the final query will look like this
Declare @vTop as int = 25000----------- Need to determine which value will perform better
Declare @1stQuery as varchar(500) = ''
Declare @LastQuery as varchar(500) = ''
select
@1stQuery =
' DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP ('+ CAST(@vTop as varchar(10)) +')
FROM '------------- Divided the query in parts
, @LastQuery =
' SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
-- CHECKPOINT; -- if Recovert Model simple
-- BACKUP LOG ... -- if Recovert Model full
END'
SELECT ts.object_id,
@1stQuery + ts.name + ' Where ' + x.colQuery + Char(10) + @LastQuery AS DeleteQuery
FROM sys.tables ts
CROSS APPLY
(
SELECT STUFF((SELECT 'AND ' + QuoteName(c.name) + ' is null '
FROM sys.all_columns c
WHERE c.object_id = ts.object_id
AND c.name != 'ID'
AND c.name != 'INDEX0'
AND (c.name NOT LIKE 'MAS%' AND c.name NOT LIKE 'Z%')
For xml path ('')
),1,3,'') AS colquery
) X
WHERE ts.object_id IN (
SELECT ac.object_id
FROM sys.all_columns ac
WHERE ac.name = 'INDEX0'
)
ORDER BY ts.name
its your choice now whether you want to run in a complete batch or you want to run that query one by one. you can do it in SSIS easy.
August 12, 2014 at 6:49 am
Hi
Thanks so much, I will start testing it and see what it does exactly, and how it fits on our tables. I will get back asap to let you know, as I said previously it may be a few days though.
Andre
August 12, 2014 at 6:55 am
Take your time. if you need any query do lets us know.
August 27, 2014 at 4:06 am
I have had a look at these and nothing really solved the issues in SSIS, I think I need to build this into SSIS, just not sure exactly what to use, I have downloaded some SSIS books but all the samples they work with are for importing multiple files into SQL. Do you have some links to videos or guides that will help with this specific case. It looks like I need to use The For each loop, a lot has changed from SQL 2008 where I last used SSIS, and it looks like there is a lot of really great things to use that will help me, I just need the basic logic that I should be using.
August 27, 2014 at 4:28 am
I have had a look at these and nothing really solved the issues in SSIS, I think I need to build this into SSIS, just not sure exactly what to use, I have downloaded some SSIS books but all the samples they work with are for importing multiple files into SQL. Do you have some links to videos or guides that will help with this specific case. It looks like I need to use The For each loop, a lot has changed from SQL 2008 where I last used SSIS, and it looks like there is a lot of really great things to use that will help me, I just need the basic logic that I should be using.
What exactly the issue you are facing write now? If you want to delete the data in bulk with performance then you SHOULD BE doing it on SQL SERVER even if you are using SSIS (e.g. Execute SQL Task). The other option is the row by row deletion from SSIS same like cursor.
Help me understand what issue you are facing against the solution shared earlier, Share some details?
Regarding SSIS basic understand you can look into the following:
Stairway to Integration Services Andy Leonard [/url]
that will get you on track of many things.
August 27, 2014 at 4:58 am
In brief here is what I have
A variable number of tables, each of them have field names ID and Index0, they each also have 1-3 data fields. I need to delete rows from each of these tables where the data fields are all nulls.
The issues is:
1- the tables have up to a billion rows or even more of which I would say on average 60% need to get deleted, so we need to do this in batches or using a cursor (which we currently use), but the cursor is 4 times slower when running through SSIS that running it straight through SQL.
I would prefer building this into SSIS somehow as all the other steps of the ETL process runs perfectly through SSIS
In our dynamic scriptwe get a list of the tables that have those field names and the second part then gets the actual field names, the cursor then uses this in combination with a search of the field values for the data columns to delete the rows.
In effect Delete from Table A where Datafield1 is null and Datafield2 is null - the issue here is that the table may have one, two or three data fields.
I hope this makes sense.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply