sql server - SQL: Persisted computed field in table sometimes not used during query execution -

-

possible duplicate:
why execution plan include user-defined function call computed column persisted?

in sql server 2008 i'm running sql profiler on long running query , can see persisted computed column being repeatedly recalculated. i've noticed before , anecdotally i'd seems occur on more complex queries and/or tables @ least few thousand rows.

this recalculation cause of long execution speeds dramatically if comment out 1 column returned results (the field computed running xpath against xml field).

edit: offending sql has following structure:

declare @orderby nvarchar(50);  select     a.[id],          case           when @orderby = 'col1' a.[computedcol1]           when @orderby = 'col2' c.[computedcol2]           else c.[computedcol3]     end [order]     [stuff] inner join     [stuffcode] sc on     a.[code] = sc.[code]

all columns nvarchar(50) except computedcol3 nvarchar(250).

the query optimizer tries pick cheapest plan, may not make right choice. persisting column putting in main table (in clustered index or heap) in order pull out these values, normal data access paths still required.

this means engine may choose other indexes instead of main table satisfy query, , choose recalculate computed column if thinks doing combined chosen i/o access pattern cost less. in general, fair amount of cpu cheaper little i/o, no internal analysis of cost of expression done, if column calls expensive udf may make wrong decision.

putting index on column make difference. note don't have make column persisted put index on it. if after making index, engine still making mistakes, check see if have proper statistics being collected , updated on indexes on table.

it if posted structure of table (just important columns) , definitions of indexes, along ideas of execution plan looks when things go badly.

one thing consider may better recompute column in cases, make sure it's correct force engine go before doing so.


Comments

Post a Comment

Popular posts from this blog

Javascript line number mapping -

-
magically formatted comments change reported line number of javascript errors in browsers; this: //@line n "f" n line number , f file name. unfortunately, //@line appears ungoogleable. know there documentation on feature, , browsers support it? (i found references here , here .) from can find available in mozilla based browsers. it using js_setoptions command can turn these options on or off depending on values passed. https://developer.mozilla.org/en/js_setoptions
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ...
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more