Improve SQL Query Performance

Question

Post reply

Improve SQL Query Performance

Viewing 11 posts - 16 through 25 (of 25 total)

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

debjani.bedajana Valued Member Points: 51 More actions · Answer 1

Hi Gail,

Here is the execution plan and the SQL scripts attached in the forum. Thanks for all your help

debjani.bedajana Valued Member Points: 51 More actions · Answer 2

Chris,

Your SQL statement really looks very good. It has definitely improved some performance. I am still testing. Will inform you. Thanks again.

debjani.bedajana Valued Member Points: 51 More actions · Answer 3

Hi Gail,

Sorry to bother you. Wondering if you could find some solution. Thanks for your help.

Debjani

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

debjani.bedajana (12/9/2008)
Hi Gail,
Sorry to bother you. Wondering if you could find some solution. Thanks for your help.
Debjani

Hi Debjani

Can you please let us know what the remaining problems are?

^{“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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

debjani.bedajana (12/9/2008)
Hi Gail,
Sorry to bother you. Wondering if you could find some solution. Thanks for your help.
Debjani

Chris seems to have the query sorted. I'll take a look when I have a few minutes, see if I can identify some indexing possibilities.

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

debjani.bedajana Valued Member Points: 51 More actions · Answer 6

In my report I am also showing the percentage of each element clicked. Before I was running one SQL to find out how many visits were there for per group. And then seperate SQL to find out one how many times each of the element is clicked .And then was calculating the percentage.

Is there any other way to get it together or do I have to do it seperately.

Thanks again for all your help.

debjani.bedajana Valued Member Points: 51 More actions · Answer 7

Hi Chris,

I needed some help. The query you had given worked good, but 1 small problem I am having. From your query we can see that By groupName which elements were clicked and what is the total count.

Suppose where are 3 elements per group, and if two elements were clicked it is only showing me the count of that 2 elements only.

But I want to show the name of the other Element too in the query result which has not been clicked

So th e query result will look like

GroupName Element1- Clicked Element2 - Clicked Element3- Clicked

group1 12 10 0

Thanks very much

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

debjani.bedajana (12/23/2008)
But I want to show the name of the other Element too in the query result which has not been clicked

No problem - post the query you're currently using.

^{“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

debjani.bedajana Valued Member Points: 51 More actions · Answer 9

The query I am using now is

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 r.Goal = 1

GROUP BY g.GroupName, e.ElementName, USERID

) d

GROUP BY GroupName, ElementName

The above query is showing me only the name of the Elements and the count of the users who has clicked from the Group =CTA3. But suppose in that group there were 3 elements . And all the 3 elements were showed but only 2 were clicked. The one which has not been clicked - nothing is showing for that.

So I want the query result should return me the User Count for the elements clicked and 0 for the elemnt which has not been clicked.

Thanks again chris

debjani.bedajana Valued Member Points: 51 More actions · Answer 10

Hi Chris,

If I write the query as

SELECT GroupName, ElementName, 0 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 r.Goal is null

GROUP BY g.GroupName, e.ElementName, USERID

) d

GROUP BY GroupName, ElementName

Then it is showing all the elements as 0 times clicked.

But what I want to do is Show the number of times each element was clicked under each group.

eg-

GroupName ElementName # TimesClicked

PHXCTA1 Element1 12

PHXCTA1 Element2 54

PHXCTA1 Element3 0

PLease help . And let me know if you can find a solution. I will appreciate.

Dutta, Amartha SSC Eights! Points: 945 More actions · Answer 11

[font="Courier New"]Hi,

Try Out the following set of SQL Statements. I hope it gives you the desired output. Please pardon, if I am wrong.

SELECT

g.GroupName,

e.ElementName,

SUM(CASE WHEN UserID IS NOT NULL THEN 1 ELSE 0 END) As [No. Of Clicks]

FROM

tbl_MVTCollection col

INNER JOIN

tbl_MVTGroup g ON g.MVTCollectionID = col.MVTCollectionID

INNER JOIN

tbl_MVTElement e ON e.MVTGroupID = g.MVTGroupID

LEFT JOIN

tbl_MVTResult r ON r.MVTElementID = e.MVTElementID

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

AND col.CollectionName = 'CTA'

AND r.ShowVariation =1

AND g.GroupName ='CTA3'

AND r.Goal is null

GROUP BY g.GroupName, e.ElementName

I think this should work out. Since in the earlier statements the Results Table Was on the left side of the join statements so it was not appearing if no entry was there. But in this statement the Result table is on the right hand of the left join statement so even if it is not there it will show up... YOu might need to slightly modify the statement as per your requirements.

I am sorry that I can't check the validity of the statements as I don't have the structure of the table being referenced in the SQL Block.

With Best Regards...

[/font]