Re: Eliminating Dynamic SQL Generation with Decision Matrices for Tighter Large Scale Applications

[talexb]

I am not cure I have understood all of your meditation, but let's see if I can address the part titled Issues with runtime SQL generation .

In your example you show a select statement that grabs certain fields (@field) from a table if the CGI variable has been passed in, based on certain conditions (@where).

I suppose an attempt to optimize would have a BEGIN statement generate all of the possible permutations of @fields and @where. The load time (i.e. time to execute the BEGIN) and the memory required to store the results could be considerable, based on the size of each of the arrays.

About the only database tuning that I can think of for a select statement would be indexing on the contrained variables; in this example, weight would be an indexed column.

Caching queries might be possible and even quite efficient, but I don't know if that type of thing can easily be generalized. In this example of a simple select statement, it's likely easier to just generate the SQL and grab the data than to search through a collection of cached queries, based on which fields and constraints have been requested.

Is there a more complicated example to explore the issues that you're interested in pursuing?

--t. alex

"Of course, you realize that this means war." -- Bugs Bunny.