Solved

HELP!  ARE THESE QUERIES EQUIVALENT?

Posted on 2013-01-30
7
184 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

728 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