Hello

I have a query that brings 0 rows back (which is correct) but takes long to execute like 10 seconds. Which isnt good because i have 6 of them. The table which i am pulling from has round 1mil rows in it however 10 seconds is to long

Any suggestions welcome.

using(var adapter = DataAccessAdapter.GetAdapter()) { var metaData = new LinqMetaData(adapter);

           var paymentInvoices = from paymentInvoice in metaData.PaymentInvoice
                                 group paymentInvoice by new {paymentInvoice.PaymentId, paymentInvoice.InvoiceId}
                                 into piCol
                                 select new
                                            {
                                                Payment = piCol.Key.PaymentId,
                                                Invoice = piCol.Key.InvoiceId,
                                                Amount = piCol.Sum
                                     (
                                         x => x.Amount
                                     )
                                            };

           var paymentRefunds = from paymentRefund in metaData.PaymentRefund
                                group paymentRefund by paymentRefund.PaymentId
                                into pfCol
                                select new
                                           {
                                               Payment = pfCol.Key,
                                               Amount = pfCol.Sum
                                    (
                                        x => x.Amount
                                    )
                                           };

           var payment = from payments in metaData.Payment

                         join paymentInvoice in paymentInvoices on payments.PaymentId equals
                             paymentInvoice.Payment
                             into piCol
                         from pi in piCol.DefaultIfEmpty()

                         join paymentRefund in paymentRefunds on payments.PaymentId equals
                             paymentRefund.Payment
                             into pfCol
                         from pf in pfCol.DefaultIfEmpty()

                         select new PaymentInvoiceClass
                                    {
                                        DateCreated = payments.DateCreated,
                                        TicketId = payments.TicketId,
                                        AccountId = payments.AccountId,
                                        Amt = payments.Amount,
                                        CreditNoteId = Guid.Empty,
                                        DateDue = payments.DateCreated,
                                        InvoiceId = pi.Invoice,
                                        PaymentId = payments.PaymentId,
                                        Name = payments.Name + (pf.Payment != Guid.Empty ? "Refund" : string.Empty),
                                        PaidAmt =
                                            (pi.Amount != null ? pi.Amount : 0) +
                                            (pf.Amount != null ? pf.Amount : 0),
                                        IsPosted = false,
                                        EvolutionId = payments.EvolutionId
                                    };

           var paymentCol = payment.Where(x => x.AccountId == AccountId && x.PaidAmt != 0);

           if (InvoiceId != null)
               paymentCol = paymentCol.Where(x => x.InvoiceId == InvoiceId);
           if (PaymentId != null)
               paymentCol = paymentCol.Where(x => x.PaymentId == PaymentId);

           Count = paymentCol.Count();

           if (PageIndex != 0 && PageSize != 0)
               paymentCol = paymentCol.TakePage(PageIndex, PageSize);

           return paymentCol.ToArray();
       }

Hi jf,

Well, you have to investigate a little bit further:

• Check the Generated Sql to see what sql is actually executed in the DB. Here you can evaluate/compare the parts of the query that take so much time. You can run this query in your database, see the execution plan, etc.

• Your query has a lot of subqueries with groupers, so it's kind of expected. See if you can reformulate it in a way it's still equivalent but faster. Here are some tips about performance tuning.

• I don't know what is your LLBLGen version and runtime library version, but it's highly recommended that you update it to the latest RTL.