Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

All I want is 1 record and FAST (an existence check)

Status
Not open for further replies.

gnealsr

IS-IT--Management
Oct 28, 2003
3
US
Regardless of how many rows of data there may be that match my selection criteria I only want one so my application knows that "Yes, there is data there that matches the selection criteria". I may or may not choose to bring in the entire result set based on other logic. My application is an interactive display and fast response times are essential.

Does anyone have any suggestions on how I can accomplish this or can you offer help around how I can do a quick existence check of data without going through the expense of bringing in the entire result set?

Is there a technique for doing this?

Of course my assumption is that because I'm returning only one record this operation will be extremely fast but I don't know (I'm a rookie so pardon me if this is a really dumb question).

Any help would be greatly appreciated.
 
Hi gnealsr,
One way to to this is:

SELECT ' '
FROM table
WHERE column1 = value
AND column2 = value

This will not actually pull ANY data from the table but will give you your existential check.

There are other ways, but I think this is the easiest. The efficiency of the SELECT will depend upon your choice of columns in the WHERE clauses. The more you hit the index, the more efficient the query.

Hope this helps
Marc
 
Hi gnealsr,

another way to get more and fast information is:

SELECT count(*)
FROM table
WHERE column1 = value
AND column2 = value

Then you'll get the number of rows that will be result of your select conditions anyway.
If there is no row found the result will be 0.

:) Stephan
 
Hi all,

As I said, there are many ways to do this. Stephan's approach is fine, but will be very marginally less efficient as it causes DB2 to perform a column function evaluation. That said, there should be little in it. In Stephan's approach you will have to check the count(*) field, whereas in the previous example, you will need to check the sqlcode.

Marc
 
gnealsr,

I think the count(*) is a very poor example of an existence check. You want DB2 to stop as soon as it hits the existence, not carry on through the result set to see how many there are.

Use something like this for optimum performance

SELECT 1
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS (
SELECT 1 FROM BIGTABLE B
WHERE COLUMN_A = 1
AND B.IBMREQD = A.IBMREQD)

or FETCH FIRST 1 ROWS ONLY if your version of DB" supports it.

Greg
 
Whoa, you guys were all over that! This was my first ever post so I wasn't sure what to expect in terms of a response but that was fast. I posted this last night and had 4 email responses this morning. Thanks to everyone who took the time to respond. I forwarded everyone's input to our development team and we're going to look at what works best, I'll let you know how it turns out. Thanks again.

Greg
 
Please do remember to let us know which method you decided on and the reasons behind it ..

This will be helpful for someone in the future when they come across this post

Good luck

More DB2 questions answered at
 
New user, so excuse the late response.

If there are contraints on the table, it may actually be quicker to try to insert a row. If that row already exists, then the insert will be rejected by just accessing the index.

Eric
 
Eric,

surely though if the row didn't exist, your now in the sorry situation of having inserted a row into a table, which you didn't really want to insert?

Greg
 
Yes, but it all depends on the application. Perhaps it is checking for existance before inserting, so the row is actually wanted, or maybe 99% of the time the row will exist, in which case it could still be quicker to resolve a sorry situation 1% of the time.

It is just another option.
 
Eric,

sorry to harp on, the original post metions nothing about wanting to do an insert. It's on about obtaining a result set if a certain condition exists.

Your "solution" is going to make DB2 take and escalate locks which it otherwise wouldn't have to do and may also involve the transaction having to be rolled back should the newly inserted record not be required. I can't imagine invoking a rollback is an efficient way to do this.

Cheers
Greg



 
Greg is correct, I wouldn't want a row inserted if I didn't get a hit on the database, that occurred to me when I read Eric's reply (although thanks for the reply Eric). In my application I'm just wanting to quickly hit the database to see if any data exists that matches my key value, based on that result I may provide something on the user interface that allows the user to drill into the result set if they so choose, but there's no need for me to go thru the expense of retrieving the result set if it's not going to be used, especially when I could be dealing with millions of rows across multiple tables.

I have not received any feedback on your suggestions yet but I will forward it when I get it.

Thanks again,
Greg
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top