July 11, 2011 at 5:08 am
Hi
If I have a sql query that looks like
SELECT * FROM SomeTable WHERE 1=1 OR Name = 'Bob'
Will the Name = 'Bob' be looked at by SQL when returning results? Because 1 will always be equal to 1.
Thanks in advanced
The Fastest Methods aren't always the Quickest Methods
July 11, 2011 at 5:20 am
the query will work as
Select * from <table>
the Or condition Name = 'Bob' is supressed bt the condition 1 = 1.
July 11, 2011 at 5:20 am
Nope, here's the plan. There's absolutely no filter being applied.
SELECT * FROM msdb.dbo.sysjobhistory WHERE 1=1 OR step_name = 'Bob'
|--Clustered Index Scan(OBJECT:([msdb].[dbo].[sysjobhistory].[clust]))
July 11, 2011 at 5:28 am
I understand that no filter will be applied what I'm trying to find out is if the first part of and is true would the second part still run?
I have a light method running as my first part of a condition and a heavier more complex method running if the first part is true and don't want the second part of my where to run if the first is true.
The Fastest Methods aren't always the Quickest Methods
July 11, 2011 at 5:36 am
BinaryDigit (7/11/2011)
I understand that no filter will be applied what I'm trying to find out is if the first part of and is true would the second part still run?I have a light method running as my first part of a condition and a heavier more complex method running if the first part is true and don't want the second part of my where to run if the first is true.
you could always run the query and include the acutal execution plan and see if it is used or not.
July 11, 2011 at 5:42 am
BinaryDigit (7/11/2011)
I understand that no filter will be applied what I'm trying to find out is if the first part of and is true would the second part still run?I have a light method running as my first part of a condition and a heavier more complex method running if the first part is true and don't want the second part of my where to run if the first is true.
What I've posted is the actual execution plan, the full plan. It doesn't contain any type of filters.
If you're unsure about what happens in your query send us the actual execution plan and we'll have a look.
July 11, 2011 at 5:45 am
also in SQL sometimes scenarios that work on a scaled down query do not follow through to work in the same way in a more complex query, as the optimizer does a lot of work in determing query plans the only sure way is to run the query and view the plan
July 11, 2011 at 5:49 am
thanks guys 😀
The Fastest Methods aren't always the Quickest Methods
July 11, 2011 at 5:55 am
In other words, please post the real query so we can help out!
July 11, 2011 at 6:05 am
The code in the stored procedure is
SELECT ISNULL(Vessels.Name, 'unknown') AS Name, AISPositionsCurrent.MMSI, AISPositionsCurrent.Position, ISNULL(VesselGroups.Description, 'Unknown')
AS GroupName, ISNULL(VesselGroups.TypeColour, '000000') AS GroupColor, ISNULL(Vessels.CallSign, 'unknown') AS CallSign, ISNULL(Vessels.IMO, 0) AS IMO,
ISNULL(Vessels.Destination, 'unknown') AS Destination
FROM AISPositionsCurrent
LEFT OUTER JOIN Vessels ON Vessels.MMSI = AISPositionsCurrent.MMSI
LEFT OUTER JOIN VesselTypes ON VesselTypes.VesselTypeID = Vessels.VesselTypeID
LEFT OUTER JOIN VesselGroups ON VesselGroups.VesselGroupID = VesselTypes.VesselGroupID
WHERE @Zone IS NULL OR (AISPositionsCurrent.Position.STIntersects(@Zone) = 1)
ORDER BY GroupName, Name
@Zone is of type geography
The Fastest Methods aren't always the Quickest Methods
July 11, 2011 at 6:10 am
You should be fine. But it type of query can be better served by dynamic sql later down the road as you add lots of optional filters and / or data.
Also keep in mind that there's s tipping point where the plan go from best to ok to bad. You might be close or very far away from that point.
Make sure you test with lots of data and a good number of parameter combos.
July 11, 2011 at 6:14 am
ok, thank you. The lots of data we have 🙂 there's over 5 mil rows, just need to make sure we use diff parameters each time
The Fastest Methods aren't always the Quickest Methods
July 11, 2011 at 6:18 am
BinaryDigit (7/11/2011)
ok, thank you. The lots of data we have 🙂 there's over 5 mil rows, just need to make sure we use diff parameters each time
5M is a decent data sample... only if the data won't grow to 100 M rows.
This is all just capacity planning and it would take a book to really talk this through!
July 11, 2011 at 6:22 am
old data is archeived every night to make sure this row count doesn't go much higher than that 🙂
The Fastest Methods aren't always the Quickest Methods
July 11, 2011 at 6:27 am
Looks like you're good to go. Let us know if you need more help with the real thing!
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply