question

sweta.dutta7 avatar image
sweta.dutta7 asked

Indexing tips

Hi All, I need your ideas and tips to performance tune a massively expensive query. How do I get around attaching the xml file over here please ? [Link to Execution plan (rename as .sqlplan)][1] [1]: /storage/temp/1633-sweta.dutta.sqlplan
tsqlperformanceindexingexecution-planquery-tuning
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Added the execution plan to the question for you
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
A brief look at the execution plan, and I see 2 things: 1. Calls to a Scalar UDF to calculate the VAT rate, for every row 2. 2 Scans of a temp table #temp_gl that return 739 million rows, each! I'd address the massive scans first, and then the scalar UDF
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Don't worry about the rename comment - that's more for people who want to download your exec plan! The index on assoc_mem_transaction_id is being ignored as you have an isnull() around the column, effectively negating the use of the index : http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/12/18/conor-vs-more-sargable-predicates.aspx If you have control over what data goes into that temp table, then maybe don't allow NULLs - that way you don't have to check later. Try re-working the query to use a join instead of the AND NOT EXISTS (SELECT 1 from #temp_gl ...) You may (or may not) get some different results : http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
1 Like 1 ·
Thanks Kev, I am trying to rename the file but excuse my ignorance, dont really know how to :-( The #temp_gl file has 1.3 M rows and I have tried putting in an index on assoc_mem_transaction_id but that also doesn't seem to help much. Any suggestions on how to find out which col to be indexed on ? sorry if that sounds lame...
0 Likes 0 ·
Possibly also look at indexing `member_account_id` on `member_account`
0 Likes 0 ·
Thanks Kev, appreciate your patience and help here. I had tried using INNER JOIN and yes, the results were different :-( NULLs in assoc_mem_transaction_id is something that can't be avoided due to the nature of the data. Though yes, I will try and have a look into ways of avoiding NULLs but really sceptical here.
0 Likes 0 ·
The NOT EXISTS would have to be replaced by a LEFT OUTER JOIN, not an INNER JOIN. Follow the link to Gail Shaw's article and it will demonstrate how you can rewrite NOT EXISTS as a LEFT OUTER JOIN. [Sorry when I said results I didn't mean the results of the query, I meant the performance results]
0 Likes 0 ·
Show more comments
sweta.dutta7 avatar image
sweta.dutta7 answered
Which one is better please ? convert(date,od.transaction_datetime) between @startDate and @endDate OR AND od.transaction_datetime >= @startDate AND od.transaction_datetime <= dateadd(day,1,@enddate)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The later. The first one has a convert-function-call on the od.transaction_datetime column and therefore possibly an index scan rather than an index seek will be performed. When it comes to comparing BETWEEN and dt >= a AND dt
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.