May 30, 2012 at 9:09 am
Type conversion in expression (CONVERT(varchar(20),[p].[InputDate],0)) may affect "CardinalityEstimate" in query plan choice.
My query works great, < one second, without Totalcount.
Why does adding Totalcount add another five seconds to the query, and is it possible to improve the totalcount perfomance, without slowing down the main query?
Will fixing the Cardinality Issue, provide much of a performance boost?
Thanks
May 30, 2012 at 2:57 pm
I can't see the attachment (security issues).
But in general you want to avoid using functions on columns, because it keeps SQL from using indexes (and likely stats) on that column.
For example, to check for one day, instead of using:
(CONVERT(varchar(20),[p].[InputDate],0))
Use this:
InputDate >= '<date>' AND
InputDate < '<date>plus one day'
For example:
InputDate >= '20120529' AND
InputDate < '20120530'
to get everything for May 29th.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 30, 2012 at 3:39 pm
Thanks for your help !
May 30, 2012 at 5:46 pm
isuckatsql (5/30/2012)
Type conversion in expression (CONVERT(varchar(20),[p].[InputDate],0)) may affect "CardinalityEstimate" in query plan choice.
The warning is probably not too important in this specific case, but you should try to specify date/time literals using an international unambiguous format like '2011-12-02T10:52:00' instead of 'Dec 2 2011 10:52AM'. Also, instead of using CAST inside the CTE, move the conversion to the final SELECT (specifying columns instead of 'SELECT *') and use CONVERT with a specific style instead of CAST.
Why does adding Totalcount add another five seconds to the query, and is it possible to improve the totalcount perfomance, without slowing down the main query?
This is a separate query, not the one with the warning. As you can see from the execution plan, the Total Count query accesses a very large number of rows, performs many clustered index scans, and 9 million rows from a full-text index. Your server also appears to have MAXDOP set to one, so this expensive query cannot use parallelism.
June 4, 2012 at 3:42 pm
SQL Kiwi (5/30/2012)
...you should try to specify date/time literals using an international unambiguous format like '2011-12-02 10:52:00' instead of 'Dec 2 2011 10:52AM'.
I recently saw this on another post on SSC. While YYYY-MM-DD is proper international standard date notation is still ambiguous in SQL Server for some configurations. Apparently YYYYMMDD is a safer way to represent dates:
-- my default setting
SET DATEFORMAT MDY;
-- OK
SELECT CAST('2011-12-02 10:52:00' AS DATETIME) AS [OK],
CAST('2011-12-02' AS DATE) AS [OK],
CAST('20111202 10:52:00' AS DATETIME) AS [OK],
CAST('20111202' AS DATE) AS [OK];
-- change dateformat
SET DATEFORMAT DMY;
SELECT CAST('2011-12-02 10:52:00' AS DATETIME) AS [NOT OK],
CAST('2011-12-02' AS DATE) AS [OK],
CAST('20111202 10:52:00' AS DATETIME) AS [OK],
CAST('20111202' AS DATE) AS [OK];
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 4, 2012 at 5:27 pm
opc.three (6/4/2012)
SQL Kiwi (5/30/2012)
...you should try to specify date/time literals using an international unambiguous format like '2011-12-02 10:52:00' instead of 'Dec 2 2011 10:52AM'.I recently saw this on another post on SSC. While YYYY-MM-DD is proper international standard date notation is still ambiguous in SQL Server for some configurations. Apparently YYYYMMDD is a safer way to represent dates:
I missed the 'T', which resolves your example. I meant to write '2011-12-02T10:52:00', which is the ISO 8601 standard. As Books Online says, "The advantage in using the ISO 8601 format is that it is an international standard. Date and time values that are specified by using this format are unambiguous. This format is not affected by the SET DATEFORMAT, SET LANGUAGE, of login default language settings."
As I mentioned, the various traps can be avoided by using CONVERT with a deterministic style parameter instead of CAST. This is sometimes required, for example in computed column definitions and indexed views:
--Msg 4936, Level 16, State 1, Line 1
--Computed column 'as_date' in table '@Dates'
--cannot be persisted because the column is non-deterministic.
DECLARE @Dates AS TABLE
(
date_string varchar(30) NULL,
as_date AS CAST(date_string AS datetime) PERSISTED
);
GO
-- OK
-- 121 is yyyy-mm-dd hh:mi:ss.mmm(24h)
-- Any deterministic style will do
DECLARE @Dates AS TABLE
(
date_string varchar(30) NULL,
as_date AS CONVERT(datetime, date_string, 121) PERSISTED
);
June 5, 2012 at 6:50 am
Thanks guys!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply