Performance Improvement

  • Hi,

    I have a small query - by changing the datatypes in a table, will it improve the performance. For example, in one of the db I had identified the column SexCode defined as Varchar(1), where as it can be defined as Char(1). By doing so, am going to save the extra 2 bytes Varchar consumes, and the value stored in the column is either - M/F. Also, the table is filled with data. Need suggestions.

  • Unlikely to have a noticeable effect on performance

    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
  • Hi Gail,

    It's not a single column in the table, almost all the columns are defined in that manner. If changing the datatype doesnot improve, do you have any other suggestion through which I can improve the performance. Other problem is, these db's are used for Reporting and they are not using any SP's to generate them, but rather dynamic SQL Statements. And am not finding an easy way to fine tune the Dynamic SQL Queries. Need your suggestions. One more point, the data in the db is always filled, whever there are any changes i.e., this db doesnot get updated frequently.

  • Tune the queries, tune the indexes.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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
  • Dont think changing datatype will have any significant performance change, unless these columns are used in where clause or filteration purpose, if yes create a index on it and then try. 🙂

  • Hi,

    Thanks for the inputs, yes the guys who have designed the db have created indexes on almost all the columns present in the table. That's the reason - in the first step am planning to change the datatype(s) of the column to the correct way and test it by rebuilding the indexes. In the second step, drop the existing indexes and check the performance by adding individually or through combination i.e., include indexes. All these are the options I had planned to do, and produce a paper to the client for his approval.

    Regards,

    Sai Viswanath

  • in the first step am planning to change the datatype(s) of the column to the correct way and test it by rebuilding the indexes.

    SQL Server can sort integer data much faster than character data, consider changing the datatype to int, if your application permits it.

  • Sai Viswanath (6/10/2013)


    yes the guys who have designed the db have created indexes on almost all the columns present in the table.

    Well that's a problem right there. Single column nonclustered indexes aren't all that useful

    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
  • Bhaskar.Shetty (6/10/2013)


    Dont think changing datatype will have any significant performance change, unless these columns are used in where clause or filteration purpose.

    Unlikely to have an measurable effect even if the columns are used in the where clause of queries. There's no implicit conversions between char and varchar.

    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
  • Hi Gail/Shetty,

    Thanks for the inputs, and I don't think will have a chance to change the datatypes from Char/Varchar to Int. Reason, they have huge archive db's and I think they also need to do some code changes which is not accpeted. Am almost finding some issue or the other against every possibility that am thinking. So, what do now?

  • Sai Viswanath (6/10/2013)


    So, what do now?

    Tune the queries, tune the indexes. If they won't allow that then they must accept that performance will remain poor.

    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
  • Hi,

    Thanks for the useful comments, and just now discussed with some QA guys and got an waque idea of how the work is happening. The job doesnot look like direct performance tuning in SQL Server. Let me come back with the complete picture of how the process is happening on the DB and my list of queries.

    Regards,

    Sai Viswanath

Viewing 12 posts - 1 through 11 (of 11 total)

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