Solved

HELP!  ARE THESE QUERIES EQUIVALENT?

Posted on 2013-01-30
7
175 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore 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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now