What''s your opinion?

Question

What''s your opinion?

SQL-290976

SSC Enthusiast

Points: 169
More actions
December 4, 2006 at 7:26 am

#170634

Dear All
I have write a SP in which i use "code" field in query in where clause. This field was int. For the purpose of optimization according to the client now i want to change the Code field into CHAR(10) so i changed and after this change when i execute the SP it's take to much time as compare to before . Before change it was executed for the month in 15 months and after change it takes more than one hour for the month.
What can be issue with it, any body help me.

Thanks
Qaiser

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Change the field back to int, make sure they are still indexed (if they were before). And never let the client again decide of the DB design!

MohammedU SSCoach Points: 19644 More actions · Answer 2

Did you change the datatype in a proc or table?

It could be Data Type Precedence issue.

http://msdn2.microsoft.com/en-us/library/ms190309.aspx

MohammedU
Microsoft SQL Server MVP

SQL-290976 SSC Enthusiast Points: 169 More actions · Answer 3

Hi Mohammad Uddin

Yes i have changed data type in proc. as well as in table.

Bhudev Prakash SSC-Addicted Points: 471 More actions · Answer 4

U must follow ---> Change the field back to int, make sure they are still indexed (if they were before). And never let the client again decide of the DB design!

Here You needed is to satisfy the Client. There was no need to touch Client Data. Simply should make temp table and steps like U want and with Display Estemated Execution Plan so that U can satisfy your client.

Bhudev Prakash

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 5

If I understand correctly, the client wants to see a character value, not the integer value. You can accomplish this by creating a lookup table that holds the text value of the code. Then all you need to do is index on the Integer and, depending on performance, the character field. You join on the lookup using your integer code and return the character value in the select. Keeps the procedure performing well and satisfies the client request.

"Never touch the client data"

I would agree with this with one addition. "Never touch the client data unless the client fully understands what you are doing". Your client will need to know the full impact of the change they request and the permissions you need to accomplish thier goal. Sometimes, for optimization purposed, it is necessary to re-architect the database and refactor the procedures.