每秒从SQL得到20个表。 [英] .net 20 tables from SQL every sec.

问题描述

我有一台服务器机器（sql2008），需要每秒从20个表中获取数据。（table1，table2，ext ..）



使用vs2015和datagridview但是本地机器每秒都无法获取数据。 （崩溃或滞后）



如果我使用1 gridview它的工作，但我需要从20个表格中获取数据



如何每秒从sql获得20个表。？



我尝试了什么：



定时器间隔= 1000

I have a server machine(sql2008) and need to get data from 20 tables every sec.(table1,table2,ext..)

Used vs2015 and datagridview but local machines can't get data every sec. (crashed or lagged)

if i use 1 gridview it's work but i need to get data from 20 tables

how can i get 20 tables from sql on every sec.?

What I have tried:

timer interval=1000

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
       getdata(DataGridView1,table1)
   End Sub

Public Sub getdata(ByVal datagrid As Object, ByVal tableno As string)
       Dim Server As String = "server"
       Dim User As String = "sa"
       Dim Pass As String = "1231231"
       Dim Database As String = "database"


       Dim ay = "; "
       Dim DBConnection As String = ""
       DBConnection = DBConnection & "Provider=SQLOLEDB.1" & ay
       DBConnection = DBConnection & "Data Source=" & Server & ay
       DBConnection = DBConnection & "Initial Catalog=" & Database & ay
       DBConnection = DBConnection & "User ID=" & User & ay
       DBConnection = DBConnection & "Password=" & Pass & ";"

       Dim SQL As String

       SQL = "select * from " & tableno & ""

       Dim Conn As New ADODB.Connection()

       Dim rs As New ADODB.Recordset()

       Dim daTitles As New OleDbDataAdapter()

       Dim dsTitles As New DataSet("test")


       Conn.Open(DBConnection, "", "", -1)

       rs.Open(SQL, DBConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic)


       daTitles.Fill(dsTitles, rs, "setting")

       datagrid.DataSource = dsTitles.Tables(0)

       rs.ActiveConnection.close()
       rs.ActiveConnection = Nothing

       Conn.Close()
       Conn = Nothing

   End Sub

推荐答案

你的要求是荒谬的。



你要么生成20个SQL查询来获取你需要的数据，要么你要用一堆连接生成至少1个查询/联合并且必须将返回的结果解析为可用的形式在你的应用程序中这并没有考虑到每条记录的大小和记录的数量。



当然，你可以在一秒钟内执行20次查询而不会真正返回什么，但你要省去返回的数据量和你身边显示数据所需的处理。



你无法保证1秒左右转所有这些查询。 SQL Server可能忙于做其他事情和/或您的客户端代码将忙于处理返回数据，将其绑定到各自的网格并处理用户鼠标和键盘事件及其处理要求。

Your "requirement" is ridiculous.

You're either going to generate 20 SQL queries to get the data you need or you're going to generate at least 1 query with a bunch of joins/unions and have to parse the returned result into a usable form in your app. That doesn't take into account the size of each record nor the number of records.

Sure, you can execute 20 queries in a second that don't really return anything, but you're leaving out the amount of data returned and the processing required on your side to display the data.

You cannot guarantee a 1 second turn around for all of these queries. The SQL Server can be busy doing other things and/or your client code will be busy processing the return data, binding it to their respective grids and handling user mouse and keyboard events and their processing requirements.

这篇关于每秒从SQL得到20个表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！