MS SQL Server :: Performance Of Table-valued Function And Execution Plan

System.DirectoryServices Performance Issue In Table-valued Function
Hi,
I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).

Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?

Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class

View Replies !

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View Replies !

In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View Replies !

The Worst Execution Plan For A Function
Hi,

I use a SQL Server 2000 and I am trying to use function instead of a ad hoc query because I REALLY have to do it, but some issues must be solved. SQL Server is using a optimal execution plan when I run query and the worst execution plan for function.

Here are the informations you might need to help me:

Table DDL

Code SnippetCREATE 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

indexes
sp_helpindex ires

Code Snippet
name        description                          keys
IRES_CPF    clustered located on PRIMARY         CPF_CNPJ
IRES_IMPED nonclustered located on PRIMARY      IMPEDIMENTO

PERFORMANCE INFO

QUERY - it takes 2s to run and server makes an index seek on IRES_CPMF index

Code Snippet
select 'query ad-hoc',*
from IRES
where CPF_CNPJ = '000002230'
and TIPO = 'F'

EXECUTION PLAN
Clustered Index Seek(OBJECT:([bmcires].[dbo].[IRES].[IRES_CPF]), SEEK:([IRES].[CPF_CNPJ]='000002230'), WHERE:([IRES].[TIPO]='F') ORDERED FORWARD)

FUNCTION - it takes 20s to run and server makes a index scan on IRES_IMPED index!!!!

Code Snippet
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'
)
go

select cpf_cnpj
from fn_ires_crivo('000000029')

EXECUTION PLAN
|--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'))

View Replies !

Table-Valued Function
I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this?

Thanks

Mangala

View Replies !

Trigger On Table-valued Function?
Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.

View Replies !

Table Valued Function And Constraints

Is it possible to define a constraint for Primary Key on more than 1 column or an alternate index on a column in a return table from an inline table valed function?

Example Header:

alter FUNCTION [dbo].[fntMetaFrame] (@ii_CompanyID int)

RETURNS @tbl_MetaFrame TABLE ( pk_Key int Identity(1,1) primary key,

ObjectID int ,

Seq int null )

I want the primary key to be pk_Key, ObjectID

OR

I want to add another index on ObjectID.

View Replies !

How To Join Using A Table-valued Function?
Hi there.    I've hit some gap in my SQL fundementals.   I'm playing with table-valued functions but I can't figure out how to join those results with another table.   I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this.   What am I misunderstanding here?

The Given Objects:
function Split(stringToSplit, delimiter)   returns table (column: token)
table Words (column: Words.word)     -- table of predefined words
table Sentences (column: Sentences.sentence)    -- table of sentences; tokens may not be in Words table, etc

The Problems:
1) how do I query a set of Sentences and their Tokens?   (using Split)
2) how do I join tables Sentences and Words using the Split function?

The Attempts:
A)
select word, sentence, token
from Words,
       Sentences,
       dbo.Split(sentence, ' ')      -- implicitly joins Split result with Sentences?
where word = token

resulting error:   "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

B)
select word, sentence
from Words, Sentences
where word in (select token from dbo.Split(sentence, ' '))   -- correlated subquery?

resulting error:   "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

View Replies !

Indexing Table-valued Function?
I am using a multi-statement table-valued function to assemble data from several tables and views for a report. To do this, I INSERT data into the first few columns and then use UPDATEs to put data additional data into each row. Each UPDATE uses a WHERE criteria that identifies a unique row, based on the value of the first few columns.

The problem I'm having is that the UPDATEs are taking forever to execute. I believe the reason is that the temporary table that's created for the function is not indexed, so each row update requires a complete search of several columns.

In other situations I've been able to define one column as a primary key for the temporary table, but in this situation the primary key would have to consist of four columns, which doesn't seem to be allowed in the table definition for the function.

Is there any way to create indexes for the temporary tables that are created for multistatement table-valued functions? I think that would improve the UPDATE performance dramatically.

Thanks,
Lee Silverman
JackRabbit Sports

View Replies !

Can I Use If Statement In A Table Valued Function?
hi,
I am using a function in sql server 2005 like this:
...... myfunction(... @FlagOn int)
.......
begin
return
(

if(@FlagOn = 1)
select * from.......
else
select * form....
)
end

But it keeps complaining there is some syntax error around if. What is it?

Thanks.

View Replies !

Join With Table Valued Function
Hi,

I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method ..
eg..
SELECT A.Col1,A.Col2,B.Col1,B.Col2
FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B
ON A.Col1 = B.Col1

any body help me ... thanx in advance..

View Replies !

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function
Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View Replies !

Table-valued Function Return Error

I have this tsql

Declare @IDtable table(id uniqueidentifier, [Value] varchar(50))

Declare @iDoc Int

exec sp_xml_preparedocument @iDoc Output, @Data

insert into @IDtable(id,[Value])

Select *

From OpenXML(@iDoc, '/listing/Item', 2)

With (Id VarChar(40),

Value varchar(20))

exec sp_xml_removedocument @iDoc

but when I tried to right table value function of it so I can use it many place with just change paremeter value like this

ALTER FUNCTION [dbo].[splitXMLvalue](@editors xml)

RETURNS @etable table(id uniqueidentifier, [name] varchar(50))

AS

BEGIN

Declare @iDoc Int

exec sp_xml_preparedocument @iDoc Output, @Data

insert into @IDtable(id,[Value])

Select *

From OpenXML(@iDoc, '/listing/Item', 2)

With (Id VarChar(40),

Value varchar(20))

exec sp_xml_removedocument @iDoc

--select id as parentId, [Value] as parentValeu from @IDtable

RETURN

END

it gave me this error

Msg 557, Level 16, State 2, Line 1

Only functions and extended stored procedures can be executed from within a function.

can any one help me what I'm doing wrongthanks

View Replies !

Multi-statement Table-Valued Function
I'm creating a Multi-statement Table-Valued Function...

Is it possible to insert variables into the table? In other words, is it possible
to have something like

declare
@value1 varchar(10)
@value2 varchar(10)

BEGIN
<do some work on value1 and value2>
INSERT @returningTable
@value1, @value2

instead of

BEGIN
<do some work on value1 and value2>
INSERT @returningTable
SELECT col1, col2 from T_SOURCE

Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key.
I'll go with an example to explain what i have to do

Source_table
col1           col2
Mike          10
Mike          20
Ben           50
John          15
John          25
John          35

The table that my function needs to create should look like this
col1          col2        col3
Mike          10          1
Mike          20          2
Ben           50          1
John          15          1
John          25          2
John          35          3

I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table.

Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated.

Thank you.

View Replies !

Indexes On Table Variable Of Table Valued Function
Hi there,

Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff

RETURN
END

View Replies !

Table-Valued Function Result Vs. Calculation Table

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.

View Replies !

Problem Passing A Variable Into A Table-valued Function
Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?

View Replies !

Inline Table-valued Function With Multi-value Parameter
Hello everybody,

I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?

Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?

Thanks in advance for your answers.

View Replies !

Temporary Table Vs. Table Valued Function
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.

View Replies !

Table-valued Function Run Once For Each Row In A Table Variable.
I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.

Code Snippet

declare @IdTable table
(
EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees

/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )

Any help is very much appreciated.
Andrew

View Replies !

Table-valued Function Into A @table Variable
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?

ALTER PROCEDURE [dbo].[rpt]

(

@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'

)
AS

--split CourtIDs into a table
DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')

View Replies !

Table-valued Function Does Not Accept Chinese Characters As Parameter
I have a table-valued function in mssql 2005 as below:
ALTER FUNCTION fn_test{ @test nvarchar(1000)}RETURNS@TEMP TABLE{ test nvarchar(1000)}ASBEGIN INSERT INTO @TEMP SELECT @test
RETURNEND
Everytime, I passed in chinese character (@test), such as æ¸¬é©—, the function will return ????. What should I do for the table-valued function, so that the chinese character can be passed in? Please help.
Note: I can search and get the chinese characters if I use stored procedures; and the columns in the tables can store chinese chararcters as well. Only table-valued function is not working with the chinese characters. Is it a bug from MSSQL 2005?

View Replies !

Can A Stored Procedure Called From An Inline Table-Valued Function
Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View Replies !

Handling Large Queries In A Table Valued User Defined Function
Hello,

We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .

What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.

Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:

Sec / Actual Rows Returned
0        0
10 237
20 447
30      481
40      585
50      655
60      725
70 793
80 860
90      940
100    1013
110    1081
120    1115
130    1151
140    1217
150    1250
160    1325
170    1325
180    1430
190    1467
200    1502
210    1539
220    1574
230    1610
240    1645
250    1679
260    1715
270    1750
280    1787
290    1822
300    1857
310    1892
320    1923
330    1956
340    1988
350    1988
360    2022
370    2060
380    2094
390    2094
400    2130
410    2160
420    2209
430    2237
440    2237
450    2274
460    2274
470    2308
480    2342
490    2380
500    2380
510    2418
520    2418
530    2451
540    2480
550    2493
560    2531
570    2566

It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).

The minute breakdown during my trial is as follows:
1 = 655   (+ 655)
2 = 1081 (+ 426)
3 = 1325 (+244)
4 = 1610 (+285)
5 = 1822 (+212)
6 = 1988 (+166)
7 = 2160 (+172)
8 = 2308 (+148)
9 = 2451 (+143)

As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.

Allow me to explain the code in further detail:

[SqlFunction(FillRowMethodName = "FillListItem")]
public static IEnumerable DiscoverListItems(...)
{

ArrayList listItems = new ArrayList();

SPToSQLService service = new SPToSQLService();

[...]

DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service

//Load the DS to the ArrayList

return listItems;
}

public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...)
{

ArrayList item = (ArrayList) obj;

col1 = item.Count > 0 ? (string) item[0] : "";
col2 = item.Count > 0 ? (string) item[1] : "";
col3 = item.Count > 0 ? (string) item[2] : "";
[...]
}

As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.

Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.

Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.

Thanks,

Dragan Radovic

View Replies !

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.

Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,

IsDeterministic=false)]

public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );

}

}

return (IEnumerable)myList;

}

}

When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.

Code SnippetEXEC GetObject 'Item'

Is there some sorf of trick I am missing?

Thank you!

View Replies !

Actual Execution Plan Vs Estimated Execution Plan
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks

View Replies !

&&"Must Declare The Scalar Variable&&" In Table-valued Function
Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
    -- Add the parameters for the function here
    @dateStart DATETIME,
    @dateEnd DATETIME
)
RETURNS @arDetail TABLE
(
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)

INSERT INTO @arTemp
   SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
       FROM Pims
       WHERE Pims.Insurer = @arTemp.Insurer AND
             Pims.NABP = @arTemp.NABP AND
             Pims.NumRx = @arTemp.NumRx
);

INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View Replies !

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View Replies !

SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With &&"sys&&"?
Hi all,

I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:

Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.

I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?

Thanks, Jos

Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/

PRINT @@VERSION
go

PRINT 'Scalar function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
       ()
RETURNS       INT
AS
BEGIN
       RETURN 1
END
go

DROP   FUNCTION sys_test
go

PRINT ''
go

PRINT 'In-line table-valued function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
       ()
RETURNS       TABLE
AS
RETURN SELECT 1      c
go

DROP   FUNCTION sys_test
go

PRINT ''
go

PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go

CREATE FUNCTION sys_tmp
       ()
RETURNS       @t     TABLE
       (c     INT)
AS
BEGIN

       INSERT INTO @t       VALUES (1)

       RETURN

END
go

DROP   FUNCTION sys_test
go

PRINT ''
go

/*
----------------------------------------------------------------------------------------------------
*/

And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
       May 3 2005 23:18:38
       Copyright (c) 1988-2003 Microsoft Corporation
       Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Scalar function with name "sys_" creates ok...

In-line table-valued function with name "sys_" creates ok...

Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.

View Replies !

Scaler- Valued Function
i have given two functions - table valued and scaler valued

when select * from table_func('')
i m getting results..

but in select * from scalar_func('')
it gives error - invalid object name,
while i have both scripts open in same database but still from one func it gives results and from another it gives error..

so can anyone tell me plz. what should i do to see results from scalar func

thanks

View Replies !

Function Vs Temp Table Calcs Performance
I need to know what is the best performance for needing to do calculations for a particular column. I want to do something like:

Select IID
, ItemNo
, StdRun
, ActRun
, dbo.fnCalc(OutCount)
From myTable

The function is basically a set of Case Statements and various calculations dependant upon the Case.

Is this the best (performance wise) way to do it or should I dump the needed info in a Temp Table and do the calcs on it and then tie the select statement to the table.

I've seen both approaches done, but they both seem to be a different way of getting to the same conclusion. I'm just wondering which puts the lightest load on the server.

Thanks,
Tim

View Replies !

Return NULL From A CLR Scalar-Valued Function
Hi,

I have an assembly that contains the following function:

Public Class Lookup

<SqlFunction()> _

Public Shared Function MyTest() As Integer

Return System.Data.SqlTypes.SqlInt64.Null

End Function

End Class

Then in SSMS:

CREATE ASSEMBLY IRS_MyTest

FROM '\machine empmyAssembly.dll'

GO

CREATE FUNCTION dbo.MyTest() RETURNS INT

AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest

GO

when I run:

SELECT dbo.MyTest()

the following is returned:

Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest':

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException:

at System.Data.SqlTypes.SqlInt64.get_Value()

at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x)

at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest()

Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal.

Thanks,

Jan.

View Replies !

Calling Scalar Valued Function From SSIS OleDB Command Transformation
Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!!

View Replies !

Execution Plan
Which of the following does NOT cause the execution plan of a query to berecompiled ?- new column is added to a table accessed by a query OR- index used by a query has been dropped from the database OR- query perfoms a join to return data from multiple tables OR- significant amount of data in a table has been mofified

View Replies !

Execution Plan Of UDF
Hi,I have a table-valued user defined function (UDF) my_fnc.The execution of statement "select * from my_fnc" takes much longertime than runnig the code inside my_fnc (with necessary changes).What can be the reason?How can I see an execution plan used for UDF?Thanks a lotMartin

View Replies !

Bad Execution Plan
I'm new to SQL server but familiar enough with databases to know this doesn't seem right.
Here's the situation:
I have a table with real estate property information. There are about 650,000 rows in it. I have a nonclustered non-unique index on the city where the property is located. There are about 40 unique values in this index.

I do a simple query like:
SELECT city,address from propinfo where city= 'CARLSBAD'. The query will return about 4,000 rows. The problem is that the execution plan that it chooses is to do a full table scan. I.E. Even though there is an index on City, it chooses to look through 650,000 rows rather than do an index seek. Something sounds inefficient here. BTW, this happens in both SQL 7 and SQL 2000. Can anyone explain why this happens? I've got to think that SQL Server is more efficient here.

View Replies !

Execution Plan
Hello, I have been looking at the execution plan for a procedure call and the select, compute scalar, stream aggregates, constant scan, nested loops, asserts are all at 0% cost, the PK costs are 2% apart from a rogue 7% and a few 20%, tables scans are all at 23%. The query cost realtive to the batch is 100%. What does this all mean?
I have put non-clustered indexes on all the table attributes that are involved in the select statements but this has made no difference, i am guessing this is because my tables are not heavily populated and i may have seen a difference if i had thousands of entries in the tables the select statements acted on, is this assumption correct?
Does anyone else bother using the execution plan to tweak there DB or is it a negligible tool?

Jill

View Replies !

Execution Plan
In sql server 2005 management studio where do I find the option to run the sql query in the query analyser and also show the execution plan?
At present I see the option under Query menu which is "Display estimated Execution plan" which only shows the plan but does not execute the query.

Thanks

View Replies !

Execution Plan
Does anyone know of a good way to copy the execution plan when using "Include Actual Execution Plan"?
I often need to copy this and mail it.

I know I can use PrintScreen button, but I need a more efficient way to do this.
If I just could rightclick the execution plan and select "Copy" and get complete plan it would be great.

Mladen?

E 12Â°55'05.25"
N 56Â°04'39.16"

View Replies !

Get Real XML Execution Plan
Hi,I want to access the real execution plan via my webapplication after I have executed an SQL statement. I know how to get the estimated execution plan:1 cmd.CommandText = "SET SHOWPLAN_XML ON";2 cmd.ExecuteNonQuery();3 4 cmd.CommandText = myStatement;5 SqlDataReader dataReader = cmd.ExecuteReader();6 7 String plan = String.Empty;8 9 while (dataReader.Read()) {10 plan += dataReader.GetSqlString(0).ToString();11 }12 13 cmd.CommandText = "SET SHOWPLAN_XML OFF";14 cmd.ExecuteNonQuery();I want do compare the estimated costs with the real costs of the same statement. If I change code line 1 an 13 to "SET STATISTICS XML [ON|OFF]" the string "plan" will contain the result of the submitted SELECT statement, but I just need to get the plan and not the result itself. Thanks in Advance,Dominik

View Replies !

Functions And Execution Plan
The cost of query with usage of functions is as same as that of withoutfunctionsIn the below code, the query cost of insert is 0.02% and two selectstatements costs same 0.04%Declare @t table(mydate datetime)Declare @i intset @i=1while @i<=5000Begininsert into @t values(getdate())set @i=@i+1EndSelect mydate from @tSelect convert(varchar,mydate,112) from @tBut I thought usage of convert function will take more query costWhat do you think of this?Madhivanan

View Replies !

Execution Plan Hinting
We've got as slightly unusual scenario happening whereby a statement ispassed to SQL which consists of two parts.BEGIN TRANSACTIONDELETE * FROM WhateverBULK INSERT INTO Whatever...(etc)COMMIT TRANSACTIONThe first is a deletion of the data and the second is the bulk insertof replacement data into that table. The error that we see is aviolation of the primary key (composite).The violation only happens if we run both processes together. If we runone, then the other, it works fine. If we set a line by line insert, itworks fine.My suspicion is that the execution plan that is being run is mostlikely working the two parts in parallel and that the records stillexist at the point that the insert is happening. Truncate is not anoption. The bulk insert was added for performance reasons. There is anoption of trying the bulk insert, and if that fails, do the line byline insert, but it's far from ideal.I think we can probably wrap this into two individual transactionswithin the one statement as follows :BEGIN TRANSACTIONDELETE * FROM WhateverCOMMIT TRANSACTIONBEGIN TRANSACTIONBULK INSERT INTO Whatever...(etc)COMMIT TRANSACTIONWill this give sufficient hint to SQL about the order it processes itso that it completes as we intend and not as it sees being the mostefficient method ?Or, is there a better approach to this ?I've seen that some hints can be passed to SQL for optimizing, but myunderstanding was that it was always better to trust the optimiser andre-work the query as needed.With the server having two processors, is it feasible that one is doingone part and the other processor the other part in parallel ? Willtelling it to use a single processor be worthwhile looking at ? MAXDOP1 ?Finally, I'd imagine that the insert is quicker to process than thedeletion. Is this correct ?ThanksRyan

View Replies !

Stuck Execution Plan?
Using SQL Server 2000 SP4.There is a relatively complex stored procedure that usually completes inless than 20 seconds. Occasionally it times out after 180 seconds. The SPis called via ADO 2.8, using adCmdStoredProc command type. If I useProfiler to capture the EXEC that ADO sends to run the procedure, and runthat from QA, the procedure completes in less than 20 seconds as it should.The procedure is created WITH RECOMPILE. One additional twist is thatsp_setapprole is called from the client before running the procedure inquestion. This may be irrelevant, because even if I include the samesp_setapprole call when running the procedure from QA, it still executesquickly, and even if I comment out the call to sp_setapprole in the clientcode, the proc still times out when run from the client.The only thing that fixes it, at least for a day or two, is DBCCFREEPROCCACHE. So it appears that a bad plan is somehow stuck in memory andis only used when the procedure is called from the client app, and is notflushed even though the procedure was created WITH RECOMPILE.Other than scheduling the DBCC call to run every night, is there anythingelse I could try to get this resolved? Thanks.--(remove a 9 to reply by email)

View Replies !

SQL 6.5 Query Execution Plan .
Hello ,

I wanted to know whether we have an execution plan enabled in SQL 6.5 as we have it in SQL 7.0 and SQL 2000 .
I.e when we execute a query and if we enable ' show execution plan 'then it creates a map and shows the vital statistics .
If that is available on SQL 6.5 then i am missing that tool .

How can i have it installed on my SQL 6.5 server ??

Thanks.

View Replies !

Execution Plan Analysis
Hi,

I want to know how to analyze query execution plan for complex queries and what information is useful from that for improving the performance. I have gone through details in some sites like www.like sql-performance.com (http://www.sql-server-performance.com/query_execution_plan_analysis.asp), where it was more generic. I want more info regarding this.

Can any one tell about the resources for this or do you have any white papers or documents, which you can share with me.

Thanks in advance,
sekhar

View Replies !

Execution Plan Different For = And &<&> Operators
Hi ,

when
operator = then index SEEK
operator <> then index SCAN

Is normal ?

Example

SELECT *
FROM dbo.Batch
WHERE (Status = 'Batch Completed')

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PriceAvisPr].[dbo].[Batch]))
|--Index Seek(OBJECT:([PriceAvisPr].[dbo].[Batch].[IX_Batch]), SEEK:([Batch].[Status]='Batch Completed') ORDERED FORWARD)

StmtText
---------------------------------------------------------------------------------
SELECT *
FROM dbo.Batch
WHERE (Status <> 'Batch Completed')

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([PriceAvisPr].[dbo].[Batch].[PK_Batch]), WHERE:([Batch].[Status]<>'Batch Completed'))

View Replies !