Function poor performance

Last Post 09 May 2008 06:54 AM by SQLUSA. 8 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
AlexB_SQL
New Member
New Member

--
07 May 2008 05:58 AM
Hi,

I have a simple select on a 20M lines table that uses 1 column in where condition on a SQL SERVER 2000 box.
As I have to restrict access to this table, I am using a view, but it cannot use variables for where clause, so I figured out that function would solve the problem. What I did not coun was a weird sql server behaviour.

Here is table strutcture and indexes

Column_name
CPF_CNPJ
TIPO
IMPEDIMENTO
GRAU
DT_ULT_OCOR

I have following indexes
index_name--------index key
IRES_CPF-----------CPF_CNPJ
IRES_IMPED--------IMPEDIMENTO

----------------------------------------------------------------------------------------------
The query is

SELECT ,*
FROM IRES
WHERE CPF_CNPJ = '000000029'
and TIPO = 'F'

EXECUTION PLAN FOR QUERY makes just an index seek on IRES_CPMF index.
----------------------------------------------------------------------------------------------

Function is

CREATE FUNCTION fn_ires_crivo
( @cpfcnpj nvarchar(9) )
RETURNS table
AS
RETURN (
SELECT CPF_CNPJ,
TIPO,
IMPEDIMENTO,
GRAU,
DT_ULT_OCOR
FROM dbo.ires WITH (INDEX (IRES_CPF) NOLOCK)
WHERE
CPF_CNPJ = @cpfcnpj
and TIPO = 'F'
)

When I execute

SELECT *
FROM fn_ires_crivo('000000029')

EXECUTION PLAN FOR FUNCTION IS

|--Filter(WHERE:([IRES].[TIPO]='F'))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([bmcires].[dbo].[IRES]))
|--Parallelism(Gather Streams)
|--Index Scan(OBJECT:([bmcires].[dbo].[IRES].[IRES_IMPED]), WHERE:(Convert([IRES].[CPF_CNPJ])='000000029'))

--------------------------------------------------------------------------------------------------

Selecting function took much more time than ad hoc select.
When I did not use any hint at function, sql server keeps using same execution plan.

Could anyone explain me why it is hapenning, since I used index hint?
Is there any way to solve it?
I cannot use procedure because another sql server makes a join with the view I am using now.


Thanks in advance


AlexB_SQL
New Member
New Member

--
07 May 2008 09:16 AM
A application makes this select, but the table has 20 million lines and execution plan does not use the "correct" index.
column cpf_cnpj use varchar datatype.

To solve my problem, I really have to use the function, but it does not use same execution plan that ad hoc query uses and I would like to know why does it happens.
AlexB_SQL
New Member
New Member

--
07 May 2008 09:48 AM
1 - varchar(9)
2 - 1 because this fiels is a pk using clustered index
AlexB_SQL
New Member
New Member

--
07 May 2008 11:53 AM
Well, it was not type mismatch. I set a varchar value to function parameter, and SQL Server still do not use clustered index.
What could be hapenning? I really do not understand why SQL Server uses "wrong" index only for the function while for the ad hoc query it uses the correct index.
following the ddl for table and index.

CREATE TABLE [IRES] (
[CPF_CNPJ] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIPO] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IMPEDIMENTO] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GRAU] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DT_ULT_OCOR] [datetime] NULL
) ON [PRIMARY]
GO

index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IRES_CPF clustered located on PRIMARY CPF_CNPJ
IRES_IMPED nonclustered located on PRIMARY IMPEDIMENTO


SQLUSA
New Member
New Member

--
07 May 2008 05:14 PM
Instead of function use stored procedure.

If you need flexibility within the sproc you can do CASE or dynamic SQL.

Functions are not really for SELECTs.

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

--
08 May 2008 05:05 AM
Folks,

first and foremost, the table really does not have a PK. it was my mistake because I know that data for this table comes from a file where cpf_cnpj is unique. sorry abou misinformation.

The problem about bad execution plans was that I used nvarchar as function variable, table and its respective index used varchar. When I rebuilt function using varchar, it used same execution plan of the query. Problem solved!

Why am I using function you ask. I will explain.There is a select from another server that joins remote table with the view in question and when it runs, it locks the table because the execution plan is not good for multi-user environment. I discovered that using profiler. So, I guess that using function forcing index use AND nolock it will not have impact to other users from this DB. I wil test it today or tomorrow and find out if my solution worked.

Thanks for all your help

Best Regards

Alex
SQLUSA
New Member
New Member

--
08 May 2008 07:54 AM
quote:

Originally posted by: AlexB_SQL
So, I guess that using function forcing index use AND nolock it will not have impact to other users from this DB.
Alex


Alex,

forcing index use and NOLOCK has nothing to do with functions. You can do it in a sproc also or in a query.

Functions are for like complex calculations or string manipulations.

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

SQLUSA
New Member
New Member

--
08 May 2008 11:03 AM
quote:

Originally posted by: gunneyk

The whole purpose of a table valued function is for returning a rowset that can be accessed just like a table. This is mainly populated with selects inside the function.


Andy,

Yes, you would use a table-valued function to return CSV list values in table format.

Why on earth (and heaven) would you use functions for regular queries? Much more limited than sprocs?

Beside in SQL Server 2008 sprocs can have table parms.

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

SQLUSA
New Member
New Member

--
09 May 2008 06:54 AM
quote:


2. i suggest u find some study material...msdn is a good place to start to learn about functions and their proper usage


SQL Server Magazine Forum Pro



Hit me Russell with real PRACTICAL examples whereby you have imbedded SELECT queries in a function? I am open to learning... in fact still learning SQL Server 2005 while preparing training cd-s for SQL Server 2008 ....

Here is how I use table-valued functions:
< link removed by SQL Server Magazine Forum Pro >

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


Acceptable Use Policy
---