Hello,
I'm trying to test and rewrite an SP PROC ( to reduce run time if possible) using temp table and insert data from a very big table and has couple of joins to other tables to get desired data .. here are some questions
1# how can I ensure my join order is correct. As of now it has Inner joins - Inner join - Inner join - Left join - Left join. I wanted to know if changing the join order would impact performance ( For testing purpose)
Can I just switch the order of join statements if I have to change join order...will that impact result? I will test however was wondering. Thanks in advice.
2#I'm using the temp table in SP and it runs fine when param are passed. However, it only shows 387 records as result and doesn't show any actual result data...why is that? Do I have to do a select from #temp table before dropping it in the SP to see those records in result?
3#Is it better to use CTE or TEMP table when you have a very big table which already has 12 indexes so creating a new one is not an option since too many already present ....when I ran the CTE and TEMP table version of SP they both run in the same time so now confused which one to pick?
4#The execution plan of the SP proc shows it needs an IDX ( It is doing a index scan instead of seek) and big difference between actual and estimated rows based on 2 parameters passed to the SP e.g. BETWEEN processtartdate and processenddate ( both us DATETIME) and ...is my only option to create that missing idx ..
5#The current execution time of SP is not much i.e 2 min. However since it is showing Idx scan in the actual plan I'm wondering if rewriting generic select with temp or cte..it can save some more seconds..not sure so trying to test various scenarios.
6#Should I create a primary key on the temp table? it might logically structure the data to some extent and cost of the scan might reduce..?
7#When there are no helpful comments in the code to understand why something is used...like why multiple joins. What are some tips here. Thanks!
I'll try to post the schema or plan however, would appreciate guidance on above questions. Please share your thoughts.
Regards!
Answers without samples
#1: joinorder doesn't matter. SQL Optimizer permutates join order in order to get the lowest cost plan. You can switch the join order in your statement. Beware Left join is different than inner join
#2: Try SET NOCOUNT ON. Does your stored procedure actually returns output (SELECT) or not (INSERT, DELETE,UDPATE)
#3: Usually temptables are faster because they can get cardinality estimates so the optimizer has a clue how many rows it contains.
CTE: are fine for a few records or when not used in another join. ( No cardinality estimates)
#4: A scan doesn't necessarily mean a bad query plan. If you need 50 percent of the data, the scan will be faster. Index have x levels to traverse before getting to the actual data. Try the query with or without the index and compare. The missing index candidate can present the wrong column order. Double check
5# Test. Creating a temptable as has an overhead because the data will be stored in tempdb. If you will join the data later on it's often beneficial.
6# Please do create a (clustered) index / primary key on your temptables
7# Try deconstructing it. What does removing/adding the extra join do?
There a lot of helpfull articles on temptables vs tablevariables, parameter sniffing (as you work with variables) on the internet. Haven't the time right now to post some
April 29, 2022 at 10:55 pm
I imagine if your statistics are up to date you will not have to consider the join order as the optimiser will define that. There may be times where it may make your query faster to force a join order with OPTION (FORCE ORDER). Such may be when a table is constrained by a inner join to the table before it. But I dont hear much about this being used in the forums.
----------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply