| Author |
Topic |
|
abarsami
Yak Posting Veteran
68 Posts |
 Posted - 2004-02-06 : 15:16:51
|
| I have about 30 of these statements and I try to execute them together but it takes sorta long time. The tables have client_id in common.Is there anything I can do to make it faster? Should I use select @count() first to see if there is anything to delete from each one? Then if it not blank go ahead with statement.Or would this add to the time?Delete aafrom aa x join client c on (c.client_id=x.client_id) where c.client_delete_yn = 1Delete bbfrom bb x join client c on (c.client_id=x.client_id) where c.client_delete_yn = 1Delete cc from cc x join client c on (c.client_id=x.client_id) where c.client_delete_yn = 1 |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-02-06 : 15:25:27
|
| Would it make sense to add a flag to each table? A tinyint column called Obsolete.Is it faster to update the tinyint field than to delete the record?Might be more work initially, but could save time when you run your "delete" script.Edited to add: Of course any select statements or joins to those tables would now have to check the Obsolete flag and filter based on it's value.Aj |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-06 : 15:26:30
|
| Your queries are already optimized. You just probably don't have indexes where they need to be.Do you have an index on client_delete_yn? If you don't, add one. Do you have an index on client_id? If you don't, add it in each of the tables.Tara |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2004-02-06 : 15:31:32
|
| No I have to delete the record.Would using temp tables be faster?So I would say select aa.id into #temp1from aa join client c on (c.client_id=x.client_id) where c.client_delete_yn = 1delete aa where aa.id in (select aa.id from #temp1)drop #temp1etc.. for all tables |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-06 : 15:33:40
|
| Your queries are already optimized. What indexes do you have on those tables?Temp tables are going to slow it down even more.Tara |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2004-02-06 : 15:38:05
|
| I have index on client_id in client table.I have index on aa_id on aa table.I have index on bb_id on bb table.etc...Do I have to change client_delete_yn from a bit to int to have a index on it?Also do I have to add an index on client_id for aa, bb, cc tables. |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2004-02-06 : 15:38:43
|
| those are actually primary keys. |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-02-06 : 15:38:54
|
| So Tara, in general is a script that deletes records going to run faster than a script that updates records?Aj |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-06 : 15:39:59
|
| Just add the index to client_delete_yn, do not change the data type. Yes you need an index on client_id in the child tables. This is due to the join. You should index the columns involved in the join conditions.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-06 : 15:41:40
|
quote:
Originally posted by ajthepoolman
So Tara, in general is a script that deletes records going to run faster than a script that updates records?Aj
It would depend on the query and the design. I am offering a solution that doesn't involve changing the design. It just involves having the appropriate indexes. It's a rather simple delete, so no reason to change the design.Tara |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2004-02-06 : 15:45:07
|
| So I would have something like this?create index ind_client_deleted_yn on Client (client_deleted_yn)create index ind_aa_client_id on aa (client_id)create index ind_bb_client_id on bb (client_id)Also I will run this procedure once a month... so when I run it do I create the indexes then drop them afterword assuming I put all these delete statements in 1 stored proc.Or should I run the create indexes command once and just leave them there. Would they hurt the db? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-06 : 15:48:25
|
| Yes that's what you would run.I would keep the indexes on them. You are probably joining to client_id in your application as well, so the app would benefit too.Tara |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2004-02-06 : 15:51:38
|
| Thanks for your help. I'll try it and let you know how it goes. |
|
|
|
abarsami
Yak Posting Veteran
68 Posts |
Posted - 2004-02-06 : 17:20:01
|
| I tried the indexes and it only help a little. But I think it is the limitation of deleting files... I don't think there is anything else I can do. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-09 : 12:19:53
|
| In SQL Server, indexing a bit field will produce little actual performance gain unless the distribution is heavily favored to one side, and the smaller side is what you are looking for.For example, if you have a Table of students, and a boy/girl flag, indexing it isn't going to help you much unless you have 90% girls and you are searching for boys. Otherwise, SQL Server is going to do a tablescan anyhow since it is more cost efective than doing an index lookup, then a row lookup, then serving up the data. In fact, adding an index to that column might even slow down your query because now it has to do an additional index delete with each row delete you do in order to delete the reference to the bit field.Indexing the join fields, however, is a good idea (the client_id in your example) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-09 : 12:21:49
|
| Yes I agree. I didn't notice/read that the column was a bit field.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-09 : 12:35:51
|
| Anyone want to know what SHOWPLAN says?Brett8-) |
|
|
|
|
Optimize delete statement