condition in where to avoid subquery
-
Hi all,
I am having trouble finding a way to query several tables which returns up to 400,000 rows. When I try to use a sub-query I get a timeout. I want to speed this up, and can do so by not returning approx 250k rows. Here is the gist of my query:
Return <account information> between <2 dates> or <condition regardless of dates>, but for <marketing campaign z> return the account info only if the account manager is not <x>
I have tried using something like, "return accounts between <2 dates> or <condition regardless of dates>, where account id not in (select accounts with campaign id z and account manager x)" but that is too slow. How can I in essence put a condition on condition in the where clause? I have tried using Case but I get a "Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DNPISIMPORT'." for the following:
SELECT c.ACCOUNTID ...... b.CONTACTNOW AS ContactNow,
DNPISIMPORT =
CASE
WHEN a.CampaignID = 'Q6UJ9A00IHI7' THEN
CASE
WHEN c.ACCOUNTMANAGERID = 'U6UJ9A00000T' THEN 'True'
ELSE 'False'
END
ELSE 'False'
END
FROM ......
WHERE (a.CREATEDATE >= '10/18/2004') AND (a.CREATEDATE < '10/19/2004') AND (DNPISIMPORT = 'False') OR
a.CONTACTRESPONSEID IN ......
DNPISIMPORT is not in my database, but I read in BOL that you could do this. Is there another way to do this??
-
Hi Brett, try putting single quotes around DNPISIMPORT and running this again.
Regards
Phil
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi
Do it in 2 steps using a table variable (or temp table)
First do your initial select "Return <account information> between <2 dates> or <condition regardless of dates>" where marketing campaign isn't "z". Save the results of this into your table variable
Then do another selection, appending to this table variable, same condititions but where marketing campaign is "z" and account manager isn't "...".
Then select * from this table variable to do what you want.
This may not be so elegant as a one-hit select but when you're dealing with large tables this is the best way.... I suspect that "under the covers" this is the sort of solution that SQL does anyway
Hope that helps
Sam
-
Hi,
Sam - I am led to believe that instead of SELECTing what you want from the temp table - DELETEing what you dont want and then returning the table is the faster option.
But I am submitting this un-tested as a vicious roumor at present 😉
Have fun
Steve
We need men who can dream of things that never were.
-
Thanks everyone for the quick reply. Phil, your method will likely work, but I have a question for you still... I currently have the SQL below:
<code>
SELECT ... ,
'DNPISIMPORT' =
CASE
WHEN a.CampaignID is null THEN 'False'
WHEN a.CampaignID = 'Q6UJ9A00IHI7' THEN
CASE
WHEN c.ACCOUNTMANAGERID is null then 'False'
WHEN c.ACCOUNTMANAGERID = 'U6UJ9A00000T' THEN 'True'
ELSE 'False'
END
ELSE 'False'
END
FROM ...
WHERE
(a.CREATEDATE BETWEEN '9/28/2004' AND '11/2/2004')
and not('DNPISIMPORT' = 'True')
</code>
I took the "OR" clause out to simplify but am still getting rows where 'DNPISIMPORT' = 'True' in my results. Why would these be returned when I specifically say "not('DNPISIMPORT' = 'True')" in my WHERE clause?
- Based on your query, your application looks like SalesLogix. If it is correct. Could you give me your contact information and I have some question about Saleslogix?My email: kmo@gsionline.comThanks
-
Brett, you have created a calculation in your SELECT clause and given it a column heading of 'dnpisimport'. You are not giving the calculation an alias of 'dnpisimport' (this cannot be done, to my knowledge) and therefore, unfortunately, you'll have to re-type all of the CASE construction in the WHERE clause before you can do what you want. Your current query is comparing the text 'dnpisimport' with the text 'true' and deciding that they are not equal!
Phil
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
This si the normal way based on what I read.
SELECT <account info>
FROM <table name>
WHERE
( <marketing campaign> = 'z' AND <account manager> != 'x' AND <account information> between <date 1> AND <date 2>
OR(<account information> between <date 1> AND <date 2>
BETWEEN btw means will include those dates and everything between inclusive.
ANd you lost me on this "or <condition regardless of dates>"
Can you give and example of what you mean. Beyond that performance will boil down to indexes.
-
Yeah - I definitely over-complicated it. After thinking more, the query is just "where combination scenario 1 is never true plus where either scenario 2 or scenario 3 are true" so that's what I tried to create. Here is what I ended up with:
WHERE NOT (a.campaignid = '<mkt campaign id>' AND f.accountmanagerid = '<user id>')
AND ((a.createdate >= '<date 1>' AND a.createdate < '<date 2>') OR a.contactresponseid IN (SELECT DISTINCT a.contactresponseid
FROM sysdba.contactresponse a
LEFT JOIN sysdba.contact c ON a.contactid = c.contactid
LEFT JOIN sysdba.opportunity d ON c.accountid = d.accountid
WHERE d.status = 'Closed - Won' AND d.actualclose >= '<date 1>'))
The OR is in there as well so you can see the entire WHERE clause.
By the way, I tested using BETWEEN instead of the date range usage above and it ran on average 5 seconds slower in QA.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply