display ony SQL time taken to retrieve info but not display data

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

display ony SQL time taken to retrieve info but not display data

Emanuel.Chiavegato
Hi,

   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.

Many thanks

Emanuel Chiavegato


***********************************************************************************
The Royal Bank of Scotland plc. Registered in Scotland No 90312.
Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
Authorised by the Prudential Regulation Authority and regulated
by the Financial Conduct Authority and Prudential Regulation Authority.
The Royal Bank of Scotland N.V. is authorised and regulated by the
De Nederlandsche Bank and has its seat at Amsterdam, the
Netherlands, and is registered in the Commercial Register under
number 33002587. Registered Office: Gustav Mahlerlaan 350,
Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and
The Royal Bank of Scotland plc are authorised to act as agent for each
other in certain jurisdictions.
 
This e-mail message is confidential and for use by the addressee only.
If the message is received by anyone other than the addressee, please
return the message to the sender by replying to it and then delete the
message from your computer. Internet e-mails are not necessarily
secure. The Royal Bank of Scotland plc and The Royal Bank of Scotland
N.V. including its affiliates ("RBS group") does not accept responsibility
for changes made to this message after it was sent. For the protection
of RBS group and its clients and customers, and in compliance with
regulatory requirements, the contents of both incoming and outgoing
e-mail communications, which could include proprietary information and
Non-Public Personal Information, may be read by authorised persons
within RBS group other than the intended recipient(s).

Whilst all reasonable care has been taken to avoid the transmission of
viruses, it is the responsibility of the recipient to ensure that the onward
transmission, opening or use of this message and any attachments will
not adversely affect its systems or data. No responsibility is accepted
by the RBS group in this regard and the recipient should carry out such
virus and other checks as it considers appropriate.

Visit our website at www.rbs.com
***********************************************************************************  
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Squirrel-sql-develop mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
Reply | Threaded
Open this post in threaded view
|

Re: display ony SQL time taken to retrieve info but not display data

John Hardin
On Mon, 9 Dec 2013, [hidden email] 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/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  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

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Squirrel-sql-develop mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop