Function poor performance 2

Last Post 09 May 2008 11:36 AM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
AlexB_SQL
New Member
New Member

--
09 May 2008 09:38 AM
Gunnyek

since my las pos was locked, and i have more doubts, can I ask you another question?

you wrote

Alex,

I am glad you were able to get the function to work properly. The table should have a PK constraint for the reasons I mentioned regardless of it's origin. In any case I want to comment on your newest piece of information regarding a remote query. I assume this is doen thru a linked server correct? If so and you are joining a remote table to a local table you will almost certainly get a full scan of the remote table due to how linked server joins work. The optimizer knows nothing of the remote table or even what type of system it is (it could be a flat file for all it knows) and it cannot do normal row by row joins as you would expect. So it is very possible that it will bring over all the rows from the remote table to a temp table and then join from there. This is obviously a problem with larger remote tables. You may be better off to call a stored proc on the remote server that will filter as many rows as possible and then put the results into a temp table locally which you can then (and index) join on the local one.

My question

If I use a function with index and nolock hints, even the query comes from a linked server, wouldn´t SQL serve where function resides use index I "pushed"? If not, why sproc would work in this case and function wouldn´t? Is it because sproc is compiled and runs on local server where it resides, not mattering who or where it was executed?
Besides that, doesn´t local sql server is responsible for planning the execution plans for queries and functions to respond to everyone, including linked servers calls?
SQLUSA
New Member
New Member

--
09 May 2008 10:09 AM
Alex,

Can you clarify the reasons you are using function?

If you have to call function repeatedly it is slow.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
AlexB_SQL
New Member
New Member

--
09 May 2008 11:12 AM
I used function because the application used a view and it was causing index scan on a clustered index from a 20million lines table.
I verified that using function it makes a index seek as I planned.
Here are the facts
1 - old view(vw_x)
select * from table

2 - application does the following
select * from vw_x where col1='111'
SQL server used index scan operator on index ix_col1 built on col1
This behaviour was causing too many locks and the rest of users were having performance issues.

3 - function fn_x
select *
from table with(index (ix_col1)) nolock
where col1=@var

4 - Application now does the following
select * from fn_x('111')

Using profiler I noticed that SQL Server is using index seek instead of index scan, and until now, no one noticed performance problem.
Thanks for your attention.
SQLUSA
New Member
New Member

--
09 May 2008 11:36 AM
Thansk Alex.

That is interesting.

I will investigate.

Generally functions are not known to have any performance advantage over inline queries or sprocs. Not sure about views.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Performance Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/order2005highperformance
SQLUSA
New Member
New Member

--
09 May 2008 11:50 AM
Alex,

I could not investigate your claim because I could not slow down my HUGE view. SQL Server 2005 is just simply amazing in performance.

You keep mentioning NOLOCK, that is not related to index performance, rather blocking conflict.

Make sure your indexes are not fragmented. If they are, you may find yourself in a confusing situation.

USE YourDB
GO
ALTER INDEX ALL ON dbo.YourTable
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO

Retest again, and you should not find that function has performance advantage over straight query or a view.

Let us know how it goes.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
09 May 2008 12:01 PM
Alex,

What Andy is saying is valid. There is no known advantage using functions for query performance gain on large tables.

Here is the proof for Andy's guess. Note that this behavior is well known in SQL Server industry circle.
Note that SalesOrderDetailHUGE is an 8 million rows versiono of SalesOrderDetail of AdventureWorks.
AltID is numeric.

create view vSOD
as select * from SalesOrderDetailHUGE

create index idxSpeed on SalesOrderDetailHUGE(altID)

set statistics io on

dbcc dropcleanbuffers
select * from vSOD
where altID=4000802
/*
Table 'SalesOrderDetailHUGE'. Scan count 1, logical reads 4, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/


dbcc dropcleanbuffers
select * from vSOD
where convert(varchar,altID)='4000802'

/*
Table 'SalesOrderDetailHUGE'. Scan count 3, logical reads 21996, physical reads 3, read-ahead reads 20047, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/


Congrats Andy! Brilliant guess.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/highperformance




Acceptable Use Policy
---