[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

HELP!  ARE THESE QUERIES EQUIVALENT?

Posted on 2013-01-30
7
Medium Priority
?
188 Views
Last Modified: 2013-02-15
The following query gets executed tens of thousands times.
I need to optimize it.
I thought that the JOIN to thesecond table was unnecessary ... so I rewrote it, and I get exactly the same results with the rewritten query ...
However, I am worried that there may be a few records that may make my rewrite return a different record set.

Logically speaking are the two queries identical??

ORIGINAL QUERY

select ppc.id, ppc.planId, ppc.subjectCategoryId, ppc.gradelevel, ppc.masterCourseId, ppc.title, ppc.units
from coursePlannerMainPlan pmp inner join coursePlannerPlannedCourses ppc on pmp.id = ppc.planId
where (ppc.planId between 740000 and 744000 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10 )
or (pmp.isPrimaryPlan = 1 and pmp.userId = 28243643 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10) order by id

--MODIFIED QUERY
---------------------------------------------------------
select ppc.id, ppc.planId, ppc.subjectCategoryId, ppc.gradelevel, ppc.masterCourseId, ppc.title, ppc.units
from coursePlannerPlannedCourses ppc
where (ppc.planId between 740000 and 744000 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10)
order by id
0
Comment
Question by:DBA2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38838541
No they are different, there is a possibility of getting different results if data in one or both the tables change.

original query coursePlannerMainPlan and coursePlannerPlannedCourses  are joined on planid.

if records from coursePlannerMainPlan are deleted or records are inserted in coursePlannerPlannedCourses only the data returned will different .
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38839733
Appari is right, your two queries aren't the same thing. But you still have a query that isn't meeting your needs anymore.
Here are a few suggestions to try that just may get you closer to the performance that you'd like to see.

In some cases an exists clause can be moderately faster than a join. You might try executing this and looking at your execution times.
select ppc.id, ppc.planId, ppc.subjectCategoryId, ppc.gradelevel, ppc.masterCourseId, ppc.title, ppc.units 
from coursePlannerPlannedCourses ppc  
where (ppc.planId between 740000 and 744000 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10 ) 
or exists
(select null from coursePlannerMainPlan pmp where pmp.id = ppc.planId and
pmp.isPrimaryPlan = 1 and pmp.userId = 28243643 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10)

Open in new window

You could look evaluate the indexes on your table if this isn't performing well. Bad indexing can really hurt performance.

Fragmented indexes are bad for performance too.
There is a lot of info out there on when to rebuild your indexes. Here is a script that will suggest which indexes to rebuild, as well as generate the sql command to do it.  I've used before:
http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx

Since your table does seem to be pretty big, depending on how often the table is modified, you might be able to create a temp table on the server and query it instead of the original table (or create it if it doesn't exist).

You would do that with something like this...
IF OBJECT_ID('tempdb.dbo.#mytable') IS NOT NULL 
BEGIN
select * from tempdb.dbo.#mytable
END
ELSE
BEGIN
select ppc.id, ppc.planId, ppc.subjectCategoryId, ppc.gradelevel, ppc.masterCourseId, ppc.title, ppc.units 
into #mytable
from coursePlannerPlannedCourses ppc  
where (ppc.planId between 740000 and 744000 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10 ) 
or exists
(select null from coursePlannerMainPlan pmp where pmp.id = ppc.planId and
pmp.isPrimaryPlan = 1 and pmp.userId = 28243643 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10)
END

Open in new window

0
 

Author Comment

by:DBA2000
ID: 38842020
Thank you, All:

Your inputs opened my eyes and I did the following:
I created an indexed-view ...  
... evaluated the tables/db usage, 90/10 ratio of read vs. right, etc.
so, not much concern with keeping both base tables and indexes in sync  ...

The following script shows the VIEW that needs to be called in place of the JOIN of the two huge tables:

CREATE view [dbo].[vw_coursePlannerMainPlan_PlannedCourses] with schemabinding as
(
select ppc.id, ppc.planId, ppc.subjectCategoryId, ppc.gradelevel, ppc.masterCourseId, ppc.title, ppc.units, pmp.isPrimaryPlan, pmp.id as PMP_Id, pmp.userId as PMP_UserId
from [dbo].coursePlannerMainPlan pmp inner join [dbo].coursePlannerPlannedCourses ppc on pmp.id = ppc.planId
);
--

--select COUNT(1) from [dbo].[vw_coursePlannerMainPlan_PlannedCourses] -- 3,839,529

Create unique clustered index IX_coursePlannerMainPlan_PlannedCourses_PPCid on [vw_coursePlannerMainPlan_PlannedCourses](Id);

CREATE INDEX IX_coursePlannerMainPlan_PlannedCourses_PlanId_Id ON vw_coursePlannerMainPlan_PlannedCourses(planId, id);

 
Modified previous join query to call view (below)

Query was 5 - 7 times faster!

What do you think? are there concerns with this?
 

select ppc.id, ppc.planId, ppc.subjectCategoryId
, ppc.gradelevel, ppc.masterCourseId, ppc.title, ppc.units
from vw_coursePlannerMainPlan_PlannedCourses ppc
where (ppc.planId = 749259 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10)
or (ppc.isPrimaryPlan = 1 and ppc.userId = 29367266 and ppc.subjectCategoryId = 30 and ppc.gradeLevel = 10)
order by id
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 39

Expert Comment

by:appari
ID: 38842047
I think its ok.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38843425
why not just write it simply as

select ppc.id, ppc.planId, ppc.subjectCategoryId
, ppc.gradelevel, ppc.masterCourseId, ppc.title, ppc.units
from vw_coursePlannerMainPlan_PlannedCourses ppc
where ppc.subjectCategoryId = 30 
     and ppc.gradeLevel = 10
    and (ppc.planId = 749259 
              or (ppc.isPrimaryPlan = 1 and ppc.userId = 29367266 )
           )
order by id

Open in new window



consider having the index on

subjectcategoryid,gradelevel,planid,userid

instead of just the id
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 2000 total points
ID: 38843642
I think this is a great solution considering the relatively small number of writes being made to the tables involved.
0
 

Author Closing Comment

by:DBA2000
ID: 38895258
Thank you for taking the time to solve the problem.
Your suggestions helped me find the right sol'n.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question