Geek Answers Handbook

What is the best way to infer literal values ​​from SQL to correctly identify the db workload?

Does anyone know of any code or tools that can infer literal values ​​from SQL statements?

The reason for the request is that I want to correctly judge the SQL workload in our database, and I'm worried about missing bad instructions whose resource usage is masked because they appear as separate statements. When in fact they are almost the same, except that different identifiers are transmitted.

I would prefer a database independent solution if one exists. I thought there might be a great Perl module for this, but I did not find it.

Thank you for your help.

+5
source share
6 answers

SQL :: Statement , in particular SQL :: Statement :: Structure , will allow you to analyze and manipulate SQL operations. A subset of the SQL syntax that he understands can be seen here .

In the corresponding DBI :: Profile note , to help with performance analysis.

+9
source

If you use JDBC or something like your SQL should not have any literals, just "?" where should they be.

+2
source

, , , , , , , , , ?

, - . , SQL, , - , '' (/'.*'/STRING_LITERAL/), , /\d*/NUMERIC_LITERAL/ .

0

, , Microsoft SQL Server, MS ReadTrace ( SQL 2005) Read80Trace ( SQL 2000). . db- ( Quest Software, db- ).

http://blogs.msdn.com/psssql/archive/2007/12/18/rml-utilities-for-microsoft-sql-server-released.aspx

0

Semantic Designs , SQL-.

, , . , / ( ) .

:   S (, S) "S" SQL. , , . , .

http://www.semanticdesigns.com/Products/SearchEngine/index.html

0

, sed, . -

$ 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 ?
-1

More articles:


All Articles