Powershell: how to execute a T-SQL query without getting all the result records?
Hi Experts:
I wrote a Powershell script using Invoke-Sqlcmd to test some T-SQL queries in a SQL Server 2012 server. I only want to make the script check whether there is some record returned or not, but don't care how many record.
The code I currently uses is like:
I encounter an issue that some query returns millions of records, such that it takes long time for the script to run and return them all to my workstation through the network. Can you help me to find a way to speed it up? I only need the script to check whether there is data return, and don't care how many.
I tried some ways but they didn't seem to work:
1. tried parameters in Invoke-Sqlcmd (MaxBinaryLength and MaxCharLength) and hope they can limmit the return length, but doesn't work
2. try to add "SELECT TOP 1 * from () as x" around the original T-SQL query, but this does not work for SELECT statement having "WITH" clause
Thank you!
I wrote a Powershell script using Invoke-Sqlcmd to test some T-SQL queries in a SQL Server 2012 server. I only want to make the script check whether there is some record returned or not, but don't care how many record.
The code I currently uses is like:
$queryResult = Invoke-Sqlcmd -Query $q -ServerInstance $serverInstance -Database $dbName -Username $dbUser -Password $dbPassword -ErrorAction StopI encounter an issue that some query returns millions of records, such that it takes long time for the script to run and return them all to my workstation through the network. Can you help me to find a way to speed it up? I only need the script to check whether there is data return, and don't care how many.
I tried some ways but they didn't seem to work:
1. tried parameters in Invoke-Sqlcmd (MaxBinaryLength and MaxCharLength) and hope they can limmit the return length, but doesn't work
2. try to add "SELECT TOP 1 * from () as x" around the original T-SQL query, but this does not work for SELECT statement having "WITH" clause
Thank you!
Ryan Chong
what's the value of $q ?
You won't find a simple solution for "WITH" because "WITH" must precede all other SQL commands.
For those without a CTE your "select TOP(1) from ( ... ) x" would work
or perhaps; SELECT COUNT(*) from ( $q ) x
----
for those using "WITH" you need to modify the query AFTER the CTE has been defined, e.g.
with myCTE as (select ...)
select * from myCTE
would need to become
with myCTE as (select ...)
select top(1) from (
select * from myCTE
) x
For those without a CTE your "select TOP(1) from ( ... ) x" would work
or perhaps; SELECT COUNT(*) from ( $q ) x
----
for those using "WITH" you need to modify the query AFTER the CTE has been defined, e.g.
with myCTE as (select ...)
select * from myCTE
would need to become
with myCTE as (select ...)
select top(1) from (
select * from myCTE
) x
Does prepending
set rowcount 1
work?
set rowcount 1
work?
ASKER
$q is a variable used in a loop, it can be any kind of "SELECT" statement including the ones with "WITH" clause
Again, but more clearly: Does this work?
$queryResult = Invoke-Sqlcmd -Query "set rowcount 1;`n$q" -ServerInstance $serverInstance -Database $dbName -Username $dbUser -Password $dbPassword -ErrorAction Stop
good thought!
https://msdn.microsoft.com/en-us/library/ms188774.asp
NB: works for SELECT, but not INSERT/UPDATE/DELETE
https://msdn.microsoft.com/en-us/library/ms188774.asp
NB: works for SELECT, but not INSERT/UPDATE/DELETE
Paul, the link is invalid.
ASKER
Qlemo:
the Invoke-Sqlcmd -Query command will still take long time to execute because there is no error.
the Invoke-Sqlcmd -Query command will still take long time to execute because there is no error.
If it still lasts very long, there is no effective query execution plan even if restricting the number of rows to get. There is no way to get around that but to change the SQL itself to e.g. some "Select 1 where exists ( $q )", which might work much better (but does not allow for WITH, for the reasons mentioned by Paul).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For future reference as an answered question could you share a powershell snippet that uses the .Net SqlCommand and a WITH CTE?
(that url above does not)
(that url above does not)
ASKER
I implemented this method and it works well.