• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Add Exists Function To DB Helper

#1
Sometimes we only want to know our criteria is there in the table. We don't want to count all of results.
In small tables it's not problem with use count_all_results() or ->get()->num_rows() but with big data this is really big performance problem.

If you add an "exists" function like "count_all_results", it will be a good choise.

You can select like these solutions:

SELECT TOP 1 * FROM TABLE INNER JOIN ANOTHERTABLE ON ....=.... WHERE ..... .....

or

SELECT EXISTS (SELECT * FROM ..... WHERE ......)

or

SELECT CASE
WHEN EXISTS (SELECT *
FROM TABLE
WHERE X <> 0
and Y = 22) THEN 1
ELSE 0
END AS NON_ZERO_EXISTS


...
...
like query results.

I think; it is simple for your current progress of file.
I'm a person from Turkiye. I don't know English very well and i can't write what i want to say sometimes (as now happenes  Blush ).

If i write something by mistake; please don't distress it and try to understand what i want to write.
Reply

#2
Oops!!, just realised this in for CI4 so it may not apply.

Not really sure what you are looking for.

But as I understand you only want to know if a value/record is found at lease once in the table


SELECT * FROM table where column1 = 'criteria1' AND column2 = 'criteria1' LIMIT 1;


Then just check if you have a record or not.

PHP Code:
$query $this->db->get_where('mytable', array('col1' => 'val1' ,'clo2' => 'val2'), 1);

if(
$query){
 echo 
'found one maybe more';
}
else {
 
 echo 'Nada';

A good decision is based on knowledge and not on numbers. - Plato

Reply

#3
Thank you. This method really usefull.

Yes you understand what i looking for.

I'm using this query simply for search if exists:
if($this->db->select('TOP 1 *',false)->from('TABLE')->where(array('col1' => 'val1' ,'clo2' => 'val2'))->get()->num_rows()===1){
//EXISTS
}else{
//NOT EXISTS
}

I only want an extra function for these type problems.

Another note; if you don't use MSSQL SERVER versions that before 2012, there are some problems with LIMIT syntax. And it will be a performance problem.
I'm a person from Turkiye. I don't know English very well and i can't write what i want to say sometimes (as now happenes  Blush ).

If i write something by mistake; please don't distress it and try to understand what i want to write.
Reply

#4
Hi,

This may still not be appropriate for CI4, I am not sure as I have not used it yet.
However, I think this is too specific to be included in a framework.

But based on you first post you are concern about performance on large datasets.

As far are I know SQL, EXISTS is use with sub-queries and in most cases (if not all) a sub-query will require the server to create a temporary table in memory or on disk ( depends on server environment ). 
If you use a Limit clause, the result is return after the limit is reach.

So in theory a limit clause would perform  better
Index columns that are use for search will increase search performance, but will affect insert/update operations.
So you need to evaluate what is more taxing on the server.
It may also be a good idea to look into different SQL server.

Just my 2 cents.
A good decision is based on knowledge and not on numbers. - Plato

Reply

#5
I don't think that it's too specific for framework because it's like "count_all_results" function. It can be counted simply with queries. But developer added this function for users may need. I think this the reason why EXISTS function may add.
I'm a person from Turkiye. I don't know English very well and i can't write what i want to say sometimes (as now happenes  Blush ).

If i write something by mistake; please don't distress it and try to understand what i want to write.
Reply

#6
Maybe it was not clear, It is just my opinion. 

I maybe wrong, but if I take your example you only want to know if a value is found in the table, no matter how many time and you don't need the records. I don't see how you can write any sort of query that will out perform:


PHP Code:
$query $this->db->get_where('mytable', array('col1' => 'val1' ,'clo2' => 'val2'), 1);

if(
$query){
 echo 
'found one maybe more';
}
else {
 
 echo 'Nada';


You could add a select to limit it to only 1 field and if you use query rather than the query_builder could be a bit better on performance, but it would be minimal
An EXISTS function to suite your example and your need in this instance would pretty much do the same as this.
A good decision is based on knowledge and not on numbers. - Plato

Reply

#7
Yes i accept i can do what i want by simple queries like yours or i wrote as example. I only recommended a function like "count_all_results" that it has a lot of simple query alternatives like this. For example:

$query = $this->db->get_where('mytable', array('col1' => 'val1' ,'clo2' => 'val2'));
$count_all_results=$query->num_rows();

And no need for "count_all_results()" function; with your thinking.
I'm a person from Turkiye. I don't know English very well and i can't write what i want to say sometimes (as now happenes  Blush ).

If i write something by mistake; please don't distress it and try to understand what i want to write.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.