Tridion Broker Dynamic query (2009)

Question

We have an scenario, Application based Search (Product Search) where we are getting information from Broker depending on the search criteria. I did not found any Store procedures for this and it uses a class which inherits the legacy Broker class.

Now, it seems it is creating some dynamic query for this and way to find out how the data (what query/sp) is used here?

I feel there should not be any SPs and the query can not be determined from web/application server. Is there any way to get this from database?

Thanks for the help.

Answer 1

Tridion Content Delivery database does not include Stored Procedures indeed, and all queries are generated on the fly. In Tridion 2009 (I believe) the queries are not cached, but the individual objects are cached - so executing the query will hit the database, but then loading each object involved in the result will come from cache (if available).

Anyway, to see what the custom query is being translated to you should increase your logging to DEBUG level and Tridion will write the actual SQL generated by the query to the log. This SQL is generated from the instructions you use with the Tridion Query API.

Answer 2

I tried but did not get the query (dynamic broker query) by writing debug/logs in eventviewer from the application dll or from the broker logs. The query interacts with the legacy Content Delivery APIs (e.g. Tridion.ContentDelivery.DynamicContent.Query)for the desired query and query objects are used here.

Then, using SQL profiler with minimum load on the server, I got the query as mentioned below:

use stridion_broker
go 

SELECT DISTINCT ITEMS.ITEM_REFERENCE_ID, ITEMS.PUBLICATION_ID, ITEMS.ITEM_TYPE 
FROM ITEMS 
INNER JOIN ITEM_CATEGORIES_AND_KEYWORDS JOIN4 ON ITEMS.ITEM_REFERENCE_ID=JOIN4.ITEM_REFERENCE_ID 
INNER JOIN ITEM_CATEGORIES_AND_KEYWORDS JOIN5 ON ITEMS.ITEM_REFERENCE_ID=JOIN5.ITEM_REFERENCE_ID 
INNER JOIN ITEM_CATEGORIES_AND_KEYWORDS JOIN6 ON ITEMS.ITEM_REFERENCE_ID=JOIN6.ITEM_REFERENCE_ID 
INNER JOIN ITEM_CATEGORIES_AND_KEYWORDS JOIN7 ON ITEMS.ITEM_REFERENCE_ID=JOIN7.ITEM_REFERENCE_ID 
INNER JOIN ITEM_CATEGORIES_AND_KEYWORDS JOIN8 ON ITEMS.ITEM_REFERENCE_ID=JOIN8.ITEM_REFERENCE_ID 
INNER JOIN ITEM_CATEGORIES_AND_KEYWORDS JOIN9 ON ITEMS.ITEM_REFERENCE_ID=JOIN9.ITEM_REFERENCE_ID 
WHERE (ITEMS.SCHEMA_ID=(tcmid of schema)
AND ITEMS.PUBLICATION_ID=(tcmid of publication)                                                                
AND JOIN4.CATEGORY=N'Product Filters' 
AND JOIN4.KEYWORD=N'20ppm Mono'                                                                
AND JOIN5.CATEGORY=N'Product Filters'  
AND JOIN5.KEYWORD=N'0-10k'                                                                 
AND JOIN6.CATEGORY=N'Product Filters'  
AND JOIN6.KEYWORD=N'A4'                                                                
AND JOIN7.CATEGORY=N'Product Filters'  
AND JOIN7.KEYWORD=N'Colour' 
AND JOIN8.CATEGORY=N'Product Filters'  
AND JOIN8.KEYWORD=N'20ppm Colour' 
AND JOIN9.CATEGORY=N'Product Categories'
AND JOIN9.KEYWORD=N'Printers Black and White'                                                                            
)

Thanks to all for helping and indicating towards the problem.