Re: [Squirrel-sql-develop] display ony SQL time taken to retrieve info but not display data

On Mon, 9 Dec 2013, Ema...@rb... wrote:

>
>   I run some simple Select queries against my datasources. Some of the
>   queries return too much data but as I am working on performance tuning
>   I am only interested in the time that it took to retrieve all rows and
>   not in the data itself.
>
>   So I would like to know if it is possible to get only the time taken
>   to return all the rows from a SQL query against my datasources without
>   bringing back any data??
>
>   Any ideas would be a great help - either with a command to be added in
>   my SQL Select statement or just pointing where in the SQuirreL java
>   code I could change to dispose the data.

Are you trying to measure the server performance or the network 
performance?

If you're trying to measure the server performance, select into a temp 
table. This is a little overstated because you are measuring the overhead 
of writing the rows to the temp table in addition to querying the rows 
from the source table(s).


MSSQL example:

 	SELECT
 	  ...
 	INTO @t_x
 	FROM
 	  ...
 	;

 	DROP TABLE @t_x;

I think that running a query in a cursor loop that simply ignores the rows 
may avoid that overhead but it probably introduces cursor-related overhead 
of its own.

I'm not sure if there's another more-efficient way to run a query and 
simply discard the results within the engine.


If you're trying to measure network performance there's really no way to 
avoid bringing back the data. However, you don't need to *keep* the data 
once it's been retrieved. I suggested earlier that there is a mechanism 
within Squirrel to redirect query output to a file. If you redirect the 
query output to the *nix file "/dev/null" or the Windows file "nul:" it 
will simply be discarded. Your timing will measure who long it takes to 
run the query and return the rows to the client, but you don't have to see 
the data, and there shouldn't be any timing impact from allocating local 
storage for the rowset or formatting for display.


Also: make sure you uncheck the rowlimit option in the query pane.

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jh...@im...    FALaholic #11174     pgpk -a jh...@im...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   "Bother," said Pooh as he struggled with /etc/sendmail.cf, "it never
   does quite what I want. I wish Christopher Robin was here."
                                            -- Peter da Silva in a.s.r
-----------------------------------------------------------------------
  7 days until Christmas