Strategies for using BLOB functions in Voyager Prepackaged Access Reports
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
What are some strategies for using the BLOB functions in Prepackaged Access Reports?
Answer
The BLOB functions (GetBibBlob, GetFieldRaw, GetSubField, etc.) are priceless for people who write queries against a Voyager database, because they often provide the only way to get at data in certain MARC fields. But if you have a large database and if you don’t use the BLOB functions wisely, you can easily create reports that run for hours.
The reason the BLOB functions are slow is this: They are implemented in Access on your workstation. If you use a BLOB function on, say, all the bib records in your database, then all of the bib records have to be brought across the network to your workstation, and then parsed out by your workstation. Today’s workstations are powerful computing machines, but parsing MARC records is an intensive task and and the data transfer processing is highly susceptible to network vagaries.
Here are a few strategies for using the BLOB functions:
- One strategy is to make sure you really need to use the BLOB functions. See: Alternatives to BLOB queries in Voyager Prepackaged Access Reports
- Test your BLOB query to make sure it works by running it on one or two records that you have determined will be included in your result set.
- Another strategy is to use the BLOB functions on the smallest set of records that will still let your query accomplish what you need to do. In other words, create the shortest possible list of BIB_IDs, MFHD_IDs or AUTH_IDs on which the BLOB function will be used. A good way to do this use a Make Table query.
- A fourth is to avoid putting criteria on a column that is created by BLOB functions. When you use criteria, Access is going to get all of the records in your database, bring them to your workstation, and parse them all there to see if they meet the criterion you have specified.
See also: How do I find records with a specific MARC field in the database? for alternatives to using Prepackaged Access Reports (such as Global Data Change) for finding records with specific MARC fields in the database.
Additional Information
BLOB stands for Binary Large OBject. The whole MARC record is stored as a Binary Large OBject and the BLOB functions allow you to get at any piece of the MARC record.
BLOB queries are a specific type of query that accesses special MS Access MARC functions provided by Ex Libris. The Blob is the full raw MARC record in one long string. These queries can be VERY SLOW. When you do a blob query, your PC sends the query to the Voyager database server. The server does *some* of the processing, but also sends entire MARC records back to your PC to be parsed on your PC by the local MS Access VB blob functions. Each nested function will have to run for every record.
For non-BLOB queries, your PC sends the query to the Voyager database server, the server processes the query and sends the results back to your PC. For the most part, all the processing is done on the server and the only data transferred across the network are the query and the results. Any slowness is typically attributable to network-related (LAN versus WAN) issues.
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 02-Jun-2020