April 12, 2017 at 10:51 am
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')
April 12, 2017 at 3:37 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 12, 2017 at 4:53 pm
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?
April 13, 2017 at 3:01 am
onemangathers - Wednesday, April 12, 2017 4:53 PMI 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
April 13, 2017 at 5:47 am
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.
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 13, 2017 at 6:34 am
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("A2:P100")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
April 13, 2017 at 7:13 am
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
April 13, 2017 at 7:15 am
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
April 13, 2017 at 8:30 am
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?
April 13, 2017 at 8:38 am
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
April 13, 2017 at 8:43 am
This could be a dumb question as I don't know a lot about oracle, but isn't there an ODBC connection for oracle? If so, couldn't you run the whole thing agasint the oracle database instead of using SQL as a middle man? That would shave off 30 seconds (as you indicated).
Then it is just a matter of optimizing it on the Oracle side. I'm assuming they have indexes and different query tuning things you can do. Stored procedures (as John Mitchell-245523 suggested) are pre-compiled and generally do have better performance in SQL if your statistics are good. I am not sure if it is the same in Oracle.
And if you want faster performance on the query, what about if you remove the aggregate functions and just do that inside excel? Might not be that much faster depending on the machine that runs the excel though, but it might improve performance a little bit.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 24, 2019 at 9:47 pm
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply