Well, I really have read so many articles, used different techniques and done some random things to do this.
My problem was that a have big tables (over one million rows) and some small (with few hundreds rows) - we are talking about 8 inner joins involved.
What reduces the execution time from over 2 minutes to 30 seconds was very strange for me and I was not able to find why this happens.
When I select columns from the tables I cast them. What I did was to cast the column to the most small possible type.
nvarchar(50 or 25)
The result was over 1 minute and 30 second better execution time. Why this happen?
For example if my variable is string with length 10 the
nvarchar(4000) and the
nvarchar(50) will cast it to
nvarchar(10) (or something close to that). So, why when I reduce the outcome type from cast the things go better?
Something more - I make a lot of test to check which function is better - cast or convert (do test for string to string, string to number, number to string) but was not able to define which works better. Sometime the convert gives me few seconds better execution time, but not enough to make a conclusion. Has anyone do something like this and succeeded in?
Thank for your time in advance.