March 10, 2021 at 6:29 pm
Morning,
I have a SP that I created that does select query, using a date parameter. Because of a few joins the query takes a little long to run. and present in an app.
What I thought of doing is changing the SP to a Insert select, and pre-fill a table with results needed, and Instead of passing the one date parameter each time, I could get it to loop through the whole week.
any thoughts on how I could apply such a parameter in the SP ?
thanks in advance.
Greg
March 10, 2021 at 8:25 pm
I think it really depends on the requirements, the source data, and the expected output.
What I mean is if you are asking for 1 date parameter and then it uses that as a "start date" and just keeps adding 1 day to it until you are at 7, I think a loop is going to be a slow method. Possible, but probably not efficient.
Since your original query is giving the result you want, but is slow, I would recommend posting the execution plan (anonymized if it contains confidential data), DDL, sample input, sample output, and I bet someone on here can help. MAY be that your statistics are out of date so it is getting bad memory grants and spilling to tempdb (for example), or you may not have the correct indexes, or something else.
With SQL, the worst thing you can do from a performance standpoint is to re-do your currently working query using loops.
If you know that you are wanting data between 2 dates, >= and <= in your WHERE is going to be a safe and fairly efficient way to do it.
If a loop is the direction you are needing to go (direction from above), you will need a starting point for the loop and some way to know when to exit the loop. Based on what you said, I would guess the starting point is the input date, the loop step would be to increment the date by 1, and the exit point would be when the date is the end of the current week. After the loop, you would select everything from the table variable/temp table and then do cleanup (if you ended up using a cursor for example, clean it up. If you have a temp table, drop it when you are done. etc).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 11, 2021 at 2:07 pm
Thanks for the input. after your comments , I went and looked at the query executions' plan and based on that added an uncluttered index and Boom. that worked great.
Thanks again.
March 11, 2021 at 3:01 pm
Glad I could help!
Adding clustered indexes is never a bad idea (you can have 1 per table), as long as your clustered index is on the correct column(s). The ideal state is an ever increasing numeric column containing something relevant that queries will be looking for (such as serial number). Ever increasing is for INSERT performance - if SQL knows that each insert will go at the end, inserts don't need to scan through the entire table (worst case) to find where to put the row, plus you don't get as many page splits happening as when you need to insert in the middle of the table. Numeric as it is an easy data type to confirm that row A is less than row B. 10 < 100 for numeric data types, but 10 < 2 if that column is a VARCHAR. And relevant because having your data ordered by an arbitrary number (such as an ID) is rarely going to be useful for SELECTing the data. How often do you ask SQL "which row was inserted most recently?". That is why "relevant" data is important. Think of it like sorting your books on a bookshelf by their UPC. Sure, they are sorted, but it is not a useful ordering for the books.
Nonclustered indexes you need to be a bit careful of though. The reason being that they can hurt your insert, update, and delete performance (and in some special cases, SELECT performance too). The reason being that a nonclustered index is essentially a subset of your base table (or the entire thing) but ordered in a different manner than the base table to make searching it faster. The nonclustered index also has a pointer back to the table (heap or clustered index). So if you have 1 nonclustered index on your table, your insert now needs to go into 2 objects (the table and the index). If you have 100 nonclustered indexes, a single insert needs to touch all 100 of those nonclustered indexes plus the table.
Index tuning is a fine balance to have just enough indexes and not too many plus to have the correct indexes so you don't end up with unused indexes. Sometimes it makes sense to add a new index, sometimes it makes sense to modify an existing one to order it by an additional column to help your query.
So when adding indexes, it is always good to watch for new performance issues that came up as your new index MAY help 1 query while hurting 1000 others.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply