Post reply

dynamic sql performance and implementation recs?

  • September 13, 2016 at 5:37 pm

    #329792

    I'm working on a new service that will need to provide reporting data for regions. Each region will have its own db table and each db table will have some columns with the same name like ReportDate. The different tables will have a lot of different/unique columns which is why I created a separate table for each region. There will be about 10 region tables.

    I'm considering using dynamic sql for my sproc so I can write a simple query and then run it generically against each table. This would be much quicker and easier to write and maintain then writing 10 separate queries that perform the same SQL against all tables.

    Is SS pretty well optimized to handle this type of implementation? Do you see any potential performance issues? Are there any specific steps I should take to optimize performance for this implmentation? Does anyone here think dynamic sql is "evil" for one reason or another? I'm referring specifically to dynamic sql completely done within SS as opposed to an external app passing in dynamic sql statements.

  • September 14, 2016 at 3:27 am

    #1900749

    Optimal, maybe. Secure, very likely not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 14, 2016 at 4:11 am

    #1900758

    If you are going to do dynamic SQL, then please, use sp_executesql and use parameter values. Don't let unchecked string parameters determine behaviors. That leads to SQL Injection. When you use sp_executesql and parameters, it's almost the same as using stored procedures in terms of compile & code reuse. Make sure you have 'Optimize for Ad Hoc' enabled. check all your data types and use parameter values. I can't repeat that enough. SQL Injection is a severe problem, and you're moving in that direction pretty rapidly.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply