4

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.

  • I am not sure if you are using the broker directly for search purposes, or if the your search also retrieves broker data. Either way, the content Broker should not be used as a search engine. Just an FYI – Hendrik Beenker Oct 8 '13 at 11:26
  • 1
    Hi Hendrik, could you please add some more information to it? From code I found the Class (Product) is inheriting BrokerDS (an abstract class) which uses legacy content delivery apis and the error is also generating from one of the catch block from this class's function. – Anupam Baksi Oct 8 '13 at 12:17
  • Actually, we are getting error with some searches (with criterias set from front end) and the portion generating the error is in the scope of the function of the class I stated above. – Anupam Baksi Oct 8 '13 at 12:32
5

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.

  • Thanks for your reply Mr. Nuno. Logging level changes need to be done in broker configuration? Please suggest. – Anupam Baksi Oct 4 '13 at 6:12
  • I have tried with setting broker log level to debug. but, no results, when I hit the page/button from website. Any suggestions? Actually, after changing the log level the log file has not been updated. Have I missed anything? – Anupam Baksi Oct 4 '13 at 8:00
  • You need to restart the web app after any change to the configuration. – Nuno Linhares Oct 4 '13 at 9:28
  • Thanks again, I tried the same but the log file is not getting updated. Any help regarding this. – Anupam Baksi Oct 4 '13 at 11:03
  • I made the log file work, but other information are coming but not the query thing. anything else need to be done for this. Sorry for disturbing you this much. – Anupam Baksi Oct 4 '13 at 11:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.