Creating parameterized queries Work with both SQL Server and Oracle

Question

Our large application (VB.Net, Framework 3.5) was developed more or less from the very beginning of using SQL Server and SQL Server. Of course, one day someone sold it to a client with the promise that we can get him to work on Oracle (10 g and above), and now this is happening, but we have significant performance problems.

This is due to the fact that almost all SQL (contained in the application code) is in parameterized form queries

SELECT Col1, Col2, Col3 FROM TableName WHERE IdCol = @EntityId 

It is then passed to our data access layer along with an array of parameter names, an array of types, and an array of values ​​and then executed using Enterprise Library 5 to handle the actual connections, etc.

When this project started, someone realized that Oracle requires parameters in the form

 :EntityId 

and decided that instead of trying to find and rewrite each bit of SQL (only one application contains about a million LOC, and there are others in the package), they would add a function that is called immediately before executing a query that replaces @: as in an array of queries , and in the parameter name. It also removes “WITH NOLOCK”, square brackets, and replaces the concatenation characters and other similar artifacts that SQL Server uses, but which Oracle does not. The problem with this, of course, is that finding and replacing strings is expensive and at some point a simple action in the application executes more than 2000 simple queries. It takes very little time against SQL Server, but it takes 20-30 seconds against the Oracle platform.

Ideally, I would like to rewrite huge code codes to weed out bad / inefficient code and design and fix the dodgy architecture and replace the batch of nHibernate or Entity Framework. But this will not happen in the near future due to commercial pressure and the size of the task.

Given that I’m unlikely to make such huge fundamental changes as switching to ORM or redesigning a lot of code, my question is pretty simple:

Is there a way to get SQL Server or Oracle to understand the parameter identifier for another, or some reasonable, easy way to write parameterized queries in a general way without huge amounts of row replacements or IF ... Other statements that depend on the target platform? I understand that I still have to edit almost every SQL statement in the application, but if so, let it be so, I just need to sell the idea to my superiors.

Greetings

Answer 1

I think the easiest way is to simply optimize the query conversion process. For example, you can just cache converted requests and as soon as the request is converted, and then the next time you need to convert it, you jast take it from the cache. And you can just use the hash code of the query string as a key for caching. Like caching query plans. And also you can profile and optimize query conversion too.

This simple caching method is used in a real application. For example, the NHibernate Linq provider (in NH3.0) converts Linq expressions to AST, it is an internal HQL language, and then converts it to sql. And at each step it caches "query plans", so if the same request (same structure, different parameters) needs to be converted again, nh can just take it from the cache