C# How to query memory data coming from Oracle?

Sorry if this question does not sounds well but the thing is that we have a huge database and we need to speed up the process of a particular query. The query displays the 'Project Numbers' for specific users. This operation takes 51 secs everytime but if we remove the 'Like's it displays a big list in just 3 secs. so what I am thinking  to do is to use the second query and make the filter  'in memory' to display the projects filtered to the user.

The question is... how can I query the memory? Can you point an example? somebody told me Linq cna be the best choice for C# but I dont know Linq.
Can you help me with an example?
LVL 2
José PerezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
The question does not make sense, to me anyway. What do you mean by querying memory data coming from a database? You do not query memory when working with a database, you query the database itself, it's designed for that purpose.

The problem is probably with the query or the design of the database.

If you are targeting "specific users", why to you need a Like? Most database would have an indexed field to target such information, so that Like would not be necessary and searching would be fast.

The problem might thus come from the structure of the database. You might be aggregating data in one field (i.e. user+region) while 2 fields would be a better choice. You might be missing an index that would be relevant to your query.
0
sdstuberCommented:
Can you post your queries?
0
José PerezAuthor Commented:
This is the query:

select distinct dm_group.group_name
from dm_group_sp  dm_group, 
	(select distinct gr1.users_names as i_all_users_names, 
	gr2.i_supergroups_names as group_name 
  from dm_group_r gr1, 
	dm_group_r gr2 
	where gr1.r_object_id = gr2.r_object_id 
	and gr1.users_names is not NULL) gr3  
where ((gr3.i_all_users_names LIKE 'John W. Adams')) 
and gr3.group_name = dm_group.group_name
and dm_group.group_name like '%controlling'
or dm_group.group_name Like '%contributing'
order by dm_group.group_name asc

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

José PerezAuthor Commented:
my idea is to take the result of the query and then filter it 'in-memory' (in a C# array, or a List). Does this make sense now? (I hope so)
0
Jacques Bourgeois (James Burger)PresidentCommented:
This might shows a design problem with the database.

If you have many groups whose name finished by "controlling", "contributing" and the likes, and if you often need to query on that particular piece of information, you should have a field, something like GroupType, that handles this information. You would thus be able to get rid of the Like.

Also, by making it numeric, such as 1 for controlling, 2 for contributing, you would be able to search for 1 and 2. Queries are a lot faster when done on a numeric field than when done on text.

And LIKE 'John W. Adams' will give the same result as 'John W. Adams', because you do not use any token in it. The LIKE is useless and slows down things.
0
sdstuberCommented:
>>> my idea is to take the result of the query and then filter it 'in-memory' (in a C# array, or a List).

by "filtering" do you mean removing unwanted rows from your results?
If so, don't do that - let the database process data - they are really good at it.

also, are your AND and OR conditions correct?

The way you have the WHERE clause written, if the first 3 conditions are FALSE but the last one is TRUE, then the entire where clause is true.

 WHERE ((gr3.i_all_users_names LIKE 'John W. Adams'))
     AND gr3.group_name = dm_group.group_name
     AND dm_group.group_name LIKE '%controlling'
      OR dm_group.group_name LIKE '%contributing'


So, if  dm_group.group_name LIKE '%contributing' is true, it doesn't matter what the other 3 conditions are.  That can be bad with join conditions because it means every row where dm_group.group_name LIKE '%contributing' will join to every row in the gr3 set, which I'm guessing is not what you really want.

What are the unique criteria for each of these tables?

And, why do you do a distinct on 2 columns in your subquery when only one of those columns is relevant to the results?  In fact, why do a distinct at all with the join, why not just use an IN or EXISTS clause?


Try one of these variations instead, I am making an assumption in these about the ANDs and ORs... so the functionality is not what you started with but maybe is more accurate to the intent as well as likely running with better performance.


  SELECT DISTINCT group_name
    FROM dm_group_sp
   WHERE (group_name LIKE '%controlling' OR group_name LIKE '%contributing')
     AND group_name IN (SELECT gr2.i_supergroups_names
                          FROM dm_group_r gr1, dm_group_r gr2
                         WHERE gr1.r_object_id = gr2.r_object_id AND gr1.users_names LIKE 'John W. Adams')
ORDER BY group_name ASC;

Open in new window

   SELECT DISTINCT group_name
    FROM dm_group_sp dm_group
   WHERE (group_name LIKE '%controlling' OR group_name LIKE '%contributing')
     AND EXISTS
             (SELECT NULL
                FROM dm_group_r gr1, dm_group_r gr2
               WHERE gr1.r_object_id = gr2.r_object_id
                 AND gr1.users_names LIKE 'John W. Adams'
                 AND gr2.i_supergroups_names = dm_group.group_name)
ORDER BY group_name ASC;

Open in new window

0
PortletPaulfreelancerCommented:
This simply makes no sense:
           SELECT DISTINCT
                 gr1.users_names AS i_all_users_names
               , gr2.i_supergroups_names AS group_name
           FROM dm_group_r gr1
              , dm_group_r gr2
           WHERE gr1.r_object_id = gr2.r_object_id
           AND gr1.users_names IS NOT NULL

Open in new window

In that you are joining the a table to itself  (FROM dm_group_r gr1 , dm_group_r gr2)
asking for a field to be equal to itself (WHERE gr1.r_object_id = gr2.r_object_id)
Just don't do this, it is a waste of time.

I also would strongly suggest you use ANSI join syntax which in the long run will help simplify your queries. (basically: stop using commas between tables in the FROM clause.)

Try this:
SELECT DISTINCT
      dm_group.group_name
FROM dm_group_sp dm_group
INNER JOIN dm_group_r gr1
      ON dm_group.group_name = gr1.i_supergroups_name
WHERE gr1.users_names = 'John W. Adams'
AND (
      dm_group.group_name LIKE '%controlling'
   OR dm_group.group_name LIKE '%contributing'
    )
ORDER BY dm_group.group_name ASC;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
José PerezAuthor Commented:
Thank you all, really helpful!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.