Using dynamic SQL
-
We have a legacy database that is "configurable" by setting values in a settings table. Many of the SP in the database then pull values from this table and create dynamic SQL to query the DB. We are now having performance issues with these queries. As an example, we push data to another DB on the same server. The name of that database is stored in the table. The SP retrieves the name, creates a SQL statement and then uses sp_execute to execute the statement. Is there a better way to handle this so that the SQL can be compiled and optimized? Thx very much. Mark Jackson
-
We have a legacy database that is "configurable" by setting values in a settings table. Many of the SP in the database then pull values from this table and create dynamic SQL to query the DB. We are now having performance issues with these queries. As an example, we push data to another DB on the same server. The name of that database is stored in the table. The SP retrieves the name, creates a SQL statement and then uses sp_execute to execute the statement. Is there a better way to handle this so that the SQL can be compiled and optimized? Thx very much. Mark Jackson
Before you make any changes, verify where the performance degredation is. Have you used SQL Profiler ? You can trace all kinds of things and try to determine what is going on. Generally, if you see high CPU usage on database server, it is an indication of lots of dynamic SQL being parsed / executed. You should see low CPU and lots of I/O on a normally operating db server. Maybe you just need to re-create the indexes.
-
We have a legacy database that is "configurable" by setting values in a settings table. Many of the SP in the database then pull values from this table and create dynamic SQL to query the DB. We are now having performance issues with these queries. As an example, we push data to another DB on the same server. The name of that database is stored in the table. The SP retrieves the name, creates a SQL statement and then uses sp_execute to execute the statement. Is there a better way to handle this so that the SQL can be compiled and optimized? Thx very much. Mark Jackson
mjackson11 wrote:
pull values from this table and create dynamic SQL
That's insane, but doing it in other code probably won't make much of an improvement. However, what I would prefer to do is to have a Connection to each database open and copy the data via a DataReader and ExecuteNonQuery. One of the benefits of this technique (in my experience) is the ability to log progress and errors as appropriate. This also makes moving one of the databases to another server less problematic.
-
mjackson11 wrote:
pull values from this table and create dynamic SQL
That's insane, but doing it in other code probably won't make much of an improvement. However, what I would prefer to do is to have a Connection to each database open and copy the data via a DataReader and ExecuteNonQuery. One of the benefits of this technique (in my experience) is the ability to log progress and errors as appropriate. This also makes moving one of the databases to another server less problematic.
PIEBALDconsult wrote:
That's insane
Insane it may be, but, there are business cases where this method is, in fact, required. We have a data warehouse system that needs to extract data from a various third party or legacy systems. We have no control over if and when the database structures will change in the other systems and we do not have access to the databases to add views to define the data we need. Since I, personally, DETEST seeing SQL statements hard-coded in an application, I am left with the option of storing the SQL statements for use at run-time. So.... the SQL statements are stored in a database and SQL statements are dynamically 'built' for execution. Is it perfect? No. Does it work? Yes. If someone can present a better solution that fits into the business climate at hand, I would be more than willing to listen. Tim
-
PIEBALDconsult wrote:
That's insane
Insane it may be, but, there are business cases where this method is, in fact, required. We have a data warehouse system that needs to extract data from a various third party or legacy systems. We have no control over if and when the database structures will change in the other systems and we do not have access to the databases to add views to define the data we need. Since I, personally, DETEST seeing SQL statements hard-coded in an application, I am left with the option of storing the SQL statements for use at run-time. So.... the SQL statements are stored in a database and SQL statements are dynamically 'built' for execution. Is it perfect? No. Does it work? Yes. If someone can present a better solution that fits into the business climate at hand, I would be more than willing to listen. Tim
Tim Carmichael wrote:
Since I, personally, DETEST seeing SQL statements hard-coded in an application
That's where they belong, so you know they can't be easily changed. And it is to be hoped that any changes are properly tracked in your version control system with a proper paper trail and then tested. Stored procedures are too fragile and should be avoided at all costs. I've even had stored procedures "disappear"... there one day, gone the next.