July 13, 2012 at 3:48 am
Hi All,
I have the below table.
---------------------------------------------------------------------
CREATE TABLE #TEMP (ACTIONID INT,BID INT,BVALUE VARCHAR(100))
--BOTH PRESENT , NO VALUE AGAINST 6 SO SHOULD BE DONE
INSERT INTO #TEMP VALUES (738775,1,'CBC')
INSERT INTO #TEMP VALUES (738775,2,'CBC')
INSERT INTO #TEMP VALUES (738775,3,'CBC')
INSERT INTO #TEMP VALUES (738775,4,'CBC')
INSERT INTO #TEMP VALUES (738775,5,'CBC')
INSERT INTO #TEMP VALUES (738775,6,NULL)
--BOTH PRESENT , NO VALUE AGAINST 5 SO SHOULD BE ERROR
INSERT INTO #TEMP VALUES (904567,1,'BBP')
INSERT INTO #TEMP VALUES (904567,2,'BBP')
INSERT INTO #TEMP VALUES (904567,3,'BBP')
INSERT INTO #TEMP VALUES (904567,4,'BBP')
INSERT INTO #TEMP VALUES (904567,5,NULL)
INSERT INTO #TEMP VALUES (904567,6,'BBP')
--6 IS MISSING AND 5 IS PRESENT SO SHOULD BE DONE
INSERT INTO #TEMP VALUES (652399,1,'BBP')
INSERT INTO #TEMP VALUES (652399,2,'BBP')
INSERT INTO #TEMP VALUES (652399,3,'BBP')
INSERT INTO #TEMP VALUES (652399,4,'BBP')
INSERT INTO #TEMP VALUES (652399,5,'BBP')
--5 IS MISSING AND 6 IS PRESENT SO SHOULD BE ERROR
INSERT INTO #TEMP VALUES (810067,1,'BBP')
INSERT INTO #TEMP VALUES (810067,2,'BBP')
INSERT INTO #TEMP VALUES (810067,3,'BBP')
INSERT INTO #TEMP VALUES (810067,4,'BBP')
INSERT INTO #TEMP VALUES (810067,6,'BBP')
---------------------------------------------------------------------
I want to group this by ActionID and also derive some value based on row values BID = 5,6 ,
Here are the conditions
1.If there is value against 5 and no value against 6
(NULL or even if there is no row with BID= 6 against that ActionID)
then consider it as 'DONE'
2.If there is value against 6 and no value against 5
(NULL or even if there is no row with BID= 5 against that ActionID)
then consider it as 'ERROR'
3.If there is value in both 5 and 6 then consider it as 'INVALID'
But there can be situation where in for few action id's (refer action id) one of the 5 or 6
is missing in that case also it should be DOne or Invalid as explained above.
I am using group by , but facing problem one of the values 5 or 6 is missing against
a action id
So the expected output is
738775 DONE
904567 ERROR
652399 DONE
810067 ERROR
---------------------------------------------------------------------
I have written the below query ad its giving me expected result , but I need to
use this as a subquery in a view which is already doing some more processing like charindex,min ,max etc,
so over all the view is taking abt 25 seconds to display 100 records.
Is there a way I can improve the above query with some simpler way. If needed i will post the view.
SELECT
T.ACTIONID,
CASE WHEN (
EXISTS (SELECT '1' FROM #TEMP T5
WHERE T5.ACTIONID = T.ACTIONID
AND T5.BID = 5 AND ISNULL(T5.BVALUE,'') <> ''
)
AND
(
NOT EXISTS (SELECT '1' FROM #TEMP T5
WHERE T5.ACTIONID = T.ACTIONID
AND T5.BID = 6 --AND ISNULL(T5.BVALUE,'') = ''
)
OR
EXISTS (SELECT '1' FROM #TEMP T6
WHERE T6.ACTIONID = T.ACTIONID
AND T6.BID = 6 AND ISNULL(T6.BVALUE,'') = ''
)
)
)
THEN 'DONE'
WHEN (
EXISTS (SELECT '1' FROM #TEMP T5
WHERE T5.ACTIONID = T.ACTIONID
AND T5.BID = 6 AND ISNULL(T5.BVALUE,'') <> ''
)
AND
(
NOT EXISTS (SELECT '1' FROM #TEMP T5
WHERE T5.ACTIONID = T.ACTIONID
AND T5.BID = 5 --AND ISNULL(T5.BVALUE,'') = ''
)
OR
EXISTS (SELECT '1' FROM #TEMP T6
WHERE T6.ACTIONID = T.ACTIONID
AND T6.BID = 5 AND ISNULL(T6.BVALUE,'') = ''
)
)
)
THEN 'ERROR'
ELSE 'UNKNOWN'
END AS CHATTYPE
FROM #TEMP T
GROUP BY T.ACTIONID
---------------------------------------------------------------------
July 13, 2012 at 5:10 am
Instead of doing all your processing in the CASE statement, try using subqueries. I was going to do this using CTEs, but decided to go with the multiple join method.
SELECT DISTINCT t1.ActionID,-- BID, BValue,
CASE WHEN t2.ActionID IS NOT NULL AND t5.ActionID IS NOT NULL THEN 'ERROR'
--5 is NULL and 6 has a value
WHEN t3.ActionID IS NOT NULL AND t4.ActionID IS NOT NULL THEN 'DONE'
--5 has a value and 6 IS NULL
WHEN t3.ActionID IS NOT NULL AND t5.ActionID IS NOT NULL THEN 'INVALID'
--5 has a value and 6 has a value
WHEN t2.ActionID IS NULL AND t3.ActionID IS NULL
AND t5.ActionID IS NOT NULL THEN 'ERROR'
--5 is missing and 6 has a value
WHEN t3.ActionID IS NOT NULL AND t4.ActionID IS NULL AND t5.ActionID IS NULL
THEN 'DONE'
--5 has a value and 6 is missing
END AS BValue
--This code does not account for missing 5 and a NULL 6 or a missing 6 and a NULL 5
FROM #Temp t1
LEFT OUTER JOIN (SELECT ActionID FROM #Temp
WHERE BID = 5 AND BValue IS NULL) t2 --5 is set to NULL
ON t1.ActionID = t2.ActionID
LEFT OUTER JOIN (SELECT ActionID FROM #Temp
WHERE BID = 5 AND BValue IS NOT NULL) t3 --5 has a value
ON t1.ActionID = t3.ActionID
LEFT OUTER JOIN (SELECT ActionID FROM #Temp
WHERE BID = 6 AND BValue IS NULL) t4 --6 is set to NULL
ON t1.ActionID = t4.ActionID
LEFT OUTER JOIN (SELECT ActionID FROM #Temp
WHERE BID = 6 AND BValue IS NOT NULL) t5 --6 has a value
ON t1.ActionID = t5.ActionID;
Let me know how this works for you.
July 13, 2012 at 5:17 am
Another thing to look at could be indexes - I assume #TEMP here is a stand in for your real table - is it indexed at all?
July 13, 2012 at 5:32 am
That is a very good point, Gazareth. Even my query won't run very well if the table is a heap.
July 13, 2012 at 7:02 am
Yes, Indexing is a good point , i will check on that. Also I have identified that since this is all static data , it can be pre calculated and saved in new columns instead of processing it each and every time as the values are not going to be changed ever.
July 13, 2012 at 7:11 am
Yay! That will certainly help you out.
July 13, 2012 at 2:42 pm
I don't think you really need indexing, since you can do everything in a single pass of the table, w/o JOINs, etc..
SELECT
ACTIONID,
CASE WHEN BID5_VALUE IS NOT NULL AND BID6_VALUE IS NULL THEN 'DONE'
WHEN BID5_VALUE IS NULL AND BID6_VALUE IS NOT NULL THEN 'ERROR'
WHEN BID5_VALUE IS NOT NULL AND BID6_VALUE IS NOT NULL THEN 'INVALID'
WHEN BID5_VALUE IS NULL AND BID6_VALUE IS NULL THEN '?MISSING?' END AS Result
FROM
(
SELECT
ACTIONID,
MAX(CASE WHEN BID = 5 THEN BVALUE END) AS BID5_VALUE,
MAX(CASE WHEN BID = 6 THEN BVALUE END) AS BID6_VALUE
FROM #TEMP
GROUP BY
ACTIONID
) AS derived
--ORDER BY
--ACTIONID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 13, 2012 at 7:18 pm
ScottPletcher (7/13/2012)
I don't think you really need indexing, since you can do everything in a single pass of the table, w/o JOINs, etc..
SELECT
ACTIONID,
CASE WHEN BID5_VALUE IS NOT NULL AND BID6_VALUE IS NULL THEN 'DONE'
WHEN BID5_VALUE IS NULL AND BID6_VALUE IS NOT NULL THEN 'ERROR'
WHEN BID5_VALUE IS NOT NULL AND BID6_VALUE IS NOT NULL THEN 'INVALID'
WHEN BID5_VALUE IS NULL AND BID6_VALUE IS NULL THEN '?MISSING?' END AS Result
FROM
(
SELECT
ACTIONID,
MAX(CASE WHEN BID = 5 THEN BVALUE END) AS BID5_VALUE,
MAX(CASE WHEN BID = 6 THEN BVALUE END) AS BID6_VALUE
FROM #TEMP
GROUP BY
ACTIONID
) AS derived
--ORDER BY
--ACTIONID
Simple, slick, easy to read, fast, and works in all versions of SQL Server. Nice!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2012 at 3:55 am
Scott, this is really simple , thanks a ton !!...
July 16, 2012 at 7:01 am
Hi Guys ,
I changed the query as per scott's reply in below query.
It is taking too long to execute , I somewhere read that there are issues with Sql server 2008 in seeking the rows whe min or max functions are used ,not sure if this is true at all, need your views on this.
SELECT SESN.*,
CASE WHEN CHARINDEX('@BT.COM',Representative) > 0 AND ISNUMERIC(LEFT(Representative,CHARINDEX('@BT.COM',Representative)-1))=1 THEN
LEFT(Representative,CHARINDEX('@BT.COM',Representative)-1) ELSE 999999999 END AS EIN,
SMART.Network_Type,
CASE WHEN SmartBoxValue5 IS NOT NULL AND SmartBoxValue6 IS NULL THEN 'L2C'
WHEN SmartBoxValue5 IS NULL AND SmartBoxValue6 IS NOT NULL THEN 'T2R'
WHEN SmartBoxValue5 IS NOT NULL AND SmartBoxValue6 IS NOT NULL THEN 'Unknown Chat Type'
WHEN SmartBoxValue5 IS NULL AND SmartBoxValue6 IS NULL THEN 'Unknown Chat Type'
END AS Chat_Type
FROM eChat.dbo.g2a_sessions SESN LEFT OUTER JOIN
(SELECT SessionId,
MIN(CASE WHEN ((SMART.SmartBoxValue LIKE '%cbuk%') or(SMART.SmartBoxValue LIKE '%bbip%'))
THEN '20C'
WHEN SMART.SmartBoxValue LIKE '%bbeu%'
THEN '21C' ELSE 'Unknown Network Type'
END) AS Network_Type,
MIN(CASE WHEN SmartBoxNumber = 5
THEN SmartBoxValue
Else Null
END) AS SmartBoxValue5,
MIN(CASE WHEN SmartBoxNumber = 6 THEN SmartBoxValue END) AS SmartBoxValue6
FROM eChat.dbo.g2a_smartbox SMART WITH(NOLOCK) GROUP BY SessionId ) SMART
ON SESN.SessionId = SMART.SessionId
Please note there are no indexs on this table as this table is populated by another component via DB link.
July 16, 2012 at 7:07 am
EDIT: changed question.
Why are you using LIKEs using wildcards on both sides of the keyword? You do it 3 times.
Also, you added a table to Scott's original query in the FROM clause. Is that other table indexed?
Lastly, why should the method of table population prevent you from adding indexes to your tables?
July 16, 2012 at 10:10 am
Thats bcoz , the text i m finding can be anywhere in the value.
There are no indexes on any of the tables used in the query.
Since its being populated from oracle DB via a DB link and it runs every 5 mins , so if insert wwould take more time as there
would be huge data (in lakhs).
July 16, 2012 at 10:33 am
Unfortunately, you can't have both fast inserts and fast reads. You're going to have to pick one or the other, fast inserts and slow reads or slower inserts and faster reads.
If you provide the DDL and sample data on the actual tables involved, we can help you troubleshoot much better. And we'd need a lot more sample data then the short few rows you gave us on the temp table just to verify the issue.
I'm sure those LIKE statements are killing you. The asterick isn't helping you much, either. Try breaking that down to individual column names.
July 16, 2012 at 12:02 pm
Brandie Tarvin (7/16/2012)
The asterick isn't helping you much, either. Try breaking that down to individual column names.
Although I am not a fan of using *, how will eliminating that improve performance? I have not heard of this.
Jared
CE - Microsoft
July 16, 2012 at 12:17 pm
SQLKnowItAll (7/16/2012)
Brandie Tarvin (7/16/2012)
The asterick isn't helping you much, either. Try breaking that down to individual column names.Although I am not a fan of using *, how will eliminating that improve performance? I have not heard of this.
First, the caveat: performance issues depends greatly upon your database and table schemas. You might never see performance issues using the *.
That being said, when you use an *, SQL has to go to the table before executing the query to see what columns are available. Again, depending on your schema, this could take time. And depending on the query (how many tables, views, indexed views are involved), it could take long enough to actually show as a performance hit. EDIT: If you list the columns, though, SQL only has to verify the exact columns are there, and it doesn't have to pull back the data immediately of all columns on the table. * means it's got to pull everything, regardless of whether or not you're using that data. And that really shows in SSIS, SSRS, and SSAS, BTW. Of course, I haven't tested 2k8 to see if the same performance issues exist that existed in 2k5.
The last issue with using * is the one that really counts in my book. If you use * in a view or function or proc, then change the underlying table schema, the cached data can cause failures because the information the object is expecting back is different from what is really there. Granted, you can't really call this a performance issue, but when I have to take 4 hours out of my work day to drop and recreate views because someone changed a table schema, I most certainly will be ranting about lost time to anyone in earshot. And this last one has happened to me often enough, that I won't let anyone write code that uses * at my work place.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply