Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-07 : 07:03:24
|
| Guruvayurappan writes "Hi,I have the following query which i would like to re-write in an optimized manner as this queries around 10 million rows. Thanks in advance. UPDATE #Office_Pricing SET default_flag = 0, schedule_date_id = ( SELECT DISTINCT MAX(schedule_date_id) FROM FOM_Sch_Assigned_vw z WITH (NOLOCK) WHERE z.office_gl_dep_id = @office_gl_dep_id AND z.Tax_Entity_Short_Nm = #Office_Pricing.Entity AND z.Effective_Dt = ( -- Get the max logical date to apply SELECT MAX(Effective_Dt) FROM FOM_Sch_Assigned_vw z WITH (NOLOCK) WHERE z.office_gl_dep_id = @office_gl_dep_id AND z.Tax_Entity_Short_Nm = #Office_Pricing.Entity AND z.Effective_Dt <= GETDATE() AND z.Effective_Dt <= @effective_dt )) Regards,G.R.Guruvayurappan" |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-07 : 07:18:33
|
| Hi Guruvayurappan, Welcome to SQL Team!Dunno whether it would improve performance, but I would make a couple of temporary tables of the MAX(dates) for all relevant rows, and then JOIN them all together for the update.Actually, I this you could get away with one temporary table with two columns - MAX(schedule_date_id) and MAX(Effective_Dt) [the second one will need a little extra effort using a CASE to cover the extra criteria for z.Effective_Dt]I presume you have got a PK/Index on your temporary #Office_Pricing table?This seems a bit redundant:AND z.Effective_Dt <= GETDATE() AND z.Effective_Dt <= @effective_dt I would just make sure that @effective_dt was the lesser of @effective_dt & GetDate()Are you sure you're happy with that (NOLOCK) hint?Kristen |
 |
|
|
|
|
|
Tune query for 10 million rows