SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Speed up Openquery


Speed up Openquery

Author
Message
onemangathers
onemangathers
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 42
Can you take a look at the following Openquery to see if there is a possible way to speed it up. It currently takes about 3 to 4 minutes. Thanks in advance. Apologies for no line continuation. I'm very new at this.
SELECT * FROM OPENQUERY(SAASUFFOLK,'SELECT bd.equipment_mac,bd.account_number,SUM(cdf.SUM_BYTES_UP),SUM(cdf.SUM_BYTES_DOWN),SUM(cdf.SUM_RESET_COUNT),AVG(cdf.AVG_TXPOWER_UP),AVG(cdf.AVG_RXPOWER_DOWN),AVG(cdf.AVG_RXPOWER_UP),AVG(cdf.AVG_PATH_LOSS_UP),MAX(cdf.MAX_CER_DOWN),MAX(cdf.MAX_CCER_DOWN),MIN(cdf.MIN_SNR_DOWN),MAX(cdf.US_CER_MAX),MAX(cdf.US_CCER_MAX),MIN(cdf.US_SNR_MIN),AVG(cdf.status_value_min),SUM(cdf.T3_TIMEOUTS),SUM (cdf.T4_TIMEOUTS),MAX(cdf.SYSUPTIME),bd.first_name,bd.last_name,bd.street_number,bd.street_name,bd.city,bd.custom_field_1,bd.phone FROM billing_data bd JOIN billing_device_mapping bdm ON bdm.equipment_model = bd.equipment_model AND bdm.device_type = ''CM'' LEFT OUTER JOIN topology_node tn ON bd.equipment_mac = tn.macaddr LEFT OUTER JOIN cm_hour_facts cdf ON tn.topologyid = cdf.cm_id AND cdf.hour_stamp >= SYSDATE-1 WHERE(ACCOUNT_NUMBER=''784000451402'' OR ACCOUNT_NUMBER=''783933175603'' OR ACCOUNT_NUMBER=''783933118803'' OR ACCOUNT_NUMBER=''783933131902'' OR ACCOUNT_NUMBER=''783937901101''
OR ACCOUNT_NUMBER=''783933119401'' OR ACCOUNT_NUMBER=''783933152602'' OR ACCOUNT_NUMBER=''783933166103'' OR ACCOUNT_NUMBER=''783933115701'' OR ACCOUNT_NUMBER=''0'')GROUP BY EQUIPMENT_MAC,bd.account_number,bd.first_name,bd.last_name,bd.street_number,bd.street_name,bd.city,bd.custom_field_1,bd.phone')

bmg002
bmg002
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12910 Visits: 2369
How slow is the query if you run it directly on the server instead of through openquery?
and what if you use a linked server connection and just use the 4-part naming on all of your select objects?

To me, I think the slowness in this is all of the aggregates in there.
For those others who are looking, this is the query that is being run with the OPENQUERY stuff removed:
SELECT
[bd].[equipment_mac] ,
[bd].[account_number] ,
SUM([cdf].[SUM_BYTES_UP]) ,
SUM([cdf].[SUM_BYTES_DOWN]) ,
SUM([cdf].[SUM_RESET_COUNT]) ,
AVG([cdf].[AVG_TXPOWER_UP]) ,
AVG([cdf].[AVG_RXPOWER_DOWN]) ,
AVG([cdf].[AVG_RXPOWER_UP]) ,
AVG([cdf].[AVG_PATH_LOSS_UP]) ,
MAX([cdf].[MAX_CER_DOWN]) ,
MAX([cdf].[MAX_CCER_DOWN]) ,
MIN([cdf].[MIN_SNR_DOWN]) ,
MAX([cdf].[US_CER_MAX]) ,
MAX([cdf].[US_CCER_MAX]) ,
MIN([cdf].[US_SNR_MIN]) ,
AVG([cdf].[status_value_min]) ,
SUM([cdf].[T3_TIMEOUTS]) ,
SUM([cdf].[T4_TIMEOUTS]) ,
MAX([cdf].[SYSUPTIME]) ,
[bd].[first_name] ,
[bd].[last_name] ,
[bd].[street_number] ,
[bd].[street_name] ,
[bd].[city] ,
[bd].[custom_field_1] ,
[bd].[phone]
FROM
[billing_data] [bd]
JOIN [billing_device_mapping] [bdm]
ON [bdm].[equipment_model] = [bd].[equipment_model]
AND [bdm].[device_type] = 'CM'
LEFT OUTER JOIN [topology_node] [tn]
ON [bd].[equipment_mac] = [tn].[macaddr]
LEFT OUTER JOIN [cm_hour_facts] [cdf]
ON [tn].[topologyid] = [cdf].[cm_id]
AND [cdf].[hour_stamp] >= [SYSDATE] - 1
WHERE
(
[ACCOUNT_NUMBER] = '784000451402'
OR [ACCOUNT_NUMBER] = '783933175603'
OR [ACCOUNT_NUMBER] = '783933118803'
OR [ACCOUNT_NUMBER] = '783933131902'
OR [ACCOUNT_NUMBER] = '783937901101'
OR [ACCOUNT_NUMBER] = '783933119401'
OR [ACCOUNT_NUMBER] = '783933152602'
OR [ACCOUNT_NUMBER] = '783933166103'
OR [ACCOUNT_NUMBER] = '783933115701'
OR [ACCOUNT_NUMBER] = '0'
)GROUP BY
[EQUIPMENT_MAC] ,
[bd].[account_number] ,
[bd].[first_name] ,
[bd].[last_name] ,
[bd].[street_number] ,
[bd].[street_name] ,
[bd].[city] ,
[bd].[custom_field_1] ,
[bd].[phone]


side note - SQL Prompt made the above code pop out super quick and easy to do. I don't work for them, but I enjoy their tools.

onemangathers
onemangathers
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 42
Direct on the server in SQL Developer its only about 30 seconds quicker. SQL server with the query above takes 3-4 minutes. This query is running inside ADO connection VBA Excel ,the response is about the same amount of time. I'm not sure if I said that all right. I don't understand the last part of your question in regards to the 4 part naming. Are you referring to the aliases just?
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79873 Visits: 17904
onemangathers - Wednesday, April 12, 2017 4:53 PM
I don't understand the last part of your question in regards to the 4 part naming. Are you referring to the aliases just?

Do away with the OPENQUERY and rewrite the query - the FROM clause would start like this:

FROM SAASUFFOLK.MyDatabase.MySchema.billing_data bd
JOIN SAASUFFOLK.MyDatabase.MySchema.billing_device_mapping bd
...


Here are a couple of tips that won't make your query run any faster but will make your code more readable and resilient:
(1) Get rid of those square brackets. They're not necessary and they make the code harder to read. Only use them for identifiers that are reserved keywords or that contain special characters (which, if your database was properly designed, won't happen).
(2) Alias all of your columns. There's nothing more irritating than having to scan through someone else's code trying to find which table the ACCOUNT_NUMBER belongs to. Worse, and I have seen this happen, if a column of the same name is added to another table in your query, the code will break.
(3) Lay out your code so that it's readable, like bmg002 showed you. If you don't care about your code, why should anyone else? You can format it manually or with a tool such as the one he mentioned. That horrendous line with no spaces in it has messed up the line breaks on this page.

Finally, please will you post the actual execution plan for this query when you run it direct on the remote server?

Edit - I notice you posted in the Working with Oracle forum. Is SAASUFFOLK an Oracle server?

John

Thomas Rushton
Thomas Rushton
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11787 Visits: 6185
bmg002 - Wednesday, April 12, 2017 3:37 PM

side note - SQL Prompt made the above code pop out super quick and easy to do. I don't work for them, but I enjoy their tools.


+1 for SQLPrompt; however, if you (or your company) doesn't run to licences for that, then try the free site http://poorsql.com/ to do query code formatting.

onemangathers
onemangathers
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 42
Thank you for the helpful tips SSChampion. I do care about my code, very much so. It has been a rough year learning as I go. The company I work for is all over the place with different languages and servers.
I spend what free time I have taking online tutorials and this old brain of mine is absorbing like a sponge treated with water repellent. I am stuck in the middle of having to pull data from MySQL, Sql Server, Oracle, Mongo, Linux, and a whole host of random APIs via Soap.
Along with coders that like VB, Python, Perl and GoogleGo. It's all starting to mash together and I'm hitting a wall. I can't keep waking up with keyboard imprints on my forehead. But people keep dropping like flies over here and fear I may be soon.
I can hear the little violins playing, so I'll stop. I appreciate the add in recommendation for making the code look readable, but the company won't swing for the cost.
To answer your question, no, SAASUFFOLK is one database running on a Linux server that I view on my server through Oracle SQL Developer and or Microsoft SQL Server 2014 Management Studio.
I need to build macro enabled Excel sheets for departments that don't have Oracle installed on there machines. So I am stuck in VBA building CommandText with fingers crossed. Hence the long garbled query (I couldn't figure out the line continuation in VBA with a string).
Here is the full code minus server info. I don't know why this site replaced ":" with a smiley face.
Public Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Adds windows spinning wheel while data is updating
Application.ScreenUpdating = True
Sheets("Accounts").Cells.Clear


Dim Server_IP As String
Dim User_ID As String
Dim Password As String

Server_IP = " xxxx "
User_ID = " xxxx "
Password = " xxxx "

Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
Dim QueryEnd As String
'The completed Connection string will look like this objMyConn.ConnectionString = Provider=SQLOLEDB;Data Source= xxxxx ;UserID= roc;Password= xxxxx ;
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & Server_IP & ";User ID=" & User_ID & ";Password=" & Password & ";"
objMyConn.Open

Set objMyCmd.ActiveConnection = objMyConn

Dim AcctARRAY() As Variant
'Range where account numbers are entered in
AcctARRAY = Range("C6:C15")

For R = 1 To UBound(AcctARRAY, 1)
If R = 1 Then
QueryEnd = "WHERE(ACCOUNT_NUMBER=''" & AcctARRAY(R, 1) & "''"
Else
QueryEnd = QueryEnd & " OR ACCOUNT_NUMBER=''" & AcctARRAY(R, 1) & "''"
End If
Next R


QueryEnd = QueryEnd & ")"


'Declare varible to insert the ListBox selection in to the Command String
Dim Instance As String
Instance = Me.ListBox1.Value


objMyCmd.CommandText = "SELECT * FROM OPENQUERY(" & Instance & "," _
& "'SELECT bd.account_number,bd.equipment_mac, " _
& " SUM(cdf.SUM_BYTES_UP), " _
& " SUM(cdf.SUM_BYTES_DOWN), " _
& " SUM(cdf.SUM_RESET_COUNT), " _
& " AVG(cdf.AVG_TXPOWER_UP), " _
& " AVG(cdf.AVG_RXPOWER_DOWN), " _
& " AVG(cdf.AVG_RXPOWER_UP), " _
& " AVG(cdf.AVG_PATH_LOSS_UP), " _
& " MAX(cdf.MAX_CER_DOWN), " _
& " MAX(cdf.MAX_CCER_DOWN), " _
& " MIN(cdf.MIN_SNR_DOWN), " _
& " MAX(cdf.US_CER_MAX), " _
& " MAX(cdf.US_CCER_MAX), " _
& " MIN(cdf.US_SNR_MIN), " _
& " SUM(cdf.T3_TIMEOUTS), " _
& " SUM (cdf.T4_TIMEOUTS), " _
& " MAX(cdf.SYSUPTIME), " _
& " bd.first_name, " _
& " bd.last_name, " _
& " bd.street_number, " _
& " bd.street_name, " _
& " bd.city, " _
& " bd.custom_field_1, " _
& " bd.phone FROM billing_data bd JOIN billing_device_mapping bdm ON bdm.equipment_model = bd.equipment_model AND bdm.device_type = ''CM'' LEFT OUTER JOIN topology_node tn ON bd.equipment_mac = tn.macaddr LEFT OUTER JOIN cm_hour_facts cdf ON tn.topologyid = cdf.cm_id AND cdf.hour_stamp >= SYSDATE-1 " & QueryEnd & "GROUP BY EQUIPMENT_MAC,bd.account_number,bd.first_name,bd.last_name,bd.street_number,bd.street_name,bd.city,bd.custom_field_1,bd.phone')"


Debug.Print objMyCmd.CommandText
objMyCmd.CommandType = adCmdText
objMyCmd.Execute


Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd

'Declare Header Names
With Sheets("Accounts")

With .Cells
.Cells(1, 1).Value = "ACCOUNT NUMBER"
.Cells(1, 2).Value = "EQUIPMENT_MAC"
.Cells(1, 3).Value = "SUM_BYTES_UP"
.Cells(1, 4).Value = "SUM_BYTES_DOWN"
.Cells(1, 5).Value = "SUM_RESET_COUNT"
.Cells(1, 6).Value = "AVG_TXPOWER_UP"
.Cells(1, 7).Value = "AVG_RXPOWER_DOWN"
.Cells(1, 8).Value = "AVG_RXPOWER_UP"
.Cells(1, 9).Value = "AVG_PATH_LOSS_UP"
.Cells(1, 10).Value = "MAX_CER_DOWN"
.Cells(1, 11).Value = "MAX_CCER_DOWN"
.Cells(1, 12).Value = "MIN_SNR_DOWN"
.Cells(1, 13).Value = "US_CER_MAX"
.Cells(1, 14).Value = "US_CCER_MAX"
.Cells(1, 15).Value = "US_SNR_MIN"
.Cells(1, 16).Value = "SUM_T3_TIMEOUTS"
.Cells(1, 17).Value = "SUM_T4_TIMEOUTS"
.Cells(1, 18).Value = "MAX_SYSUPTIME"
.Cells(1, 19).Value = "FIRST_NAME"
.Cells(1, 20).Value = "LAST_NAME"
.Cells(1, 21).Value = "STREET_NUMBER"
.Cells(1, 22).Value = "STREET_NAME"
.Cells(1, 23).Value = "CITY"
.Cells(1, 24).Value = "DEVICE_TYPE"
.Cells(1, 25).Value = "PHONE"
End With

.Range("A2").CopyFromRecordset (objMyRecordset)

End With

'close sql connection
objMyConn.Close

'Switch to the Accounts Sheet
Sheets("Accounts").Activate

'Sort on Column A running out to column P down to row 100. 100 should be enough
With ActiveWorkbook.Worksheets("Accounts").Sort
.SetRange Range("A2Tongue100")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With



End Sub
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79873 Visits: 17904
There's no cost in making your code look readable, just as there's no cost in writing in paragraphs instead of one long sentence. Laying out your code will become second nature as you become more experienced. You may even develop coding standards that everybody in your organisation has to adhere to. In the meantime, you can use one of the tools already mentioned to prettify your code. Another product that has a free offering is SQL Complete.

So the database runs on a Linux server, but what type of database is it - SQL Server, Oracle or something else? I only have experience with SQL Server, so if it's that, write a stored procedure so that all your logic is in one place and not duplicated in all Excel workbooks. I'd be surprised if there's no equivalent in Oracle, as well.

Coming back to your original question, though, you need to improve the performance of that query. If it's SQL Server, please post the actual execution plan. If it's Oracle or something else, this isn't the best place to be asking for help.

Good luck
John
J Livingston SQL
J Livingston SQL
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27074 Visits: 41312
doesnt answer your question....but if you are looking for a free SQL refactoring tool that is configurable

http://www.apexsql.com/sql_tools_refactor.aspx

....Note...I do not work for these guys

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

onemangathers
onemangathers
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 42
The database type is Oracle that we have a linked connection to in Microsoft Sql Server Management Studio. We also have Oracle Sql Developer running on our server that we use to connect to it also.
I'm not sure what you mean by execution plan. Would I be able to look that up in the connection properties?
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79873 Visits: 17904
Please see this article about execution plans - or make your own investigations. There's a lot of stuff out there. The article is specifically about SQL Server execution plans, so it isn't going to be much help beyond explaining what one is. You need to go on to an Oracle forum to get help with this.

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search