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
Solved

HELP!  ARE THESE QUERIES EQUIVALENT?

Posted on 2013-01-30
7
181 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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Overlapping reports 2 32
Better way to make a query with date filter. 5 36
MS SQL Update query with connected table data 3 38
Negative isnull? 3 14
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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