About OCTET_LENGTH()

Hello everyone,

I am confused about this function OCTET_LENGTH().

I am using File with 200 MB size, and store that file content by converting into binary, and then store that binary stream into the blob field of the firebird database.

Now when i am using above function for getting the size of that blob field, what is actually happening into the server?

1) Server is using its own indexing system, where it gets the stored size of the blob fields and return.

OR

2) It takes whole fields into memory and then gets the size of that field?

This is important for us for the performance reason.

Any help will be greatly appreciated.
Thanks in advance

Best Regards,
Jay.

I don't think server automagically uses indexes for this, you might try declaring a function based index on the blob column incorporating the function.

Hello,

Thanks for reply.
But, I am not following you.

I want to know, either firebird calculates the size of the blob fields runtime when we use this function, or it stores blob field's size into the database.

Thanks
Best Regards
Jay.

I meant it is calculated at runtime unless you build the function based index I mentioned.
Anyway this can be checked with an explain of your select.

Hello,
Thanks again for your quick reply.

Still I am having some confusion.

As per my requirement, I am having a bunch of files those are going to store into Firebird Database from my application. And while retriving them from database, I want to display their size. As far as performance reason is conserned, I want to know that OCTET_LENGTH() 's working manner. So that I deside that which option is more fisible for my application...

1. Store file size while storing the file content.
2. Calculate file size while retriving the file from database.

For 2nd option, I am using SELECT query like...
SELECT OCTET_LENGTH(blob field) FROM TABLE WHERE ID = 9;

Is OCTET_LENGTH() calculates the content size without taking the whole blob field's data in memory? Or is there any other technique used by Firebird. Please explain in brief.

Thanks in advance.
Best Regards,
Jay.

I think what he's trying to ask is, when you use OCTET_LENGTH on a blob field, does it calculate how many bytes it is by loading the ENTIRE BLOB into memory and then determining how much space it takes up, or when it stores the blob, does it also store its size somewhere too, and its THIS value which is returned by the query.

Pabloj, your answers aren't quite clear in this thread. But since I am still what would be considered a "n00b" when it comes to the intricacies of Firebird, it could be quite possible your answer is still beyond my understanding.

I have done my own tests querying the octet length of blobs, to see if there is a difference in the amount of time. The biggest file I have in my little test database is 16MB, but I would still expect there be a difference in execution time with a 16MB file and a 100KB file, if it did load the entire thing into memory. But my little tests so far have shown absolutely no difference in execution time, no matter what file I try it with.

Note, the largest file I have in my database is only 16MB, so this may not have been a decent test. But I didn't experience any difference in the execution time of the query. Since there was no difference, I'm guessing it doesn't load it entirely into memory.

Thanks to all,

This issue is becoming clear for me but still
needs to get on the destination for this issue, so that our performance worry can be solved.

Jay