February 20, 2015 at 5:40 am
Hi,
Sorry for posting such a trifle question, but couldn't find a sufficient answer.
I am UK based and have a customer with SQL Server installed in Australia. There is network infrastructure in place, which lets me to connect
AU server from my Studio - all is well.
I would like to know how my SQL queries work in terms of bandwidth consumption and will be much obliged if one can point me to some relevant literature, please.
For example, SELECT * FROM myTable returns 1000 records. I would like to know if those 1,000 records have been cashed first on AU server and then all in once brought to
my SQL or there are patches of data going to and for all the time?
Or in the following example for instance, will all the query performed on DB Server or partially? Will I get ten thousands record on my computer and then SELECT TOP 1
will be performed locally on the cashed data?
WITH resultSet AS (
SELECT ten thousands records
FROM myTable
WHERE multiple conditions)
SELECT TOP 1 * FROM resultSet
Much obliged!
February 20, 2015 at 6:14 am
The query will return what the query returns. So, in your first example, all the 1,000 rows are loaded into memory in Australia and then they're sent over the wire as fast as they can to you. In your second example, the processing will occur in australia and you'll get one row sent across to you. Nothing will be cached locally on your machine except for the actual results sent. The bandwidth consumed will all be based on the query you send and the results returned. Processing all occurs in Australia.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2015 at 6:34 am
Thank you, Grant
Is it correct to assume that SQL Studio will ALWAYS only send a request, e.g. EXEC stored procedure, and receive the result and all the queries and calculations will be
performed on SQL Server?
Regards
February 20, 2015 at 7:29 am
Yeah, the queries run on the server. Filtering, ordering, etc., that are defined within the query, will all be done on the server. Now, once you have the result set, if you do things with it from within Management Studio, that processing is local. But that's after the query is run.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2015 at 7:47 am
Grant Fritchey (2/20/2015)
Yeah, the queries run on the server. Filtering, ordering, etc., that are defined within the query, will all be done on the server. Now, once you have the result set, if you do things with it from within Management Studio, that processing is local. But that's after the query is run.
Sorry, can you elaborate, what did you mean by "if you do things with it from within Management Studio, that processing is local", please? obviously if I copy / paste it to anywhere it will be local, as it is our of SQL scope, but anything within SQL scope?
Kind Regards
February 20, 2015 at 7:52 am
BOR15K (2/20/2015)
Grant Fritchey (2/20/2015)
Yeah, the queries run on the server. Filtering, ordering, etc., that are defined within the query, will all be done on the server. Now, once you have the result set, if you do things with it from within Management Studio, that processing is local. But that's after the query is run.Sorry, can you elaborate, what did you mean by "if you do things with it from within Management Studio, that processing is local", please? obviously if I copy / paste it to anywhere it will be local, as it is our of SQL scope, but anything within SQL scope?
Kind Regards
Right. I mean copy & paste, or search within a result set in the grid, rearranging columns in the grid. All that sort of thing.
Queries go to the server. That's how it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2015 at 8:39 am
Regardless of where the remote server is located, so long as there is a LAN connection between, then latency is not an issue when pulling across hundreds or a few thousand rows.
If you're doing large ETL batches between local and remote servers, then performance issues will present themselves, but it's manageable.
However, network performance will be a serious problem when running distributed queries, meaning a SQL query that joins local tables and remote tables using 4 part naming convention.
http://sqlbits.com/Sessions/Event10/Distributed_Query_Deep_Dive
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 20, 2015 at 8:47 am
Thank you, Eric
The DB is in AU and only SQL Studio is on my machine, which is in the UK.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply