using a view - performance

am using a view from the DB for presenting details to users on grid. then after they have done their updates, am using that view as the basis for updating the DB but am getting poor performance on the first pass through - times out on client PC.....(c 20-30+ secs)

if then re-do the processing, all works fine

bit of the code that loops through the view - to get the BrandTree record and update it

        using (var transactionScope = new TransactionScope())
        {
            foreach (VwProductbyBrandTree dt in DataGridDisplayData)
            {
                if (dt.Include == true)
                {
                    BrandTree bt = _vm.GetBrandTreeByProductID(dt.Gxhid).First();  // should only be 1 !!
                    DBContexts.UnitOfWork.Detach(dt);  //leave the view record alone - dont want update

                    bt.SupplierIdgxhId = SelectedSupplierToComboItem.Id;
                    bt.BrandGrpIdgxhId = SelectedBrandGrpToComboItem.Id;
                    bt.BrandIdgxhId = SelectedBrandToComboItem.Id;

                    btList.Add(bt);
                }
            }

            _btRepo.UpdateFromList(btList);

            transactionScope.Complete();
        }

updating 1 record on first pass-thru took c 38 secs updating 1 record on second pass-thru took c 6 secs

is there anything I can do to get the first "pass-thru" faster / not time out

From your description it sounds like there is a lazy load of data occurring on the first pass which is not needed on the second pass. Try adding a logger to review what SQL is being executed and compare this between the two passes to confirm if this is the case.

Jeremy

re ran - took 47 secs on first pass, 6 on second.

tried to look at the SQL via SQLProfiler - but nothing was shown there as being unexpected. after doing the update there was a re-read of the DB to show the updated records but that wasn't the big time delay experienced picture attached

also tried using the Lightspeed logger - but no joy - couldn't see the SQL in the VStudio output window attached screen shot of the config section entry - any ideas on what is missing ?

It looks like the first fetch took 12 seconds, how many rows are being returned?

Jeremy

Jeremy,

would prob have been c 10-20 records (can't remember what test data I was using). the view being read has c 204k records.

attached is results of same test - with bit more detail as to what is occurring. as far as I can see, lightspeed is performing as expected, just the timings being a bit out....

12 seconds to return 10 records?

Have you checked what the view is doing when you make those modifications? Is it being thrown out by the record updates and performs poorly on the next successive call? It might pay to manually execute the statements as captured via the profiler and see if you can reproduce the problem directly and then you can examine whats happening with the view (assuming thats whats happening).

Jeremy