Angus0011 (1) [Avatar] Offline
#1
Hello Ian,

Could you please show some details about the 'Cache function values' in Table11.12. I googled but didn't get anything useful. May be a script example can help me out. Thanks a lot.

Regards,
Angus
ian.stirk (27) [Avatar] Offline
#2
Re: Cache function values
Hi Angus,

Let me try to explain in another way… If you have a function (e.g. one that returns a constant value), and that function is used in a SELECT statement, for example on the JOIN or WHERE clause, the function is called each time for every row the SELECT statement produces. This is very inefficient, and I have seen this is many code samples.

Instead, if the value of the function is obtained and stored in a local variable (i.e. it is cached), that cached value can then be used in the SELECT query instead of the function call. This means

I once saw a function that was part of a WHERE clause, it was called 47,000 times each second, for a whole day! But putting the function value in a local variable, and then using that local variable in the place of the function call in the WHERE clause improved the query dramatically.

You can see more information here:

https://www.google.co.uk/#hl=en&sclient=psy-ab&q=%22sql+server%22+function+called+repeatedly&oq=%22sql+server%22+function+called+repeatedly&aq=f&aqi=&aql=&gs_l=hp.3...1186.18761.0.19033.45.41.4.0.0.0.157.2741.38j3.41.0...0.0.hRJPztqG9T8&pbx=1&bav=on.2,or.r_gc.r_pw.r_qf.,cf.osb&fp=1821ba54f6c138b6&biw=1366&bih=653

Thanks
Ian