SQL Server 2008 timeout exception

Question

I am developing the wireless application, when I do query 2000+ rows from SQL Server 2008, the timeout exception happens.

I am using a lot of linq in my program in a loop. When I comment some of them, it will be better but still happen.

Does Linq cause the slow performance? Is there any other reason cause this issue? And how can I solve it?

Thanks.

enter code here
                    var listShipUnit = from ShipUnit su in bdd.ShipUnit
                                       where (su.ShipmentID == shipmentNumber
                                        && (su.QStatus != "1" || su.QStatus == null)
                                        && (!su.CaseID.Equals(null) 
                                        && !su.CaseID.ToUpper().Equals("UNKNOWN") && 
                                        !su.CaseID.Equals(string.Empty) && 
                                        !su.CaseID.ToUpper().Equals("NULL") 
                                        && su.CaseID.Length > 0))
                                           group su by su.CaseID into gsu
                                           select new
                                           {
                                               gsu.Key,
                                               gsu,
                                               TotalWeight = gsu.Sum(w => w.Weight),
                                               TotalVolume = gsu.Sum(v => v.Volume),
                                               TotalCount = gsu.Sum(v => v.ShipUnitCount)
                                           };



                    foreach (var shipUnit in listShipUnit)
                    {
                        tempListShipUnitGroup.Add(new ShipUnitGroupSTL(shipUnit.gsu.ToList(), shipUnit.Key, shipUnit.TotalVolume.ToString(), shipUnit.TotalWeight.ToString(), shipUnit.TotalCount.ToString()));
                    }

                    List<ShipUnitGroupSTL> revShipUnitGroup = new List<ShipUnitGroupSTL>();
                    List<string> finishPallet = new List<string>();
                    List<string> finishCase = new List<string>();


                    // 2013.12.19 device null exception ---> start
                    var stlunits = bdd.ScanToLoad;
                    List<ScanToLoad> stlList = stlunits.ToList();

                    //var shipunits = bdd.ShipUnit;
                    //List<ShipUnit> shipunitList = shipunits.ToList();

                    if (stlList != null && stlList.Count() > 0)
                    // 2013.12.19 device null exception --- end
                    {
                        //// get the recently record from STL talbe
                        foreach (var shipUnitGroup in tempListShipUnitGroup)
                        {
                            string tempCaseID;
                            string tempPalletID;

                            var unit = shipUnitGroup.GroupShipUnit;


                            tempCaseID = shipUnitGroup.GroupKey;
                            tempPalletID = unit.FirstOrDefault().PalletID;

                        //    // 2013.12.19 device null exception ---> start
                        //    // look up the caseId in the STL table
                            var stlunit = stlList
                                        .Where(s => s.CaseID.Equals(tempCaseID))
                                        .OrderByDescending(s => s.UpdateDate);

                        //    //var stlunit = bdd.ScanToLoad
                        //    //            .Where(s => s.ScanToLoadID.Equals(tempCaseID)
                        //    //                || s.CaseID.Equals(tempCaseID))
                        //    //            .OrderByDescending(s => s.UpdateDate);

                        //    // 2013.12.19 device null exception ---> end



                            if (stlunit != null && stlunit.Count() > 0)
                            {
                                string stlPallet = null;

                                stlPallet = stlunit.FirstOrDefault().NewPalletID;

                                if (!string.IsNullOrEmpty(stlPallet))
                                {
                                    if (tempPalletID.Equals(stlPallet))
                                    {
                                        finalListShipUnitGroup.Add(shipUnitGroup);
                                        //continue;
                                    }
                                    else
                                    {
                                        var revShipUnit = bdd.ShipUnit
                                                    .Where(su => su.ShipmentID == shipmentNumber
                                                        && (su.QStatus != "1" || su.QStatus == null)
                                                        && su.PalletID.Equals(stlPallet));

                                        if (revShipUnit != null && revShipUnit.Count() > 0)
                                        {
                                            unit.FirstOrDefault().PalletID = stlPallet;
                                            finalListShipUnitGroup.Add(shipUnitGroup);
                                        }
                                    }
                                }
                                else
                                {
                                    finalListShipUnitGroup.Add(shipUnitGroup);
                                    //continue;
                                }


                            }
                            else
                            {
                                finalListShipUnitGroup.Add(shipUnitGroup);
                                //continue;
                             }

                            finishCase.Add(tempCaseID);
                            var checkFinshPallet = finishPallet
                                                    .Contains(tempPalletID);

                            if (checkFinshPallet)
                            {
                                continue;
                            }

                            finishPallet.Add(tempPalletID);

                        }


                        foreach (var tempPalletID in finishPallet)
                        {
                            // look up the pallet in the STL table, add others new caseId in STL table
                            var stlunitPal = stlList
                                        .Where(s => s.NewPalletID.Equals(tempPalletID))
                                        .OrderByDescending(s => s.UpdateDate);

                            if (stlunitPal != null && stlunitPal.Count() > 0)
                            {
                                //IEnumerable<MobilePlusServer.ShipUnit> newCaseList;
                                string stlPalletID;

                                // add and remove case from the Pallet
                                foreach (var s in stlunitPal)
                                {
                                    stlPalletID = s.NewPalletID;

                                    //var addSus = from ShipUnit su in bdd.ShipUnit
                                    //             where (su.CaseID.Equals(s.CaseID) && su.RecordID.Equals(s.CaseRecordID))
                                    //                    && (su.QStatus != "1" || su.QStatus == null)
                                    //             group su by su.CaseID into gsu
                                    //             select new
                                    //             {
                                    //                 gsu.Key,
                                    //                 gsu,
                                    //                 TotalWeight = gsu.Sum(w => w.Weight),
                                    //                 TotalVolume = gsu.Sum(v => v.Volume),
                                    //                 TotalCount = gsu.Sum(v => v.ShipUnitCount)
                                    //             };

                                    var addSus = from ShipUnit su in bdd.ShipUnit
                                                 where (su.CaseID.Equals(s.CaseID) && su.RecordID.Equals(s.CaseRecordID)
                                                     && (su.QStatus != "1" || su.QStatus == null))
                                                 select su;


                                    if (addSus != null && addSus.Count() > 0)
                                    {
                                        var addSu = addSus.FirstOrDefault();

                                        // get the remove pallet id
                                        string adShipmentID = addSu.ShipmentID;
                                        string adPalletID = addSu.PalletID;
                                        //string adRecordID = unit.FirstOrDefault().RecordID.ToString();
                                        //string adCaseID = unit.FirstOrDefault().RecordID.ToString();
                                        if (!shipmentNumber.Equals(adShipmentID)
                                            && !finishCase.Contains(s.CaseID))
                                        {
                                            // set new pallet id
                                            addSu.PalletID = stlPalletID;

                                            finalListShipUnitGroup.Add(new ShipUnitGroupSTL(
                                                addSus.ToList(),
                                                s.CaseID,
                                                addSu.Volume.ToString(),
                                                addSu.Weight.ToString(),
                                                addSu.ShipUnitCount.ToString()));
                                        }
                                    }
                                }
                            }
                        }
                    }
                    // 2013.12.19 device null exception ---> start
                    else
                    {
                        finalListShipUnitGroup = tempListShipUnitGroup;
                    }

Answer 1

Having such complicated queries, could easily be interpreted as an indication of poor design (either in the queries or in the database). I would suggest breaking your queries to smaller ones and then compose them. This would lead you to clearer code and logic. Then you could use the SQL server profiler to see the exact sql queries executed, and see if the delay is due to the translation between Linq to Entities and SQL. If you are certain that you could not make your queries simpler, then you could use indexing on the level of SQL Server

Hope I helped!

Answer 2

you can use Indexing on SQL for getting faster queries I recommend use index on your joining fields

see this documentations here and here and here