Query Optimization
-
Hi all.. To keep it short - We automatically update a set of tables (lets call it the DATA tables) in a client's database by dumping the raw data into a table (DUMP table) which has in INSERT trigger on it, which will then go and update the various data tables. I'm extending this functionality by adding a SETTING table which will allow clients to specify which fields they want updated. The SETTING table has two fields, an ID for each field in the DATA tables, and a setting field, which can be 0 for never update, 1 for always update, and 2 for only update if the corresponding field is null in the data table. Some people might complain that this is horrible design - There are reasons for it. My problem lies with optimization of this query. I get 30000 rows (bout 30-40 columns) at a time in the dump table and the trigger runs for about 40 minutes if it updates blindly, but now I need two extra SELECTs per field - first to check the update setting, then to check if the value is NULL should the setting be 2. So this comes down to more or less (ignore syntax trivialities):
SELECT @setting = UpdateSetting FROM Setting WHERE ID = @fieldid IF @setting = 1 OR (@setting = 2 AND IS NULL (SELECT field FROM Data WHERE id = @rowID) UPDATE Data SET Field = @newfieldval WHERE ID = @rowIDObviously this query will take AGES to complete, compared to the previous:UPDATE Data SET field1 = @newField1Val, field2 = @newField2Val, [etc] ... WHERE id = @rowIDHow should I go about optimising this? I am still somewhat novice when it comes to SQL. So far I'm considering building up an update string per row, which should at least cut down the number of updates significantly, but the number of extra selects are still a problem. Ideas? Clarifications needed? Thanks in advance. -
Hi all.. To keep it short - We automatically update a set of tables (lets call it the DATA tables) in a client's database by dumping the raw data into a table (DUMP table) which has in INSERT trigger on it, which will then go and update the various data tables. I'm extending this functionality by adding a SETTING table which will allow clients to specify which fields they want updated. The SETTING table has two fields, an ID for each field in the DATA tables, and a setting field, which can be 0 for never update, 1 for always update, and 2 for only update if the corresponding field is null in the data table. Some people might complain that this is horrible design - There are reasons for it. My problem lies with optimization of this query. I get 30000 rows (bout 30-40 columns) at a time in the dump table and the trigger runs for about 40 minutes if it updates blindly, but now I need two extra SELECTs per field - first to check the update setting, then to check if the value is NULL should the setting be 2. So this comes down to more or less (ignore syntax trivialities):
SELECT @setting = UpdateSetting FROM Setting WHERE ID = @fieldid IF @setting = 1 OR (@setting = 2 AND IS NULL (SELECT field FROM Data WHERE id = @rowID) UPDATE Data SET Field = @newfieldval WHERE ID = @rowIDObviously this query will take AGES to complete, compared to the previous:UPDATE Data SET field1 = @newField1Val, field2 = @newField2Val, [etc] ... WHERE id = @rowIDHow should I go about optimising this? I am still somewhat novice when it comes to SQL. So far I'm considering building up an update string per row, which should at least cut down the number of updates significantly, but the number of extra selects are still a problem. Ideas? Clarifications needed? Thanks in advance.Would you consider taking the logic that builds up your queries out to a service at the client? This way the service could build up your required updates and then fire them at your database, not taking up large amounts of DB resource, or locking tables. IMHO it would also be somewhat easier to maintain, as the updates you build up here are going to be quite large? Could make it somewhat more readable... I know that's not what you're asking for, but i think you'll find it difficult to optimise your update by too much.
-
Hi all.. To keep it short - We automatically update a set of tables (lets call it the DATA tables) in a client's database by dumping the raw data into a table (DUMP table) which has in INSERT trigger on it, which will then go and update the various data tables. I'm extending this functionality by adding a SETTING table which will allow clients to specify which fields they want updated. The SETTING table has two fields, an ID for each field in the DATA tables, and a setting field, which can be 0 for never update, 1 for always update, and 2 for only update if the corresponding field is null in the data table. Some people might complain that this is horrible design - There are reasons for it. My problem lies with optimization of this query. I get 30000 rows (bout 30-40 columns) at a time in the dump table and the trigger runs for about 40 minutes if it updates blindly, but now I need two extra SELECTs per field - first to check the update setting, then to check if the value is NULL should the setting be 2. So this comes down to more or less (ignore syntax trivialities):
SELECT @setting = UpdateSetting FROM Setting WHERE ID = @fieldid IF @setting = 1 OR (@setting = 2 AND IS NULL (SELECT field FROM Data WHERE id = @rowID) UPDATE Data SET Field = @newfieldval WHERE ID = @rowIDObviously this query will take AGES to complete, compared to the previous:UPDATE Data SET field1 = @newField1Val, field2 = @newField2Val, [etc] ... WHERE id = @rowIDHow should I go about optimising this? I am still somewhat novice when it comes to SQL. So far I'm considering building up an update string per row, which should at least cut down the number of updates significantly, but the number of extra selects are still a problem. Ideas? Clarifications needed? Thanks in advance.You could try combining the logic to the update. Something like (don't mind the typos):
UPDATE Data SET Field = @newfieldval
WHERE ID = @rowID
AND ( 1 = (SELECT UpdateSetting FROM Setting WHERE ID = @fieldid)
OR ( 1 = (SELECT UpdateSetting FROM Setting WHERE ID = @fieldid)
AND IS NULL (SELECT field FROM Data WHERE id = @rowID)))However, few considerations: - where does the rowid come from, could you eliminate that variable - as I understood that this is from inside the trigger, what happens if several rows are updated If you're using SQL Server, I would combine the previous statement with
inserted(and perhapsdeleted) table table if possible. That would help to make the whole update process a single batch. Also don't forget about good indexing. Hope it helps, MikaThe need to optimize rises from a bad design.My articles[^]