I have two ways to get the result from sql server
first way
select * from my_view where key='key'
second way
select * from my_function('key')
the two ways do the same thing. actually the size of the database is low so the execution time is too low ,and i can't tell witch one take less time, but I m not sure that they still be the same if the data base come to grow
So my question is: Is there any way or free tool to compare the exact execution time of the two queries?
key='key' will be always faster or the same. Never use function on column name (if applicable, use function on the value in the condition eg.: key=function('key')). If you do, SQL Server is not able to use any optimizations on the query (eg. indexes will be ignored).
To ensure query will keep its performance level as your table grows, you will have to set an index on the column.
You can deduce performance from Execution Plan in SQL Server Management Studio (when you execute the query with Include Actual Execution Plan enabled - Ctrl+M). You can also enable Include Statistics (Ctr+Alt+S) to see some performance info.
If you run multiple queries in a batch (one query window) and enable "Include Actual Execution Plan", it'll give each query a "relative cost to the batch".
So if you had two in your batch, if one is < 50% then it could be faster than the other.
Edit: Changed to "could" per Grant's comment.
Here is my simple way to evaluate the execution performance of your queries:
First is execute this:
select * from my_view where key='key'
Then review it in your SSMS Properties > Elapsed Time
Do it the same way here:
select * from my_function('key')
You can visually evaluate each of them, particularly if you need to see which is quicker to execute in more precise details.
In SQL Server Management Studio, if you put both statements into a query window, you can press CTRL-L (for Display Estimate Query Plan). This will give you an estimated cost for each query in respect to the others in the batch. In the example below, you can see that my first query had less cost compared to the 2nd one.
If you press CTRL-M (for Display Actual Execution Plan) you'll get the same but with actual execution statistics. There are always exceptions, but SQL is super-smart and Estimated is accurate.
Query 1: Query cost (relative to the batch): 25%
Query 2: Query cost (relative to the batch): 75%
There are many factors to consider when comparing and optimizing queries, this is just one easy way to get an idea. It's also easy to understand as you only have to compare two numbers in relation to each other.
Using your example as an example: Both of your queries could be doing a table scan against a calculated field with no index on a remote server. Sure, they may both come back 50%/50% (meaning they have the same cost) but they're still going to be inefficient.
And reminder that "Cost" does not actually mean "quicker". You can have very inefficient query that runs "quick" because it only returns 10 records in 1-min verses a highly optimized query that returns a million rows of data but takes 10-min to run.
