Timeline for Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow
Current License: CC BY-SA 4.0
27 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 8, 2023 at 14:05 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 6, 2023 at 15:04 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 8, 2023 at 15:04 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Oct 28, 2022 at 23:03 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jun 24, 2022 at 21:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Feb 21, 2022 at 19:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Oct 21, 2021 at 9:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Sep 14, 2021 at 21:51 | answer | added | Graham | timeline score: 1 | |
| Jul 3, 2021 at 13:08 | comment | added | ck123 | I updated my post with some more details and sample data. | |
| Jul 3, 2021 at 5:27 | history | edited | ck123 | CC BY-SA 4.0 |
added 2 characters in body
|
| Jul 3, 2021 at 5:06 | history | edited | ck123 | CC BY-SA 4.0 |
added 5826 characters in body
|
| Jul 2, 2021 at 1:29 | comment | added | ck123 | I'll update my post with more details (I should have a lot of time this weekend). But just wanted to add, I think String_Agg is the way to go (thanks @HandyD). In about 2 seconds it returns data (when I select ALL genres, media, and title types). In about 4 seconds, cross apply does the same. I cleared data cache in between each run. I have to play around with it a little more. FYI, I did an upgrade of visual studio and I'm not sure if I'm imaging it, but my queries seemed to be overall faster. Want to do some more validation before posting what I've come up with. Thanks for all the help. | |
| Jun 30, 2021 at 17:33 | comment | added | Hannah Vernon♦ | It would be quite helpful if you could provide a minimal reproducible example with the relevant table structure since that would allow us to easily repro the issue and provide the best possible answer. | |
| Jun 30, 2021 at 3:01 | history | tweeted | twitter.com/StackDBAs/status/1410070848069853187 | ||
| Jun 29, 2021 at 15:57 | comment | added | ck123 | @SMor, I’ll take some more time to review your response later this evening. Pre-aggregating in a table is something that I considered, so that may make some sense. Thank you. | |
| Jun 29, 2021 at 14:11 | comment | added | SMor | And since movie genres will not be changing generally, you might consider the benefit of simply pre-aggregating those values into a table. That is a small denormalization that might be worth the cost of space and a little processor work during DML. In addition, you over-complicate by trying to do too much in too many places. Suggest your view simply sticks to the aggregation of genres ONLY - allowing it to be used anywhere that wants a single row per movie with all related genres in a single column. | |
| Jun 29, 2021 at 14:04 | comment | added | SMor | The posted links demonstrate exactly how to do that and also discuss the problems (and solutions) to the poor-performance aspect. And consider what actually happens (vs. what is desired) when at least one of the search parameters for your procedure contains nothing. | |
| Jun 29, 2021 at 12:30 | comment | added | ck123 | @SMor, I made a bad update on the fly before I submitted this question. Those IDs should all be Nvarchar. I want to have the ability for multi select, I want to be able to convert a string of multiple values (“1,2,3,4”) into individual values to check against the values in my genre, title type and media tables. The split function is being used to split the string into multiple int values. Also, I used distinct because in my JoinedGenres view, I need to I return a genreID in addition to a movieID, which means that for every genre the movie is repeated, so I specify distinct in the Procedure. | |
| Jun 29, 2021 at 12:29 | comment | added | ck123 | @J.D., I’ll update with an execution plan later today. I won’t have computer access fit most of the day. | |
| Jun 29, 2021 at 12:29 | comment | added | ck123 | @HandyD, not sure if there’s a better way to do this in SSRS. I will take a look into string_agg. | |
| Jun 29, 2021 at 12:13 | history | edited | ck123 | CC BY-SA 4.0 |
added 18 characters in body
|
| Jun 29, 2021 at 11:24 | comment | added | SMor |
[fn_SplitInt](@GenreID, ',') No idea what this does nor how you expect it to be useful. The datatype of that parameter (like all the others) is tinyint so I fail to see how it could be "split". Use of DISTINCT is questionable. But you seem to have a kitchen-sink report coupled with a need to pass a list as a parameter.
|
|
| Jun 29, 2021 at 3:31 | comment | added | J.D. | Could you please update your post to include some information on your current runtimes, and a link to the actual execution plan? (You can use Paste The Plan to upload it.) | |
| Jun 29, 2021 at 2:46 | comment | added | HandyD | What version of SQL Server? if 2017 or greater, you can use STRING_AGG. Otherwise, is this something you can handle in the reporting tool, rather than doing string manipulation in SQL, which is always prone to performance problems? | |
| Jun 29, 2021 at 2:33 | history | edited | Hannah Vernon♦ | CC BY-SA 4.0 |
formatting and dbms tag
|
| Jun 29, 2021 at 1:06 | review | First posts | |||
| Jun 29, 2021 at 14:07 | |||||
| Jun 29, 2021 at 1:03 | history | asked | ck123 | CC BY-SA 4.0 |