如何通过 VB net（odbc 连接）使用用户定义的函数在 SQL 表达式中添加列

Question

我是 VB.net 新手，我想在新列中转换并显示 UnixStamp 数据。

1. 变体 DatGridView = 数据源是数据集。 我可以在 SQL 查询中创建空列 (DataGridView_Dataset)，不幸的是我不能直接使用使用我自己的函数将数据转换为新列。错误请参阅。SQL 错误代码 该功能独立工作。看。 工作 UnixStampFunction

我在 7 秒内得到了 56,000 个句子的结果，但没有从中获取日期和时间值UnixTimeStamp

在SQL语句中使用udf有解决方案吗？

2. DataGridView 变体 - odbcExecuteRead 解决方案

使用给定的代码显示例如 10 个句子不是问题（10 个句子结果），但如果记录超过大约 100 条（每月大约 5 万条），则会显示类似这样的错误 (Managed Debug Helper ContextSwitchDeadlock: The module CLR might 60 秒内不会脱离上下文 COM 0xb45680 到上下文 0xb455c8。）。

取消选中 ContextSwitchDeadlock 选项 调试>窗口> VS 2019中的异常设置 我在 228 秒内得到了 56 000 条记录的结果。

是否可以优化代码或者是否可以使用其他解决方案？

代码：

Public Class Form1
Public strDateTime, strDate, strTime As String
Public x As Integer =0
Public y As Integer =0

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try

        Dim dgvDT As New DataGridView
        Dim odbcConn As OdbcConnection
        Dim odbcComm As OdbcCommand
        Dim odbcAdapter As New Odbc.OdbcDataAdapter
        Dim odbcDataset As New DataSet
        Dim odbcDataTable As DataTable
        Dim strConn, strSQL, strSQL1 As String


        dgvDT.Location = New Point(382, 2)
        dgvDT.Width = 360
        dgvDT.Height = 600

        Me.Controls.Add(dgvDT)
        strConn = "Driver=Firebird/InterBase(r) driver;User=;Password=;DataSource=...." ' 
        odbcConn = New OdbcConnection(strConn)
        odbcConn.Open()

        strSQL = "SELECT TEST.UNIXTIMESTAMP, " & "'dd.mm.yyyy" & "'" & "AS Date_ , " & "'hh:mm:ss" & "'" & "AS Time_ " _
                    & "From TEST " _
                    & "Where TEST.UNIXTIMESTAMP > 1646092800 " _ '1.3.2022
                    & "Order By TEST.ID "

        strSQL1 = "SELECT TEST.UNIXTIMESTAMP, UnixTimestampToDateOrTime(TEST.UNIXTIMESTAMP,1) As Date_, " & "'hh:mm:ss" & "'" & "AS Time_ " _
                    & "From TEST " _
                    & "Where TEST.UNIXTIMESTAMP > 1646092800 " _  '1.3.2022
                    & "Order By TEST.ID "

        odbcComm = New OdbcCommand(strSQL, odbcConn)
        'odbcComm = New OdbcCommand(strSQL1, odbcConn)

        odbcAdapter.SelectCommand() = odbcComm
        odbcAdapter.Fill(odbcDataset, "TEST")


        odbcDataTable = odbcDataset.Tables("TEST")
        dgvDT.DataSource = odbcDataTable
        dgvDT.Columns(0).HeaderText = "UnixTimeStamp"
        dgvDT.Columns(1).HeaderText = "Date"
        dgvDT.Columns(2).HeaderText = "Time"
        dgvDT.Visible = True

    Catch ex As Exception

        MessageBox.Show("Error:  " & ex.Message, "Error")

    End Try

End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

    Try
        Dim dgvDT1 As New DataGridView
        Dim odbcConn1 As OdbcConnection
        Dim odbcComm1 As OdbcCommand
        Dim odbcDR As OdbcDataReader
        Dim x As Integer = 0
        Dim y As Integer = 0
        Dim strConn1, strSQL, strSQL2 As String


        dgvDT1.Location = New Point(382, 2)
        dgvDT1.Width = 360
        dgvDT1.Height = 600


        For i As Integer = 0 To 2
            Dim dgvNC As New DataGridViewTextBoxColumn
            dgvNC.Name = "Column" & i.ToString
            dgvDT1.Columns.Add(dgvNC)
        Next

        dgvDT1.Columns(0).HeaderText = "UnixTimeStamp"
        dgvDT1.Columns(1).HeaderText = "Date"
        dgvDT1.Columns(2).HeaderText = "Time"
        dgvDT1.ReadOnly = True
        dgvDT1.AllowUserToAddRows = False
        dgvDT1.AllowUserToDeleteRows = False


        strSQL2 = "SELECT TEST.UNIXTIMESTAMP " _
                    & "From TEST " _
                    & "Where TEST.UNIXTIMESTAMP > 1646092800 " _
                    & "Order By TEST.ID "

        strConn1 = "Driver=Firebird/InterBase(r) driver;User=;Password=;DataSource="

        odbcConn1 = New OdbcConnection(strConn1)
        odbcConn1.Open()

        odbcComm1 = New OdbcCommand(strSQL2, odbcConn1)
        odbcDR = odbcComm1.ExecuteReader()


        While (odbcDR.Read()) 'And y <= 10
            dgvDT1.Rows.Add()
            dgvDT1.Rows(y).Cells("Column0").Value = (odbcDR.GetValue(0).ToString())
            dgvDT1.Rows(y).Cells("Column1").Value = (UnixTimestampToDateOrTime(odbcDR.GetValue(0), 1))
            dgvDT1.Rows(y).Cells("Column2").Value = (UnixTimestampToDateOrTime(odbcDR.GetValue(0), 2))

            y = y + 1

        End While

        Me.Controls.Add(dgvDT1)
        dgvDT1.Visible = True

    Catch ex As Exception
        MessageBox.Show("Error:  " & ex.Message, "Error")
    End Try
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    MsgBox(UnixTimestampToDateOrTime(1646092800, 1) & vbNewLine & UnixTimestampToDateOrTime(1646092800, 2))

End Sub

Public Function UnixTimestampToDateOrTime(ByVal _UnixTimeStamp As Long, ByRef _Parameter As Integer) As String

    strDateTime = New DateTime(1970, 1, 1, 0, 0, 0).AddSeconds(_UnixTimeStamp).ToString()
    strDate = strDateTime.Substring(0, strDateTime.IndexOf(" "))
    strTime = strDateTime.Substring(strDateTime.IndexOf(" "), strDateTime.Length - strDateTime.IndexOf(" "))

    If _Parameter = 1 Then
        Return (strDate)
    Else
        Return (strTime)
    End If

End Function

End Class

Answer 1

你写：

我想在新列中转换并显示 UnixStamp 数据。

是的，你可以。但我不相信这会让你的应用程序更快。

不过，我将概述我个人在没有 UDF 的情况下使用的数据转换策略。

我相信，删除 UDF 总体上会增强您的数据库。但不会解决性能问题。由于信息量很大，我无法将其格式化为纯粹的评论。

那么，关于从查询中删除 UDF。

Firebird 2.5 文档链接： https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-triggers

Firebird 3 文档链接：
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html

主要区别在于 FB3+ 具有“存储函数”和甚至是确定性的，而 FB2 - 仅是“存储过程”。

，如果您只有用于 Win64 的 UDF DLL，则不能使用用于 Linux/ARM 的 Firebird）。

我会避免在这里使用 UDF：它们被声明为过时的，它们使部署更加复杂，并且会降低您的灵活性（例如 我推测，你可以使用。

1. 您必须找到一种算法，如何将 UNIX 日期/时间“解析”为日、月等单独的值。

2. 您必须在过程 SQL 存储函数中实现该算法（在 FB3 中，使其成为“DETERMINISTIC”）或存储过程（FB2，在设置输出值后，通过使用“SUSPEND”命令使其“可选择”）参数和退出前） - 请参阅 Ch.手册第 7 条。

对于组装 DATE 或 TIME 或 DATETIME 类型的值，您必须使用 integer 来字符串 ( VARCHAR) 到结果类型强制转换，请参阅第 1 章。手册 3.8。

例子：

<前><代码>选择
演员表( 2010 || '-' || 2 || '-' || 11 AS DATE ),
演员（ 23 || ':' || 2 || ':' || 11 AS TIME ）
来自rdb$数据库

演员 |投掷    
:--------- | :--------
2010-02-11 | 23:02:11

db<>fiddle 此处

3. 您应该将转换后的列添加到您的表中。这些列 q 将是只读的，但它们会将“unix 时间”更改为 Firebird 本机日期和/或类型。

4. 阅读第 1 章5.4.2.手册中，关于 ALTER TABLE 的内容ADD 命令。

5. 阅读 5.4.1。关于计算字段（列，使用COMPUTED BY (<表达式>)而不是实际数据类型声明的手册中的CREATE TABLE。您可以使用这些列必须创建，这就是 FB3 和 FB2 之间的区别所在。

6. 在 FB3 中，我相信您将能够直接使用 PSQL 函数作为表达式。

7. 在 FB2 中，我相信您必须使用一个相当具体的蹦床，将存储过程强制转换为表达式：

ALTER TABLE {tablename}
ADD {columnname} COMPUTED BY
(
  (
     SELECT {sp_output_param_name} FROM {stored_proc_name}( {table_unixtime_column} )
  )
)

8. 在 Visual Basic 应用程序中，您将读取那些只读转换列而不是原始值列

Answer 2

现在，这将解决性能问题。再次，将此a写为答案，因为它太大，不适合作为评论。

1. 不，虽然 UDF 有许多问题 - 这些问题与性能无关，而是与“老派”有关并且容易出现低级问题，例如内存泄漏。如果在查询的其他地方使用 UDF，通过禁止 SQL 优化器使用快速的索引代码路径，UDF 可能会引发问题，但您的情况并非如此。您仅在 SELECT 的结果列列表中使用 UDF - 这里应该不会出现性能问题。

因此，您的应用程序/数据库应该存在其他瓶颈。

2. 首先，您使用 Where TEST.UNIXTIMESTAMP >查询中的 1646092800 和 Order By TEST.ID 。明显的问题是您的表在这些列上是否有索引。如果不是，则强制数据库服务器执行完全自然扫描以应用where条件，然后使用外部临时文件排序。这可能非常慢，并且随着表的增长可能会严重扩展。

使用您选择的数据库设计工具来检查您选择的查询计划。
Firebird 使用索引还是非索引访问路径。

网上有文章如何阅读Firebird的查询。不过，我不会立即懂英语。另外，它会特定于您的数据库设计工具，以及如何获取它。

抱歉，没有灵丹妙药。这就是需要学习数据库的地方。

阅读文档的数据定义语言/创建索引章节。

Firebird 2.5 文档链接： https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-triggers

Firebird 3 文档链接： https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html

检查您的数据库结构，如果 UNIXTIMESTAMP 和 ID 是否有索引。一般来说，索引会加快一些（不是全部）读取查询的速度，并会（稍微）减慢所有写入查询的速度。

如果这些索引尚不存在，您可能会决定添加它们。

同样，这取决于您的数据库设计工具，如何检查现有索引。它还取决于您的数据和应用程序是否需要哪种索引。这不是其他人可以为你决定的。

3. 我对 odbcAdapter.Fill(odbcDataset, "TEST") 命令也有很多怀疑。基本上，您尝试一次性读取所有数据。您可以通过 ODBC 连接来完成此操作，这对于 C# 来说并不自然。

通常桌面应用程序仅读取前 100 行左右。人们很少会在第一页或两页之后真正阅读任何内容。人类不是机器。

尝试以某种方式将视觉网格连接到 select 查询，而不读取所有表格。应该有办法。

此外，还有免费的 Firebird .Net Provider - 它应该与 VB.Net 一起工作，并且应该是您的首选，而不是 ODBC。

还有商业 IBProvider，基于本机 OLE DB 技术，它应该是比 .Net Provider 更糟糕的选择，但它也可以工作，并且有一些 VB.Net 的代码示例，我想它仍然是比 ODBC 驱动程序维护得更好。

4. 您还可以更改 Firebird 配置并允许其使用更多 RAM 进行缓存。这可能会在一定程度上缓解无索引选择和排序的问题。但只是有所放松和抵消，而不是解决。您可以在 www.ib-aid.com 上找到相关文章