clr data access really that slow?

Question

clr data access really that slow?

dant12

SSCrazy

Points: 2712
More actions
August 5, 2010 at 1:07 pm

#225801

for the sole reason of needing to use dynamic-sql inside a scalar function i wrote a simple CLR UDF that opens a context connection execute a reader inside a DataReader and reads the output. the query only returns 1 row and 1 column which is what i return, but for some reason it takes 20 minutes to return 300k rows.
i have heard accessing data inside CLR is always slower but i didnt think it would be this slow, am i doing things wrong?
--
Thiago Dantas
@DantHimself

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Doing data access in a user defined function, whether it be TSQL or CLR is really slow because it is a RBAR (row by agonizing row) process. Without code and more information like why you need dynamic sql in this case, its hard to offer any kind of advice here.

One thing I do see is that you don't need a datareader, you can use ExecuteScalar to get the single value result back. However, that isn't likely the performance problem here. In most cases where I've seen this kind of thing, you are attacking the problem wrong.

dant12 SSCrazy Points: 2712 More actions · Answer 2

we have a table that stores a formula in T-SQL form in a column and that needs to enter in a T-SQL query, the only way to it on T-SQL is inside a EXEC() block, which i cant call inside a scalar so i tried doing it on CLR. Sorry but can't provide code on this, thanks anyway.

--
Thiago Dantas
@DantHimself

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Sorry, but without code its impossible to help you solve this. Take a little bit of time and mock up a similar scenario using different information. I can guarantee you that I am not interested in using code or design that has this kind of problem or requirement beyond helping you with your specific problem.

It sounds to me like your implementation of this is inherently flawed from its basic design. Why do you need to store TSQL code in a table to concatenate dynamically into your request? What/who changes these TSQL formula's stored in the table? What protects that process from being injected into and putting your database and data at risk, or validates that a change to the formula in the table doesn't result in invalid syntax at run time? How often do formula's actually change and how many formula's actually exist?

dant12 SSCrazy Points: 2712 More actions · Answer 4

i agree the design is flawed but its one of those things that has always been there (way before i entered the company) and too much has been built on top of it, but i assure its secure in its place, its just a hassle to work with it. ill try and make a sample code tomorrow and post it

--
Thiago Dantas
@DantHimself

Ted Krueger Ten Centuries Points: 1171 More actions · Answer 5

dant12 (8/5/2010)
we have a table that stores a formula in T-SQL form in a column and that needs to enter in a T-SQL query, the only way to it on T-SQL is inside a EXEC() block, which i cant call inside a scalar so i tried doing it on CLR. Sorry but can't provide code on this, thanks anyway.

Everything you are doing here will be inherently slow. However, without a valid reason to why ExecuteScalar will not work for you, I agree it to be the best option. You can execute your "formula" directly after returning the one string (formula). Again, that will be painfully slow as you are doing this row-by-row as Jonathan pointed out.

Redesign: This is more than a perfect candidate for a redesign initiative. If you give us some details on the storage requirements, business process of what the formulas are being used for, I think we can give you a really good direction to push for. Commonly when a poor design gets this far, the development time to work around the issues they bring with them take as long as rethinking and using the database and development platforms more efficiently.

dant12 SSCrazy Points: 2712 More actions · Answer 6

heres the VB code

<Microsoft.SqlServer.Server.SqlFunction(Name:="UF_DAYS", _

SystemDataAccess:=SystemDataAccessKind.Read, DataAccess:=SystemDataAccessKind.Read)> _

Public Shared Function FRAMINGDAYS(ByVal ID As Integer, <SqlFacet(maxsize:=500)> ByVal FORMULA As String, ByVal DAY As Date, <SqlFacet(maxsize:=4000)> Optional ByVal TITULOS As String = "") As SqlInt32

Dim query As String

Dim ret As Integer = -999999999

If DAY = Nothing Then

DAY = Now.Date

Else

If Not IsDate(DAY) Then

DAY = Now.Date

Else

DAY = DAY.Date

End If

If FORMULA = "" Then

Return SqlInt32.Null

End If

If TITULOS = "" Then

TITULOS = "''"

End If

If ID <> 0 And ID <> Nothing Then

Using conn As New SqlConnection("context connection=true")

conn.Open()

FORMULA = FORMULA.Replace("@DAY", "CONVERT(DATETIME,'" & DAY.ToString() & "',103)").Replace("@TITULOS", "'" & TITULOS & "'")

query = "SELECT MAX(" & FORMULA & ") FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID AND "

query &= "(" & TITULOS & " IN ('','0') OR TABLE2.ID IN (SELECT DATA FROM DBO.UF_VARCHAR_TO_TABLE('" & TITULOS & "',','))) WHERE "

query &= "TABLE1.ID = " & ID

Dim cmd As New SqlCommand(query, conn)

Try

Dim dr As SqlDataReader = cmd.ExecuteReader()

dr.Read()

If dr.HasRows Then

ret = dr.GetInt32(0)

End If

dr.Close()

cmd.Dispose()

conn.Close()

Catch

End Try

If ret <> Nothing And ret <> -999999999 Then

Return New SqlInt32(ret)

Else

Return SqlInt32.Null

End If

End Using

Else

Return SqlInt32.Null

End If

End Function

wonder if i converted it into a TVF it would get faster?

--
Thiago Dantas
@DantHimself