Optimizing queries with isnull
in [Programming SQL]
|
From: tshad on 30 Mar 2010 01:38 If I had a query with columns surrounded by IsNull, would that slow the query down? For example if I had something.like: SELECT Phone= ISNULL(e.Phone, m.Phone), Name = ISNULL(e.Name,m.Name) FROM Employees e LEFT JOIN Managers m Would that have an affect on the query? Thanks, Tom
From: Plamen Ratchev on 30 Mar 2010 09:20 The use of ISNULL will have some effect on performance. As usual, best to test with your data (just run the query once using ISNULL, and then without). -- Plamen Ratchev http://www.SQLStudio.com
From: Uri Dimant on 30 Mar 2010 09:29 Hi > If I had a query with columns surrounded by IsNull, would that slow the > query down? No. I do not see ON clause in nyour query as well as WHERE condition, or was it only for demonstartion? "tshad" <tfs(a)dslextreme.com> wrote in message news:uUEY3s8zKHA.4384(a)TK2MSFTNGP06.phx.gbl... > If I had a query with columns surrounded by IsNull, would that slow the > query down? > > For example if I had something.like: > > SELECT Phone= ISNULL(e.Phone, m.Phone), > Name = ISNULL(e.Name,m.Name) > FROM Employees e > LEFT JOIN Managers m > > Would that have an affect on the query? > > Thanks, > > Tom >
From: tshad on 30 Mar 2010 16:03 "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:5JudnfxIit0qZyzWnZ2dnUVZ_tWdnZ2d(a)speakeasy.net... > The use of ISNULL will have some effect on performance. As usual, best to > test with your data (just run the query once using ISNULL, and then > without). > But in this case, the ISNULLs are using either on or the other as a value based on whether the first one is null or not. Is there a better way to do this or would the difference be negligable? In this select there are about 15 ISNULLS (one for each column) because this is FULL OUTER JOIN. So it is saying if it was null in the first table use the value in the second table. I had heard using functions in the select statement would prevent the optimizer from choosing the correct plan. Thanks, Tom > -- > Plamen Ratchev > http://www.SQLStudio.com
From: tshad on 30 Mar 2010 16:05
"Uri Dimant" <urid(a)iscar.co.il> wrote in message news:OetZezA0KHA.4752(a)TK2MSFTNGP04.phx.gbl... > Hi >> If I had a query with columns surrounded by IsNull, would that slow the >> query down? > No. I do not see ON clause in nyour query as well as WHERE condition, or > was it only for demonstartion? > Just a demonstration, just showing the function I was concerned with and whether that would prevent the optimizer from choosing the correct plan. Thanks, Tom > "tshad" <tfs(a)dslextreme.com> wrote in message > news:uUEY3s8zKHA.4384(a)TK2MSFTNGP06.phx.gbl... >> If I had a query with columns surrounded by IsNull, would that slow the >> query down? >> >> For example if I had something.like: >> >> SELECT Phone= ISNULL(e.Phone, m.Phone), >> Name = ISNULL(e.Name,m.Name) >> FROM Employees e >> LEFT JOIN Managers m >> >> Would that have an affect on the query? >> >> Thanks, >> >> Tom >> > > |