Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-08 : 10:16:08
|
| writes "I am having problems with the performance of SQL-Server 7.0. Most of my problems occur when trying to execute queries with several inner joins and aggregate functions. I have tried indexing the fields that are involved in the joins and aggregate functions, and I noticed a better improvement. However, some queries are still running slow. Below is an example of one of my queries. Thanks for any advice.SELECT Cash.location, location.Auction, location.LocationName, location.state,Cash.type, Cash.datestamp, Cash.WtCode, WtCode.WeightRange, Sum(Cash.NumofHead)AS SumOfNumofHead, Sum(Cash.[NumofHead]*[Price]) AS totalprice, Sum(Cash.price) AS weightedprice INTO CashQTable FROM location INNER JOIN (WtCode INNER JOIN Cash ON WtCode.WtCode = Cash.WtCode) ON location.ID = Cash.location GROUP BY Cash.location, location.Auction, location.LocationName,location.state, Cash.type, Cash.datestamp, Cash.WtCode, WtCode.WeightRange" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-01-08 : 12:06:50
|
| performance is a function of power, effort and technique....where power is the speed of your servereffort is the number of records it must traverse to do the joband technique is the method you use to perform a job....so far you've only said you've a problem, without saying anything about any of the 3 items above....ie how powerful your server is, how many records being you have in your database or which fields you have indexes on.but to get you looking in the right area....if you execute the query in Query Analyser and prior to executinon, press Control-K....then F5 (to execute)....and look at the execution plan in the sub-window at the bottom....this will show you where your query is spending it's time....(or an estimate of where it will spend it's time to be more accurate!).items which are bad(poor)...are table scans....and items that are good(better) are index scans...use this tool to find which sub-parts of your query would benefit from indexes.also search this site for indexes and/or perforamance....this topic has come up before. |
 |
|
|
|
|
|
|
|
Slow Performance Using Inner Joins and Aggregate Functions