Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

HELP! ARE THESE QUERIES EQUIVALENT?

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
DBA2000
Asked:
DBA2000
  • 2
  • 2
  • 2
  • +1
1 Solution
 
appariCommented:
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
 
Jared_SCommented:
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
 
DBA2000Author Commented:
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.

 
appariCommented:
I think its ok.
0
 
LowfatspreadCommented:
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
 
Jared_SCommented:
I think this is a great solution considering the relatively small number of writes being made to the tables involved.
0
 
DBA2000Author Commented:
Thank you for taking the time to solve the problem.
Your suggestions helped me find the right sol'n.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now