August 5, 2010 at 1:07 pm
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
August 5, 2010 at 3:07 pm
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.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
August 5, 2010 at 3:56 pm
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
August 5, 2010 at 5:51 pm
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?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
August 5, 2010 at 5:55 pm
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
August 5, 2010 at 5:57 pm
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.
August 9, 2010 at 7:52 am
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply