What does "cost" mean?

[BillYater]

I am examining the execution plan for several different SQL queries to find the best one for optimizecd performance. My test queries return 70 records out of a 38,000 record table.

Query A is the fastest and has a cost of 0.584
Query B takes 4 times as long and has a cost of 0.150
Query C is the slowest, but has the lowest cost, 0.0424.

What does this cost mean? I've looked in SQL Server Books Online, and it does not provide a definition of the signifigance of this number.

More important for my purposes, and does it's inverse proportion to the total time of my test queries mean that query C will be faster against large datasets? I can deal with slow performance against a 38000 record table, but I need fast performance against a 250,000 row table.

 

[hmckillop]

You've opened a can of worms now - best place to read is at

http://www.sql-server-performance.com/jc_sql_server_quantative_analysis1.asp

the guy Joe chang knows his stuff.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[mrdenny]

Cost is a total of two numbers. Amount of CPU time needed to execute the query, and amount of disk time needed to execute the query.

When you look at the execution plan, put your mouse over the objects shown. Many of them will show the actual cost for each one. Idealy you will want to have low cost on the disk, and low cost on the CPU. If you do this on your 3 queries, you should see that the costs are comming from different places based on which indexes are being used, where full table scans are being done, etc.

The best way to test how everything will respond against a table with 250,000 records, is to load the table(s) up with the rows and start running queries.

The other thing you are going to want to consider is when is this query going to be used. Is it going to be used by a person, or only by the system. If it is going to be used by a system, then length of time to run isn't very important. If it is going to be used by a person, you want to keep the execution time as short as possible. The longer queries take for a person to run, the more that query is costing the company in $$$.

One of my instructors from a SQL class said that he had read in a programming article that if a task in an application takes more than 3 seconds to complete the person using the application has already forgotten about what they are doing and has started day-dreaming. This then leads to costing additional time while the person finishs day-dreaming and comes back to the task at hand.

I hope that my rambling has helped.

Denny

--Anything is possible. All it takes is a little research. (Me)