mibuso.com
Home› SQL General
Options

SQL View

bjerrisbjerris Member Posts: 3
edited 2010-12-02 in SQL General
Hi

I've created a view in SQL 2008 SP2 for usage in Navision 6.01. The view actually works, but I'm not sure that I've designed the view correctly. The main idea is to create a customer list which also shows the balance of the customer. When I created it in Navision it was very slow, but with this it's quite fast. Any cents?

SELECT
dbo.[CRONUS Danmark A_S$Customer].No_ AS [Customer No_],
dbo.[CRONUS Danmark A_S$Customer].Name AS [Customer Name],
dbo.[CRONUS Danmark A_S$Customer].[Responsibility Center],
dbo.[CRONUS Danmark A_S$Customer].[Location Code],
dbo.[CRONUS Danmark A_S$Customer].[Phone No_],
dbo.[CRONUS Danmark A_S$Customer].Contact,
dbo.[CRONUS Danmark A_S$Customer].[Search Name],
SUM(dbo.[CRONUS Danmark A_S$Detailed Cust_ Ledg_ Entry$VSIFT$4].SUM$Amount) AS Amount

FROM
dbo.[CRONUS Danmark A_S$Customer] INNER JOIN dbo.[CRONUS Danmark A_S$Detailed Cust_ Ledg_ Entry$VSIFT$4] ON
dbo.[CRONUS Danmark A_S$Customer].No_ = dbo.[CRONUS Danmark A_S$Detailed Cust_ Ledg_ Entry$VSIFT$4].[Customer No_]

GROUP BY
dbo.[CRONUS Danmark A_S$Customer].No_,
dbo.[CRONUS Danmark A_S$Customer].Name,
dbo.[CRONUS Danmark A_S$Customer].[Responsibility Center],
dbo.[CRONUS Danmark A_S$Customer].[Location Code],
dbo.[CRONUS Danmark A_S$Customer].[Phone No_],
dbo.[CRONUS Danmark A_S$Customer].Contact,
dbo.[CRONUS Danmark A_S$Customer].[Search Name]

Comments

  • Options
    rhpntrhpnt Member Posts: 688
    If it's working then it's correct. If you like experimentig you can try a subselect statement like:
    select
	[No_],
	[Name],
	(
	select
		sum(Amount)
	from
		dbo.[xxxdb$Detailed Cust_ Ledg_ Entry] as dcle
	group by
		[Customer No_]
	having
		cs.[No_] = dcle.[Customer No_]
	) as Amount
from
	dbo.Customer as cs

    I don't know which one's faster...
  • Options
    kapamaroukapamarou Member Posts: 1,152
    bjerris wrote:
    I've created a view in SQL 2008 SP2 for usage in Navision 6.01. ... When I created it in Navision it was very slow

    Can you explain this further? Why was it slow?
  • Options
    bjerrisbjerris Member Posts: 3
    Hi kapamarou

    The customer has a lot cust. ledg. entries, and thats why I had to make an SQL view and use that in Navision (via linked objects) instead of a flowfield in customer list form.
  • Options
    kapamaroukapamarou Member Posts: 1,152
    The flowfield should be fast to calculate. There must be something else going on... Maybe disabled keys, a hotfix missing... I wouldn't use SQL view for such tasks...Maybe you should investigate this more. Have you used client monitor or profiler to see what is sent to SQL and why it is delaying?
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    What object version are you using, and what build is this?

    The flow field should not be slow, it's generating the data that is slow. It looks (as stated above) that someone has broken something.
    David Singleton
Sign In or Register to comment.