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"
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
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