Michael -
Do you get the same answer sets in both? Mixing AND / OR without explicit () can be
trouble. My initial read of
version is
WHERE (itemnum = 739434311 AND (dynamic = 0 AND date_end <{ts '2003-06-28 23:12:47'})
OR status = 0)
That is not necessarily how SQL Server see the Where clause. Regarding your question
of the second occurrence of
itemnum, that depends on how SQL Server parses the Where into execution steps.
Typically the equality tests are
faster than non-equality AND tests with functions (CONVERT and DATETIME) are usually
slower. With that in mind, I
suggest executing the query with the date < CONVERT(DATETIME, '2003-06-28 23:12:47',
102) at the end of the query.
All of these statements assume that NONE of the Where columns are indexed.
I am more familiar with DB2's Explain than SQL Server's utility tool. If you can find
that and a decent reference
manual on how to interpret the information then you should be almost home. The only
remaining piece would be some
application knowledge, like what is indexed, how many unique occurrences of an index
value are there, etc? If you
know how to use one of the tools, then you can tweak your code. Most of the time, DB2
does a great job of choosing
the best path. There are times when tweaking your code yields dramatic results.
-brian
Michael Dinowitz wrote:
> OK, I've got one query that was written by someone else that goes one way. When
> I run it directly in the SQL2000 interface, it gets turned into the second
> version below. Is the second more efficient? It doesn't look like it is...
> SELECT COUNT(itemnum) AS isComplete
> FROM items
> WHERE itemnum = 739434311
> AND (dynamic = 0 AND date_end <{ts '2003-06-28 23:12:47'} OR status =
> 0)
>
> SELECT COUNT(itemnum) AS isComplete
> FROM items
> WHERE (itemnum = 739434311) AND (dynamic = 0) AND (date_end <
> CONVERT(DATETIME, '2003-06-28 23:12:47', 102)) OR
> (itemnum = 739434311) AND (status = 0)
>
> Bottom line question: Is the second query more efficient and why?
> Thanks
>
> Michael Dinowitz
> Finding technical solutions to the problems you didn't know you had yet
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
