Dynamics Community Forum Thread Details

SQL Query Performance

Good day all

I am joining Navision tables to get a certain result

firstly I am analyzing the values in the value entry table for a specific customer in NAV vs my OLAP Cube report and the values do match , my analysis will be done in Tableau later.

So in order for me to get the Sales Invoice Line and Sales Cr_Memo data together I have inserted the data from both tables into one SQL table. Then I took the customer table and Value entry table to join to the new table I have created , however my query runs very long , how can I optimize the query to run faster in less time

SELECT 
  [Spier Live$Value Entry].[Posting Date] AS [Posting Date],
  [Spier Live$Value Entry].[Inventory Posting Group] AS [Inventory Posting Group],
  [Spier Live$Value Entry].[Invoiced Quantity] AS [Invoiced Quantity],
  [Spier Live$Value Entry].[Cost per Unit] AS [Cost per Unit],
  [Spier Live$Value Entry].[Sales Amount (Actual)] AS [Sales Amount (Actual)],
  [Spier Live$Value Entry].[Salespers__Purch_ Code] AS [Salespers__Purch_ Code],
  [Spier Live$Value Entry].[Discount Amount] AS [Discount Amount],
  [Spier Live$Value Entry].[Global Dimension 1 Code] AS [Brand Class Code],
  [Spier Live$Value Entry].[Cost Amount (Actual)] AS [Cost Amount (Actual)],
  [Spier Live$Value Entry].[Expected Cost] AS [Expected Cost],
  [Spier Live$Value Entry].[Item Charge No_] AS [Item Charge No_],
  [Spier Live$Value Entry].[Sales Amount (Expected)] AS [Sales Amount (Expected)],
  [Spier Live$Value Entry].[Cost Amount (Expected)] AS [Cost Amount (Expected)],
  [Spier Live$Value Entry].[Variant Code] AS [Variant Code],

  [Sales Invoice_CrMemo Tbl].[Sell-to Customer No_] AS [Sell-to Customer No_],
  [Sales Invoice_CrMemo Tbl].[Posting Group] AS [Posting Group],
  [Sales Invoice_CrMemo Tbl].[Shipment Date] AS [Shipment Date],
  [Sales Invoice_CrMemo Tbl].[Product Group Code] AS [Product Group Code],

  [Spier Live$Customer].[Name] AS [Name],
  [Spier Live$Customer].[City] AS [City],
  [Spier Live$Customer].[Currency Code] AS [Currency Code],
  [Spier Live$Customer].[Shipment Method Code] AS [Shipment Method Code],
  [Spier Live$Customer].[Country_Region Code] AS [Country Code],
  [Spier Live$Customer].[Excise Location] AS [Excise Location]
 
FROM [dbo].[Sales Invoice_CrMemo Tbl] [Sales Invoice_CrMemo Tbl]
  LEFT JOIN [dbo].[Spier Live$Value Entry] [Spier Live$Value Entry] ON ([Spier Live$Value Entry].[Item No_] = [Sales Invoice_CrMemo Tbl].[No_]) 
  LEFT JOIN [dbo].[Spier Live$Customer] [Spier Live$Customer] ON ([Sales Invoice_CrMemo Tbl].[Sell-to Customer No_] = [Spier Live$Customer].[No_])

Quick Links

Leaderboard