Post reply

need help with tuning

  • December 7, 2012 at 9:59 am

    #285920

    Hello Everyone,

    I need help in tuning a complex EF generated sql query. Any inputs are highly appreciated.

    I have attached the query plan, query and ddl scripts.

    Thanks again.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • December 7, 2012 at 10:31 am

    #1566700

    Not everyone has the ability to open a .rar compressed file. You may want to put the files in a .zip file instead.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • December 7, 2012 at 2:13 pm

    #1566769

    since it comes from EF, i assume you cannot fix the query, but must instead fix the tables and indexes to improve the query?

    it's a big job, you need clustered indexes, some new indexes it looks like, adding include columns to some of them all could increase the performance; but it requires a lot of testing.

    i see several tables are HEAPs without a clustered index.

    adding a clustered index on these two tables would probably help:

    ImportPersonFlex

    ImportPerson

    PhoneLogs

    my first guess would be a clustered index on OlympusWebsiteDB.dbo.ImportPersonFlex on FlexDefID?

    and you need a covering index on FlexDefName,FlexListItemId,Value

    another clustered index on PhoneLogs, but since you are returning what looks like the whole table(32 columns), i don't see how an index with covering columns would help for that one.,

    from there i see there are a lot of implicit conversions that should be fixed; the implicit conversions can slow things down.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 7, 2012 at 3:16 pm

    #1566787

    Thanks for the reply. I can definitely add the clustered indexes on our test database first. But I was assuming that a Clustered Index on GUID column wud cause performance issues. Also the query is executed once every 6 seconds.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • December 7, 2012 at 3:18 pm

    #1566788

    Zip file has been attached

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply