May 26, 2010 at 1:10 pm
I'm trying to add some computed fields to my select by adding something like column = (Select count(*)...)
But, it is making the query take a really long time to run. Is ther a better way to do it? Below is my query.
SELECT DISTINCT RTOV_USERS.user_id,
RTOV_USERS.fname,
RTOV_USERS.lname,
RTOV_USERS.email,
RTOV_USERS.password,
RTOV_USERS.phone,
RTOV_USERS.delete_date,
RTOV_USERS.suspend_date,
RTOV_USERS.create_date,
RTOV_USERS.user_ref1,
RTOV_USERS.user_ref2,
'0' is_selected,
no_roles = (Select count(*) from RTOV_USERS_ROLES
WHERE upper(user_id) = upper(RTOV_USERS.user_id)),no_ext_roles = (Select count(*) from RTOV_EXT_APPS_USERS_ROLES
WHERE upper(user_id) = upper(RTOV_USERS.user_id)),
inc_req = (Select count(*) from RTOV_USER_MAINT_REQ
WHERE upper(user_id) = upper(RTOV_USERS.user_id) AND (complete_yorn = 'N' OR complete_yorn IS NULL)),
inactive = (Select count(*) from RTOV_USER_MAINT_REQ
WHERE upper(RTOV_USER_MAINT_REQ.user_id) = upper(RTOV_USERS.user_id)
AND request_type = 'DUSER' and approval_status = 'Y' and complete_yorn = 'N'),
approval = (Select count(*) from RTOV_USER_MAINT_REQ
WHERE upper(RTOV_USER_MAINT_REQ.user_id) = upper(RTOV_USERS.user_id)
AND (approval_status = 'N' OR approval_status IS NULL)),
last_changed = (Select max(complete_date) from RTOV_USER_MAINT_REQ
WHERE upper(RTOV_USER_MAINT_REQ.user_id) = upper(RTOV_USERS.user_id))
FROM RTOV_USERS,
RTOV_USER_CUST
I should add that for the computed columns in which I am selecting count(*), I don't really need to know the total number but just if it exists or is > 0.
May 26, 2010 at 4:38 pm
What is that UPPER(user_ID) good for that is used so heavily within your query?
If it's an integer you actually force it to an implicit conversion into an character value just to figure that there is not really an UPPER value of '123'... When using fuctions like that on a column you actually prevent any index to be used leadingto a table scan and dropping performance.
If you just want to know if a row exist, you should look into rewriting your query using the EXISTS claus instead of COUNT(*).
May 26, 2010 at 6:04 pm
Rog Saber (5/26/2010)
I'm trying to add some computed fields to my select by adding something like column = (Select count(*)...)But, it is making the query take a really long time to run. Is ther a better way to do it? ...
I should add that for the computed columns in which I am selecting count(*), I don't really need to know the total number but just if it exists or is > 0.
You might want to check out the EXISTS function then.
Also, if you have a case-insensitive collation, you don't need the UPPER functions. As Lutz pointed out, using a function on the field means you won't be able to use any index on it.
There are 4 counts going on for the RTOV_USER_MAINT_REQ table... this will probably run better by making it a derived table that calculates those 4 values for you. Then just join to it with the user_id to get your data.
You might also want to look at the COUNT(*) OVER (PARTITION BY <field>) function for doing an aggregate windowing function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 27, 2010 at 8:57 am
It is not the UPPER that is causing the problem, it is the select count(*). When I remove the select count(*), the query is very fast. Adding the count(*) bogs it down.
All I want is a flag for the no_roles column to let me know if there are any roles for that user. It doesn't have to be the count.
Here's what I want my output to be:
user_id.....fname....user_ref2.....no_roles
joeabc......Joe.......Colorado.......1
jimabc......Jim........Texas...........0
SELECT DISTINCT RTO_USERS.user_id,
RTO_USERS.fname,
RTO_USERS.user_ref2,
--no_roles = (Select count(*) from RTO_USERS_ROLES
--WHERE upper(RTO_USERS_ROLES.user_id) = upper(RTO_USERS.user_id)),
no_roles = (Select distinct 1 from RTO_USERS_ROLES
WHERE upper(RTO_USERS_ROLES.user_id) = upper(RTO_USERS.user_id)),
FROM RTO_USERS
How can I change this to an exists?
May 27, 2010 at 11:46 am
I ALWAYS tell my developers to isolate logic into functions. Write a user-defined function that inputs your variables and returns your value. This way you simply add this as a field to your query (SELECT xx, yy, dbo.MyFunction(passedValue) FROM AA).
Any change to your logic takes place in the function. You avoid nasty multi-nested select statements, and depending on your need, may only require one function to do multiple purposes (based on the input parameters).
May 28, 2010 at 2:39 am
HI, I think the reason your query is slow is due to the calculated fields within the main select statement. Try changing the query and use derived tables this should make it run more efficiently.
Below is and example, I have only included one of the calculated fields try this and it should return the data more efficiently and then add the other calculated fields in the same manner.
SELECT DISTINCT RTOV_USERS.user_id,
RTOV_USERS.fname,
RTOV_USERS.lname,
RTOV_USERS.email,
RTOV_USERS.password,
RTOV_USERS.phone,
RTOV_USERS.delete_date,
RTOV_USERS.suspend_date,
RTOV_USERS.create_date,
RTOV_USERS.user_ref1,
RTOV_USERS.user_ref2,
'0' is_selected,
alias.NO_ROLES
FROM RTOV_USERS, RTOV_USER_CUST,
(Select count(*) as NO_ROLES from RTOV_USERS_ROLES) as Alias
WHERE upper(alias.user_id) = upper(RTOV_USERS.user_id)
May 28, 2010 at 7:56 am
Thank you for this example - I will give it a try.
Abs-225476 (5/28/2010)
HI, I think the reason your query is slow is due to the calculated fields within the main select statement. Try changing the query and use derived tables this should make it run more efficiently.Below is and example, I have only included one of the calculated fields try this and it should return the data more efficiently and then add the other calculated fields in the same manner.
SELECT DISTINCT RTOV_USERS.user_id,
RTOV_USERS.fname,
RTOV_USERS.lname,
RTOV_USERS.email,
RTOV_USERS.password,
RTOV_USERS.phone,
RTOV_USERS.delete_date,
RTOV_USERS.suspend_date,
RTOV_USERS.create_date,
RTOV_USERS.user_ref1,
RTOV_USERS.user_ref2,
'0' is_selected,
alias.NO_ROLES
FROM RTOV_USERS, RTOV_USER_CUST,
(Select count(*) as NO_ROLES from RTOV_USERS_ROLES) as Alias
WHERE upper(alias.user_id) = upper(RTOV_USERS.user_id)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply