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 |
|
TheMilkMan
Starting Member
10 Posts |
 Posted - 2002-05-01 : 06:37:26
|
| Hi Guys,Would like some opinions on what is best.Multiple querys to do multiple tasks.Or one big query to do all jobs tasks in one go.The reason i ask is in access its best to do most calculations on the last query, for speed i guess.thanks for your input.// SlowlyGetting There // |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-05-01 : 20:08:34
|
| Access is quite a different kettle of fish, because by-and-large the optimisation of your queries is up to you. SQL Server is much smarter, however, you can still check the query execution plan to see what impact your query will have.ie you should be able to answer your question yourself by trying a couple of different querys and checking the execution plans. It's going to depend a lot on what exactly you are doing, my guess is that it's going to depend more on the way you organise it, than whether you do it all in one go or not.A lot of people out there know more than I do though....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
TheMilkMan
Starting Member
10 Posts |
Posted - 2002-05-02 : 05:10:31
|
| thank you for your thoughts, your right, i have tried and tested it and sql is good, the best, but because it was fast, i didnt know if it mattered or not. I did it in access and so slow and a pain.thank you again.jd// SlowlyGetting There // |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-05-02 : 19:16:47
|
| Yep, and SQL is also much smarter with caching. An indictative technique is just to do the following:declare @startdate datetimeset @startdate = getdate()--Run your code hereselect datediff(ms, @startdate, getdate())That tells you how long it took to do your code. Of course it'll be affected by load, but along with the execution plan, it should give you a realistic idea of which query organisation is better.Gerneally, however, as you already know, any filter which reduces the number of records involved should go first, making subsequent queries quicker, however the main difference between Access and SQL that I've noticed is here:select project from timesheets where project in (select proj from myprojects)Access is very slow at this kind of thing, but SQL optimizes this for you.HTH--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
Multi query or One query