The one-developer-to-many-projects setting has really helped me make a lot of progress after only a year of experience. The company though wouldn't be teaching you the skills. They expect you to know the skills they hired you for.
In my first 7 months, I was in an individual project but in a team with a senior developer who wouldn't be teaching you the step-by-step but is kind enough to assist you in case you are having problem with something. Problem is, I was transferred in a diferent project in which department, I was the only developer. That means, no senior developer or whoever to assist me on the technical side.
So, dreamincode has always been my best friend and so are all the guys in this forum who are passionate with programming. I hope you guys can assist me in my journey.
My problem is a very common issue of "Performance".
I am trying to work on something. The idea is as easy as searching or filtering.
I have coded a lot of searches before but this one I am trying to do takes a lot of time.
I'll disguise the project I am working on by changing the type of data to a very simple example.
There are a lot of manga or anime nowadays from different authors and from different genres. Let's imagine there is a database table containing, the anime/manga title, the author, the year it was animated/published, and the genre.
Suppose our database table has millions of records.
Now user searches for a certain anime. He doesn't know the exact title he wants but decided to filter results by the indicating the name of his favorite author and selects the genres he would like to read.
If it is only one author and genre, then I can pass @authorname and @genre as parameters to my stored procedure and query
SELECT * FROM tblAnime WHERE Author=@authorname AND Genre=@genre
Now, how about if the user is allowed to select one author but multiple genres at once? Lets say, there are 50 available genres. The user can select some (26 for example) or even all 50 genres.
What I did is store the author and genres in a dataset, and passed it to the stored proceure as an xml, stored the xml to a temp table
SELECT * FROM tblAnime anm INNER JOIN #TEMP tmp ON anm.Author=tmp.Author AND anm.Genre=tmp.Genre
Problem is... it takes a lot of time. The first query runs for 8 seconds and the next for 33 seconds. Considering both only get 683 rows.
Is there anything I need to do to improve performance. Can you suggest a better approach? Maybe using XML is not a best approach at all.