Data Type Conversion Performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data Type Conversion Performance

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-18 : 09:07:03

Kenneth writes "Straight to the point.... I've got 2 different scenarios:

Scenario 1:

select * from creditor
WHERE convert(nvarchar(10),updated,21) = '01/04/2003'

Scenario 2:

SELECT * from creditor
WHERE Updated >= '01/04/2003 00:00:00' AND
Updated <= '02/04/2003 00:00:00'

Note: The 'Updated' column is of SQL type "DATETIME" as defined in the table design for Creditor. Both return the same data.

Questions:

1. Which of the 2 scenarios will perform the quickest??

Scenario 1 implicitly seems to convert the date value "01/04/2003 00:00:00" provided to a DATETIME.

Scenario 2 explicitly converts the updated field to a NVARCHAR type (10 in length).

The following will help answer Question 1:

2. For Scenario 1, does SQL Server convert 'UPDATED' for each row processed in table CREDITOR?

3. For Scenario 2, does SQL Server do an implicit conversion of '01/04/2003 00:00:00' the ONCE or does it convert this value for each row returned from table CREDITOR?

Note: Have already used the Trace in SQL Query Analyser... and I'm getting inconsistent CPU and DURATION times when executing each of these querys after clearing the CACHE before running each one."

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-18 : 09:18:02

Would expect 2: to be faster but would prefer
SELECT * from creditor
WHERE Updated between '20030401' AND
'20030402'

It should do the convertion once and has a chance of using an index on Updated. Doing the convert on Updated would mean it has to scan.

Using yyyymmdd for chanracter dates is unambiguous and you won't get caught out by any user setting for date format.
Note that yyyy-mm-dd can also cause problems (surprisingly).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mfemenel
Professor Frink

1421 Posts

Posted - 2003-04-18 : 09:34:10

To build on what NR said, I'd agree that #2 should be faster. Anytime you're making a function call, there's extra overhead. You normall won't realize this unless you're running a statment that impacts a lot of rows, but if you're concerned about shaving off fractions of a millisecond, anywhere you can cut out a function call is a performance savings. For some things it's worth the overhead for the ease of using the function and repeating the same logic over and over again, but that's your call what's more important.

Mike
"oh, that monkey is going to pay"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-18 : 10:30:20

Never code a WHERE clause in this format:

WHERE SomeFunction(column) = SomeValue

That will not allow an index to be used on that column, because the function must be applied to all rows. Of course, you may really need to do this sometimes, but try to avoid it if possible.

Example:

WHERE Left(Column,2) = 'AB'

vs.

WHERE Column like 'AB%'

The second should be faster (the optimizer may be smart enough to know that some functions, like LEFT(), might be able to make use of an index but I am not sure about that -- i would tend to doubt it).

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources