December 4, 2008 at 1:10 pm
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
December 4, 2008 at 1:16 pm
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
December 4, 2008 at 1:20 pm
Ok sure , I will post the execution plan soon. Thanks.
December 5, 2008 at 6:53 am
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
December 5, 2008 at 8:56 am
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
December 5, 2008 at 4:54 pm
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'
December 5, 2008 at 5:10 pm
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
December 6, 2008 at 1:03 am
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
December 6, 2008 at 1:59 am
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
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
December 7, 2008 at 2:38 am
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
December 7, 2008 at 2:44 am
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
December 7, 2008 at 2:45 am
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
December 7, 2008 at 9:54 am
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
December 7, 2008 at 3:42 pm
Thanks for being so patient. I will script out everything and post it to you.
Thanks,
December 8, 2008 at 2:22 am
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]
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