Performance Improvement

Question

Performance Improvement

Sai Viswanath

Old Hand

Points: 342
More actions
June 9, 2013 at 10:59 pm

#394936

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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Sai Viswanath Old Hand Points: 342 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 4

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. 🙂

Sai Viswanath Old Hand Points: 342 More actions · Answer 5

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

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

Sai Viswanath Old Hand Points: 342 More actions · Answer 9

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

Sai Viswanath Old Hand Points: 342 More actions · Answer 11

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