stored procedures - avoiding recompilations [modified]
-
I am working on optimizing an application that uses stored procedures heavily. Almost every procedure starts with:
SET DATEFORMAT dmy
which causes a recompilation during the procedure execution. It takes about 5 seconds. Is there any way to set the
dmyas a default setting on the startup of the application? Any other ideas how to solve this problem? I use MS SQL Server 2000.modified on Monday, August 30, 2010 6:32 AM
-
I am working on optimizing an application that uses stored procedures heavily. Almost every procedure starts with:
SET DATEFORMAT dmy
which causes a recompilation during the procedure execution. It takes about 5 seconds. Is there any way to set the
dmyas a default setting on the startup of the application? Any other ideas how to solve this problem? I use MS SQL Server 2000.modified on Monday, August 30, 2010 6:32 AM
I know Oracle has a default date format that is set as part of the database configuration. I would suspect the same is true for SQL Server, but I'm not up on it. Either I try to avoid depending upon defaults when writing a stored procedure. In the long run, you will have less headaches if within your procedure, you translate between the strings and date types using explicit formats. For example;
convert a date to a string
to_char(some_date_field,'dmy')convert a string to a date
to_date(some_char_field,'dmy')Chris Meech It's much easier to get rich telling people what they want to hear. Christopher Duncan I can't help getting older, but I refuse to grow up. Roger Wright I've been meaning to change my sig. Thanks! Alvaro Mendez We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
-
I am working on optimizing an application that uses stored procedures heavily. Almost every procedure starts with:
SET DATEFORMAT dmy
which causes a recompilation during the procedure execution. It takes about 5 seconds. Is there any way to set the
dmyas a default setting on the startup of the application? Any other ideas how to solve this problem? I use MS SQL Server 2000.modified on Monday, August 30, 2010 6:32 AM
I'd recommend using invariant date formats throughout: yyyymmdd. You could also set the default language database option - see this article in Books Online[^] for more details.
-
I know Oracle has a default date format that is set as part of the database configuration. I would suspect the same is true for SQL Server, but I'm not up on it. Either I try to avoid depending upon defaults when writing a stored procedure. In the long run, you will have less headaches if within your procedure, you translate between the strings and date types using explicit formats. For example;
convert a date to a string
to_char(some_date_field,'dmy')convert a string to a date
to_date(some_char_field,'dmy')Chris Meech It's much easier to get rich telling people what they want to hear. Christopher Duncan I can't help getting older, but I refuse to grow up. Roger Wright I've been meaning to change my sig. Thanks! Alvaro Mendez We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
Chris Meech wrote: In the long run, you will have less headaches if within your procedure, you translate between the strings and date types using explicit formats I thought about it. However, the problem is that my task is to optimize a system that uses many stored procedures written by different people. I would have to go through all the procedures (I haven't written any of them!) and find places where using converting functions is necessary. Sounds like a nightmare to me. :)
modified on Monday, August 30, 2010 6:32 AM
-
I'd recommend using invariant date formats throughout: yyyymmdd. You could also set the default language database option - see this article in Books Online[^] for more details.
Mike Dimmick wrote: You could also set the default language database option Great idea! I did not know it sets not only the language but also the date format. Thanks.
modified on Monday, August 30, 2010 6:33 AM