使用visual basic console更快地查询azure SQL [英] Faster query to azure SQL with condition using visual basic console

问题描述

伙计们，



如何更快地查询和检索结果？在此之前，查询完全可以接受等待。目前，它执行了10分钟，仍然在查询。我的查询是从位于Azure SQL的5个表中选择计数。我想与Local Server SQL进行数据比较。

Guys,

How to query and retrieve result faster? Before this, query quite acceptable to wait. Currently, it executing for 10 minutes and still querying. My query is to select count from 5 tables located at Azure SQL. I want to do data compare with Local Server SQL.

Dim CommandText As String = "WAITFOR DELAY '0:0:01';SELECT
(SELECT COUNT(*) FROM [dbo].[TableFirst] WHERE LineIDKey=" & idkey & ") as TableFirst,
(SELECT COUNT(*) FROM [dbo].[TableSecond] WHERE LineIDKey= " & idkey & ") As TableSecond,
(SELECT COUNT(*) FROM [dbo].[TableThird] WHERE LineIDKey=" & idkey & ") as TableThird,    
(SELECT COUNT(*) FROM [dbo].[TableForth] WHERE LineIDKey=" & idkey & ") as TableForth,
(SELECT COUNT(*) FROM [dbo].[TableFifth] WHERE LineIDKey=" & idkey & ") as TableFifth"

LineIDKey是Number。



我试过这个我在互联网上找到的查询..超级大规模更快！

The LineIDKey is Number.

I have try this query which I found on internet.. Super-massive faster!

SELECT T.name AS TableName, 
       I.rows AS TotalCount 
FROM   sys.tables AS T 
       INNER JOIN sys.sysindexes AS I 
               ON T.object_id = I.id 
                  AND I.indid < 2
ORDER  BY T.name asc

但是如何插入条件语句？此查询显示数据库中的所有表。哪个套房我想要的。待定： -

1.插入条件 WHERE LineIDKey =& idkey&

2.抓取行TableCount和TableName





＃注意：数据库中的所有表都有LineIDKey列



我尝试过：



Dim CommandText As String =WAITFOR DELAY'0：0：01'; SELECT

（SELECT COUNT（*）FROM [dbo]。[TableFirst] WHERE LineIDKey =& idkey&）作为TableFirst，

（SELECT COUNT（*）FROM [dbo] 。[TableSecond] WHERE LineIDKey =& idkey&）作为TableSecond，

（SELECT COUNT（*）FROM [dbo]。[TableThird] WHERE LineIDKey =& idkey& ）如TableThird，

（SELECT COUNT（*）FROM [dbo]。[TableForth] WHERE LineIDKey =& idkey&）as TableForth，

（SELECT COUNT（*）FROM [dbo]。[TableFifth] WHERE LineIDKey =& idkey&）as TableFifth







SELECT T.name AS TableName，

I.rows AS TotalCount

FROM sys.tables AS T

INNER JOIN sys.sysindexes AS I

ON T.object_id = I.id

AND I.indid< 2

ORDER BY T.name asc

But how to insert Condition Statement? This query show all tables from the database. Which suite what I want. By pending in:-
1. Insert condition of WHERE LineIDKey=" & idkey & "
2. Grab rows TableCount and TableName


#Note: All table in the database have LineIDKey column

What I have tried:

Dim CommandText As String = "WAITFOR DELAY '0:0:01';SELECT
(SELECT COUNT(*) FROM [dbo].[TableFirst] WHERE LineIDKey=" & idkey & ") as TableFirst,
(SELECT COUNT(*) FROM [dbo].[TableSecond] WHERE LineIDKey= " & idkey & ") As TableSecond,
(SELECT COUNT(*) FROM [dbo].[TableThird] WHERE LineIDKey=" & idkey & ") as TableThird,
(SELECT COUNT(*) FROM [dbo].[TableForth] WHERE LineIDKey=" & idkey & ") as TableForth,
(SELECT COUNT(*) FROM [dbo].[TableFifth] WHERE LineIDKey=" & idkey & ") as TableFifth"



SELECT T.name AS TableName,
I.rows AS TotalCount
FROM sys.tables AS T
INNER JOIN sys.sysindexes AS I
ON T.object_id = I.id
AND I.indid < 2
ORDER BY T.name asc

推荐答案

您遇到了性能问题，因为您的数据库未经过优化。



Mehdi Gholam [ ^ ]在问题的评论中提到，您可以通过添加索引来提高查询性能。检查一下：

使用Index Advisor提高Azure SQL数据库的性能博客| Microsoft Azure [ ^ ]

单个数据库的Azure SQL数据库性能指南 [ ^ ]

Azure SQL数据库查询性能洞察 [ ^ ]

解决问题并优化Que与Azure SQL数据库相关 - TechNet文章 - 美国（英语） - TechNet Wiki [ ^ ]

You've got performance issues, because your database is not optimized.

As Mehdi Gholam[^] mentioned in the comment to the question, you can improve query performance by adding indexes. Check this:
Improve the performance of your Azure SQL Databases using Index Advisor | Blog | Microsoft Azure[^]
Azure SQL Database performance guidance for single databases[^]
Azure SQL Database Query Performance Insight[^]
Troubleshoot and Optimize Queries with Azure SQL Database - TechNet Articles - United States (English) - TechNet Wiki[^]

您可能需要查看您的数据库设计通过在字段 LineIDKey 上添加索引，它将大大提高查询的性能。

SQL CREATE INDEX语句 [ ^ ]



在查询中传递 idkey 的值的方式是坏的，因为它打开了代码注入的大门，而不是使用参数

SQL注入 [ ^ ]



这个网站包含一个很好的用于SQL的tuto

SQL教程 [ ^ ]

You probably need to review your database design by adding an index on field LineIDKey, it will greatly improve the performance of the query.
SQL CREATE INDEX Statement[^]

The way you pass value of idkey in the query is bad because it open the door to code injection, use parameter instead
SQL Injection[^]

This site contain a good tuto for SQL
SQL Tutorial[^]

这篇关于使用visual basic console更快地查询azure SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！