Q

Using dynamic SQL statements in SQL Server and Oracle database design

I am currently in the design phase of a new application. I would like the application to be flexible enough to use either SQL Server or Oracle databases. (End user can choose either.) Both databases can utilize stored procedures, but their approaches are different (syntax, etc. My question: Should I only use dynamic SQL statements in the application or invest the time and resources to create and maintain different set of Stored Procedures?
That's an excellent question. It all comes down to one basic issue: performance. Since stored procedures will execute faster than any dynamic SQL, if you have a very high throughput and volume application, you might want to consider it. However, in reality, the majority of applications are not that demanding so dynamic SQL would be just fine. That would make maintenance easier which could means you probably will ship faster. One approach I have seen work successfully is to develop the application with a wrapper layer on the database access. The wrapper is written completely with dynamic SQL. Once there's enough of the application put together to test, start performance testing. You'll quickly find that query or two that is taking up the bulk of the time. You can then change those into stored procedures. With the wrapper in place, the rest of the program probably won't even know about the change
This was first published in March 2001

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchServerVirtualization

SearchCloudComputing

SearchExchange

SearchSQLServer

SearchWinIT

SearchEnterpriseDesktop

SearchVirtualDesktop

  • Virtual desktop security guide

    To secure virtual desktops, consider antivirus, certificates and network vulnerabilities. Just remember, VDI doesn't always ...

  • Guide to low-cost desktop virtualization

    In this guide, learn to virtualize desktops without spending more than you would when deploying PCs, and what VDI vendors are ...

  • VDI pilot project guide

    A VDI pilot project should start with a VDI project plan. Know what pitfalls to avoid and test product options to achieve a ...

Close