none
SQLCE Table Indexes RRS feed

  • Question

  • I've got a very simple database  with sales and sale items tables and three simple product tables which is basically just a an id and a description column.  I'm using EF to access data.  I get sale items based on the SaleID that they belong to -  then usually do a loop though the list of items comparing the description to the sale item description.  Any of these lists will never be over about 15 items but it still takes a few seconds to iterate through the sale items by SaleID.  The comparison is string match to string on the description.

    Should I add an index on the SaleID in the SaleItems table.  The more sales and sale Items the longer the present process lasts.

    My boss wants me to speed up the response time.  Any suggestions - possibly even on the C# side to shorten the list loop time?

    TIA

    Harry


    • Edited by Mimosa Arts Friday, August 30, 2019 12:23 PM
    Friday, August 30, 2019 12:22 PM

Answers

  • Hi,

    >> ... then usually do a loop though ...

    The data query in EF does not need to use loops, you can implement it through LINQ.

        MyTest2Entities mt = new MyTest2Entities();
    
        var classes = from t1 in mt.table1
                        from t2 in mt.table2
                        where t1.T1Id == t2.T2Id
                        select new
                        {
                            ID1 = t1.T1Id,
                            ID2 = t2.T2Id
                        };

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Mimosa Arts Tuesday, September 10, 2019 7:54 PM
    Monday, September 2, 2019 5:41 AM
    Moderator
  • Thanks Kyle,

    Using Linq with EF will take some getting used to.  Think I need to revisit the Pluralsight Linq course.

    I ended up adding an index on the SaleId of the SaleItems table and that really cut the time!  T

    Thanks

    Harry

    • Marked as answer by Mimosa Arts Tuesday, September 10, 2019 7:54 PM
    Tuesday, September 10, 2019 7:54 PM

All replies

  • Hi,

    >> ... then usually do a loop though ...

    The data query in EF does not need to use loops, you can implement it through LINQ.

        MyTest2Entities mt = new MyTest2Entities();
    
        var classes = from t1 in mt.table1
                        from t2 in mt.table2
                        where t1.T1Id == t2.T2Id
                        select new
                        {
                            ID1 = t1.T1Id,
                            ID2 = t2.T2Id
                        };

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Mimosa Arts Tuesday, September 10, 2019 7:54 PM
    Monday, September 2, 2019 5:41 AM
    Moderator
  • Thanks Kyle,

    Using Linq with EF will take some getting used to.  Think I need to revisit the Pluralsight Linq course.

    I ended up adding an index on the SaleId of the SaleItems table and that really cut the time!  T

    Thanks

    Harry

    • Marked as answer by Mimosa Arts Tuesday, September 10, 2019 7:54 PM
    Tuesday, September 10, 2019 7:54 PM