Does anybody are conscious of any code or tools that may strip literal values from SQL claims?
The reason behind asking is I wish to properly judge the SQL workload within our database and I am worried I would lose out on bad claims whose resource usage get masked since they're displayed separate claims. When, the truth is, they're virtually exactly the same factor aside from different IDs being passed in.
I'd should you prefer a database independent solution if any is available. I'd thought there can be a great Perl module with this however i haven't found any.
If you are using JDBC or something like that like thar your SQL should not have literals, just '?' marking where they must be.
So, what you are saying is you might have some badly carrying out queries which are similar since they're built dynamically, and you would like them to become arranged together being that they are from the same call to ensure that the entire time spent performing will popularity inside your profiling?
I think you will have additional having a regex or something like that. You may can pass the claims via a
SQL prettifier so that they are consistent first, then sanitize them - literal strings ought to be the simplest since they're all in
'' (/'.*'/STRING_LITERAL/), and so the number literals may be possible by getting rid of
/\d*/NUMERIC_LITERAL/ or similar.
I understand you desired a database-independent option, but when you are using Microsoft SQL Server, the free MS ReadTrace (for SQL 2005) and Read80Trace (for SQL 2000) just do this. They have a trace output file and strip the literals. I've not seen a db-independent solution either (and that i work with Mission Software, a writer of db-independent tools).
The Semantic Designs source code internet search engine is going to do this trivially, having its SQL scanner.
The Internet Search Engine lexes source code to create lexemes, including exactly individuals literal strings you after. It indexes all of the lexemes and uses that index to help you search/browse your source code base (even when its huge) within an interactive manner.
The Internet Search Engine query: S (yes, just an S) will locate all literal "S"trings inside your SQL text. All of the query answers are proven inside a hit window, and you will click the right path straight to see the code under consideration from the hit. There is a logging facility, which will record all the hits for later analysis.
I believe I'd use
sed to resolve this. Something similar to this
$ cat sql.txt SELECT * FROM USER WHERE USERID = 123 OR USERNAME LIKE 'Name1%' SELECT * FROM USER WHERE USERID = 124 OR USERNAME LIKE 'Name2%' SELECT * FROM USER WHERE USERID = 125 OR USERNAME LIKE 'Name3%' SELECT * FROM USER WHERE USERID = 126 OR USERNAME LIKE 'Name4%' $ sed -e "s/\([0-9]\+\)\|\('[^']*'\)/?/g" sql.txt SELECT * FROM USER WHERE USERID = ? OR USERNAME LIKE ? SELECT * FROM USER WHERE USERID = ? OR USERNAME LIKE ? SELECT * FROM USER WHERE USERID = ? OR USERNAME LIKE ? SELECT * FROM USER WHERE USERID = ? OR USERNAME LIKE ?