| Author |
Topic |
|
KazaX
Starting Member
28 Posts |
 Posted - 2008-11-12 : 17:36:57
|
| Hi folks!have idea?How can one estimate i/o required for a naive join and a block nested loop join if we have as followsselect r.a, s.c from r,s where r.b=s.br has 10^6 records and occupies 100mbs has 10^5 " " 200mb and cache holds 2 blocksand blocks read from disk 50kband which outer loop - on R or S - is most efficient? cheers |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-11-13 : 05:12:21
|
And stop using that syntax. It won't keep working forever!Do, select r.a , s.cfrom r INNER JOIN s ON s.b = r.b Instead.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
KazaX
Starting Member
28 Posts |
Posted - 2008-11-14 : 14:01:29
|
quote:
Originally posted by Transact Charlie
And stop using that syntax. It won't keep working forever!Do, select r.a , s.cfrom r INNER JOIN s ON s.b = r.b Instead.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
thats good but writing that all staff, "inner join ON s.b" .... just "=" saves u time and effort :) |
|
|
|
KazaX
Starting Member
28 Posts |
Posted - 2008-11-14 : 14:08:21
|
quote:
Originally posted by tkizer
Just view the execution plan and the statistics to determine the costs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Yeah, that s an idea :) but not effective. You got to be PROACTIVE. MAYBE execution will take up forever, what a gonna da :) ? If you know beforehand that execution never ends why to run it ?! As an expert how can one evaluate with figures that he has as knowledge beforehand and estimate i/o required, like # of records, cache etc. There must be a way. Knowledge is power, can anyone bear original ideas? |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-14 : 14:08:44
|
| No Kasa, charlie is right, that the ANSI Standard Joining Query, and one thing more that though "=" saves ur time while righting code but every time this query will run, Sql Server have to interpret it and placing INNER JOIN against your "=" sign, So Charlie Query is ANSI standard and Fast too. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-14 : 14:13:13
|
| There is an estimated query plan in management studio that can give you some information about a query based on the server statistics. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
KazaX
Starting Member
28 Posts |
Posted - 2008-11-17 : 05:52:10
|
quote:
Originally posted by tkizer
quote:
Originally posted by KazaX
quote:
Originally posted by tkizer
Just view the execution plan and the statistics to determine the costs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Yeah, that s an idea :) but not effective. You got to be PROACTIVE. MAYBE execution will take up forever, what a gonna da :) ? If you know beforehand that execution never ends why to run it ?! As an expert how can one evaluate with figures that he has as knowledge beforehand and estimate i/o required, like # of records, cache etc. There must be a way. Knowledge is power, can anyone bear original ideas?
I can't figure out what you are asking.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Hello, thereThx 4 reply..The thing is I do not have million records in none of tables test and see the costs. I am wondering how Do I estimate if I am asked given that information? That is all. Cheers |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-17 : 06:23:33
|
Insert millions of dummy records to see which plan you get.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
efficiency