How to improve performance for Query

Bartho

Hi,

I have made a rather simple query for a customer, but the performance is really awful. It currently takes several hours to complete and the CPU load of the SQL Server is 100%.

My query looks like this:

DataItem - Customer (Cross Join)
Field - Customer No.
-DataItem - Vendor (Cross Join)
-Field - Vendor No.
--DataItem - Calendar (Cross Join) (this is a simple table just containing some dates)
--Field - Date
----DataItem - G/L Entry (Left Outer Join) (Filters on Customer No., Vendor No. and Document Date)
----Field - Account No. (some hardcoded filters)
----Field - Amount (Method Type: Totals, Method: Sum)

On my G/L Entry table I have a key for Customer No., Vendor No., Document Date and Account No. together with Amount under SumIndexFields.

I have used the SQL Server Profiler to get the SQL query created by NAV and analyzed the execution plan.

/*
Missing Index Details from SQLQuery1.sql
The Query Processor estimates that implementing the following index could improve the query cost by 99.9232%.
*/

/*
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CompanyName$G_L Entry$VSIFT$13] ([Document Date],[Vendor No_],[Customer No_])
INCLUDE ([G_L Account No_],[SUM$Amount])
GO
*/

Can I do anything in NAV to increase performance for this Query?

Thanks for your help

Regards,

Bart

kaspermoerch

The DataItems you use - are they custom tables or default Customer, Vendor, Calendar and G/L Entry tables?

Could you shed some light on what result you except of your query? One line per unique combination of Customer, Vendor, Document Date and Account with a sum of Amount?

Kishorm

It's the crossjoins that are causing the performance issue - do you really need these?

Could you perhaps restructure your query to have G/L Entry as your main data item with left joins onto the other tables?