I would dump the results of your subquery into a temp table or table variable and do a join
-----Original Message-----
From: David.Ekren [mailto:mssqldba-ezmlmshield-x8721703.[Email address protected]
Sent: Monday, March 31, 2008 3:51 PM
To: LazyDBA Discussion
Subject: RE: Comparing SQL times
Here is the corrected query example
select f_companyid,
f_companyname,
f_od
from t_tran (NOLOCK)
where f_companyid + '@@' + f_od in (select distinct company_id + '@@' +
od_id
from alerts ALERT6(NOLOCK))
-----Original Message-----
From: David.Ekren
[mailto:mssqldba-ezmlmshield-x73892837.[Email address protected]
Sent: Monday, March 31, 2008 3:39 PM
To: LazyDBA Discussion
Subject: RE: Comparing SQL times
No table scans. I have one table, t_tran, with 17 columns that holds 550
million rows. I have found a report query that is concatenating two of
the columns, both varch(20) and separating them with '@@' and comparing
them to the same concatenated scheme in another table.
Here is the part of the SQL that is brining the query to its knees:
select f_companyid,
f_companyname,
f_od
OR f_companyid + '@@' + f_od in (select distinct company_id + '@@' +
od_id
-- from alerts ALERT6
(NOLOCK)
from t_tran
There has to be a better way to write this. Any ideas?
-----Original Message-----
From: James Fogel
[mailto:mssqldba-ezmlmshield-x46274566.[Email address protected]
Sent: Monday, March 31, 2008 3:53 PM
To: LazyDBA Discussion
Subject: RE: Comparing SQL times
What are the result of ctrl+l? Any table scans?
-----Original Message-----
From: David.Ekren
[mailto:mssqldba-ezmlmshield-x14683393.[Email address protected]
Sent: Monday, March 31, 2008 10:51 AM
To: LazyDBA Discussion
Subject: Comparing SQL times
I am running different queries in SQL Analyzer that all get the same
result sets, but with different indexes and different t-sql. Do I need
to clear the query cache or anything between running these queries to
make sure I am compare the execution times correctly? This is SQL 2000.
Thanks in advance,
David
****************************************************************
The information contained in this E-mail and any of its attachments is
intended only for the use of the address(es) indicated above, and is
confidential. This information may also be legally privileged. If you
are not the intended recipient(s), you are hereby notified that any
dissemination, review or use of the information contained herein is
strictly prohibited. You may not copy, forward, disclose or use any part
of this information. If you have received this information in error,
please delete it and all copies from your system and notify the sender
immediately by return E-mail.
Thank you.
Internet communications are not always timely or secure, and can contain
errors and viruses. The sender does not accept liability for any errors
or omissions which arise as a result.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba
job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
The information contained in this E-mail and any of its attachments is
intended only for the use of the address(es) indicated above, and is
confidential. This information may also be legally privileged. If you
are not the intended recipient(s), you are hereby notified that any
dissemination, review or use of the information contained herein is
strictly prohibited. You may not copy, forward, disclose or use any part
of this information. If you have received this information in error,
please delete it and all copies from your system and notify the sender
immediately by return E-mail.
Thank you.
Internet communications are not always timely or secure, and can contain
errors and viruses. The sender does not accept liability for any errors
or omissions which arise as a result.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
The information contained in this E-mail and any of its
attachments is intended only for the use of the address(es)
indicated above, and is confidential. This information may
also be legally privileged. If you are not the intended
recipient(s), you are hereby notified that any dissemination,
review or use of the information contained herein is
strictly prohibited. You may not copy, forward, disclose or use
any part of this information. If you have received this
information in error, please delete it and all copies from your
system and notify the sender immediately by return E-mail.
Thank you.
Internet communications are not always timely or
secure, and can contain errors and viruses. The sender does not
accept liability for any errors or omissions which arise as a
result.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Disclaimer: This e-mail message is intended only for the personal use of
the recipient(s) named above. If you are not an intended recipient, you
may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete
the original message.
This e-mail expresses views only of the sender, which are not to be
attributed to Rite Aid Corporation and may not be copied or distributed
without this statement.
MS Sql Server LazyDBA home page