Query Performance

• Grant Fritchey

  SSC Guru

  Points: 398886

  More actions

  October 6, 2011 at 12:14 pm

  #1391377

  chandan_jha18 (10/6/2011)

  ---

  Grant Fritchey (10/5/2011)

  ---

  paul_ramster (10/5/2011)

  ---

  Your outer query has the WHERE clause:

  where PostedDate='1/19/2011'

  and PostedDate is defined in the view as:

  CAST(CAST(SUBSTRING(WH_Status.StatusHistory,CHARINDEX('Batch posted successfully',CONVERT(VARCHAR(MAX),WH_Status.StatusHistory))-35,26) AS DATE) AS DATETIME) AS PostedDate

  You will always get a scan on the WH_Status table unless you can filter on an indexed field.

  Yeah, there are functions all over the place leading to scans. Data types are there for you to use. Not using them will cost you.

  Can you please explain me what advice you gave me here.

  You are comparing all sorts of disparate data types. This leads to conversion functions to get them to match up. A function on a column, any function on a column, means that any indexes that might be useful, can't be used because the data has become non-deterministic, meaning, it can't be known except by running the function. So that leads to scans. When comparing data in a JOIN or filtering data in a WHERE clause, you can't use functions on those columns. Every place you have one will lead to a scan.

  Now, you said you you changed a couple of data types so now you're comparing the same thing to the same thing. No functions or still functions? If still functions see above. If not, what does the execution plan look like now?

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• chandan_jha18

  SSChampion

  Points: 11330

  More actions

  October 6, 2011 at 12:21 pm

  #1391388

  Grant Fritchey (10/6/2011)

  ---

  chandan_jha18 (10/6/2011)

  ---

  Grant Fritchey (10/5/2011)

  ---

  paul_ramster (10/5/2011)

  ---

  Your outer query has the WHERE clause:

  where PostedDate='1/19/2011'

  and PostedDate is defined in the view as:

  CAST(CAST(SUBSTRING(WH_Status.StatusHistory,CHARINDEX('Batch posted successfully',CONVERT(VARCHAR(MAX),WH_Status.StatusHistory))-35,26) AS DATE) AS DATETIME) AS PostedDate

  You will always get a scan on the WH_Status table unless you can filter on an indexed field.

  Yeah, there are functions all over the place leading to scans. Data types are there for you to use. Not using them will cost you.

  Can you please explain me what advice you gave me here.

  You are comparing all sorts of disparate data types. This leads to conversion functions to get them to match up. A function on a column, any function on a column, means that any indexes that might be useful, can't be used because the data has become non-deterministic, meaning, it can't be known except by running the function. So that leads to scans. When comparing data in a JOIN or filtering data in a WHERE clause, you can't use functions on those columns. Every place you have one will lead to a scan.

  Now, you said you you changed a couple of data types so now you're comparing the same thing to the same thing. No functions or still functions? If still functions see above. If not, what does the execution plan look like now?

  I got rid of cast functions for two columns for which you mentioned earlier. We were comparing int to varchar so i changed the data type so that we can convert varchar to varchar.

  Now , I am left posteddate which is being calculated as:

  CAST(CAST(SUBSTRING(WH_Status.StatusHistory,CHARINDEX('Batch posted successfully',CONVERT(VARCHAR(MAX),WH_Status.StatusHistory))-35,26) AS DATE) AS DATETIME) AS PostedDate

  This leads to massive scan on WH_Status. What do you think can be done here?

  Thanks

  Chandan

• Grant Fritchey

  SSC Guru

  Points: 398886

  More actions

  October 6, 2011 at 12:34 pm

  #1391405

  chandan_jha18 (10/6/2011)

  ---

  Grant Fritchey (10/6/2011)

  ---

  chandan_jha18 (10/6/2011)

  ---

  Grant Fritchey (10/5/2011)

  ---

  paul_ramster (10/5/2011)

  ---

  Your outer query has the WHERE clause:

  where PostedDate='1/19/2011'

  and PostedDate is defined in the view as:

  CAST(CAST(SUBSTRING(WH_Status.StatusHistory,CHARINDEX('Batch posted successfully',CONVERT(VARCHAR(MAX),WH_Status.StatusHistory))-35,26) AS DATE) AS DATETIME) AS PostedDate

  You will always get a scan on the WH_Status table unless you can filter on an indexed field.

  Yeah, there are functions all over the place leading to scans. Data types are there for you to use. Not using them will cost you.

  Can you please explain me what advice you gave me here.

  You are comparing all sorts of disparate data types. This leads to conversion functions to get them to match up. A function on a column, any function on a column, means that any indexes that might be useful, can't be used because the data has become non-deterministic, meaning, it can't be known except by running the function. So that leads to scans. When comparing data in a JOIN or filtering data in a WHERE clause, you can't use functions on those columns. Every place you have one will lead to a scan.

  Now, you said you you changed a couple of data types so now you're comparing the same thing to the same thing. No functions or still functions? If still functions see above. If not, what does the execution plan look like now?

  I got rid of cast functions for two columns for which you mentioned earlier. We were comparing int to varchar so i changed the data type so that we can convert varchar to varchar.

  Now , I am left posteddate which is being calculated as:

  CAST(CAST(SUBSTRING(WH_Status.StatusHistory,CHARINDEX('Batch posted successfully',CONVERT(VARCHAR(MAX),WH_Status.StatusHistory))-35,26) AS DATE) AS DATETIME) AS PostedDate

  This leads to massive scan on WH_Status. What do you think can be done here?

  Thanks

  Chandan

  What I said earlier. You've got a date embedded in the middle of a bunch of text. You can't index it and any query against it is going to scan. So you have to change the structure somehow. Create a second column, populate that using your function, and then index that column (but be wary of key lookup operations, although I think that will be better than where you are currently).

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning