February 9, 2012 at 9:49 am
there is a complex SELECT query, that takes long to run, and returns lot of rows.
i want to find the query's :
- execution time ( STATISTICS TIME ),
- row count ( @@RowCount )
but i don't want it to :
- show the results in SSMS
- insert results into #tmp
- write results to file
because :
- there is too much data.
- also, these distort query time, by including network-transfer / disk-writing time, right?
simple e.g.
set statistics time on
select *
--into #tmp
from master..spt_values
where type = 'P'
select @@rowcount
i don't want the results - they'll be used later by a bigger query/SP that uses this query.
just want it execute the Joins & Where clauses etc. but discard the results for now.
Note: SET FMTONLY doesn't work because BOL says it doesn't RUN the query.
February 9, 2012 at 9:58 am
Anything wrong with using a variable as a holder?
e.g.
DECLARE @holderValue VARCHAR(MAX)
PRINT REPLICATE('-', 80)
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @holderValue = number /*use primary key*/
FROM master..spt_values
WHERE type = 'P'
PRINT REPLICATE('-', 80)
PRINT 'RowCount : ' + CAST(@@ROWCOUNT AS VARCHAR(10))
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
DECLARE @holderValue VARCHAR(MAX)
PRINT REPLICATE('-', 80)
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @holderValue = number /*use primary key*/
FROM master..spt_values
WHERE type = 'P'
PRINT REPLICATE('-', 80) + CHAR(13) + CHAR(10) +'RowCount : ' + CAST(@@ROWCOUNT AS VARCHAR(10))
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
February 9, 2012 at 11:48 am
that was just a simple query example.
but for a complex one with functions:
select x,y,z, min(..), max(..), substring(..)
from ...
?
February 9, 2012 at 12:28 pm
I'm not sure if I understand correctly what you're looking for.
If it's just to avoid to display the data in SSMS, you could use the following approach:
open a new query window and change the setting for that window using Query-> Query Options->Results->Discard Results...
Then run your code wrapped by some code to capture rowcount and duration:
DECLARE @dat DATETIME,
@cnt BIGINT
SET @dat = GETDATE()
SELECT number /*use primary key*/
FROM master..spt_values
WHERE type = 'P'
SET @cnt= @@rowcount
SELECT DATEDIFF(ms,@dat,GETDATE()) dat, @cnt cnt
INTO ##results
--DROP TABLE ##results
In a new query window, run
SELECT *
FROM ##results
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply