CODEWITHSUNDEEP
sql-server
AndyZ
AndyZ

Reputation: 615

Find out the popularity of values

I have a table with 1000 rows. Each row represents a prompt text for an application. For the start I only want to translate the most used 20% of the promts. In the daily use some dialogs appear more often than others. So the prompt texts for the most displayed dialogs get fetched more often than the others. However, it looks to me like there is no built-in mechanism to analyse the data by their select rates.

There are no triggers on select. There is no way to filter the data in the profiler. There is no way to filter data in an Audit. Is that true?

Are there any options to do that inside the SQL Server?

Upvotes: 0

Views: 68

Answers (2)

AndyZ
AndyZ

Reputation: 615

@TabAlleman: "no, there's nothing you can do"

Upvotes: 0

Paul Williams
Paul Williams

Reputation: 17020

No. There is no way to track the frequency of how often data is selected.

This sounds like application metrics. You will have to write metrics logic yourself.

For example, you might create a table of MissingTranslations that tracks the frequency of requests. If your application detects a missing translation, insert a row into this table with a frequency of 1, or increment the counter if it already exists in the table.

You could then write another application that sorts the missing translations by frequency descending. When a user enters the translation, the translation app removes the entry from the list of missing translations or marks it as complete.

All that being said, you could abuse some SQL Server features to get some information. For example, a stored procedure that returns these translations could generate a user-configurable trace event with the translation info. A SQL Profiler session could listen for these events and write them to a table. This would get you a basic frequency.

It might be possible to get the same information from implementing auditing and then calling sys.fn_get_audit_file, but that sounds cumbersome at best.

In my opinion, it sounds easier and more stable to me to write this logic yourself.

Upvotes: 1

Related Questions