View Issues
-
Hey everyone,
I have a problem with one of the views I have created - It takes about 2 mintues to compute the count of all it's records!!
The view is basically all records that are NOT in 3 other views...(everything else kinda thing).
Here's the view:
CREATE VIEW dbo.else_vw
AS SELECT UnlockID, dbo.TrimUser(UserCode) AS UserCode, ExpiryDate, Frequency, Attempted, LastAttemptDate, Success, Email, InsertDate
FROM dbo.Table
WHERE (dbo.TrimUser(UserCode) NOT IN (SELECT UserCode FROM view1))
AND (dbo.TrimUser(UserCode) NOT IN (SELECT UserCode FROM view2))
AND (dbo.TrimUser(UserCode) NOT IN (SELECT UserCode FROM view3))
View1,2 &3 are something like this:
CREATE VIEW dbo.view1
AS SELECT UnlockID, dbo.TrimUser(UserCode) AS UserCode, ExpiryDate, Frequency, Attempted, LastAttemptDate, Success, Email, InsertDate
FROM dbo.Table
WHERE (ExpiryDate = '20040630') AND (CONVERT(varchar(10), InsertDate, 112) = '20040310')
TrimUser() function returns a substring.
My question is, is there any way to optimize this query?? So it doesn't take 2 minutes to get the total count...
-
This should speed it up (assuming views 2 and 3 have the same selection criteria as view1) ...
CREATE VIEW dbo.else_vw
AS SELECT UnlockID, dbo.TrimUser(UserCode) AS UserCode, ExpiryDate, Frequency, Attempted, LastAttemptDate, Success, Email, InsertDate
FROM dbo.Table
WHERE (ExpiryDate) NOT IN ('20040630', '(date2)', '(date3)'))
AND (CONVERT(varchar(10), InsertDate, 112) NOT IN ('20040310', '(date2)', '(date3)'))
Steve
-
Actually I think the where would be more like
WHERE
(ExpiryDate != '20040630' AND CONVERT(varchar(10), InsertDate, 112) != '20040310') OR
(ExpiryDate != 'Edate2' AND CONVERT(varchar(10), InsertDate, 112) != 'Idate2') OR
(ExpiryDate != 'Edate3' AND CONVERT(varchar(10), InsertDate, 112) != 'Idate3')
Also you should not it can sometimes take longer to use NOT conditions and may make more sense to do something like
CREATE VIEW dbo.else_vw
AS
SELECT T1.UnlockID, dbo.TrimUser(T1.UserCode) AS UserCode, T1.ExpiryDate, T1.Frequency, T1.Attempted, T1.LastAttemptDate, T1.Success, T1.Email, T1.InsertDate
FROM dbo.Table T1
LEFT JOIN
dbo.Table T2
ON
(conditions that make a row unique as I don't know from your info') AND
(
(T2.ExpiryDate = '20040630' AND CONVERT(varchar(10), T2.InsertDate, 112) = '20040310') OR
(T2.ExpiryDate = 'Edate2' AND CONVERT(varchar(10), T2.InsertDate, 112) = 'Idate2') OR
(T2.ExpiryDate = 'Edate3' AND CONVERT(varchar(10), T2.InsertDate, 112) = 'Idate3')
 
WHERE
T2.(nonnullablecolumn) IS NULL
And if InsertDate is an indexed field the convert will generally not use the index. May try using BETWEEN instead like so
T2.InsertDate BETWEEN '20040310' AND '20040311'
Which will work only if there is no chance of next day midnight being InsertDate value, otherwise try
T2.InsertDate BETWEEN '20040310' AND '20040310 23:59:59.997'
-
Antares686,
You are absolutely right. Thank you for catching that!
Steve
-
I've tried Using the OR operator beofre (that's actually the first thing I did, something like what Antares686 posted..) but appearantly SQL server doesn't group that statement like I wanted it to:
(this AND this) or (this AND this) or...
is seen as:
(this) AND (this) OR (this) AND (this)
when I run it in Enterprise Manager...and it gives incorrect results that's why I'm using the NOT IN...
Any ideas??
-
SOrry see your point should be
WHERE
(ExpiryDate != '20040630' AND CONVERT(varchar(10), InsertDate, 112) != '20040310') AND
(ExpiryDate != 'Edate2' AND CONVERT(varchar(10), InsertDate, 112) != 'Idate2') AND
(ExpiryDate != 'Edate3' AND CONVERT(varchar(10), InsertDate, 112) != 'Idate3')
with that regards as you want to make sure it is not any of those conditions. However when you use IN
ExpiryDate NOT IN ('20040630','date2'date3') AND
CONVERT(varchar(10), InsertDate, 112) NOT IN ( '20040310','date2'date3')
You actually are doing not equal to any of the following condtions
date1 and date1
date1 and date2
date1 and date3
date2 and date1
date2 and date2
date2 and date3
date2 and date1
date2 and date2
date2 and date3
This is what I could see but still errored with the ORs as should be and since if it is equal to condition set 1 but not to condition set 2 the OR says is valid. By changing to and it will say cannot be Condition Set 1 And it cannot be Condition Set 2 And it cannot be Condition Set 3. Which is what you want here.
The other method with the join would still be right as you want records only when they are any 1 of the 3 conditions not all three.
Those are easy to reverse.
-
Antares686,
I see what you mean, I've tried that already before (when I first encountered the OR problem ) but this causes me to loose records that, for instance, have an expiry date I'm trying to ignore BUT have a different InsertDate and I DO want to count those....
Edate = 20040630 Idate = 20040310 - Ignore
Edate = 20040630 Idate = 20041013 - Count...etc.
That's why creating views and doing the NOT IN op was the onlt idea I could come up with to solve this, but it takes to long.....
*sigh*
-
There must be a better (more efficient) way to do this. My op simply counts the number of records that are NOT IN the specified dates.
There are only about 2400 records....should it really be taking 2 minutes?
-
If all you want is the count, you could simply subtract the count(*) of the 3 views from the count(*) of the base table.
Steve
-
Oops I see the issue now. Silly me if you do this
WHERE
(ExpiryDate != '20040630' AND CONVERT(varchar(10), InsertDate, 112) != '20040310') AND
(ExpiryDate != 'Edate2' AND CONVERT(varchar(10), InsertDate, 112) != 'Idate2') AND
(ExpiryDate != 'Edate3' AND CONVERT(varchar(10), InsertDate, 112) != 'Idate3')
Then you will kill things that are '20040630' and IDate2 instead of getting that one.
Try this
WHERE
(CASE WHEN ExpiryDate = '20040630' AND CONVERT(varchar(10), InsertDate, 112) = '20040310' THEN 0
WHEN ExpiryDate = 'Edate2' AND CONVERT(varchar(10), InsertDate, 112) = 'Idate2' THEN 0
WHEN ExpiryDate = 'Edate3' AND CONVERT(varchar(10), InsertDate, 112) = 'Idate3' THEN 0 ELSE 1 END) = 1
-
Antares686,
Thanks a million! it works in the blink of an eye now! (with the CASE op) ...
Thanx to everyone else who also replied!!
-
Glad could help, sorry thou took me so long to realize what was going on in the query with the ANDs and ORs.
-
Don't be sorry!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply