Read MS Sql query high latency w.r.t. Remote Query

I'm trying to read a table in a MS SQL Server using python, specifically SQLalchemy, pymssql, and pandas.read_sql. I want to execute the query, put the results into a pandas Dataframe, and continue on with my work. The sql database I'm accessing is a part of the Wonderware product line, is composed of more than 700 tags over a time period of 2 years.

I want to be able to read a simple query such as the following:

sql_query = '''

SELECT DateTime, TagName, Value



    FROM Runtime.dbo.AnalogHistory



    WHERE

        DateTime BETWEEN '2014-05-26 00:00' AND '2014-06-26 00:00'

    AND 

        TagName IN (

            'dPC503.ActualValue', 

            'dPC504.ActualValue')

    AND

        wwRetrievalMode = 'Cyclic'

    AND

        wwResolution = 5000

'''

The query results in a dataframe object about 100 MB in size and takes a little under 10 min (423 s)

If my math is correct, 100 MB in 423 seconds is about 230 kB/s, which I think over an Ethernet connection is just painfully slow. The first question I have is, where is this speed going?

I posted a related question here on stackexchange.com. Someone commented on my post and told me to look into the Execution plan - I'm just a beginner and have no clue what to do with this information.

Running the query on a desktop with SSMS installed resulted in the same query runtime. After looking into it, I see that the execution plan is broken into 3 parts, heavily favoring the remote query:

SELECT - Cost: 0%

Compute Scalar - Cost: 0%

Remote Query - Cost: 100%

screenshot

Opening up the Remote Query execution plan into an xml file results in this file snippet.

I'm not sure what this is telling me. The gentlemen who kindly commented on my previous question and asked/gave me some helpful questions/pointers that I hope someone here would be able to expand on. The following are from him, and my follow up questions are in italics:

Which indices are defined for the Runtime.dbo.AnalogHistory table and the execution plan for this query? How do I determine this?

I think when you executed exactly the same query a few times the vast majority of the data pages needed for the result set were already in the MS SQL Server buffer pool and because of that 100% of the cost is in the final 'Remote Query' step, where it 'Sends a sql query to another than the current sql server', otherwise it would have to read data from disk, which is usually the slowest part (unless you have an extremely slow network). So, anyway you would have to check the execution plan. Where in the execution plan is this information hiding?

edited May 23 '17 at 12:40

Community♦

asked Jun 20 '16 at 19:28

cbcoutinho

1034

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

A remote query is accessing a remote server, such as a linked server. Your slowness is over the linked server. Can you run your query on the source server?

– Arthur D
Jun 20 '16 at 20:05

I don't think I have access to a source server. As far I understand it, Wonderware has a proprietary database format that it stores and gives access to via MSSQL using some special API. I think what's happening is Wonderware is 'constructing' the table asked for by the query, subject to various data retrieval options. More info on available options can be read here. I think the construction of the table itself is taking all of the time, not the transferring of the data.

– cbcoutinho
Jun 20 '16 at 20:31

How do you notice that the remote query is accessing a separate server?

– cbcoutinho
Jun 20 '16 at 20:31

I'm afraid I'm very out of my depth when it comes to Wonderware. Here is the TechNet article for the Remote Query operator: technet.microsoft.com/en-us/library/ms189297(v=sql.105).aspx It looks like it is not a linked server then, just the Wonderware application. I have no idea why the query takes long to return from Wonderware.

– Arthur D
Jun 20 '16 at 20:37

add a comment |

I want to be able to read a simple query such as the following:

sql_query = '''

SELECT DateTime, TagName, Value



    FROM Runtime.dbo.AnalogHistory



    WHERE

        DateTime BETWEEN '2014-05-26 00:00' AND '2014-06-26 00:00'

    AND 

        TagName IN (

            'dPC503.ActualValue', 

            'dPC504.ActualValue')

    AND

        wwRetrievalMode = 'Cyclic'

    AND

        wwResolution = 5000

'''

The query results in a dataframe object about 100 MB in size and takes a little under 10 min (423 s)

If my math is correct, 100 MB in 423 seconds is about 230 kB/s, which I think over an Ethernet connection is just painfully slow. The first question I have is, where is this speed going?

Running the query on a desktop with SSMS installed resulted in the same query runtime. After looking into it, I see that the execution plan is broken into 3 parts, heavily favoring the remote query:

SELECT - Cost: 0%

Compute Scalar - Cost: 0%

Remote Query - Cost: 100%

screenshot

Opening up the Remote Query execution plan into an xml file results in this file snippet.

Which indices are defined for the Runtime.dbo.AnalogHistory table and the execution plan for this query? How do I determine this?

I think when you executed exactly the same query a few times the vast majority of the data pages needed for the result set were already in the MS SQL Server buffer pool and because of that 100% of the cost is in the final 'Remote Query' step, where it 'Sends a sql query to another than the current sql server', otherwise it would have to read data from disk, which is usually the slowest part (unless you have an extremely slow network). So, anyway you would have to check the execution plan. Where in the execution plan is this information hiding?

edited May 23 '17 at 12:40

Community♦

asked Jun 20 '16 at 19:28

cbcoutinho

1034

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

A remote query is accessing a remote server, such as a linked server. Your slowness is over the linked server. Can you run your query on the source server?

– Arthur D
Jun 20 '16 at 20:05

I don't think I have access to a source server. As far I understand it, Wonderware has a proprietary database format that it stores and gives access to via MSSQL using some special API. I think what's happening is Wonderware is 'constructing' the table asked for by the query, subject to various data retrieval options. More info on available options can be read here. I think the construction of the table itself is taking all of the time, not the transferring of the data.

– cbcoutinho
Jun 20 '16 at 20:31

How do you notice that the remote query is accessing a separate server?

– cbcoutinho
Jun 20 '16 at 20:31

I'm afraid I'm very out of my depth when it comes to Wonderware. Here is the TechNet article for the Remote Query operator: technet.microsoft.com/en-us/library/ms189297(v=sql.105).aspx It looks like it is not a linked server then, just the Wonderware application. I have no idea why the query takes long to return from Wonderware.

– Arthur D
Jun 20 '16 at 20:37

add a comment |

I want to be able to read a simple query such as the following:

sql_query = '''

SELECT DateTime, TagName, Value



    FROM Runtime.dbo.AnalogHistory



    WHERE

        DateTime BETWEEN '2014-05-26 00:00' AND '2014-06-26 00:00'

    AND 

        TagName IN (

            'dPC503.ActualValue', 

            'dPC504.ActualValue')

    AND

        wwRetrievalMode = 'Cyclic'

    AND

        wwResolution = 5000

'''

The query results in a dataframe object about 100 MB in size and takes a little under 10 min (423 s)

If my math is correct, 100 MB in 423 seconds is about 230 kB/s, which I think over an Ethernet connection is just painfully slow. The first question I have is, where is this speed going?

Running the query on a desktop with SSMS installed resulted in the same query runtime. After looking into it, I see that the execution plan is broken into 3 parts, heavily favoring the remote query:

SELECT - Cost: 0%

Compute Scalar - Cost: 0%

Remote Query - Cost: 100%

screenshot

Opening up the Remote Query execution plan into an xml file results in this file snippet.

Which indices are defined for the Runtime.dbo.AnalogHistory table and the execution plan for this query? How do I determine this?

I think when you executed exactly the same query a few times the vast majority of the data pages needed for the result set were already in the MS SQL Server buffer pool and because of that 100% of the cost is in the final 'Remote Query' step, where it 'Sends a sql query to another than the current sql server', otherwise it would have to read data from disk, which is usually the slowest part (unless you have an extremely slow network). So, anyway you would have to check the execution plan. Where in the execution plan is this information hiding?

edited May 23 '17 at 12:40

Community♦

asked Jun 20 '16 at 19:28

cbcoutinho

1034

I want to be able to read a simple query such as the following:

sql_query = '''

SELECT DateTime, TagName, Value



    FROM Runtime.dbo.AnalogHistory



    WHERE

        DateTime BETWEEN '2014-05-26 00:00' AND '2014-06-26 00:00'

    AND 

        TagName IN (

            'dPC503.ActualValue', 

            'dPC504.ActualValue')

    AND

        wwRetrievalMode = 'Cyclic'

    AND

        wwResolution = 5000

'''

The query results in a dataframe object about 100 MB in size and takes a little under 10 min (423 s)

If my math is correct, 100 MB in 423 seconds is about 230 kB/s, which I think over an Ethernet connection is just painfully slow. The first question I have is, where is this speed going?

Running the query on a desktop with SSMS installed resulted in the same query runtime. After looking into it, I see that the execution plan is broken into 3 parts, heavily favoring the remote query:

SELECT - Cost: 0%

Compute Scalar - Cost: 0%

Remote Query - Cost: 100%

screenshot

Opening up the Remote Query execution plan into an xml file results in this file snippet.

Which indices are defined for the Runtime.dbo.AnalogHistory table and the execution plan for this query? How do I determine this?

I think when you executed exactly the same query a few times the vast majority of the data pages needed for the result set were already in the MS SQL Server buffer pool and because of that 100% of the cost is in the final 'Remote Query' step, where it 'Sends a sql query to another than the current sql server', otherwise it would have to read data from disk, which is usually the slowest part (unless you have an extremely slow network). So, anyway you would have to check the execution plan. Where in the execution plan is this information hiding?

sql-server remote

edited May 23 '17 at 12:40

Community♦

asked Jun 20 '16 at 19:28

cbcoutinho

1034

edited May 23 '17 at 12:40

Community♦

asked Jun 20 '16 at 19:28

cbcoutinho

1034

edited May 23 '17 at 12:40

Community♦

edited May 23 '17 at 12:40

Community♦

edited May 23 '17 at 12:40

Community♦

asked Jun 20 '16 at 19:28

cbcoutinho

1034

asked Jun 20 '16 at 19:28

cbcoutinho

1034

asked Jun 20 '16 at 19:28

cbcoutinho

1034

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

A remote query is accessing a remote server, such as a linked server. Your slowness is over the linked server. Can you run your query on the source server?

– Arthur D
Jun 20 '16 at 20:05

I don't think I have access to a source server. As far I understand it, Wonderware has a proprietary database format that it stores and gives access to via MSSQL using some special API. I think what's happening is Wonderware is 'constructing' the table asked for by the query, subject to various data retrieval options. More info on available options can be read here. I think the construction of the table itself is taking all of the time, not the transferring of the data.

– cbcoutinho
Jun 20 '16 at 20:31

How do you notice that the remote query is accessing a separate server?

– cbcoutinho
Jun 20 '16 at 20:31

I'm afraid I'm very out of my depth when it comes to Wonderware. Here is the TechNet article for the Remote Query operator: technet.microsoft.com/en-us/library/ms189297(v=sql.105).aspx It looks like it is not a linked server then, just the Wonderware application. I have no idea why the query takes long to return from Wonderware.

– Arthur D
Jun 20 '16 at 20:37

add a comment |

A remote query is accessing a remote server, such as a linked server. Your slowness is over the linked server. Can you run your query on the source server?

– Arthur D
Jun 20 '16 at 20:05

I don't think I have access to a source server. As far I understand it, Wonderware has a proprietary database format that it stores and gives access to via MSSQL using some special API. I think what's happening is Wonderware is 'constructing' the table asked for by the query, subject to various data retrieval options. More info on available options can be read here. I think the construction of the table itself is taking all of the time, not the transferring of the data.

– cbcoutinho
Jun 20 '16 at 20:31

How do you notice that the remote query is accessing a separate server?

– cbcoutinho
Jun 20 '16 at 20:31

I'm afraid I'm very out of my depth when it comes to Wonderware. Here is the TechNet article for the Remote Query operator: technet.microsoft.com/en-us/library/ms189297(v=sql.105).aspx It looks like it is not a linked server then, just the Wonderware application. I have no idea why the query takes long to return from Wonderware.

– Arthur D
Jun 20 '16 at 20:37

A remote query is accessing a remote server, such as a linked server. Your slowness is over the linked server. Can you run your query on the source server?

– Arthur D
Jun 20 '16 at 20:05

I don't think I have access to a source server. As far I understand it, Wonderware has a proprietary database format that it stores and gives access to via MSSQL using some special API. I think what's happening is Wonderware is 'constructing' the table asked for by the query, subject to various data retrieval options. More info on available options can be read here. I think the construction of the table itself is taking all of the time, not the transferring of the data.

– cbcoutinho
Jun 20 '16 at 20:31

How do you notice that the remote query is accessing a separate server?

– cbcoutinho
Jun 20 '16 at 20:31

I'm afraid I'm very out of my depth when it comes to Wonderware. Here is the TechNet article for the Remote Query operator: technet.microsoft.com/en-us/library/ms189297(v=sql.105).aspx It looks like it is not a linked server then, just the Wonderware application. I have no idea why the query takes long to return from Wonderware.

– Arthur D
Jun 20 '16 at 20:37

add a comment |

1 Answer
1

active

oldest

votes

Create a copy of the table into a #temporary table on the server you are running the query on, then run the query based off the temp table.

I am not an expert on why certain queries run slower over linked servers and others do not. However, if you move that data temporarily over to the server you are running the query on, it often makes it run faster. Someone more experienced can answer why this is the case.

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

Thanks for the tip @agenovese, I'll try it out this week

– cbcoutinho
Jun 21 '16 at 13:10

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f141752%2fread-ms-sql-query-high-latency-w-r-t-remote-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Create a copy of the table into a #temporary table on the server you are running the query on, then run the query based off the temp table.

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

Thanks for the tip @agenovese, I'll try it out this week

– cbcoutinho
Jun 21 '16 at 13:10

add a comment |

Create a copy of the table into a #temporary table on the server you are running the query on, then run the query based off the temp table.

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

Thanks for the tip @agenovese, I'll try it out this week

– cbcoutinho
Jun 21 '16 at 13:10

add a comment |

Create a copy of the table into a #temporary table on the server you are running the query on, then run the query based off the temp table.

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

Create a copy of the table into a #temporary table on the server you are running the query on, then run the query based off the temp table.

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

answered Jun 20 '16 at 20:48

Anthony Genovese

1,6672924

Thanks for the tip @agenovese, I'll try it out this week

– cbcoutinho
Jun 21 '16 at 13:10

add a comment |

Thanks for the tip @agenovese, I'll try it out this week

– cbcoutinho
Jun 21 '16 at 13:10

Thanks for the tip @agenovese, I'll try it out this week

– cbcoutinho
Jun 21 '16 at 13:10

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vryjdfkk