SQL View

bjerris · 2010-12-01

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]

rhpnt · 2010-12-01

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...

kapamarou · 2010-12-01

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?

bjerris · 2010-12-02

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.

kapamarou · 2010-12-02

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?

David_Singleton · 2010-12-02

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.

SQL View

Comments

Categories