Improve SQL Query Performance

  • In my database , I am storing millions of records in a table. I have done indexing based on the where clauses of the SQl . But still I do not see any change in the performance.

    With the record saved I am trying to create a report and it is very very slow. I am performing Select Count (DISTINCT UserID) from a table . And also I am calculating the percentage in my report.

    So when I am pulling the report with all the values. it is performing very slow.

    Can you please help .

    Thanks in advance.

    Debjnai

  • Without seeing the query, the table definition and the indexes (and preferably the execution plan, as a .sqlplan file), it's hard to offer any useful advice.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ok sure , I will post the execution plan soon. Thanks.

  • Please do as Gail requested in order to get detailed help, but I will point out these two phrases in your post:

    debjani.bedajana (12/4/2008)


    In my database , I am storing millions of records in a table. ...

    So when I am pulling the report with all the values. it is performing very slow.

    If you're pulling aggregates against millions of rows... prepare to wait a bit for results.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Without more data, the main thing I can suggest is, make sure it's a covering index that you're using.

    In other words, if there are columns in the Select clause that aren't in the Where clause, make sure those are in the Include part of the index.

    Also, this may not pertain, but columns used in Joins in the From clause are just as important to have in the index as columns in the Where clause, if you're pulling data from more than one table.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • All the columns in my JOIN are indexed and also the ones in the where clause. The problem is 10 sql statements are running together. They are all helping to generate a report for Multivariate Testing. It is checking how many users came to the page , and how many clicked on certain buttons. So I am compiling the results from all the sql s are putting in a datagrid to show the report.

    example of my SQl scripts are like this

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA1'

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA3'

  • My Question is when Google Access the pge hits records through google Analytics. it does not take more than 5 secs. But mine is taking so longer . So there must be somthing which I am missing out. Please help.

    I have run the execution plan. But I do not know how to send it u as a file . Please let me know.

    Thanks

  • Tables, indexes and execution plan please.

    For the exec plan, right click on the plan (in management studio) select save as. Save it as a .sqlplan file. Zip that file and attach it to your post.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In addition to providing the material Gail has requested and following the advice of Gsquared, can you please post the whole batch of statements? Looking at the two queries you've already posted, I reckon there's considerable scope for performance enhancement:

    SELECT GroupName, COUNT(*) AS NumberOfUsers

    FROM (SELECT g.GroupName, USERID

    FROM tbl_MVTResult r

    LEFT JOIN tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    WHERE Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008'

    AND col.CollectionName = 'CTA'

    AND r.ShowVariation =1

    GROUP BY g.GroupName, USERID) d

    GROUP BY GroupName

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thank you very much for rearranging the SQL Statement.

    The main idea is the database stores , how many users are getting which variation GRoupA, GroupB, GroupC, GroupD. And each group has four elements. So how many users are clicking on the elemnets, under which Group. When the user clicked on the element , it means the goal becomes 1.

    The rest of the statements are showing how many times the individual buttons in the webpages have been clicked.

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA1'

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA3'

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA1' AND e.ElementName = 'checkA' r.Goal = 1

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA3' AND e.ElementName = 'checkA' r.Goal = 1

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA1' AND e.ElementName='checkB' AND r.Goal = 1

    Select Count(DISTINCT USERID) from tbl_MVTResult r

    LEFT JOIN

    tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

    LEFT JOIN

    tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

    LEFT JOIN

    tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

    where Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008' AND col.CollectionName = 'CTA' AND r.ShowVariation =1 AND g.GroupName ='CTA3' AND e.ElementName='checkB' AND r.Goal = 1

  • Gail,

    I might sound stupid, but I could not figure out how can I attach my database diagram which is a .pdf file along with this post.

    And I can run the execution plan in SQL 2000 query analyzer. But it is not letting me save it.

    I was wondering , is there any other way, I could send to ur email address or so. In the post I can see only a link for insert image and insert hyperlink.

    I feel sorry to ask help. I wish there was some way out.

    Debjani

  • Gail,

    I might sound stupid, but I could not figure out how can I attach my database diagram which is a .pdf file along with this post.

    And I can run the execution plan in SQL 2000 query analyzer. But it is not letting me save it.

    I was wondering , is there any other way, I could send to ur email address or so. In the post I can see only a link for insert image and insert hyperlink.

    I feel sorry to ask help. I wish there was some way out.

    Debjani

  • debjani.bedajana (12/7/2008)


    Gail,

    And I can run the execution plan in SQL 2000 query analyzer. But it is not letting me save it.

    Please post in the correct forum in the future. If you post in the 2005 forum we'll assume you're using SQL 2005 and will offer suggestions appropriate for SQL 2005, wasting both our times. Saving exec plans is a 2005-only feature.

    Since you're on SQL 2000, please switch SHOWPLAN_ALL on before running the query (SET SHOWPLAN_ALL ON), copy the contents of the grid and paste into excel. Save the excel spreadsheet and zip it.

    Below the text box to type your message, you'll see Post options. In there is attachments. Use that to attach the file.

    I might sound stupid, but I could not figure out how can I attach my database diagram which is a .pdf file along with this post.

    I don't want the database diagram. What we need is the schema and index definitions of the tables involved. Script them out and past the CREATE statements.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for being so patient. I will script out everything and post it to you.

    Thanks,

  • debjani.bedajana (12/7/2008)


    The main idea is the database stores , how many users are getting which variation GRoupA, GroupB, GroupC, GroupD. And each group has four elements. So how many users are clicking on the elemnets, under which Group. When the user clicked on the element , it means the goal becomes 1.

    Compare the counts you are getting from your individual statements from the counts which this statement generates:

    [font="Courier New"]SELECT GroupName, ElementName, COUNT(*) AS UserCount

    FROM (

       SELECT g.GroupName, e.ElementName, USERID

       FROM tbl_MVTResult r

       LEFT JOIN tbl_MVTElement e ON r.MVTElementID = e.MVTElementID

       LEFT JOIN tbl_MVTGroup g ON e.MVTGroupID = g.MVTGroupID

       LEFT JOIN tbl_MVTCollection col ON g.MVTCollectionID = col.MVTCollectionID

       WHERE Last_Accessed BETWEEN '10/23/2008' AND '12/06/2008'

           AND col.CollectionName = 'CTA'

           AND r.ShowVariation =1

           --AND g.GroupName ='CTA3' AND e.ElementName = 'checkA'

           AND r.Goal = 1

       GROUP BY g.GroupName, e.ElementName, USERID

    ) d

    GROUP BY GroupName, ElementName

    [/font]

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 15 posts - 1 through 15 (of 25 total)

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